PostgreSQL测试工具PGbench

2014.04.08 | Comments

pgbench 是一个简单的给 PostgreSQL 做性能测试的程序。它反复运行同样的 SQL 命令序列,可能是在多个并发数据库会话上头,然后检查平均的事务速度(每秒的事务数 tps)。缺省的时候,pgbench 测试一个(松散的)接近 TPC-B 的情况,每个事务包括五个 SELECT,UPDATE,和 INSERT命令。不过,我们可以很轻松地使用自己的事务脚本文件来实现其它情况。

典型的输出看上去会是这样:

transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 85.184871 (including connections establishing)
tps = 85.296346 (excluding connections establishing)

头四行只是报告一些最重要的参数设置。跟着的一行报告完成的事务数和期望完成的事务数(后者只是客户端数乘以事务数);这两个会相等,除非在完成之前运行就失败了。最后两行报告 TPS 速率,分别有计算启动数据库会话时间和不计算启动会话时间的。

使用环境:

在比较新的9.1,9.2,9.3数据库的发行版本中,pgbench是在安装contrib包时直接编译的,可以在postgres的bin目录下找到该命令,如果没有发现该命令可以在安装contrib的目录下找到pgbench的源码文件包,编译一下就可以使用。

1. pgbench测试库初始化

postgres$ pgbench --help                 # 和postgres其他命令的使用方式一样,--help获取命令使用方式的简单介绍
postgres$ createdb pgbench               # 创建测试库
postgres$ pgbench -i pgbench             # 初始化测试库

默认会在测试库中建4张表pgbench_accountspgbench_branchespgbench_historypgbench_tellers 。当然也可以自己建表,自己写测试脚本,这四张表只是默认的测试脚本会用到。

pgbench在建默认库时 -s 参数设定测设表的大小,默认参数是1 。pgbench_accounts 总行数是10W,-s后面接具体数值如100,则pgbench_accounts中的测试数据将达到1千万行

2. 默认的测试脚本介绍

默认的测试脚本可以在官方文档中找到(新的版本中不指定模板就会使用默认模板)

$ cat test.sql
\set nbranches 1 * :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
\setrandom bid 1 :nbranches
\setrandom tid 1 :ntellers
\setrandom delta -5000 5000
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;

脚本说明:

可以看到脚本中的一个事物包含了update,select,insert操作,不同的操作起到不同的测试目的

  • (1)UPDATE pgbench_accounts:作为最大的表,起到促发磁盘I/O的作用。
  • (2)SELECT abalance:由于上一条UPDATE语句更新一些信息,存在于缓存内用于回应这个查询。
  • (3)UPDATE pgbench_tellers:职员的数量比账号的数量要少得多,所以这个表也很小,并且极有可能存在于内存中。
  • (4)UPDATE pgbench_branches:作为更小的表,内容被缓存,如果用户的环境是数量较小的数据库和多个客户端时,对其锁操作可能会成为性能的瓶颈。
  • (5)INSERT INTO pgbench_history:history表是个附加表,后续并不会进行更新或查询操作,而且也没有任何索引。相对于UPDATE语句,对其的插入操作对磁盘的写入成本也很小。

3. 测试结果说明

postgres$ pgbench -c 15 -t 300 pgbench -r -f test.sql             #执行命令
starting vacuum...end.
transaction type: Custom query
scaling factor: 1
query mode: simple 
number of clients: 15                                            #-c参数控制并发量
number of threads: 1                                                    
number of transactions per client: 300                           #每个客户端执行事务的数量
number of transactions actually processed: 4500/4500             #总执行量
tps = 453.309203 (including connections establishing)            #tps每秒钟处理的事务数包含网络开销      
tps = 457.358998 (excluding connections establishing)            #不包含网络开销
statement latencies in milliseconds:                             #带-r的效果,每个客户端事务具体的执行时间,单位是毫秒
0.005198 \set nbranches 1 * :scale                               
0.001144 \set ntellers 10 * :scale
0.001088 \set naccounts 100000 * :scale                     
0.001400 \setrandom aid 1 :naccounts
0.000814 \setrandom bid 1 :nbranches
0.000929 \setrandom tid 1 :ntellers
0.000981 \setrandom delta -5000 5000
0.613757 BEGIN;
1.027969 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.754162 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
14.167980 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
13.587156 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.582075 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
1.628262 END;

