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/con

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                                                     @H_403_41@tps = 624.747958 (including connections establishing)                                                @H_403_41@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                                                      @H_403_41@tps = 399.847446 (including connections establishing)                                                @H_403_41@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,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                                                      @H_403_41@tps = 370.510431@H_403_41@ (including connections establishing)                                                @H_403_41@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,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                                                      @H_403_41@tps = 292.556692 (including connections establishing)                                                @H_403_41@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,CURRENT_TIMESTAMP);        2.710619        END;

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

总结

以上是内存溢出为你收集整理的PostgreSQL数据库压力测试工具pgbench简单应用全部内容，希望文章能够帮你解决PostgreSQL数据库压力测试工具pgbench简单应用所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。