本文首发于 2015-12-23 21:04:17

环境

PG数据库提供了一款轻量级的压力测试工具叫 pgbench，其实就是一个编译好后的扩展性的可执行文件。

测试环境：

CentOS 5.7 in VMWare 8.0

PG：9.1.2

数据库参数：

max_connection=100

其余默认

留神： 本文只为阐明 pgbench 的应用办法，因而，并未对数据库参数调优。

装置

进入源码安装包，编译、装置：

cd postgresql-9.1.2/contrib/pgbench/make allmake install

装置结束当前能够在 bin 文件夹下看到新生成的 pgbench 文件：

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

参数介绍

[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 exit

局部参数中文含意：

-c, --client=NUM数据库客户端数量, 能够了解为数据库会话数量(postgres过程数), 默认为1-C, --connect每个事务创立一个连贯,因为PG应用过程模型, 能够测试频繁Kill/Create过程的性能体现-j, --jobs=NUMpgbench的工作线程数-T, --time=NUM以秒为单位的压测时长-v, --vacuum-all每次测试前执行vacuum命令, 防止"垃圾"空间的影响-M, --protocol=simple|extended|prepared提交查问命令到服务器应用的协定, simple是默认选项, prepared是相似绑定-r, --report-latencies报告每条命令(SQL语句)的均匀延时-S, --select-only只执行查问语句

初始化测试数据

初始化数据：

[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

阐明：

1. 这里应用的是默认的参数值，-s参数时可指定测试数据的数据量，-f能够指定测试的脚本，这里用的是默认脚本。
2. 不要在生产的库上做，新建一个测试库（当生产上有同名的测试表时将被重置）。

测试

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;

2. 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;

3. 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;

参考

http://www.postgresql.org/doc...

欢送关注我的微信公众号【数据库内核】：分享支流开源数据库和存储引擎相干技术。