PostgreSQL数据库提供了一款轻量级的压力测试工具叫pgbench,其实就是一个编译好后的扩展性的可执行文件。介绍如下。
环境:
CentOS 6.4(final)
PG:9.1.2
数据库参数: max_connection=100 ,其他略,默认
1.安装
进入源码安装包,编译,安装
a.这里使用的是默认的参数值,带-s 参数时可指定测试数据的数据量,-f可以指定测试的脚本,这里用的是默认脚本
[postgres@localhost ~]$ nohup pgbench -c 1 -T 20 -r pgbench > file.out 2>&1
[postgres@localhost ~]$ more file.out
nohup: ignoring input
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 20 s
number 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 input
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 30
number of threads: 1
duration: 20 s
number 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 input
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 50
number of threads: 1
duration: 20 s
number 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 input
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 1
duration: 20 s
number 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),这个值是反映的每秒处理的事务数,反过来也可以查出每个事务数所消耗的平均时间,一般认为能将硬件用到极致,速度越快越好。
本篇文章来源于 Linux公社网站(www.linuxidc.com) 原文链接:http://www.linuxidc.com/Linux/2012-07/64794p2.htm
环境:
CentOS 6.4(final)
PG:9.1.2
数据库参数: max_connection=100 ,其他略,默认
1.安装
进入源码安装包,编译,安装
[postgres@localhost ~]$ cd postgresql-9.1.2/contrib/pgbench/ [postgres@localhost pgbench]$ ll total 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 --help
pgbench is a benchmarking tool for PostgreSQL.
Usage:
pgbench [OPTIONS]... [DBNAME]
Initialization options:
-i invokes initialization mode
-F NUM fill factor
-s NUM scaling factor
Benchmarking 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 tests
Common 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 exit
Report bugs to .
3.初始化测试数据
前提先创建数据库pgbench
[postgres@localhost ~]$ pgbench -i pgbench creating 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 pgbench psql (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: no Options: fillfactor=100 pgbench=# \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: no Options: fillfactor=100 pgbench=# \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: no pgbench=# \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: no Options: fillfactor=100说明:
a.这里使用的是默认的参数值,带-s 参数时可指定测试数据的数据量,-f可以指定测试的脚本,这里用的是默认脚本
b.不要在生产的库上做,新建一个测试库,当生产上有同名的测试表时将被重置
4.测试过程(注意以下参数r只有在9.1以上版本才有)
目录要到安装目录下 否则可能会文件没有写权限
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 input
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 20 s
number 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 input
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 30
number of threads: 1
duration: 20 s
number 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 input
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 50
number of threads: 1
duration: 20 s
number 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 input
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 1
duration: 20 s
number 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),这个值是反映的每秒处理的事务数,反过来也可以查出每个事务数所消耗的平均时间,一般认为能将硬件用到极致,速度越快越好。
本篇文章来源于 Linux公社网站(www.linuxidc.com) 原文链接:http://www.linuxidc.com/Linux/2012-07/64794p2.htm