PostgreSQL性能调优参考
2020-03-22 13:57:37 阿炯

PostgreSQL性能调优有太多的方面可以讨论,本文收集了一些调优的文章,以供参考。

PostgreSQL 硬件性能调优

PostgreSQL最初由加大伯克利分校在1996年开发,开始在互联网上开发这个数据库,通过邮件来交流思想并且通过文件服务器来共享代码。PostgresSQL现在在专业特性、性能、可靠性上比得上商业数据库。具有事务、视图、存储过程、引用一致性约束,支持许多编程接口,包括ODBC、JDBC、TCL/TK、PHP、Perl和Python。本文转自: wangzhen3798的个人主页,原文为:PostgreSQL Hardware Performance Tuning

性能概念

有两个方面的数据库性能调优,一个是提高数据库服务器使用的CPU、内存、磁盘驱动;第二个是优化发送到数据库的查询。这篇文章讨论硬件方面的性能调优。查询优化可以通过SQL命令如 CREATE INDEX、VACUUM 、VACUUM FULL、ANALYZE、CLUSTER 和 EXPLAIN 来完成。这些在《PostgreSQL:Introduction and Concepts》中有所讨论。。

为了理解硬件调优问题,理解计算机里正在发生什么是非常重要的。简单来说,计算机可以想象成一个被存储包围着的CPU。在和CPU同一芯片上是几个用来存储中间结果和不同指针和计数的CPU寄存器,在这些周围是用来保存最近访问信息的CPU缓存。在CPU缓存外面是大量的用来保存正在执行程序和数据的随机访问主存(RAM)。在主存的外面是存储更多信息的磁盘驱动,磁盘驱动是唯一的持久存储区,所以任何要在关机后保持的信息都必须放在那里。在CPU的周围有这些存储:
存储区域     度量单位
CPU 寄存器     bytes
CPU 缓存     kilobytes
RAM     megabytes
磁盘驱动     gigabytes

可以看到距离CPU越远存储越大。理论上大量的持久存储可以放在CPU旁边,但是会特别慢且比较昂贵。在实践中,经常被使用的信息存储在CPU旁边,不经常访问的信息存储远离CPU,当使用时被带到CPU。

保持信息靠近CPU

在不同存储区之间移动信息是自动发生的,编译器决定哪些信息保存在寄存器中,CPU芯片逻辑保证最近使用的信息保存在CPU缓存中。操作系统控制哪些信息存储在RAM并在磁盘驱动器间来回穿梭。

CPU寄存器和CPU缓存不能被数据库管理员有效的调优。有效的数据库调优涉及在RAM中增加有用的信息,来尽可能的阻止磁盘访问。

你可能认为这很容易,但实际上相去甚远,计算机的RAM中包含很多东西:
正在执行的程序
程序数据和栈
PostgreSQL shared buffer cache
内核磁盘缓存
内核

合适的调优包括在RAM中尽可能多的保存数据库信息,同时不影响操作系统的其他区域
PostgreSQL Shared Buffer Cache



PostgreSQL 不直接更改磁盘上的信息,相反它请求读数据到PostgreSQL shared buffer cache。PostgreSQL后台来读和写这些块,并且最终刷到磁盘上。

需要访问表的后台先在缓存中查找需要的块,如果它们已经存在,可以马上继续处理;如果没有,一个操作系统请求被发送去加载这些块。这些块从内核磁盘缓存或者直接从磁盘加载。这可能是昂贵的操作。

多大算太大?


你可能想,“我把全部的RAM都给PostgreSQL shared buffer cache”。然而如果那样做,会没有其他的空间分配给内核和其他任何要运行的程序。合适的PostgreSQL shared buffer cache 是不产生其他不良活动的最大空间。

