PostgreSQL 性能调优

ZellaGoulet 8年前
   <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>