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

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

CentOS 5.7 in VMWare 8.0
PG：9.1.2

数据库参数：

max_connection=100
其他默认
注意： 本文只为说明 pgbench 的使用方法，因此，并未对数据库参数调优。

安装 【实用工具 | PostgreSQL 数据库压力测试工具 pgbench 使用示例】进入源码安装包，编译、安装：

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

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

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

参数介绍

[postgres@localhostbin]$ pgbench --help pgbench is a benchmarking tool for PostgreSQL.Usage: pgbench [OPTIONS]... [DBNAME]Initialization options: -iinvokes initialization mode -F NUMfill factor -s NUMscaling factorBenchmarking options: -c NUMnumber of concurrent database clients (default: 1) -Cestablish new connection for each transaction -D VARNAME=VALUE define variable for use by custom script -f FILENAMEread transaction script from FILENAME -j NUMnumber of threads (default: 1) -lwrite transaction times to log file -M {simple|extended|prepared} protocol for submitting queries to server (default: simple) -ndo not run VACUUM before tests -Ndo not update tables "pgbench_tellers" and "pgbench_branches" -rreport average latency per command -s NUMreport this scale factor in output -Sperform SELECT-only transactions -t NUMnumber of transactions each client runs (default: 10) -T NUMduration of benchmark test in seconds -vvacuum all four standard tables before testsCommon options: -dprint debugging output -h HOSTNAMEdatabase server host or socket directory -p PORTdatabase server port number -U USERNAMEconnect as specified database user --helpshow this help, then exit --versionoutput version information, then exit

部分参数中文含义：

-c, --client=NUM 数据库客户端数量, 可以理解为数据库会话数量(postgres进程数), 默认为1-C, --connect 每个事务创建一个连接,由于PG使用进程模型, 可以测试频繁Kill/Create进程的性能表现-j, --jobs=NUM pgbench的工作线程数-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 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=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: no Options: 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: no Options: fillfactor=100

说明：

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

测试 1个session

[postgres@localhost~]$ nohup pgbench -c 1 -T 20 -r pgbench > file.out2>&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: 12496tps = 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.050707BEGIN; 0.200909UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.098718SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 0.111621UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 0.107297UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 0.095156INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 0.919101END;

2. 50个session

[postgres@localhost~]$nohup pgbench -c 50 -T 20 -r pgbench > file.out2>&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: 7504tps = 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.604671BEGIN; 1.480723UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.401148SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 104.713566UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 21.562787UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 0.412209INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 2.243497END;

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

[postgres@localhost~]$ nohup pgbench -c 100 -T 20 -r pgbench> file.out2>&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: 6032tps = 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.660809BEGIN; 4.198062UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 1.727076SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 281.955832UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 27.054125UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 0.524155INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 2.710619END;

参考 http://www.postgresql.org/doc...
欢迎关注我的微信公众号【数据库内核】：分享主流开源数据库和存储引擎相关技术。

文章图片

标题	网址
GitHub	https://dbkernel.github.io
知乎	https://www.zhihu.com/people/...
思否（SegmentFault）	https://segmentfault.com/u/db...
掘金	https://juejin.im/user/5e9d3e...
开源中国（oschina）	https://my.oschina.net/dbkernel
博客园（cnblogs）	https://www.cnblogs.com/dbkernel

推荐阅读

• 

  如何科学坐月子 春季坐月子这6件事千万别做
• 

  广汽本田广汽本田皓影三大件是进口的吗
• 

  小森生活抗寒衣服怎么做 小森生活抗寒套装制作攻略
• 

  冬枣泡了糖精水的怎么看
• 

  怎么用炉甘石治荨麻疹
• 

  警察是否有权上门抓狗
• 

  伊利鲜奶好还是蒙牛纯牛奶好 带你了解伊利纯牛奶和蒙牛纯牛奶有什么区别
• 

  正常胸和下垂胸对比 如何揉胸才能变大
• 

  ps快速替换靠山颜色
• 

  夏天发面要几个小时才会发的好 夏天发面是用冷水还是用温水
• 

  圆点|每日好价：大牌小圆点机械键盘开售 颜值超高
• 

  臀部|臀部引发各种疼痛，令人很苦恼，这些锻炼方法，您会吗？尝试看一看
• 

  接触区域按接触还是挤压强度分析
• 

  NLP教程（Python NLTK用法示例和完整指南）
• 

  三国志战略版震慑状态是什么 九大震慑类战法使用攻略
• 

  如何为企业选择合适的云服务器？ 企业要用云服务器怎么搞
• 

  lol2018战斗之夜奖励重随在哪领取 战斗之夜奖励重随领取地址
• 

  果树环剥的方法及作用 果树环剥注意事项
• 

  考研什么时候出成绩 考研是什么
• 

  瓜蒌皮的功效与作用及禁忌

• Docker应用:容器间通信与Mariadb数据库主从复制
• 数据库设计与优化
• 数据库总结语句
• MySql数据库备份与恢复
• 数据库|SQL行转列方式优化查询性能实践
• MySQL数据库的基本操作
• springboot整合数据库连接池-->druid
• Android|Android sqlite3数据库入门系列
• Python3|Python3 MySQL 数据库连接
• 达梦数据库|DM8表空间备份恢复