网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!
参数名称 | 参数意义 | 优化思路 |
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数量,在使用后台写进程写回脏页面,使缓冲区能使用的干净页面达到这个估计值。 | 依据系统单位时间数据的增删改量来修改。 |
2。 tpcc/压力测试时pg常调参数示例:
max_connections = 200
#根据数据量尽量调大shared_buffer值,把所有数据都放到内存中更好,
#曾经在32G内存的服务器上把shared_buffert调到了26G
#wal_buffers根据产生的wal日志量也适当设大点
shared_buffers=1200MB
wal_buffers = 2000kB
#work_mem要适可而止,每个连接都要用这么大的
work_mem = 1024kB
#一般做做检查点的时间长于压力测试的时间,这样性能数据会更好,等压力测试完了再去做检查点吧。
Checkpoint_timeout=120min
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 75
full_page_writes = off
log_min_messages = fatal
#压力测试时由于高并发等锁的时间可以长一些
deadlock_timeout = 3s
网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!
可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!**