devbox
繁依Fanyi0
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

进阶数据库系列（二十三）：PostgreSQL 性能优化_postgresql 性能优化宝典

作者：繁依Fanyi0 | 2024-04-11 16:23:37

赞

踩

postgresql 性能优化宝典

前面介绍了 PostgreSQL基于 Patroni 高可用架构部署及故障切换、 基于 repmgr 高可用架构实践、基于 pgpool 实现读写分离实践、数据库备份与恢复、主从数据目录同步工具 pg_rewind、数据库作业调度工具等相关的知识点，今天我将详细的为大家介绍 PostgreSQL 性能优化相关知识，希望大家能够从中收获多多！如有帮助，请点在看、转发支持一波！！！

性能优化

优化简介

  • PostgreSQL优化一方面是找出系统的瓶颈，提高PostgreSQL数据库整体的性能；

  • 另一方面，需要合理的结构设计和参数调整，以提高用户操作响应的速度；

  • 同时还要尽可能的节省系统资源，以便系统可以提供更大负荷的服务。

PostgreSQL 数据库优化是多方面的，原则是减少系统的瓶颈，减少资源的占用，增加系统的反应速度。例如：

  • 通过优化文件系统，提高磁盘IO的读写速度；

  • 通过优化操作系统调度策略，提高 PostgreSQL 的在高负荷情况下负载能力；

  • 优化表结构、索引、查询语句等使查询响应更快。

首先了解系统情况后便可做相关合理的调整，以达到性能优化的目的。

  1. /*CPU查看CPU型号*/
  2. cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c
  3.  
  4. /*查看物理CPU个数*/
  5. cat /proc/cpuinfo | grep "physical id" | sort -u | wc -l  
  6.  
  7. /*查看逻辑CPU个数*/
  8. cat /proc/cpuinfo | grep "processor" | wc -l  
  9.  
  10. /*查看CPU内核数*/
  11. cat /proc/cpuinfo | grep "cpu cores" | uniq  
  12.  
  13. /*查看单个物理CPU封装的逻辑CPU数量*/
  14. cat /proc/cpuinfo | grep "siblings" | uniq  
  15.  
  16. /*计算是否开启超线程
  17. ##逻辑CPU > 物理CPU x CPU核数 #开启超线程
  18. ##逻辑CPU = 物理CPU x CPU核数 #没有开启超线程或不支持超线程*/
  19.  
  20. /*查看是否超线程,如果cpu cores数量和siblings数量一致，则没有启用超线程，否则超线程被启用。*/
  21. cat /proc/cpuinfo | grep -e "cpu cores"  -e "siblings" | sort | uniq
  22.  
  23. /*内存
  24. TOP
  25. /*命令经常用来监控linux的系统状况，比如cpu、内存的使用等。*/
  26. /*查看某个用户内存使用情况,如:postgres*/
  27. top -u postgres
  28. /*
  29. 内容解释：
  30.   PID：#进程的ID
  31.   USER：#进程所有者
  32.   PR：#进程的优先级别，越小越优先被执行
  33.   NInice：#值
  34.   VIRT：#进程占用的虚拟内存
  35.   RES：#进程占用的物理内存
  36.   SHR：#进程使用的共享内存
  37.   S：#进程的状态。S表示休眠，R表示正在运行，Z表示僵死状态，N表示该进程优先值为负数
  38.   %CPU：#进程占用CPU的使用率
  39.   %MEM：#进程使用的物理内存和总内存的百分比
  40.   TIME+：#该进程启动后占用的总的CPU时间，即占用CPU使用时间的累加值。
  41.   COMMAND：#进程启动命令名称
  42.  
  43. 常用的命令：
  44.   P：#按%CPU使用率排行
  45.   T：#按MITE+排行
  46.   M：#按%MEM排行
  47.  
  48. /*查看进程相关信息占用的内存情况，(进程号可以通过ps查看)如下所示：*/
  49. pmap -d 14596
  50. ps -e -o 'pid,comm,args,pcpu,rsz,vsz,stime,user,uid' 
  51. ps -e -o 'pid,comm,args,pcpu,rsz,vsz,stime,user,uid' | grep postgres |  sort -nrk5
  52. /*其中rsz为实际内存，上例实现按内存排序，由大到小*/
  53.  
  54. /*看内存占用*/
  55. free -m
  56.  
  57. /*看硬盘占用率*/
  58. df -h
  59.  
  60. /*查看IO情况*/
  61. iostat -x 1 10
  62. /*如果 iostat 没有，要 yum install sysstat安装这个包，第一眼看下图红色圈圈的那个如果%util接近100%,表明I/O请求太多,I/O系统已经满负荷，磁盘可能存在瓶颈,一般%util大于70%,I/O压力就比较大，读取速度有较多的wait，然后再看其他的参数，
  63.  
  64. 内容解释:
  65. rrqm/s:#每秒进行merge的读操作数目。即delta(rmerge)/s 
  66. wrqm/s:#每秒进行merge的写操作数目。即delta(wmerge)/s 
  67. r/s:#每秒完成的读I/O设备次数。即delta(rio)/s 
  68. w/s:#每秒完成的写I/0设备次数。即delta(wio)/s 
  69. rsec/s:#每秒读扇区数。即delta(rsect)/s 
  70. wsec/s:#每秒写扇区数。即delta(wsect)/s 
  71. rKB/s:#每秒读K字节数。是rsec/s的一半，因为每扇区大小为512字节 
  72. wKB/s:#每秒写K字节数。是wsec/s的一半 
  73. avgrq-sz:#平均每次设备I/O操作的数据大小(扇区)。即delta(rsect+wsect)/delta(rio+wio) 
  74. avgqu-sz:#平均I/O队列长度。即delta(aveq)/s/1000(因为aveq的单位为毫秒) 
  75. await:#平均每次设备I/O操作的等待时间(毫秒)。即delta(ruse+wuse)/delta(rio+wio) 
  76. svctm:#平均每次设备I/O操作的服务时间(毫秒)。即delta(use)/delta(rio+wio) 
  77. %util:#一秒中有百分之多少的时间用于I/O操作,或者说一秒中有多少时间I/O队列是非空的
  78.  
  79. /*找到对应进程*/
  80. ll /proc/进程号/exe

