PostgreSQL数据库压力测试工具pgbench简单应用

PG数据库提供了一款轻量级的压力测试工具叫pgbench,其实就是一个编译好后的扩展性的可执行文件。介绍如下。 
 环境： 
CentOS 5.7(final) 
PG:9.1.2 
Vmware 8.0 

数据库参数： max_connection=100 ,其他略，默认 

1.安装 

进入源码安装包,编译,安装

[postgres@localhost  ~]$ cd postgresql-9.1.2/contrib/pgbench/[postgres@localhost  pgbench]$ lltotal 164-rw-r--r--. 1 postgres postgres   538 Dec  1  2011 Makefile-rwxrwxr-x. 1 postgres postgres 50203 Apr 26 23:50 pgbench-rw-r--r--. 1 postgres postgres 61154 Dec  1  2011 pgbench.c-rw-rw-r--. 1 postgres postgres 47920 Apr 26 23:50 pgbench.o[postgres@localhost  pgbench]$make all[postgres@localhost  pgbench]$make install

安装完毕以后可以在bin文件夹下看到新生成的pgbench文件

[postgres@localhost  bin]$ ll $PGHOME/bin pgbench-rwxr-xr-x. 1 postgres postgres 50203 Jul  8 20:28 pgbench

2.参数介绍

[postgres@localhost  bin]$ pgbench --helppgbench is a benchmarking tool for PostgreSQL.Usage:  pgbench [OPTIONS]... [DBNAME]Initialization options:  -i           invokes initialization mode  -F NUM       fill factor  -s NUM       scaling factorBenchmarking options:  -c NUM       number of concurrent database clients (default: 1)  -C           establish new connection for each transaction  -D VARNAME=VALUE               define variable for use by custom script  -f FILENAME  read transaction script from FILENAME  -j NUM       number of threads (default: 1)  -l           write transaction times to log file  -M {simple|extended|prepared}               protocol for submitting queries to server (default: simple)  -n           do not run VACUUM before tests  -N           do not update tables "pgbench_tellers" and "pgbench_branches"  -r           report average latency per command  -s NUM       report this scale factor in output  -S           perform SELECT-only transactions  -t NUM       number of transactions each client runs (default: 10)  -T NUM       duration of benchmark test in seconds  -v           vacuum all four standard tables before testsCommon options:  -d           print debugging output  -h HOSTNAME  database server host or socket directory  -p PORT      database server port number  -U USERNAME  connect as specified database user  --help       show this help, then exit  --version    output version information, then exitReport bugs to .

3.初始化测试数据

[postgres@localhost  ~]$ pgbench -i pgbenchcreating tables...10000 tuples done.20000 tuples done.30000 tuples done.40000 tuples done.50000 tuples done.60000 tuples done.70000 tuples done.80000 tuples done.90000 tuples done.100000 tuples done.set primary key...NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_branches_pkey" for table "pgbench_branches"NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_tellers_pkey" for table "pgbench_tellers"NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_accounts_pkey" for table "pgbench_accounts"vacuum...done.[postgres@localhost  ~]$ psql -d pgbenchpsql (9.1.2)Type "help" for help.pgbench=# select count(1) from pgbench_accounts; count  -------- 100000(1 row)pgbench=# select count(1) from pgbench_branches; count -------     1(1 row)pgbench=# select count(1) from pgbench_history; count -------     0(1 row)pgbench=# select count(1) from pgbench_tellers; count -------    10(1 row)pgbench=# \d+ pgbench_accounts                Table "public.pgbench_accounts"  Column  |     Type      | Modifiers | Storage  | Description ----------+---------------+-----------+----------+------------- aid      | integer       | not null  | plain    |  bid      | integer       |           | plain    |  abalance | integer       |           | plain    |  filler   | character(84) |           | extended | Indexes:    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)Has OIDs: noOptions: fillfactor=100pgbench=# \d+ pgbench_branches                Table "public.pgbench_branches"  Column  |     Type      | Modifiers | Storage  | Description ----------+---------------+-----------+----------+------------- bid      | integer       | not null  | plain    |  bbalance | integer       |           | plain    |  filler   | character(88) |           | extended | Indexes:    "pgbench_branches_pkey" PRIMARY KEY, btree (bid)Has OIDs: noOptions: fillfactor=100pgbench=# \d+ pgbench_history                       Table "public.pgbench_history" Column |            Type             | Modifiers | Storage  | Description --------+-----------------------------+-----------+----------+------------- tid    | integer                     |           | plain    |  bid    | integer                     |           | plain    |  aid    | integer                     |           | plain    |  delta  | integer                     |           | plain    |  mtime  | timestamp without time zone |           | plain    |  filler | character(22)               |           | extended | Has OIDs: nopgbench=# \d+ pgbench_tellers                 Table "public.pgbench_tellers"  Column  |     Type      | Modifiers | Storage  | Description ----------+---------------+-----------+----------+------------- tid      | integer       | not null  | plain    |  bid      | integer       |           | plain    |  tbalance | integer       |           | plain    |  filler   | character(84) |           | extended | Indexes:    "pgbench_tellers_pkey" PRIMARY KEY, btree (tid)Has OIDs: noOptions: fillfactor=100

