DBAIOps的博客

WORK_MEM参数是可会话级动态设置的，如果我们的某些要做大型排序或者HASH JOIN的SQL能够在应用层面做设置，执行大型SQL的时候设置一个较大的值，SQL执行完毕RESET一下参数，这样WORK_MEM的使用效率是最高的。刚开始就有点扯远了，今天我们的重点不是讨论NL和HASH JOIN的差异，而是带大家看看PG数据库的HASH JOIN执行计划中的一些容易被忽略的点，在查看执行计划的时候，如果能够比较好的抓住这些关注点，对于SQL优化来说很有帮助。

这种情况下，数据预热可以加速查询的同时，减少I/O操作的次数，从而提高系统的稳定性。不过用户并不认同我的观点，他们认为如果日常运维遇到了必须分析TOP SQL的时候往往就是遇到了十分严重的性能问题，对于他们这种金融服务企业，这个时候定位问题解决问题的时间是十分关键的，这时候就需要每个操作都有十分快的响应。需要注意的是，如果数据表的大小比较小，或者该数据表的查询不频繁，或者反过来说，某些特别热的小表，其数据大部分都在共享缓冲区中，那么进行数据预热的效果可能不太明显，反而会浪费系统资源。# 获取表所在的目录。

对于数据库参数来说，实际上不同的应用场景下的最佳调整方案是不同的，一般来说，设置合理的shared_buffers，以及优化好相关的而bgwriter，WAL，checkpoint，work_mem，VACUUM等相关的参数，就能够满足大多数应用的需求了。硬件资源不足的问题我们就不多加讨论了，这种情况一般会出现在CPU、IO等方面，在分析这方面问题的时候，需要关注R队列的长度是否超过CPU逻辑核数的2倍以上，对于IO来说，不仅仅要看IOPS/IO吞吐量等指标，更重要的是要看IO延时是否合理。

调整 wal_buffers 的值时，重要的是要考虑生成 WAL 数据的速率，增加 wal_buffers 的值有助于降低磁盘写入频率并提高性能，不过在普通的负载下，调整wal_buffers并不能看到数据库性能的提升，只有当WAL写入BUFFER的速度大于Walwriter写盘的速度的时候，加大wal_buffers才会有特别明显的性能提升。举个例子，如果你的物理内存是256GB，而你的常用设数据是100GB，那么设置一个128GB的shared_buffers有可能是比较好的配置。

这种智能化辅助的手段可以减轻DBA的工作负担，提高问题定位的正确性。于是我们想到了模仿Oracle的统计信息，以一定周期为单位（比如一周，半个月），滚动更新统计数据，采集指标的数据进行采样，构建常规运行的数据模型，然后根据这些数据模型，建立算力要求较低的算法，用于生产环境的指标异常检测。当时我们采用在生产系统中采样，并进行专家标注的方法构建分析中最为核心的异常检测算法，结合知识图谱定义的诊断路径，找出与某个故障现象相关的指标集，然后通过异常检测发现其中存在问题的子集，最后通过收敛算法，找到问题的根因。

分区表往往是优化大数据量业务的利器，分区表可以让每个表分区的数据量得到有效的控制，从而减少Seq Scan的成本，也可以降低表维护的成本。因此在POSTGRESQL的SQL优化工作中，一定要认真研究使用适当的索引类型来优化某个比较难以解决的问题，这种工作思路是其他数据库中所没有的，对于解决复杂的SQL性能问题十分有效。：确保SQL语句使用了合适的扫描方式。这些技巧可能很多都是总体性的，并不能直接应用于你的SQL之中，不过这些技巧如果应用得当，会让你的POSTGRESQL数据库的SQL性能得到极大的提升。

如果这张表上的数据量比较大，那么这种扫描方式可能会产生较大的IO，消耗较多的CPU资源，持续较长的时间。否则，将使用临时文件。不过我们要注意的是，有些时候，索引扫描的效率还不一定比顺序扫描高，比如某个扫描需要返回的行数较多，底层存储的顺序读性能远高于离散读，这种情况下，如果我们还一味追求索引扫描，那么可能会起到副作用。PG的扫描方式与Oracle等其他数据库类似，但也存在较大的不同，为了掌握好SQL语句优化的技术，我们首先要学会看SQL语句的执行计划，而看执行计划的最为基础的能力就是看懂每一步的扫描方式。

