PostgreSQL配置优化

淡淡的烟草味﹌ 2022-11-24 12:55 130阅读 0赞

*  PostgreSQL配置优化
    
     *   *  硬件和系统配置
         *  测试工具
         *  配置文件
         *  主要选项
         *  测试数据
         *  总结

### 硬件和系统配置 ###

<table> 
 <tbody> 
  <tr> 
   <td>操作系统</td> 
   <td>Ubuntu13.04</td> 
  </tr> 
  <tr> 
   <td>系统位数</td> 
   <td>64</td> 
  </tr> 
  <tr> 
   <td>CPU</td> 
   <td>Intel(R) Core(TM)2 Duo CPU</td> 
  </tr> 
  <tr> 
   <td>内存</td> 
   <td>4G</td> 
  </tr> 
  <tr> 
   <td>硬盘</td> 
   <td>Seagate ST2000DM001-1CH164</td> 
  </tr> 
  <tr> 
   <td>测试工具</td> 
   <td>PostgreSQL-9.1.11</td> 
  </tr> 
 </tbody> 
</table>

### 测试工具 ###

<table> 
 <tbody> 
  <tr> 
   <td>工具名称</td> 
   <td>pgbench</td> 
  </tr> 
  <tr> 
   <td>数据量</td> 
   <td>200W（整个数据库大小约为300M）</td> 
  </tr> 
  <tr> 
   <td>模拟客户端数</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>线程数</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>测试时间</td> 
   <td>60秒</td> 
  </tr> 
 </tbody> 
</table>

*  准备命令：pgbench -i -s 20 pgbenchdb
 *  测试命令：pgbench -r -j4 -c4 -T60 testdb

### 配置文件 ###

默认的配置配置文件是保存在/etc/postgresql/VERSION/main目录下的postgresql.conf文件

*  如果想查看参数修改是否生效，可以用psql连接到数据库后，用<show 选项名> 来查看。
 *  如果要修改shared\_buffers， 在ubuntu下可能需要执行命令<sysctl -w>Managing Kernel Resources

### 主要选项 ###

<table> 
 <tbody> 
  <tr> 
   <td>选项</td> 
   <td>默认值</td> 
   <td>说明</td> 
   <td>是否优化</td> 
   <td>原因</td> 
  </tr> 
  <tr> 
   <td>max_connections</td> 
   <td>100</td> 
   <td>允许客户端连接的最大数目</td> 
   <td>否</td> 
   <td>因为在测试的过程中，100个连接已经足够</td> 
  </tr> 
  <tr> 
   <td>fsync</td> 
   <td>on</td> 
   <td>强制把数据同步更新到磁盘</td> 
   <td>是</td> 
   <td>因为系统的IO压力很大，为了更好的测试其他配置的影响，把改参数改为off</td> 
  </tr> 
  <tr> 
   <td>shared_buffers</td> 
   <td>24MB</td> 
   <td>决定有多少内存可以被PostgreSQL用于缓存数据（推荐内存的1/4)</td> 
   <td>是</td> 
   <td>在IO压力很大的情况下，提高该值可以减少IO</td> 
  </tr> 
  <tr> 
   <td>work_mem</td> 
   <td>1MB</td> 
   <td>使内部排序和一些复杂的查询都在这个buffer中完成</td> 
   <td>是</td> 
   <td>有助提高排序等操作的速度，并且减低IO</td> 
  </tr> 
  <tr> 
   <td>effective_cache_size</td> 
   <td>128MB</td> 
   <td>优化器假设一个查询可以用的最大内存，和shared_buffers无关（推荐内存的1/2)</td> 
   <td>是</td> 
   <td>设置稍大，优化器更倾向使用索引扫描而不是顺序扫描</td> 
  </tr> 
  <tr> 
   <td>maintenance_work_mem</td> 
   <td>16MB</td> 
   <td>这里定义的内存只是被VACUUM等耗费资源较多的命令调用时使用</td> 
   <td>是</td> 
   <td>把该值调大，能加快命令的执行</td> 
  </tr> 
  <tr> 
   <td>wal_buffer</td> 
   <td>768kB</td> 
   <td>日志缓存区的大小</td> 
   <td>是</td> 
   <td>可以降低IO，如果遇上比较多的并发短事务，应该和commit_delay一起用</td> 
  </tr> 
  <tr> 
   <td>checkpoint_segments</td> 
   <td>3</td> 
   <td>设置wal log的最大数量数（一个log的大小为16M）</td> 
   <td>是</td> 
   <td>默认的48M的缓存是一个严重的瓶颈，基本上都要设置为10以上</td> 
  </tr> 
  <tr> 
   <td>checkpoint_completion_target</td> 
   <td>0.5</td> 
   <td>表示checkpoint的完成时间要在两个checkpoint间隔时间的N%内完成</td> 
   <td>是</td> 
   <td>能降低平均写入的开销</td> 
  </tr> 
  <tr> 
   <td>commit_delay</td> 
   <td>0</td> 
   <td>事务提交后，日志写到wal log上到wal_buffer写入到磁盘的时间间隔。需要配合commit_sibling</td> 
   <td>是</td> 
   <td>能够一次写入多个事务，减少IO，提高性能</td> 
  </tr> 
  <tr> 
   <td>commit_siblings</td> 
   <td>5</td> 
   <td>设置触发commit_delay的并发事务数，根据并发事务多少来配置</td> 
   <td>是</td> 
   <td>减少IO，提高性能</td> 
  </tr> 
 </tbody> 