说明： 
a.这里使用的是默认的参数值,带-s 参数时可指定测试数据的数据量,-f可以指定测试的脚本,这里用的是默认脚本 
b.不要在生产的库上做，新建一个测试库，当生产上有同名的测试表时将被重置 

4.测试过程 

4.1 1个session

[postgres@localhost  ~]$ nohup pgbench -c 1 -T 20 -r pgbench > file.out  2>&1[postgres@localhost  ~]$ more file.out nohup: ignoring inputstarting vacuum...end.transaction type: TPC-B (sort of)scaling factor: 1query mode: simplenumber of clients: 1number of threads: 1duration: 20 snumber of transactions actually processed: 12496                                                                                     tps = 624.747958 (including connections establishing)                                                                                tps = 625.375564 (excluding connections establishing)statement latencies in milliseconds:        0.005299        \set nbranches 1 * :scale        0.000619        \set ntellers 10 * :scale        0.000492        \set naccounts 100000 * :scale        0.000700        \setrandom aid 1 :naccounts        0.000400        \setrandom bid 1 :nbranches        0.000453        \setrandom tid 1 :ntellers        0.000430        \setrandom delta -5000 5000        0.050707        BEGIN;        0.200909        UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;        0.098718        SELECT abalance FROM pgbench_accounts WHERE aid = :aid;        0.111621        UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;        0.107297        UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;        0.095156        INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);        0.919101        END;

4.2 30个session

[postgres@localhost  ~]$nohup pgbench -c 30 -T 20 -r pgbench > file.out  2>&1[postgres@localhost  ~]$ more file.out nohup: ignoring inputstarting vacuum...end.transaction type: TPC-B (sort of)scaling factor: 1query mode: simplenumber of clients: 30number of threads: 1duration: 20 snumber of transactions actually processed: 8056                                                                                      tps = 399.847446 (including connections establishing)                                                                                tps = 404.089024 (excluding connections establishing)statement latencies in milliseconds:    0.004195        \set nbranches 1 * :scale    0.000685        \set ntellers 10 * :scale    0.000887        \set naccounts 100000 * :scale    0.000805        \setrandom aid 1 :naccounts    0.000656        \setrandom bid 1 :nbranches    0.000523        \setrandom tid 1 :ntellers    0.000499        \setrandom delta -5000 5000    0.515565        BEGIN;    0.865217        UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;    0.307207        SELECT abalance FROM pgbench_accounts WHERE aid = :aid;    50.543371       UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;    19.210089       UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;    0.384190        INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);    2.116383        END;

4.3 50个session

[postgres@localhost  ~]$nohup pgbench -c 50 -T 20 -r pgbench > file.out  2>&1[postgres@localhost  ~]$ more file.out nohup: ignoring inputstarting vacuum...end.transaction type: TPC-B (sort of)scaling factor: 1query mode: simplenumber of clients: 50number of threads: 1duration: 20 snumber of transactions actually processed: 7504                                                                                      tps = 370.510431 (including connections establishing)                                                                               tps = 377.964565 (excluding connections establishing)statement latencies in milliseconds:        0.004291        \set nbranches 1 * :scale        0.000769        \set ntellers 10 * :scale        0.000955        \set naccounts 100000 * :scale        0.000865        \setrandom aid 1 :naccounts        0.000513        \setrandom bid 1 :nbranches        0.000580        \setrandom tid 1 :ntellers        0.000522        \setrandom delta -5000 5000        0.604671        BEGIN;        1.480723        UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;        0.401148        SELECT abalance FROM pgbench_accounts WHERE aid = :aid;        104.713566      UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;        21.562787       UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;        0.412209        INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);        2.243497        END;

4.4 100个session 

超过100个会报错，因为数据库当前设置最大session是100

[postgres@localhost  ~]$ nohup pgbench -c 100 -T 20 -r pgbench> file.out  2>&1[postgres@localhost  ~]$ more file.out nohup: ignoring inputstarting vacuum...end.transaction type: TPC-B (sort of)scaling factor: 1query mode: simplenumber of clients: 100number of threads: 1duration: 20 snumber of transactions actually processed: 6032                                                                                      tps = 292.556692 (including connections establishing)                                                                                tps = 305.595090 (excluding connections establishing)statement latencies in milliseconds:        0.004508        \set nbranches 1 * :scale        0.000787        \set ntellers 10 * :scale        0.000879        \set naccounts 100000 * :scale        0.001620        \setrandom aid 1 :naccounts        0.000485        \setrandom bid 1 :nbranches        0.000561        \setrandom tid 1 :ntellers        0.000656        \setrandom delta -5000 5000        3.660809        BEGIN;        4.198062        UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;        1.727076        SELECT abalance FROM pgbench_accounts WHERE aid = :aid;        281.955832      UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;        27.054125       UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;        0.524155        INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);        2.710619        END;

5.说明 

我们主要关心的是最后的输出报告中的TPS值，里面有两个,一个是包含网络开销(including),另一个是不包含网络开销的(excluding),这个值是反映的每秒处理的事务数，反过来也可以查出每个事务数所消耗的平均时间，一般认为能将硬件用到极致，速度越快越好。 

参考：http://www.postgresql.org/docs/9.1/static/pgbench.html