它是一个带有 WHERE 子句的索引。对于自定义索引，是PostgreSQL的一个更为强大的功能，我们可以通过编写插件的方式来实现自己的索引，根据自己的业务逻辑去对数据创建个性化的索引，从而提升应用访问的效率。实际上索引优化是应用驱动的，索引的优化一定要根据应用的特点来做专业的设计，否则索引有可能会成为应用系统出现问题的隐患。如果键值存在某种单边增长的趋势，那么创建BRIN索引后，根据这个键值做范围扫描的时候，可以根据BRIN索引找到所需要扫描的数据块，跳过其他的所有数据块，快速的将所需的数据扫描出来。

不过随着这些年PostgreSQL数据库的快速发展，每个版本对Vacuum的算法都有了极大的改善，在目前硬件性能极大提升与PostgreSQL Vacuum的算法的优化双重作用下，目前Vacuum对高负载数据库的影响也越来越低了。前阵子我在网上看到过一个某互联网汽车租赁公司的案例，他们的PostgreSQL数据库的AutoVacuum出现了严重的性能问题，租车订单表每天要消耗几百万个xid，而每天的autovacuum只能回收其中70%的xid，这意味着一个十分严重的问题，xid wraps的问题。

在Oracle 9iR2之前，所有执行计划都是在变量绑定之前完成的，从9.2开始,Oracle将执行计划的生成放到了变量绑定之后，这样就让执行计划的生成更为精准了，不过这也带来了另外一个问题，那就是SQL第一次执行时的变量成为生成执行计划的依据，因此CURSOR共享会导致存在多种最优执行计划的SQL语句的运行性能变得不稳定。在PostgreSQL的一个会话中，一条SQL的前五次执行，每次都会重新生成执行计划，这样就可以避免因为绑定变量的差异导致存在多种最优执行计划的问题无法被发现的问题出现。

分析的结果与专家的水平有关，有时候好像分析出问题了，也不一定是正确的，特别是对于一些亚健康问题，表现出来的时间很短，等到专家到现场的时候，这个现象已经无法重现了，用于分析的指标，日志等也很少，往往会不了了之。因为我今天要和大家探讨的就是一种构建PG运维经验，并利用运维经验的积累来实现运维能力提升的方法，我们把这个称之为“运维知识自动化”，这个运维知识自动化不同于“运维自动化”，运维自动化是实现运维中各种操作的自动化，比如部署自动化，变更自动化，迁移自动化，采集自动化，备份自动化等。

大家都知道，PG的每个事务都需要记录在CLOG中，而很多事务可能是只读事务或者空事务，如果每个空事务或者只读事务都分配了一个真实的transactionid，那么CLOG中就会产生大量的浪费，TransactionId也会浪费。不过其实PG的事务锁锁定的就是事务号，通过事务号实现锁的互斥操作实际上在大多数数据库里都是类似的，只是其他数据库都称之为事务锁，而PG成为针对TransactionId的对象锁，以Oracle为例，Oracle的事务锁TX锁实际上也是指向了一个事务。那么元组锁是怎么产生的呢？

如果你是一个Oracle DBA，那么你可能习惯于看dba_locks这样的视图，Oracle的锁划分的很细，有上百种锁，不过Oracle的锁机制的底层是统一的，都是Enqueue，所以从监控视图上，Oracle的所有锁都是统一的。如果从对象层面来看，需要进行串行化处理的对象层次十分复杂，正是因为PG锁考虑了对象级的多个层面的东西，把所有的需要串行化的需求都归纳到有限的几类锁中（相对于Oracle锁的种类有上百种），因此要想把PG的锁完全统一起来并不容易。对于锁的类型，不同的PG版本可能锁的种类略有不同。

创建表时要设置FILLFACTOR，应该写到表的WITH子句中，表创建后也可以使用ALTER TABLE命令去进行修改，不过和Oracle类似的是，对已经存在的表进行参数修改，只会影响修改后新建的数据块，老的数据块不会自动重组。在PostgreSQL数据库中，也有一个类似的参数，称为FILLFACTOR，这个参数控制PG数据库中的新的数据块插入数据时的限制，当某个数据块的使用率超过这个值的时候，该数据块不再接受新数据的插入，剩余的空间也是被用于UPDATE等需要额外空间的操作。

梳理知识图谱是这个工作中最难得一步，虽然PG的等待事件梳理不足Oracle的一半，不过因为我们对PG的研究还刚刚起步，接触到的使用案例也不够多，因此虽然我们可以借助开源项目的源码来理解一些等待事件的产生原因，但是实际上很多等待事件的内因并不简单，因为一段源码可能会在很多不同类型的场景中被使用，因此光是通过静态的源码还是不够的。我们完全可以通过对这些等待事件的梳理，形成知识图谱，然后利用知识图谱，实现对PG等待事件的深度分析。并通过等待事件的异常，发现系统存在的潜在问题，对严重的系统问题进行提前预警。