更多关于 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。

优化查询

分析查询语句EXPLAIN

使用 EXPLAIN 语句来分析一个查询语句，执行如下语句：

EXPLAIN ANALYZE SELECT * FROM fruits;
索引对查询速度的影响

下面是查询语句中不使用索引和使用索引的对比。首先，分析未使用索引时的查询情况，EXPLAIN 语句执行如下：

EXPLAIN  SELECT * FROM fruits WHERE f_name='apple';

然后，在fruits表的f_name字段上加上索引。执行添加索引的语句及结果如下：

CREATE INDEX index_name ON fruits(f_name);

现在，再分析上面的查询语句。执行的 EXPLAIN 语句及结果如下：

EXPLAIN ANALYZE  SELECT * FROM fruits WHERE f_name='apple';
优化子查询

子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。子查询虽然可以使查询语句很灵活，但执行效率不高。执行子查询时，PostgreSQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记 录。查询完毕后，再撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。

在 PostgreSQL 中可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用到索引的话，性能会更好。连接之所以更有效率，是因为PostgreSQL不需要在内存中创建临时表来完成查询工作。

优化数据库结构

  • 将字段很多的表分解成多个表

  • 增加中间表

  • 增加冗余字段

设计数据库表时尽量遵循范式理论的规约，尽可能少的冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。

分析表的统计信息

PostgreSQL中提供了 ANALYZE 语句收集表内容的统计信息，然后把结果保存在系统表 pg_statistic 里。

使用 ANALYZE 来分析fruits表，执行的语句：

ANALYZE VERBOSE fruits;

更多关于 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。

优化PostgreSQL服务器

优化服务器硬件

  • 配置较大的内存。足够大的内存，是提高PostgreSQL数据库性能的方法之一。内存的速度比磁盘I/0快得多，可以通过增加系统的缓冲区容量，使数据在内存中停留的时间更 长，以减少磁盘I/0。

  • 配置高速磁盘系统，以减少读盘的等待时间，提高响应速度。

  • 合理分布磁盘I/O，把磁盘I/O分散在多个设备上，以减少资源竞争，提高并行操作能力。

  • 配置多处理器，PostgreSQL是多线程的数据库，多处理器可同时执行多个线程。

PostgreSQL 系统参数

shared_buffers

PostgreSQL既使用自身的缓冲区，也使用内核缓冲IO。这意味着数据会在内存中存储两次，首先是存入PostgreSQL缓冲区，然后是内核缓冲区。这被称为双重缓冲区处理。对大多数操作系统来说，这个参数是最有效的用于调优的参数。此参数的作用是设置PostgreSQL中用于缓存的专用内存量。