为了理解不良活动,需要去理解UNIX操作系统怎么管理内存。如果有足够的内存去装进所有的程序和数据,只需要很少的内存管理。然而,如果所有东西不能装进内存,内核开始强制内存刷出到叫swap磁盘区域。它把最近没有使用的内存页移出,这个操作叫做swap pageout。Pageouts 不是问题因为它们发生在非活动时间段。糟糕的是当这些页必须从swap返回时,意为着移到swap的旧页必须移入RAM,这种叫 swap pagin。这种情况糟糕因为当页从swap移动时,程序必须暂停直到pagein完成。

Pagein 活动可以被系统分析工具如vmstat和sar展示,并且暗示没有足够的内存去有效运行。不要混淆swap pageins和ordinary pageins,它包含的页是作为的正常的操作系统读取文件系统的一部分。如果你不能发现swap pageins,许多pageouts 是一个好的暗示,你也正在进行swap pageins。

缓存大小的影响

可能好奇为什么缓存大小如此重要。首先假设PostgreSQL shared buffer cache 足够大去装下整个表。这张表的重复顺序扫描不需要访问磁盘,因为所有的数据已经在缓存中了。现在假设缓存比表小一个块大小。这个表的顺序扫描需要加载所有的表数据块进入缓存直到最后一个。当需要块时,最老的块被移出,这种情况就是表的第一个块。当另外一个顺序扫描发生时,第一个块已经不在缓存中,需要把它重新装入,这时最老的块,也就是这个表的第二个块被移出缓存。这又会把下一个需要的块继续推出去直到这个表的最后。这是一个极端的例子,但是可以明白减少一个块可以把缓存的效率从100%变为0%。它展示了找到正确的缓存大小可以极大的影响性能。

合适的Shared Buffer Cache大小

理想的,PostgreSQL shared buffer cache 应该是:
足够大能装入经常访问的表
足够小能避免swap pageins活动

记住postmaster分配所有的共享内存在它启动的时候,这片区域保持相同的大小即使没有一个人访问数据库。一些操作系统pageout不涉及共享内存,一些锁定主存中的共享内存,锁定共享内存是推荐的。PostgreSQL管理员指南有关于各种操作系统内核参数配置信息。

批量排序内存大小

另外一个调优的参数是批量排序使用的内存大小。当排序大表或者结果集,PostgresSQL会分成几部分排序它们,把中间结果放到临时文件中。这些文件合并并且重新排序直到所有的行完成排序,增加批量大小能减少临时文件且经常能加快排序。然而,如果批量排序太大,它们会引起pageins,因为在排序时一部分批量排序pageout到swap区,这种情况下使用更小的批量排序和更多的临时文件是更快的。所以再次强调,swap pageins 指示已经分配了太多的内存。记住每个后台排序如ORDER BY,CREATE INDEX,或者merge join都使用这个参数,有几个并行的排序会使用几倍的这个内存大小。

缓存大小和排序大小

缓存大小和排序大小都影响内存使用,所以你不可能最大化一个而不影响另一个。记住缓存大小在postmaster启动是分配,而排序大小由正在执行的排序操作的数量决定,通常缓存大小比排序大小更重要。然而,可预见的使用ORDER BY ,CREATE INDEX或者merge joins的确定查询可以被大的批量排序大小加速。

很多操作系统限制了多少共享内存可以分配,要增加这些限制需要了解操作系统相关的知识:去重新编译或者重新配置内核,更多的信息可以在PostgreSQL管理员指南中找到。

作为调优的起点,如果只有一些大的会话,用15%的主存作为缓存大小,2-4%作为排序大小。如果有很多小的会话可以调的更小,可以尝试增加他们去看是否有性能提升同时没有swap发生。如果shared buffer cache太大,会浪费维护太多缓存的管理开销,并且占用其他进程和额外的内核磁盘缓存的RAM。

一个有价值的参数是effective_cache_size,调优者使用这个参数去估计内核磁盘缓存的大小。在不固定缓存大小的内核中,这个参数可以设置成未使用RAM的平均大小,因为这些内核使用未使用的RAM去缓存最近访问的磁盘页。在其他固定大小磁盘缓存的内核中,通常应该把你的内核缓存设置成10%RAM大小。