本文很长，有8000字，建议大家先收藏再阅读。本文全面介绍了PG shared buffer pool的结构和一些常见的性能优化点和优化策略。在很多技术特征和概念上，与Oracle数据库做了一定的对比，可以让OracleDBA通过参照更好的掌握PG的相关概念。数据库的内存架构对数据库的性能影响十分大，PostgreSQL这些年的发展十分快，其内存架构的核心部分除了共享池外，都和Oracle有一定的类似。下图是PostgreSQL内存架构的一张逻辑示意图。这些结构中，shared memory对Post

如果采用LINUX上的传统技术，ramfs等技术，将高速的内存访问接口转换为相对缓慢的文件系统接口，那么内存的性能要牺牲很多，恐怕就无法把非易失性内存的性能完全发挥出来了。使用PMEM优化PG数据库的两条技术路线可以参考上图，使用支持PMEM的文件系统存储PG数据库是最为简单的方案，利用PMDK提供的DAX技术，可以创建一个支持DAX高速访问的全新的内存文件系统，不需要修改PG数据库的代码，取消OS的文件缓冲就可以了。CHECKPOINT时间的减少，对于高并发写入数据库性能的提升是显而易见的。

上面的汇总数据是数据库启动以来或者pg_stat_statements_reset执行之后的累计值，你可以通过pg_stat_statements_reset先清除相关的数据，然后隔一段时间再来执行该SQL,也可以把这些数据定期采集到某张表中，过一段时间后进行对减后获得某个时间段内的增量值，从而更准确的了解某个时间段内数据库的TOP SQL的汇总情况。如果你只关心当前的数据库，那么你可以使用上面的语句，或者直接在where条件中输入某个数据库的名字，从而更为精准的分析你的应用系统的TOP SQL总体情况。

该阈值的计算是基于一些参数的设置的，这个设置可以是全局性的，也可以是针对某张表的（比如上面的storage参数），如autovacuum_vacuum_threshold，autovacuum_analyze_threshold，autovacuum_vacuum_scale_factor，和autovacuum_analyze_scale_factor。基于上面的原理，为某些大表设置合理的阈值，从而确保autovacuum在一种对业务系统影响尽可能小的平衡状态下运行，就可以确保业务系统长期稳定的运行。

我们可以看到，一个系统中的有些表的死元组比例十分高，有些表则很低。一个比较简单和常用的做法是，首先列出你的系统中的超过1GB的大表（这个阈值根据你的系统的特点去调整，我这里只是举例），然后分析其行数和死元组的数量，比例。甚至在给一个快递公司做一个执行计划稳定的优化的时候，我发现因为每天有几次大批量的数据写入，经常会导致某些时段中的表统计数据必然会导致错误的执行计划出现，因为应用中存在应用中动态拼凑SQL，导致所需的执行计划无法通过HINT固定，而白天业务高峰期经常对超大型的表做分析又风险太大。

大家回过头来再去看刚才那条索引创建语句的语法，WITH后面有一组参数，其中length是签名的长度，也就是位图的长度，这个参数必须是16的倍数。如果我们只是简单的了解了BLOOM索引的基础情况，没有经过合理的计算去采用较优化的参数，那么查询的性能也会受到很大的影响。，是查询字段在索引中存在的字段的位图之和。我们可以通过不断地尝试来获得这些参数地最佳值，不过这并不是最好的办法，如果我们了解Bloom Filter的数学原理，那么我们可以通过所需创建索引的表的基本信息去计算出这些参数来了。

不过这个物理读并不一定真正的从物理存储介质上读取，如果数据在LINUX的OS CACHE里，那么可以直接从CACHE中获取，这种物理读是很快的，如果OS CACHE没有命中，那么就只能真正的从物理存储介质中去读取了，相对于直接从内存中读取，这种物理读是较慢的。我今天提出几点优化的建议。除此之外，怕抖动的核心交易系统尽可能使用比较快的存储介质，比如NVME SSD，尽可能降低平均IO延时是从根本上降低IO抖动带来的问题的最便宜的方法，不要觉得SSD很贵，实际上在这里多花点钱可以在其他地方少花更多的钱。