shared_buffers 的默认值设置得非常低，因为某些机器和操作系统不支持使用更高的值。但在大多数现代设备中，通常需要增大此参数的值才能获得最佳性能。

建议的设置值为机器总内存大小的25％，但是也可以根据实际情况尝试设置更低和更高的值。实际值取决于机器的具体配置和工作的数据量大小。举个例子，如果工作数据集可以很容易地放入内存中，那么可以增加shared_buffers的值来包含整个数据库，以便整个工作数据集可以保留在缓存中。

在生产环境中，将shared_buffers设置为较大的值通常可以提供非常好的性能，但应当时刻注意找到平衡点。

查看当前shared_buffers的值：
  1. postgres=# show shared_buffers;
  2.  shared_buffers 
  3. ----------------
  4.  128MB
  5. (1 row)
wal_buffers

PostgreSQL将其WAL（预写日志）记录写入缓冲区，然后将这些缓冲区刷新到磁盘。由wal_buffers定义的缓冲区的默认大小为16MB，但如果有大量并发连接的话，则设置为一个较高的值可以提供更好的性能。

查看当前wal_buffers的值：
  1. postgres=# show wal_buffers;
  2.  wal_buffers 
  3. -------------
  4.  4MB
  5. (1 row)
effective_cache_size

effective_cache_size提供可用于磁盘高速缓存的内存量的估计值。它只是一个建议值，而不是确切分配的内存或缓存大小。它不会实际分配内存，而是会告知优化器内核中可用的缓存量。在一个索引的代价估计中，更高的数值会使得索引扫描更可能被使用，更低的数值会使得顺序扫描更可能被使用。在设置这个参数时，还应该考虑PostgreSQL的共享缓冲区以及将被用于PostgreSQL数据文件的内核磁盘缓冲区。默认值是4GB。

查看当前effective_cache_size的值：
  1. postgres=# show effective_cache_size;
  2.  effective_cache_size 
  3. ----------------------
  4.  4GB
  5. (1 row)
work_mem

此配置用于复合排序。内存中的排序比溢出到磁盘的排序快得多，设置非常高的值可能会导致部署环境出现内存瓶颈，因为此参数是按用户排序操作。如果有多个用户尝试执行排序操作，则系统将为所有用户分配大小为work_mem *总排序操作数的空间。全局设置此参数可能会导致内存使用率过高，因此强烈建议在会话级别修改此参数值。默认值为4MB。

查看当前work_mem的值：
  1. postgres=# show work_mem;
  2.  work_mem 
  3. ----------
  4.  4MB
  5. (1 row)
maintenance_work_mem

maintenance_work_mem是用于维护任务的内存设置。默认值为64MB。设置较大的值对于VACUUM，RESTORE，CREATE INDEX，ADD FOREIGN KEY和ALTER TABLE等操作的性能提升效果显著。

查看当前maintenance_work_mem的值：
  1. postgres=# show maintenance_work_mem;
  2.  maintenance_work_mem 
  3. ----------------------
  4.  64MB
  5. (1 row)
synchronous_commit

此参数的作用为在向客户端返回成功状态之前，强制提交等待WAL被写入磁盘。这是性能和可靠性之间的权衡。如果应用程序被设计为性能比可靠性更重要，那么关闭synchronous_commit。这意味着成功状态与保证写入磁盘之间会存在时间差。在服务器崩溃的情况下，即使客户端在提交时收到成功消息，数据也可能丢失。

查看当前synchronous_commit的设置值：
  1. postgres=# show synchronous_commit;
  2.  synchronous_commit 
  3. --------------------
  4.  on
  5. (1 row)
checkpoint_timeout和checkpoint_completion_target

PostgreSQL将更改写入WAL。检查点进程将数据刷新到数据文件中。发生CHECKPOINT时完成此操作。这是一项开销很大的操作，整个过程涉及大量的磁盘读/写操作。用户可以在需要时随时发出CHECKPOINT指令，或者通过PostgreSQL的参数checkpoint_timeout和checkpoint_completion_target来自动完成。

checkpoint_timeout参数用于设置WAL检查点之间的时间。将此设置得太低会减少崩溃恢复时间，因为更多数据会写入磁盘，但由于每个检查点都会占用系统资源，因此也会损害性能。此参数只能在postgresql.conf文件中或在服务器命令行上设置。

checkpoint_completion_target指定检查点完成的目标，作为检查点之间总时间的一部分。默认值是 0.5。这个参数只能在postgresql.conf文件中或在服务器命令行上设置。高频率的检查点可能会影响性能。