磁盘局限性


磁盘驱动的物理特性导致了不同于文章中提到的其他存储区域的性能特点,其他的存储区域可以以相同速度访问任何字节。磁盘驱动有它自己的自旋盘和移动头,访问在磁头附近的数据比距离远的数据快的多。

移动磁头去盘面的另外磁柱需要花费一些时间。Unix开发者知道这个特性。当排序磁盘上的大文件,尝试把文件的各个条放在一起。例如,假设一个文件需要10个磁盘块。操作系统可能把1-5块放在一个磁柱上,6-10块放在另外磁柱上。如果这个文件需要从头读到未,只需要磁头移动两次:一次获取1-5块的磁柱,一次获取6-10块。如果文件不是顺序读,如要按如下的顺序读取块1,6,2,7,3,8,4,9,5,10,这就需要10次磁头移动。可以看到,在磁盘上,顺序读比随机读快很多。这是为什么如果一个表的大部分内容需要被读,PostgreSQL偏向于顺序扫描而不是索引扫描,这也凸显了缓存的价值。

多磁盘Spindles

磁头在数据库活动期间移动相当多。如果产生太多的读写请求,驱动会变得饱和,导致性能不佳(vmstat和sar可以提供每个磁盘活动量的信息)。

一个磁盘饱和的解决方法是移动一些PostgreSQL的数据文件到其他磁盘。记着,移动文件到同一磁盘的其他文件系统没有帮助,所有在同一磁盘的文件系统使用同一个磁头。

通过磁盘驱动的数据库访问可以通过以下几个方法加速:
移动数据库、表、索引
表空间允许你在不同的驱动上创建对象。
移动WAL

initdb -X 和符号连接可以用来移动pg_xlog目录到不同的磁盘驱动。不像其他的写操作,PostgreSQL 写日志必须在事务完成前刷到磁盘,缓存不能用来推迟这些写操作。为写日志使用单独的磁盘可以允许磁头停留在当前日志的磁柱而没有移动延迟(你可以使用postgres -F 参数来阻止日志刷到磁盘,但是一个操作系统灾难需要从备份中恢复)。

其他的选项包括使用磁盘阵列,通过多个磁盘驱动加速一个文件系统。镜像会降低数据库写速度,但是会加速数据库读,因为数据可以从每个驱动中获得。许多站点使用RAID1+0或RAID0+1,因为它同时提供了性能和可靠的优势,RAID5对于6个或者更多驱动来说是更快的。理论上,RAID 5将有一个由电池支持的高速缓存,因此写入可以以一种有效的方式刷新到磁盘,而不是在编写文件的时候减慢应用程序的速度。

磁盘驱动缓存

现代磁盘驱动都有读和写缓存,读缓存保证最近请求的磁盘块在磁盘内存中可用,写缓存保存写请求直到它们可以有效的存储到盘面上。这可能产生另外一个问题:如果磁盘断电了,当它持有的写数据还没写到磁盘上怎么办?一些磁盘驱动和RAID控制器有后备电池写缓存来保证数据安全,且当电源完全恢复时把数据写到盘面。然而大部分驱动没有这种特性,因此它们是不可靠的。

幸运的是,在大多数驱动你可以关闭写缓存。SCSI驱动默认关闭写缓存,IDE设备写缓存通常是打开的,但是可以从操作系统命令关闭:hdparm -W0,sysctl hw.ata.wc=0或者scsimd。但一些IDE驱动虽然报告写缓存关闭了,但是仍在使用它,这是不可靠的。没有精确的测试很难判断哪个驱动在撒谎。

因为PostgreSQL每个事务提交时使用fsync()写WAL日志到磁盘,并且等待写完成。当使用写缓存时用户能看到巨大的性能提升,因此为了性能和可靠性,如果PostgreSQL能使用有后备电池的写缓存是一个理想的解决方案。

SCSI vs IDE

