转载

PostgreSQL 性能调优

批量导入性能优化

关闭自动提交

在批量插入数据时，如果每条数据都被自动提交，当中途出现系统故障时，不仅不能保障本次批量插入的数据一致性，而且由于有多次提交操作的发生，整个插入效率也会受到很大的打击。解决方法是，关闭系统的自动提交(SET AUTOCOMMIT = OFF)，并且在插入开始之前，显式的执行 BEGIN TRANSACTION 命令，在全部插入操作完成之后再执行 COMMIT 命令提交所有的插入操作。

使用 COPY 命令

使用 COPY 在一条命令里装载所有记录，而不是一系列的 INSERT 命令。COPY 命令是为装载数量巨大的数据行优化过的，它不像 INSERT 命令那样灵活，但是在装载大量数据时，系统开销也要少很多。注：因为 COPY 是单条命令，因此在填充表的时候就没有必要关闭自动提交了。

删除索引

如果你正在装载一个新创建的表，最快的方法是创建表，用 COPY 批量装载，然后创建表需要的任何索引。因为在已存在数据的表上创建索引比维护逐行增加要快。当然在缺少索引期间，其它有关该表的查询操作的性能将会受到一定的影响，唯一性约束也有可能遭到破坏。

删除外键约束

和索引一样，"批量地"检查外键约束比一行行检查更加高效。因此，我们可以先删除外键约束，装载数据，然后再重建约束。

临时增大 maintenance_work_mem

在装载大量数据时，临时增大 maintenance_work_mem 系统变量的值可以改进性能。这个系统参数可以提高CREATE INDEX 命令和 ALTER TABLE ADD FOREIGN KEY 命令的执行效率，但是它不会对 COPY 操作本身产生多大的影响。

临时增大 checkpoint_segments

临时增大 checkpoint_segments 系统变量的值也可以提高大量数据装载的效率。这是因为在向 PostgreSQL 装载大量数据时，将会导致检查点操作(由系统变量 checkpoint_timeout 声明)比平时更加频繁的发生。在每次检查点发生时，所有的脏数据都必须 flush 到磁盘上。通过提高 checkpoint_segments 变量的值，可以有效的减少检查点的数目。

设置为 UNLOGGED 表

PostgreSQL 的 unlogged table 是不记录 xlog 的，因此批量导入的时候 unlogged 表速度比 logged 表性能要快上一倍。但是， unlogged 表不是异常安全的：数据库崩溃或者异常关闭后， unlogged 表中的数据会被自动删减；另外， unlogged 表中的数据也不会备份到从服务器。任何 unlogged 表上创建的索引也是 unlogged 的。对于批量数据导入，我们可以临时设置表为 unlogged(ALTER TABLE tb SET UNLOGGED)，之后再将表重新设置为 logged(ALTER TABLE tb SET LOGGED)。

事后运行 VACUUM ANALYZE

在增加或者更新了大量数据之后，应该立即运行 VACUUM ANALYZE 命令，这样可以保证规划器得到基于该表的最新数据统计。换句话说，如果没有统计数据或者统计数据太过陈旧，那么规划器很可能会选择一个较差的查询规划，从而导致查询效率过于低下。

查询性能优化

关于 PostgreSQL 的性能参数，主要以下5个起重要作用:

shared_buffers

这是最重要的参数， PostgreSQL 通过 shared_buffers 和内核和磁盘打交道，因此应该尽量大，让更多的数据缓存在 shared_buffers 中。通常设置为实际 RAM 的 10% 是合理的，比如50000(400M),也有说法是 25% – 40%.

要在系统中设置 kernel.shamax 的值，该值决定了进程可调用最大共享内存数量。

简单的计算方法是： kernel.shmmax = postgres shared_buffers + 32MB
work_mem在 PostgreSQL 8.0之前叫做 sort_mem。PostgreSQL 在执行排序操作时，会根据 work_mem 的大小决定是否将一个大的结果集拆分为几个小的和 work_mem 差不多大小的临时文件。显然拆分的结果是降低了排序的速度。因此增加 work_mem 有助于提高排序的速度。通常设置为实际 RAM 的 2% – 4%，根据需要排序结果集的大小而定，比如 81920(80M)

注意：这是 per connection and per sort 的设定。如果有100个连接，每个连接有2个 sort 运算，那么需要的总内存是： 100 * 2 * work_mem。

利用 EXPLAIN ANALYZE 可以检查是否有足够的 work_mem。
effective_cache_size设置稍大，优化器更倾向使用索引扫描而不是顺序扫描，建议的设置为可用空闲内存的 25%，这里的可用空闲内存指的是主机物理内存在运行 pg 时的空闲值。
maintenance_work_mem这里定义的内存只是在 CREATE INDEX, VACUUM 等时用到，因此用到的频率不高，但是往往这些指令消耗比较多的资源，因此应该尽快让这些指令快速执行完毕：给 maintence_work_mem 大的内存，比如 512M(524288)
max_connections通常， max_connections 的目的是防止 max_connections * work_mem 超出了实际内存大小。比如，如果将 work_mem 设置为实际内存的 2% 大小，则在极端情况下，如果有50个查询都有排序要求，而且都使用2% 的内存，则会导致 swap 的产生，系统性能就会大大降低。
FSYNC vs ASYNCPostgreSQL 默认做 fsync，也就是说 PostgreSQL 会等待数据被写入硬盘，才会给 query返回成功的信号。如果设定 sync=no 关闭 fsync 的话， PostgreSQL 不会等待 WAL 写回硬盘，就直接返回 query 成功。通常这个会带来 15-25% 的性能提升.但是缺点就是，如果系统崩溃（断电， PostgreSQL 挂掉）的时候，你将有可能丢失最后那个 transcation. 不过这个并不会造成你系统的数据结构问题。如果说在系统出问题的时候丢失1-2笔数据是可以接受的，那么 25% 的性能提升是很可观的。
WAL设定fsync 可以选择 on 或者 off
wal_buffersWAL 的储存大小。default 是 64 kb。实验证明，设定这个值在 256 kb 到 1 MB 之间会提升效能。
wal_writer_delayWAL 检查 WAL 数据（回写）的间隔时间。值是毫秒(milliseconds)
Checkpoints

确保数据回写硬盘。

如果 checkpoint 运行频率高于checkpint_warning 值，dirty data page 会被 flushed 回硬盘。PostgreSQL 会在日志（log）中记录出来，通过观察log，可以来决定 checkpoint_segments 的设定。增加

checkpoint_segments 或者 checkpoint_timeout 可以有一定的效能提升。而唯一的坏处就是如果系统挂了，在重启的时需要多一点时间来回复（系统启动恢复期间数据库是不能用的）鉴于 PostgreSQL 很少挂掉，这个其实可以设定的很长（1天都可以）。

设定：
- checkpoint_segments：最多的wal log数量，到达后会激发 checkpoint，通常设置定为30
- checkpoint_timeout：一般设置15-20分钟，常的可以设定1天也没关系
- checkpoint_completion_target：这个保持不动就好。内建是0.5，意思就是每个 checkpoint 预计在下个 checkpoint 完成前的一半时间内完成。
- checkpoint_warning：如果checkpint速度快于这个时间，在log中记录。内建是30秒