查看当前checkpoint_timeout和checkpoint_completion_target的值：
  1. postgres=# show checkpoint_timeout;
  2.  checkpoint_timeout
  3. --------------------
  4.  5min
  5. (1 row)
  6.  
  7. postgres=# show checkpoint_completion_target;
  8.  checkpoint_completion_target
  9. ------------------------------
  10.  0.5
  11. (1 row)
max_connections

允许客户端连接的最大数目

fsync

强制把数据同步更新到磁盘,如果系统的IO压力很大，把改参数改为off

在fsync打开的情况下，优化后性能能够提升30%左右。因为有部分优化选项在默认的SQL测试语句中没有体现出它的优势，如果到实际测试中，提升应该不止30%。

测试的过程中，主要的瓶颈就在系统的IO，如果需要减少IO的负荷，最直接的方法就是把fsync关闭，但是这样就会在掉电的情况下，可能会丢失部分数据。

commit_delay

事务提交后，日志写到wal log上到wal_buffer写入到磁盘的时间间隔。需要配合commit_sibling。能够一次写入多个事务，减少IO，提高性能

commit_siblings

设置触发commit_delay的并发事务数，根据并发事务多少来配置。减少IO，提高性能

注意：并非所有参数都适用于所有应用程序类型。某些应用程序通过调整参数可以提高性能，有些则不会。必须针对应用程序及操作系统的特定需求来调整数据库参数。

下面介绍几个我认为重要的：

增加maintenance_work_mem参数大小

增加这个参数可以提升CREATE INDEX和ALTER TABLE ADD FOREIGN KEY的执行效率。

增加checkpoint_segments参数的大小

增加这个参数可以提升大量数据导入时候的速度。

设置archive_mode无效

这个参数设置为无效的时候，能够提升以下的操作的速度

autovacuum相关参数 (autovacuum介绍文章)

autovacuum：默认为on，表示是否开起autovacuum。默认开起。特别的，当需要冻结xid时，尽管此值为off，PG也会进行vacuum。

autovacuum_naptime：下一次vacuum的时间，默认1min。这个naptime会被vacuum launcher分配到每个DB上。autovacuum_naptime/num of db。

log_autovacuum_min_duration：记录autovacuum动作到日志文件，当vacuum动作超过此值时。“-1”表示不记录。“0”表示每次都记录。

autovacuum_max_workers：最大同时运行的worker数量，不包含launcher本身。

autovacuum_work_mem ：每个worker可使用的最大内存数。

autovacuum_vacuum_threshold ：默认50。与autovacuum_vacuum_scale_factor配合使用，autovacuum_vacuum_scale_factor默认值为20%。当update,delete的tuples数量超过autovacuum_vacuum_scale_factor*table_size+autovacuum_vacuum_threshold时，进行vacuum。如果要使vacuum工作勤奋点，则将此值改小。

autovacuum_analyze_threshold ：默认50。与autovacuum_analyze_scale_factor配合使用。

autovacuum_analyze_scale_factor ：默认10%。当update,insert,delete的tuples数量超过autovacuum_analyze_scale_factor*table_size+autovacuum_analyze_threshold时，进行analyze。

autovacuum_freeze_max_age：200 million。离下一次进行xid冻结的最大事务数。

autovacuum_multixact_freeze_max_age：400 million。离下一次进行xid冻结的最大事务数。

autovacuum_vacuum_cost_delay ：如果为-1，取vacuum_cost_delay值。

autovacuum_vacuum_cost_limit：如果为-1，到vacuum_cost_limit的值，这个值是所有worker的累加值。

PostgreSQL 配置参数修改

修改配置文件

在配置文件C:\PostgreSQL\data\pg96\postgresql.conf中直接修改，修改前记得备份一下原文件，因为你不知道意外和明天不知道哪个会先来。修改完成之后，记得重启数据库哦。

命令行的修改方式
 ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }

例如：我们现在要修改 maintenance_work_mem。

  1. --查看所有数据库参数的值
  2. show all;
  3.  
  4. show maintenance_work_mem;
  5. --注意这里的设置不会改变postgresql.conf,只会改变postgresql.conf
  6. ALTER SYSTEM SET maintenance_work_mem= 1048576;
  7.  
  8. --重启数据库
  9. show maintenance_work_mem; 
  10.  
  11. --取消postgresql.auto.conf的参数设置
  12. ALTER SYSTEM SET maintenance_work_mem= default;