默认的基准测试给出了一个指标TPS,同样的测试参数,tps的值越高,相对来说服务器的性能越好。上面的测试由于数据量的问题,表的内容全部缓存进了内存,磁盘io对上面的结果影响较小。

4. 自定义测试环境

在实际的应用中测试可以自己定义测试环境,模拟生产需求。

测试举例

pgbench=# create table pg_test (a1 serial,a2 int,a3 varchar(20),a4 timestamp);                        #创建测试表
postgres$cat pg_test.sql
pgbench=# insert into pg_test(a2,a3,a4) select (random()*(2*10^5)),substr('abcdefghijklmnopqrstuvwxyz',1, (random()*26)::integer),now();
                                                                                                      #每个事务插入一条数据 
postgres$pgbench -c 90 -T 10 pgbench -r -f pg_test.sql                                                   #90个并发测试每秒插入的数据量

测试结果截取:

number of transactions actually processed: 20196             #10秒钟90并发用户共插入20196条数据,每条数据插入费时42ms,平均每秒插入2000条数据 
tps = 1997.514876 (including connections establishing)
tps = 2119.279239 (excluding connections establishing)
statement latencies in milliseconds:
42.217948
 
pgbench=# select count(*) from pg_test;
count 
-------
20196

5. pgbench在参数调节上的辅助使用

简单举例:work_mem

postgres=# show work_mem ;                                              #数据库当前的work_mem
work_mem 
----------
1MB

查询样本:

postgres$cat select.sql
SELECT customerid FROM customers ORDER BY zip;                          #orders表是一张postgres样例表,样例库全名dellstore2
postgres$pgbench -c 90 -T 5 pgbench -r -f select.sql                    #多用户并发做单表排序操作单个事务执行的时间可能会很大,但是平均事务执行时间和单个用户的执行时间差距没那么明显。

执行结果截取

number of clients: 90
number of threads: 1
duration: 5 s
number of transactions actually processed: 150
tps = 26.593887 (including connections establishing)
tps = 27.972988 (excluding connections establishing)
statement latencies in milliseconds:
3115.754673 SELECT customerid FROM customers ORDER BY zip;

测试环境相同调节work_mem参数为2M试试

number of clients: 90
number of threads: 1
duration: 5 s
number of transactions actually processed: 243
tps = 44.553026 (including connections establishing)
tps = 47.027276 (excluding connections establishing)
statement latencies in milliseconds:
1865.636761 SELECT customerid FROM customers ORDER BY zip;             #5s内事务执行的总量明显增加一共做了243次单表排序

原因分析,由于排序操作会关系到work_mem,排序操作能全在缓存中进行当然速度会明显加快,查看执行计划

postgres=# explain analyze SELECT customerid FROM customers ORDER BY zip;
QUERY PLAN 

--------------------------------------------------------------------------------------------
Sort (cost=2116.77..2166.77 rows=20000 width=8) (actual time=42.536..46.117 rows=20000 loo
ps=1)
Sort Key: zip
Sort Method: external sort Disk: 352kB
-> Seq Scan on customers (cost=0.00..688.00 rows=20000 width=8) (actual time=0.013..8.9
42 rows=20000 loops=1)
Total runtime: 48.858 ms

由上面的执行计划可以看出在work_mem大小为1M的时候排序一共需要1.352M空间做排序,所以加大work_mem参数排序速度明显增加。

这只是个简单的例子,work_mem的大小调节还有很多其他方面要考虑的,比如在高并发的情况下,需要为每个用户分配同样大小的排序空间,会占用大量的内存空间。参数调节在任何时候保持一个均衡才是应该考虑的。

参考文章


原创文章,转载请注明: 转载自JavaChen Blog,作者:JavaChen
本文链接地址:http://blog.javachen.com/2014/04/08/a-benchmark-tool-on-postgresql.html
本文基于署名2.5中国大陆许可协议发布,欢迎转载、演绎或用于商业目的,但是必须保留本文署名和文章链接。 如您有任何疑问或者授权方面的协商,请邮件联系我。