SCSI驱动通常比IDE驱动贵很多。SCSI驱动有一个协议用来在操作系统和控制器间通信,然而IDE驱动简单的多且同一时刻只能接受一个请求。有标记队列的SCSI驱动可以接受多个请求并且重新排列它们提高效率。这是为什么当单用户或者单文件IO时SCSI和IDE驱动有相似的性能特性,但是当多个用户或者进程请求时SCSI比IDE性能更好。因此SCSI更适合重负载数据库服务器。

实际上,SCSI或IDE是唯一的方法区分两种主要的驱动:企业驱动,为高性能和高可靠设计,个人电脑驱动为最小耗费设计。具体可以参考这篇文章,做了优秀的工作来描述在生产基于性能可靠性或者减小耗费的驱动。它是一个优秀的指导在选择基于这些特性的驱动。

文件系统

一些操作系统支持多磁盘文件系统,在这种情况下可能很难知道哪个文件系统表现最好。PostgreSQL通常在传统的Unix文件系统表现最好,像很多操作系统支持的BSD UFS/FFS等,UFS默认的8K块大小和PostgreSQL的页大小一样。你可以运行在日志和基于日志的文件系统,但是这些会在WAL的fsync执行期间引起额外的开销。老的基于SvR3的文件系统变的太碎片难以有好的性能。

因为有许多文件系统可以选择并且没有一个是最优的,Linux上的文件系统选择是特别困难的:ext2不是完全的灾难安全的,ext3、XFS和JFS是基于日志的且Reiser是对小文件优化且记录日志。虽然ext2比日志文件系统块很多,但当要求灾难恢复时ext2是不可选的。如果必须使用ext2,使用sync enabled的选项来挂载,一些人推荐使用ext3时用data=writeback挂载。

不推荐PostgreSQL使用NFS和其他远程文件系统,NFS和本地文件系统没有一样的文件系统特性,这些不一致可能引起数据可靠和灾难恢复问题。

多CPU

PostgreSQL使用多进程模型,意为着每个数据库连接拥有它自己的unix进程,所有多CPU操作系统可以通过可用的CPU来加速多数据库连接。如果只有一个数据库连接是活动的那么它只能使用一个CPU。PostgreSQL没有使用多线程模型从而允许一个进程使用多个CPU。

检查点

当WAL文件充满时,一个检查点被执行来强制所有的脏缓存刷到磁盘,从而使日志文件可以循环。检查点也会在固定的时间间隔被执行,通常是5分钟。如果有很多的数据库写活动,WAL段会很块被充满,导致所有磁盘缓存被刷到磁盘引起系统过度的缓慢。

检查点应该每几分钟发生一次,如果在一分钟发生几次,性能会变差。在服务日志中查找"checkpoint_warning"消息来决定你的检查点是否执行太频繁。如果30秒内检查点执行超过一次,会出现这个消息。

减小检查点的频率包括增加在data/pg_xlog中WAL文件的数量,每个文件是16 megabyte,所以它影响磁盘使用率。默认的配置使用了最小数量的日志文件,为了减小检查点频率,你需要增加这个参数:
checkpoint_segments=3

默认值是3,增加它直到检查点几分钟出现一次。另外一个可能出现的消息是:
LOG:XLogWrite:new log file created - consider increasing WAL_FILES

这个消息暗示postgresql.conf中的wal_files参数需要增加。


幸运的是,PostgreSQL不需要太多调优,大部分参数自动调整到最佳性能。缓存大小和排序大小是两个参数管理员能够控制去更好的利用可用内存,磁盘访问可以通过多个驱动加速。其他参数可以参考share/postgresql.conf.sample,可以复制到data/postgresql.conf来实验其他的PostgreSQL的参数。