而不同的索引类别，支持不同种类的数据访问方式，因此在给数据建索引的时候，不能盲目的去使用默认的方式创建索引（PostgreSQL数据库和其他大多数数据库一样，默认的模式创建B-TREE结构的索引）。如果我们使用普通的B-TREE索引会怎样呢，我们删除这个索引然后再创建新的索引，从创建索引的速度上我们就看出BRIN索引创建的时间比B-TREE索引要快得多，因为BRIN索引的大小要小很多。这样的语法的时候，如果我们在字段col1上创建一个索引是没用的，我们必须创建一个针对to_char(col1)的索引。

在X86时代，内存已经不少昂贵的硬件了，因此在配置PG服务器的时候，不要为了省几个内存的钱而让自己今后的运维陷入左右为难的境地是比较明智的选择。当然配置足够的物理内存，会让运维与优化更为简单一些。第四是针对DB CACHE的脏块写入进行OS方面的优化，对于较大规模的shared buffers，其脏块刷新的每个批次的数量会有所增加，使用buffered io的系统的脏块是先写到FILE CACH中的，针对FILE CACHE脏数据后台落盘的优化决定了数据库系统是否能够平稳运行，消除写盘高峰。

到时间来说说我们的观点了，对于PG的Double Buffering，网上的很多观点都有些偏颇，可能持这些观点的人面对的都是自己的系统，并没有关注过不同负载的数据库的需求。我们看到最上面的shared buffer是PG的主BUFFER，PG数据库使用bufferd io和OS的文件系统进行交互，因此PG数据库的读写都需要使用OS CACHE作为缓冲，读取的数据可以通过OS CACHE来进行加速，写入的数据先写入OS CACHE中，然后再通过fsync()来强制刷盘。这个开源插件，应该会有一些收获的。

最后一方面，PG开源的第三方工具和第三方生态产品较多，而且这些工具与Oracle的第三方工具、生态产品不同，如果不能很好的掌握这些工具和生态产品，会对PG数据库的运维产生比较大的影响。Oracle DBA转而学习PG数据库，实际上还是比较容易的，因为大型关系型数据库的基本概念是相通的，而且PG数据库因为没有了共享池和全局共享CURSOR这个超级复杂的机制，也要简单的多。关于留言，我简单说几句，因为怕麻烦，我没有开启留言讨论的公众浏览功能，因此你们给我的留言我都是可以看见的，不过其他朋友可能看不见。

最后一方面，PG开源的第三方工具和第三方生态产品较多，而且这些工具与Oracle的第三方工具、生态产品不同，如果不能很好的掌握这些工具和生态产品，会对PG数据库的运维产生比较大的影响。而PG数据库则不同，PG社区仅仅提供了一个RDBMS和一些必要的外围工具，剩下的应用所需要的功能都是其他的开源项目提供的。PG生态的发展虽然也和它的用户群体不断壮大有关，不过PG生态发展过程中，出现了一些与MYSQL生态发展不同的特征，PG数据库的发展的推动主力并不是互联网企业，而是企业用户。今天我来聊聊PG的生态。

较大的PAGETABLE不仅仅会占用过多的物理内存，也会让内存碎片化，同时访问较大的PAGETABLE还存在寻址开销增大的问题，因此对于使用较大shared buffer pool的PG数据库而言，如果会话数很多，那么不使用HUGEPAGE，回对数据库带来较大的性能下降，并可能在数据库启动一段时间之后因为内存碎片而出现内存访问引起的性能问题。在一般情况下，并不建议使用。要强调的一点是，对于绝大多数系统来说，过度的内存优化并不一定是必须的，只要保证足够的物理内存，就可以让PG数据库运行的十分好。

到时间来说说我们的观点了，对于PG的Double Buffering，网上的很多观点都有些偏颇，可能持这些观点的人面对的都是自己的系统，并没有关注过不同负载的数据库的需求。我们看到最上面的shared buffer是PG的主BUFFER，PG数据库使用bufferd io和OS的文件系统进行交互，因此PG数据库的读写都需要使用OS CACHE作为缓冲，读取的数据可以通过OS CACHE来进行加速，写入的数据先写入OS CACHE中，然后再通过fsync()来强制刷盘。这个开源插件，应该会有一些收获的。

在Oracle 9iR2之前，所有执行计划都是在变量绑定之前完成的，从9.2开始,Oracle将执行计划的生成放到了变量绑定之后，这样就让执行计划的生成更为精准了，不过这也带来了另外一个问题，那就是SQL第一次执行时的变量成为生成执行计划的依据，因此CURSOR共享会导致存在多种最优执行计划的SQL语句的运行性能变得不稳定。在PostgreSQL的一个会话中，一条SQL的前五次执行，每次都会重新生成执行计划，这样就可以避免因为绑定变量的差异导致存在多种最优执行计划的问题无法被发现的问题出现。