数据库参数优化总结

  1. max_connections = 300       #(change requires restart)
  2.  
  3. unix_socket_directories = '.'   #comma-separated list of directories
  4.  
  5. shared_buffers = 194GB    #尽量用数据库管理内存，减少双重缓存，提高使用效率
  6.  
  7. huge_pages = on    #on, off, or try，使用大页
  8.  
  9. work_mem = 256MB # min 64kB ，减少外部文件排序的可能，提高效率
  10.  
  11. maintenance_work_mem = 2GB  #min 1MB，加速建立索引
  12.  
  13. autovacuum_work_mem = 2GB   #min 1MB, or -1 to use maintenance_work_mem  ，加速垃圾回收。
  14.  
  15. dynamic_shared_memory_type = mmap   #the default is the first option
  16.  
  17. vacuum_cost_delay = 0      #0-100 milliseconds，垃圾回收不妥协，极限压力下，减少膨胀可能性。
  18.  
  19. bgwriter_delay = 10ms      #10-10000ms between rounds，刷shared buffer脏页的进程调度间隔，尽量高频调度，减少用户进程申请不到内存而需要主动刷脏页的可能（导致RT升高）。
  20.  
  21. bgwriter_lru_maxpages = 1000   #0-1000 max buffers written/round ,  一次最多刷多少脏页。
  22.  
  23. bgwriter_lru_multiplier = 10.0    #0-10.0 multipler on buffers scanned/round 一次扫描多少个块，上次刷出脏页数量的倍数。
  24.  
  25. effective_io_concurrency = 2       #1-1000; 0 disables prefetching ， 执行节点为bitmap heap scan时，预读的块数。
  26.  
  27. wal_level = minimal     #minimal, archive, hot_standby, or logical ， 如果现实环境，建议开启归档。
  28.  
  29. synchronous_commit = off    #synchronization level; ，异步提交。
  30.  
  31. wal_sync_method = open_sync    # the default is the first option ，因为没有standby，所以写xlog选择一个支持O_DIRECT的fsync方法。
  32.  
  33. full_page_writes = off      # recover from partial page writes ，生产中，如果有增量备份和归档，可以关闭，提高性能。
  34.  
  35. wal_buffers = 1GB           # min 32kB, -1 sets based on shared_buffers  ，wal buffer大小，如果大量写wal buffer等待，则可以加大。
  36.  
  37. wal_writer_delay = 10ms     #1-10000 milliseconds wal buffer调度间隔，和bg writer delay类似。
  38.  
  39. commit_delay = 20      #range 0-100000, in microseconds ，分组提交的等待时间。
  40.  
  41. commit_siblings = 9    #range 1-1000 , 有多少个事务同时进入提交阶段时，就触发分组提交。
  42.  
  43. checkpoint_timeout = 55min  #range 30s-1h 时间控制的检查点间隔。
  44.  
  45. max_wal_size = 320GB    #2个检查点之间最多允许产生多少个XLOG文件。
  46.  
  47. checkpoint_completion_target = 0.99     #checkpoint target duration, 0.0 - 1.0 ，平滑调度间隔，假设上一个检查点到现在这个检查点之间产生了100个XLOG，则这次检查点需要在产生100*checkpoint_completion_target个XLOG文件的过程中完成。PG会根据这些值来调度平滑检查点。
  48.  
  49. random_page_cost = 1.0     #same scale as above , 离散扫描的成本因子，本例使用的SSD IO能力足够好。
  50.  
  51. effective_cache_size = 240GB  #可用的OS CACHE
  52.  
  53. log_destination = 'csvlog'  #Valid values are combinations of
  54.  
  55. logging_collector = on          #Enable capturing of stderr and csvlog
  56.  
  57. log_truncate_on_rotation = on   #If on, an existing log file with the
  58.  
  59. update_process_title = off
  60. track_activities = off
  61.  
  62. autovacuum = on    #Enable autovacuum subprocess?  'on'
  63.  
  64. autovacuum_max_workers = 4 #max number of autovacuum subprocesses ，允许同时有多少个垃圾回收工作进程。
  65.  
  66. autovacuum_naptime = 6s  #time between autovacuum runs，自动垃圾回收探测进程的唤醒间隔。
  67.  
  68. autovacuum_vacuum_cost_delay = 0    #default vacuum cost delay for，垃圾回收不妥协。

更多关于 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/繁依Fanyi0/article/detail/406077
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号