postgresql有话的简单介绍

如何优化postgreSQL中的删除语句，好慢

The usual advice when you complain about slow bulk deletions in postgresql is "make sure the foreign keys (pointing to the table you are deleting from) are indexed". This is because postgresql doesn't create the indexes automatically for all the foreign keys (FK) which can be considered a degree of freedom or a nuisance, depends how you look at it. Anyway, the indexing usually solves the performance issue. Unless you stumble upon a FK field that is not indexable. Like I did.

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，控制在分钟以内了。

技术文档 - 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 比较稳妥。

SQL语句优化，使用postgresql数据库，查询下面sql,需要20多分钟：

postgresql（8.2）的配置文件中有一个参数log_min_duration_statement，意思是只log执行时间大于设定值的语句，如果设为0，表示log所有语句；如果设为-1，表示不log任何语句。

看起来，这个配置选项对性能的调整是很有用的，比如可以设置：

log_min_duration_statement = 1000

则只log执行时间大于1s的语句，重点优化这些sql语句就好了。

然而，奇怪的，这个选项不太容易生效！经过反复试验，原来需要如下配置：

#debug_print_parse = off

#debug_print_rewritten = off

#debug_print_plan = off

#debug_pretty_print = off

log_connections = off

#log_disconnections = off

log_duration = off

log_line_prefix = '%t [%p]: [%l-1] ' # Special values:

# %u = user name

# %d = database name

# %r = remote host and port

# %h = remote host

# %p = PID

# %t = timestamp (no milliseconds)

# %m = timestamp with milliseconds

# %i = command tag

# %c = session id

# %l = session line number

# %s = session start timestamp

# %x = transaction id

# %q = stop here in non-session

# processes

# %% = '%'

# e.g. '%u%%%d '

log_statement = 'none' # none, mod, ddl, all

#log_statement = 'all' # none, mod, ddl, all

#log_hostname = off

注意看上面的其中两个选项的设置：

log_duration = off

log_statement = 'none'

这两个选项的意思是不log任何sql语句和执行时间，但是恰恰是关闭了这两个，log_min_duration_statement才会生效！可能postgresql内部 对这两个选项做了“互斥”处理吧。