一个简单PGSQL调优的例子

         这几天，在写一个关于PGSQL性能的测试报告。

        今天找了台顶配的R720做测试。R720配置如下：E5-2650 v2两颗，128G内存，磁盘为两块SATA盘做RAID0 拼出来的一个T的磁盘。

        之前同事在上面测试mysql的QPS大概是20W多，具体不详。

        本着简单试试的想法，源码编译安装了pgsql，pgsql的配置如下：

         

max_connections = 512
shared_buffers = 30720MB
maintenance_work_mem = 512MB
vacuum_cost_delay = 10
vacuum_cost_limit = 10000               # 1-10000 credits
bgwriter_delay = 10ms                   # 10-10000ms between rounds
bgwriter_lru_maxpages = 1000            # 0-1000 max buffers written/round
wal_level = hot_standby 
wal_writer_delay = 10ms         # 1-10000 milliseconds
checkpoint_segments = 256               # in logfile segments, min 1, 16MB each
checkpoint_timeout = 10min              # range 30s-1 

   基本上都是经验值。shared_buffer设置主要是根据建议设置为内存的20%上下

 

   然后使用pgbenc生成了大概60个G的数据,其中最大的pgbench_acount 表中会有4亿条数据：

pgbench -i -s 4000

   测试QPS时，按照如下方式直接运行pgbench。

pgbench -S -M prepared -c N -j N -T 60 postgres

其中：-S选项指定只执行select，也就是只执行

SELECT abalance FROM pgbench_accounts WHERE aid = $1

 这样的SQL，pebench_acounts是刚才提到的那张大表，对应一个星形模型中的那个大表，里面有4亿条数据，表的主键是aid这个字段，查询的参数是随机生成的；-M prepared是使用预编译的方式执行SQL,避免重复解析SQL；N是连接数和模拟的客户端，在这里是每个客户端使用一个连接； -T 60指定连续执行60秒。

       在启动数据库之后，先用上面的命令运行了300秒，来预热数据。然后再进行实际的测试。

      在测试中，发现N在增大到128之后，增大N无法明显获得更大的QPS，如是将N设置为128。但是跑出来的结果让人感觉还行，快20W了：

 

查看了磁盘的IO，发现不断的有读的请求，于是直接查看数据数据库的block命中率，发现命中率不高，不到90%：

 感觉主要是没有缓存住所有热数据，有潜力可挖，于是试着将shared_buffer调整到了50G继续测试，QPS到了26W，而命中率也到了96%。但是，发现IO还是偶尔会达到10M/s。

  于是，将shared_buffer调整到了60G，这基本上应该能缓存住所有的数据了。结果TPS到了34W多，命中率也到了快100%：


     如果没有发生checkpoint，实际测试中可以达到35W多的QPS，这里就不贴了。

    明显，这个已经是最理想的情况了。数据都在shared buffer里面了。如果还要进一步的提高QPS，则需要更多更强的CPU了。当然，继续调高shared buffer也没有必要了，因为实际的数据就只有60G了。

   另外，拿着update和insert操作测试过，数据不是很理想，主要是因为瓶颈在IO能力上，毕竟SATA盘距离实际使用的SSD还是有不小距离的，这里就不贴了。