数据库优化是一个综合工程,不仅仅是需要DBA参与,更重要的是研发设计人员针对PG数据库的特点来进行相关的优化设计。不过对于DBA来说,一旦接到上线和运维任务,基本上都是木已成舟,软件设计方面留下的坑已经挖好,DBA的作为已经十分有限了。不过既然要干运维,那么少不了就要参与优化。PG的优化工作该如何开展呢?今天我从几个主要的方面聊聊PG优化的几个常见的角度。针对PG数据库,只要做好了下面几个方面的优化工作,那么运维起来也就比较省心了。

1、硬件资源问题:如果数据库服务器硬件资源不足,例如 CPU、内存、磁盘 IO 等,会导致系统性能下降,响应时间变慢。

2、操作系统配置不合理:如果操作系统没有针对PG数据库进行优化,那么PG数据库也无法发挥最佳的效能,因此针对PG数据库的优化,从操作系统参数调整入手永远是不会错的。

3、文件系统配置不合理:对于一些负载较高的大型数据库来说,如果无法发挥后端存储的IO能力,或者说让后端磁盘出现了性能问题,那么就会严重影响PG数据库的性能甚至稳定性。对于大型数据库来说,文件系统设计与配置一定要十分用心。

4、SQL不够优化:如果应用没有经过优化,可能会导致查询效率低下,索引设计不合理,缺少必要的索引,过多的单列索引以及索引类型使用不合理等都会带来性能问题。最后不合理多表的 JOIN、WHERE 子句和大表并行扫码都可能成为性能杀手。

5、数据库结构设计不合理:如果数据库结构设计不合理,可能会导致查询效率低下,例如表过度归一化、大表未分区或者分区设置不合理,表或者索引的的FILL FACTOR参数设置不合理导致的热块冲突。索引设计不合理产生的不必要的写成本过高。应该存储到对象存储中的非结构化数据存储到PG数据库中等。表分区设计不合理,时序数据没有使用timescaledb的自动分区与自动压缩特性也会导致时序数据访问的性能不佳。

6、数据库参数设置不合理:如果 PostgreSQL 数据库参数设置不合理,可能会导致数据库性能低下,例如 shared_buffers、work_mem、WAL/Checkpoint 等参数的设置等。

7、并发控制不合理:如果数据库并发控制不合理,可能会导致性能下降,这方面包含事务隔离级别设置不合理,并发度相关参数设置不合理等。

8、缓存命中率低:如果缓存命中率低,会导致频繁的磁盘 IO 操作,从而降低数据库性能。

9、访问冷数据的性能不足:PG数据库是采用DOUBLE CACHE机制的,冷数据是指在SHARED BUFFERS和OS CACHE中都不存在的数据,这些数据一旦要访问,要产生大量的物理IO,访问性能较差。

10、自动化任务冲突:如果数据库中存在大量的自动化任务,例如备份、VACUUM、定时任务等,可能会导致任务之间的冲突,从而影响系统性能。

硬件资源不足的问题就不多加讨论了,这种情况一般会出现在CPU、IO等方面,在分析这方面问题的时候,需要关注队列的长度是否超过CPU逻辑核数的2倍以上,对于IO来说,不仅仅要看IOPS/IO吞吐量等指标,更重要的是要看IO延时是否合理。

操作系统配置不合理是绝大多数PG数据库都存在的问题,这方面实际上是有一些最佳实践的。

[sysctl]
vm.swappiness = 1
vm.dirty_background_ratio = 10
vm.dirty_ratio = 40
vm.dirty_expire_centisecs = 3000
vm.dirty_writeback_centisecs = 500

kernel.shmmax = 18446744073692700000
kernel.shmall = 18446744073692700000
kernel.shmmni = 4096
kernel.sem = 250 512000 100 2048

fs.file-max = 312139770
fs.aio-max-nr = 1048576

net.ipv4.ip_local_port_range = 2048 65000
# Permits sockets in the time-wait state to be reused for new connections:
net.ipv4.tcp_tw_reuse = 1
net.core.netdev_budget = 1024
net.core.netdev_max_backlog = 2048
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.core.somaxconn = 4096

kernel.panic_on_oops = 1

# We don't need NUMA balancing in this box:
kernel.numa_balancing = 0