</table>

### 测试数据 ###

*  测试的数据是运行3次，取平均值。
 *  关闭fsync是为了更好的体现出其他参数对PostgreSQL的影响。

<table> 
 <tbody> 
  <tr> 
   <td>参数</td> 
   <td>修改值</td> 
   <td>事务总数</td> 
   <td>tps(包括建立连接)</td> 
   <td>tps(不包括建立连接)</td> 
  </tr> 
  <tr> 
   <td>默认设置</td> 
   <td>&nbsp;</td> 
   <td>8464</td> 
   <td>140.999792</td> 
   <td>141.016182</td> 
  </tr> 
  <tr> 
   <td>fsync</td> 
   <td>off</td> 
   <td>92571</td> 
   <td>1479.969755</td> 
   <td>1480.163355</td> 
  </tr> 
  <tr> 
   <td>shared_buffers</td> 
   <td>1GB</td> 
   <td>100055</td> 
   <td>1635.759275</td> 
   <td>1635.977823</td> 
  </tr> 
  <tr> 
   <td>work_mem</td> 
   <td>10MB</td> 
   <td>101209</td> 
   <td>1665.804812</td> 
   <td>1666.04082</td> 
  </tr> 
  <tr> 
   <td>effective_cache_size</td> 
   <td>2GB</td> 
   <td>98209</td> 
   <td>1636.733152</td> 
   <td>1636.970271</td> 
  </tr> 
  <tr> 
   <td>maintenance_work_mem</td> 
   <td>512MB</td> 
   <td>92930</td> 
   <td>1548.029233</td> 
   <td>1548.223108</td> 
  </tr> 
  <tr> 
   <td>checkpoint_segments</td> 
   <td>32</td> 
   <td>195982</td> 
   <td>3265.995</td> 
   <td>3266.471064</td> 
  </tr> 
  <tr> 
   <td>checkpoint_completion_target</td> 
   <td>0.9</td> 
   <td>194390</td> 
   <td>3239.406493</td> 
   <td>3239.842596</td> 
  </tr> 
  <tr> 
   <td>wal_buffer</td> 
   <td>8MB</td> 
   <td>198639</td> 
   <td>3310.241458</td> 
   <td>3310.724067</td> 
  </tr> 
  <tr> 
   <td>恢复fsync</td> 
   <td>off</td> 
   <td>11157</td> 
   <td>185.883542</td> 
   <td>185.909849</td> 
  </tr> 
  <tr> 
   <td>commit_delay &amp;&amp; commit_siblings</td> 
   <td>10 &amp;&amp; 4</td> 
   <td>11229</td> 
   <td>187.103538</td> 
   <td>187.131747</td> 
  </tr> 
 </tbody> 
</table>

### 总结 ###

<table> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td>事务总数</td> 
   <td>tps(包括建立连接)</td> 
   <td>tps(不包括建立连接)</td> 
  </tr> 
  <tr> 
   <td>优化前</td> 
   <td>8464</td> 
   <td>140.999792</td> 
   <td>141.016182</td> 
  </tr> 
  <tr> 
   <td>优化后(fsync=on)</td> 
   <td>11229</td> 
   <td>187.103538</td> 
   <td>187.131747</td> 
  </tr> 
  <tr> 
   <td>优化后(fsync=off)</td> 
   <td>198639</td> 
   <td>3310.241458</td> 
   <td>3310.724067</td> 
  </tr> 
 </tbody> 
</table>

在fsync打开的情况下，优化后性能能够提升30%左右。因为有部分优化选项在默认的SQL测试语句中没有体现出它的优势，如果到实际测试中，提升应该不止30%。  
测试的过程中，主要的瓶颈就在系统的IO，如果需要减少IO的负荷，最直接的方法就是把fsync关闭，但是这样就会在掉电的情况下，可能会丢失部分数据。

作者：roddick621 发表于2013-12-26 9:21:16 [原文链接][Link 1]

阅读：0 评论：0 [查看评论][Link 2]

[Link 1]: http://blog.csdn.net/kyle__shaw/article/details/17576259
[Link 2]: http://blog.csdn.net/kyle__shaw/article/details/17576259#comments