PostgreSQL 性能调优

   <h2>批量导入性能优化</h2>    <h3>关闭自动提交</h3>    <p>在批量插入数据时，如果每条数据都被自动提交，当中途出现系统故障时，不仅不能保障本次批量插入的数据一致性，而且由于有多次提交操作的发生，整个插入效率也会受到很大的打击。解决方法是，关闭系统的自动提交(SET AUTOCOMMIT = OFF)，并且在插入开始之前，显式的执行 BEGIN TRANSACTION 命令，在全部插入操作完成之后再执行 COMMIT 命令提交所有的插入操作。</p>    <h3>使用 COPY 命令</h3>    <p>使用 COPY 在一条命令里装载所有记录，而不是一系列的 INSERT 命令。COPY 命令是为装载数量巨大的数据行优化过的，它不像 INSERT 命令那样灵活，但是在装载大量数据时，系统开销也要少很多。注：因为 COPY 是单条命令，因此在填充表的时候就没有必要关闭自动提交了。</p>    <h3>删除索引</h3>    <p>如果你正在装载一个新创建的表，最快的方法是创建表，用 COPY 批量装载，然后创建表需要的任何索引。因为在已存在数据的表上创建索引比维护逐行增加要快。当然在缺少索引期间，其它有关该表的查询操作的性能将会受到一定的影响，唯一性约束也有可能遭到破坏。</p>    <h3>删除外键约束</h3>    <p>和索引一样，"批量地"检查外键约束比一行行检查更加高效。因此，我们可以先删除外键约束，装载数据，然后再重建约束。</p>    <h3>临时增大 maintenance_work_mem</h3>    <p>在装载大量数据时，临时增大 maintenance_work_mem 系统变量的值可以改进性能。这个系统参数可以提高CREATE INDEX 命令和 ALTER TABLE ADD FOREIGN KEY 命令的执行效率，但是它不会对 COPY 操作本身产生多大的影响。</p>    <h3>临时增大 checkpoint_segments</h3>    <p>临时增大 checkpoint_segments 系统变量的值也可以提高大量数据装载的效率。这是因为在向 PostgreSQL 装载大量数据时，将会导致检查点操作(由系统变量 checkpoint_timeout 声明)比平时更加频繁的发生。在每次检查点发生时，所有的脏数据都必须 flush 到磁盘上。通过提高 checkpoint_segments 变量的值，可以有效的减少检查点的数目。</p>    <h3>设置为 UNLOGGED 表</h3>    <p>PostgreSQL 的 unlogged table 是不记录 xlog 的，因此批量导入的时候 unlogged 表速度比 logged 表性能要快上一倍。但是， unlogged 表不是异常安全的：数据库崩溃或者异常关闭后， unlogged 表中的数据会被自动删减；另外， unlogged 表中的数据也不会备份到从服务器。任何 unlogged 表上创建的索引也是 unlogged 的。对于批量数据导入，我们可以临时设置表为 unlogged(ALTER TABLE tb SET UNLOGGED)，之后再将表重新设置为 logged(ALTER TABLE tb SET LOGGED)。</p>    <h3>事后运行 VACUUM ANALYZE</h3>    <p>在增加或者更新了大量数据之后，应该立即运行 VACUUM ANALYZE 命令，这样可以保证规划器得到基于该表的最新数据统计。换句话说，如果没有统计数据或者统计数据太过陈旧，那么规划器很可能会选择一个较差的查询规划，从而导致查询效率过于低下。</p>    <h2>查询性能优化</h2>    <p>关于 PostgreSQL 的性能参数，主要以下5个起重要作用:</p>    <ol>     <li> <p>shared_buffers</p> <p>这是最重要的参数， PostgreSQL 通过 shared_buffers 和内核和磁盘打交道，因此应该尽量大，让更多的数据缓存在 shared_buffers 中。通常设置为实际 RAM 的 10% 是合理的，比如50000(400M),也有说法是 25% – 40%.</p> <p>要在系统中设置 kernel.shamax 的值，该值决定了进程可调用最大共享内存数量。</p> <p>简单的计算方法是： kernel.shmmax = postgres shared_buffers + 32MB</p> </li>     <li> <p>work_mem在 PostgreSQL 8.0之前叫做 sort_mem。PostgreSQL 在执行排序操作时，会根据 work_mem 的大小决定是否将一个大的结果集拆分为几个小的和 work_mem 差不多大小的临时文件。显然拆分的结果是降低了排序的速度。因此增加 work_mem 有助于提高排序的速度。通常设置为实际 RAM 的 2% – 4%，根据需要排序结果集的大小而定，比如 81920(80M)</p> <p>注意：这是 <strong>per connection and per sort</strong> 的设定。如果有100个连接，每个连接有2个 sort 运算，那么需要的总内存是： 100 * 2 * work_mem。</p> <p>利用 EXPLAIN ANALYZE 可以检查是否有足够的 work_mem。</p> </li>     <li> <p>effective_cache_size设置稍大，优化器更倾向使用索引扫描而不是顺序扫描，建议的设置为可用空闲内存的 25%，这里的可用空闲内存指的是主机物理内存在运行 pg 时的空闲值。</p> </li>     <li> <p>maintenance_work_mem这里定义的内存只是在 CREATE INDEX, VACUUM 等时用到，因此用到的频率不高，但是往往这些指令消耗比较多的资源，因此应该尽快让这些指令快速执行完毕：给 maintence_work_mem 大的内存，比如 512M(524288)</p> </li>     <li> <p>max_connections通常， max_connections 的目的是防止 max_connections * work_mem 超出了实际内存大小。比如，如果将 work_mem 设置为实际内存的 2% 大小，则在极端情况下，如果有50个查询都有排序要求，而且都使用2% 的内存，则会导致 swap 的产生，系统性能就会大大降低。</p> </li>     <li> <p>FSYNC vs ASYNCPostgreSQL 默认做 fsync，也就是说 PostgreSQL 会等待数据被写入硬盘，才会给 query返回成功的信号。如果设定 sync=no 关闭 fsync 的话， PostgreSQL 不会等待 WAL 写回硬盘，就直接返回 query 成功。通常这个会带来 15-25% 的性能提升.但是缺点就是，如果系统崩溃（断电， PostgreSQL 挂掉）的时候，你将有可能丢失最后那个 transcation. 不过这个并不会造成你系统的数据结构问题。如果说在系统出问题的时候丢失1-2笔数据是可以接受的，那么 25% 的性能提升是很可观的。</p> </li>     <li> <p>WAL设定fsync 可以选择 on 或者 off</p> </li>     <li> <p>wal_buffersWAL 的储存大小。default 是 64 kb。 实验证明， 设定这个值在 256 kb 到 1 MB 之间会提升效能。</p> </li>     <li> <p>wal_writer_delayWAL 检查 WAL 数据（回写）的间隔时间。值是毫秒(milliseconds)</p> </li>     <li> <p>Checkpoints</p> <p>确保数据回写硬盘。</p> <p>如果 checkpoint 运行频率高于checkpint_warning 值，dirty data page 会被 flushed 回硬盘。PostgreSQL 会在日志（log）中记录出来，通过观察log，可以来决定 checkpoint_segments 的设定。增加</p> <p>checkpoint_segments 或者 checkpoint_timeout 可以有一定的效能提升。而唯一的坏处就是如果系统挂了，在重启的时需要多一点时间来回复（系统启动恢复期间数据库是不能用的）鉴于 PostgreSQL 很少挂掉，这个其实可以设定的很长（1天都可以）。</p> <p>设定：</p>      <ul>       <li>checkpoint_segments： 最多的wal log数量，到达后会激发 checkpoint，通常设置定为30</li>       <li>checkpoint_timeout： 一般设置15-20分钟，常的可以设定1天也没关系</li>       <li>checkpoint_completion_target： 这个保持不动就好。内建是0.5，意思就是每个 checkpoint 预计在下个 checkpoint 完成前的一半时间内完成。</li>       <li>checkpoint_warning： 如果checkpint速度快于这个时间，在log中记录。内建是30秒</li>      </ul> </li>    </ol>    <p>Reference：</p>    <p><a href="/misc/goto?guid=4959674291705790224" rel="nofollow,noindex">PostgreSQL Performance Optimization</a></p>    <p><a href="/misc/goto?guid=4959674291787345991" rel="nofollow,noindex">PostgreSQL 优化数据的批量插入</a></p>    <p> </p>    <p>来自： <a href="/misc/goto?guid=4959674291871833790" rel="nofollow">http://www.mutouxiaogui.cn/blog/?p=413</a></p>    <p> </p>