net.ipv4.tcp_window_scaling = 1
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
net.netfilter.nf_conntrack_max = 250000
net.ipv4.tcp_max_syn_backlog=4096

[vm]
transparent_hugepages=never

上面是红帽公司对于PG数据库RHEL参数优化的建议,大家可以参考,对于绝大多数高负载的系统来说,都是有效的。大家要注意的是,关于脏块回写的设置,对于不同的写IO负载以及不同的底层IO硬件,可能调整会有不同,甚至会有截然相反的配置策略。要注意的是,绝对不能因为不合理的脏块刷新策略导致了OS IO负载的过载。在此前提下,缩短IO写盘的周期对于提高并发负载是有帮助的。

文件系统的设计对于大型系统来说十分关键,除了使用XFS与EXT4等带日志的文件系统并且打开日志功能外,设置文件系统的MOUNT参数对性能也有很大影响。文件系统的条带大小、块大小要与PG数据库匹配,MOUNT时也要加入nobarrier、noatime,nodiratime等参数,并做好扇区对齐,除此之外就是文件存储方面的性能优化了。

很多DBA都只会设置一个$PGDATA,整个数据库都放在同一个文件系统上,这需要对文件系统底层的卷做十分细致的优化,确保整个卷的IO能力是优秀的,这一点总是无法做到的。因此在数据库设计的时候就通过WAL与数据文件分离,热数据与冷数据分离,通过表空间隔离热点IO等方式规划PG数据库的文件存储。如果应用系统已经无法通过表空间来隔离IO热点,那么通过软连接将部分库的目录迁移到其他文件系统也是一个可行的方案。

对于数据库参数来说,实际上不同的应用场景下的最佳调整方案是不同的,一般来说,设置合理的shared_buffers,以及优化好相关的而bgwriter,WAL,checkpoint,work_mem,VACUUM等相关的参数,就能够满足大多数应用的需求了。

并发控制不合理方面的问题是比较容易被忽视的问题,事务隔离级别用错对于性能的影响极大,不过一般情况下我们都是使用read committed,不要轻易去修改数据库级的事务隔离级别。

并发的另外一个方面是系统中的各类并发访问的控制,特别是并行执行的设置。max_worker_processes、max_parallel_workers、max_parallel_maintenance_workers和max_parallel_workers_per_gather等参数对数据库的并发度控制都至关重要。

如果并发相关的设置过小,那么当活跃会话数量不高的时候,无法充分发挥服务器硬件的资源优势,造成巨大的浪费。PG数据库可以支撑巨大的数据库与极高的并发,因此如果服务器的配置足够好,系统资源使用率不高,但是应用性能无法达到设计要求,那么就应该关注一下是否并发控制相关的参数设置过低了。默认的PG参数里,max_worker_processes是偏小的,仅仅是8,对于有上百甚至上千个逻辑核数的服务器来说是完全不够用的。

当然如果因为并发控制参数设置的过高而导致了CPU等资源出现了不足,因为IOPS过大或者IO吞吐量过大,底层存储能力不足导致的IO延时过大等现象,那么适当调低这些参数对数据库的整体性能提升是有帮助的。

PG的SHARED_BUFFERS设置不合理可能会导致缓冲区命中率不高,从而影响SQL的执行性能。不过PG数据库是使用DOUBLE BUFFER机制的,要想为应用调整好缓冲区并不容易。再怎么调整都无法满足不同场景的应用,有些时候DBA真的很难通过调整来优化这方面的性能。对于一些定期的报表等应用,在跑批之前做数据预热可能是DBA能够控制的优化方法,也是最为有效的提升统计报表性能的方法。

最后一点,自动化任务冲突是所有数据库都会遇到的性能问题,如果数据库备份,大批量统计作业与大数据量导入导出同时发生,再好的硬件也可能撑不住,因此在设计这些定期任务的时候,一定要通过算法将这些作业分开,千万不要让这些大型操作存在最大公约数。