psql 参数调整

最新推荐文章于 2024-07-16 10:40:44 发布

水滴石-_-

最新推荐文章于 2024-07-16 10:40:44 发布

阅读量1.6k

点赞数

分类专栏： postgresql 文章标签： postgresql

postgresql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

shared_buffers：

这是最重要的参数，postgresql通过shared_buffers和内核和磁盘打交道，因此应该尽量大，让更多的数据缓存在shared_buffers中。通常设置为实际RAM的10％是合理的，比如50000(400M)。将所有的内存都给 shared_buffers 将导致没有内存来运行程序。 unix 管理内存使用 swap, 当没有内存可用的时候，内核会将长时间不用的内存挪到 swap 去，这个叫：swap pageout，当你再次使用它的时候内核又会将其挪回来，这个叫：swap pagein，这个操作有很大的不好，因为它会导致你的程序挂起直到操作完成

 
 大的shared_buffers需要大的checkpoint_segments,同时需要申请更多的System V共享内存资源. 

 
 这个值不需要设的太大, 因为PostgreSQL还依赖操作系统的cache来提高读性能, 另外, 写操作频繁的数据库这个设太大反而会增加checkpoint压力 

work_mem:

在pgsql 8.0之前叫做sort_mem。postgresql在执行排序操作时，会根据work_mem的大小决定是否将一个大的结果集拆分为几个小的和 work_mem查不多大小的临时文件。显然拆分的结果是降低了排序的速度。因此增加work_mem有助于提高排序的速度。通常设置为实际RAM的2% -4%，根据需要排序结果集的大小而定，比如81920(80M)。work_mem是per connection and per sort的设定

举例来说，如果设定working memory为32MB，那么以下例子：
select * from lines, lineitems
where lines.lineid = lineitems.lineid
and lineid=6
order by baz;
这里就可能用到64MB的内存。
hashjoin between lines and lineitems (32MB)
order by baz (32MB)
要注意自己有多少query是用到了order by或者join
如果同时有100个链接，那么就是 100 connection X 64MB = 6400MB (6G) 内存

利用 explain analyze可以检查是否有足够的work_mem
sort (cost=0.02..0.03 rows=1 width=0) (actual time=2270.744..22588.341 rows=1000000 loops=1)
Sort Key: (generate_series(1, 1000000))
Sort Method: external merge Disk:13696kb
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.006..144.720 rows=1000000 loops=1)
Total runtime: 3009.218 ms
(5 rows)
以上的 query分析显示，这里需要从硬盘走13MB的东西。所以这个query应给set work_mem到16MB才能确保性能。

在postgres 8.3之后的版本，working mem可以在query中设定

