postgre优化的简单介绍

技术文档 - PostgreSQL 性能优化之 fsync 参数

目 录

总 结

PostgreSQL 通过调用系统 fsync() 或者其他使得事务内容写入到物理磁盘，这样可以保证操作系统或者数据库出现宕机后，仍然可以恢复到某一个一致性的状态。理论上讲 PostgreSQL 的 fsync 功能关闭，可以实现性能的提升，但是带来的影响就是需要承担数据的丢失，因为出现系统宕机或者数据库崩溃的时候有一些数据是没有落盘的。

本文将验证 fsync 参数的性能影响，以及参数关闭时数据库宕机后的影响。

数据量：1000W

fsync 参数：on

初始化表：user_info

pgbench 压测

pgbench 结果

pgbench 压测

pgbench 结果

数据量：1000W

fsync 参数：off

初始化表：user_info

pgbench 压测

pgbench 结果

pgbench 压测

pgbench 结果

通过对比发现，将 fsync 改为 off，对于读 TPS，参数 fsync 的影响不大，对于写 TPS，性能有一定提升。

现在验证参数关闭时数据库宕机后的影响

首先，使用将数据库性能跑起来

然后，模拟服务器断电

之后，启动数据库

提示信息：比致命错误还过分的错误。

结果：数据库无法启动，原因就是因为无法找到一个有效的 checkpoint 记录，这就是因为 fsync 设置为 off，由于数据库异常宕机导致。可以通过使用 pg_resetxlog 恢复数据库，但是会造成部分数据无法找回，数据丢失；也可以通过备份恢复，同样也会丢失部分数据。

fsync 参数对于读 TPS 的性能影响不大，对于写 TPS 的性能有一些影响，设置为 off，写 TPS 性能有一定提升，但是存在数据库宕机后无法正常启动，即使恢复后启动数据库，也会有数据丢失的很大风险。因此生产环境非必要时，不要将此参数设置为 off，还是使用默认的 on 比较稳妥。

相关问答

Q1: Postgresql 建索引性能优化

客户新上线了一套监控系统，可以监控到所有的执行慢的SQL，监控到有个批量任务导入大量数据后，进行索引创建。耗时需要几分钟，虽然不影响业务，但是需要整改。

这种问题的处理思路，都是大拆小，搞并发。

在测试环境，创建一个大表进行测试，创建大量的假数据。

创建表

随机字符串生成函数

生成大量的数据

经测试发现这种方法创建数据太慢了，改成使用COPY的方式创建数据。

排查发现random_string效率太低，生成一条数据接近1ms

重新创建表

写程序创建8600万条数据放在test.csv中

导入大量数据

测试基准数据

\timing 开启计时

耗时532824.434 ms

耗时385838.893 ms，提升 38%的性能，非常不错，但是远远不够。

仍然会出发告警。

重新创建表

创建分区

并发创建INDEX，并记录每个分区索引创建的开始时间和结束时间；

耗时 = 最大结束时间 - 最小开始时间 = 137 s，速度提升接近4倍。

顺序创建INDEX，并记录每个分区索引创建的开始时间和结束时间；

耗时 = 每个索引的耗时相加 = 457358.14 ms，速度提升 16.5%

顺序创建INDEX，优化并发

耗时 = 每个索引的耗时相加 = 292027.642 ms, 速度提升接近两倍。

在开启了并发参数的情况下，如果再叠加并发分区INDEX创建，会不会有惊喜呢？

并发创建INDEX，并记录每个分区索引创建的开始时间和结束时间；

耗时 = 最大结束时间 - 最小开始时间 = 141 s，速度还不如默认并发参数下的表现。应该是资源发生争抢导致的，通过系统监控发现CPU已经打满了。

分区并发是目前能想到的最优化手段了。

还需要结合查询的情况进行分析，分区会带来一点点的性能下降是否影响也需要考虑一下。

分区时目前能避开监控报警的唯一手段了，另外还钻了监控报警的空子。

客户的监控是基于单条语句的，单个分区的最大创建时间为47s，控制在分钟以内了。

Q2: PostgreSQL count 做了全表扫描 怎么优化

首先看你的全表扫描是否必须 如果不是，可以通过索引等方式避免全表扫描 如果必须，可以通过并行查询等进行优化

Q3: postgresql 查询优化是选择动态规划算法还是遗传算法

不是。遗传算法可以用于蕨类，但绝不限于此，实际它的应用极广，它是种优化算法，是种算法设计思想，比如贪吃法和动态规划，绝不会局限于某个应用领域。数据挖掘算法太窄了，像apriori,k-mean等算法都是针对关联规则挖掘和聚类提出的具体算法。

关于postgre优化和的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站。