一个简单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还是有不小距离的,这里就不贴了。
 

 

 

 

原文链接:加载失败,请重新获取