参数名称	参数意义	优化思路
shared_buffers	数据库服务器将使用的共享内存缓冲区大小，该缓冲区为所有连接共用。从磁盘读入的数据（主要包括表和索引）都缓存在这里。	提高该值可以减少数据库的磁盘IO。
work_mem	声明内部排序和哈希操作可使用的工作内存大小。该内存是在开始使用临时磁盘文件之前使用的内存数目。数值以kB为单位的，缺省是 1024 (1MB)。请注意对于复杂的查询，可能会同时并发运行好几个排序或者哈希操作，每个都会使用这个参数声明的这么多内存，然后才会开始求助于临时文件。同样，好几个正在运行的会话可能会同时进行排序操作。因此使用的总内存可能是 work_mem 的好几倍。ORDER BY, DISTINCT 和mergejoin都要用到排序操作，而哈希操作在哈希连接、哈希聚集和以哈希为基础的 IN 子查询处理中都会用到。该参数是会话级参数。	执行排序操作时，会根据work_mem的大小决定是否将一个大的结果集拆分为几个小的和 work_mem差不多大小的临时文件写入外存。显然拆分的结果是导致了IO，降低了排序的速度。因此增加work_mem有助于提高排序的速度。通常设置时可以逐渐调大，知道数据库在排序的操作时不会有大量的写文件操作即可。该内存每个连接一份，当并发连接较多时候，该值不宜过大。
effective_cache_size	优化器假设一个查询可以使用的最大内存（包括pg使用的和操作系统缓存），和shared_buffer等内存无关，只是给优化器生成计划使用的一个假设值。	设置稍大，优化器更倾向使用索引扫描而不是顺序扫描，建议的设置为可用空闲内存的25%，这里的可用空闲内存指的是主机物理内存在运行pg时得空闲值。
maintenance_work_mem	这里定义的内存只是在CREATE INDEX, VACUUM等时用到，因此用到的频率不高，但是往往这些指令消耗比较多的资源，因此应该尽快让这些指令快速执行完毕。	在数据库导入数据后，执行建索引等操作时，可以调大，比如512M。
wal_buffers	日志缓冲区，日志缓冲区的大小。	两种情况下要酌情调大：1.单事务的数据修改量很大，产生的日志大于wal_buffers，为了避免多次IO，调大该值。 2.系统中并发小数据量修改的短事务较多，并且设置了commit_delay，此时wal_buffers需要容纳多个事务（commit_siblings个）的日志，调大该值避免多次IO。
commit_delay	事务提交后，日志写到wal_buffer上到wal_buffer写到磁盘的时间间隔。	如果并发的非只读事务数目较多，可以适当增加该值，使日志缓冲区一次刷盘可以刷出较多的事务，减少IO次数，提高性能。需要和commit_sibling配合使用。
commit_siblings	触发commit_delay等待的并发事务数，也就是系统的并发活跃事务数达到了该值事务才会等待commit_delay的时间才将日志刷盘，如果系统中并发活跃事务达不到该值，commit_delay将不起作用，防止在系统并发压力较小的情况下事务提交后空等其他事务。	应根据系统并发写的负载配置。例如统计出系统并发执行增删改操作的平均连接数，设置该值为该平均连接数。
fsync	设置为on时，日志缓冲区刷盘时，需要确认已经将其写入了磁盘，设置为off时，由操作系统调度磁盘写的操作，能更好利用缓存机制，提高IO性能。	该性能的提高是伴随了数据丢失的风险，当操作系统或主机崩溃时，不保证刷出的日志是否真正写入了磁盘。应依据操作系统和主机的稳定性来配置。
autovacuum	是否开启自动清理进程（如开启需要同时设置参数stats_start_collector = on，stats_row_level = on，），整理数据文件碎片，更新统计信息。	如果系统中有大量的增删改操作，建议打开自动清理进程，这样一方面可以增加数据文件的物理连续性，减少磁盘的随机IO，一方面可以随时更新数据库的统计信息，使优化器可以选择最优的查询计划得到最好的查询性能。如果系统中只有只读的事务，那么关闭自动清理进程。
autovacuum_naptime	自动清理进程执行清理分析的时间间隔	应该根据数据库的单位时间更新量来决定该值，一般来说单位时间的更新量越大该时间间隔应该设置越短。由于自动清理对系统的开销较大，该值应该谨慎配置（不要过小）。
bgwriter_delay	后台写进程的自动执行时间	后台写进程的作用是将shared_buffer里的脏页面写回到磁盘，减少checkpoint的压力，如果系统数据修改的压力一直很大，建议将该时间间隔设置小一些，以免积累的大量的脏页面到checkpoint，使checkpoint时间过长（checkpoint期间系统响应速度较慢）。
bgwriter_lru_maxpages	后台写进程一次写出的脏页面数	依据系统单位时间数据的增删改量来修改
bgwriter_lru_multiplier	后台写进程根据最近服务进程需要的buffer数量乘上这个比率估算出下次服务进程需要的buffer数量，在使用后台写进程写回脏页面，使缓冲区能使用的干净页面达到这个估计值。	依据系统单位时间数据的增删改量来修改。