PGbench压测工具， 对postgre进行压测

首先 创建要执行的sql文件， 登录服务器到…/bin/pgbench 同目录

工具下载安装

有两种方式对pgbench工具进行安装：

源码安装：下载开源数据库PostgreSQL源码，然后到pgbench对应的目录中单独对pgbench进行编译生成可执行的二进制文件。

二进制安装：可以先直接yum install postgresql-server来安装PostgreSQL程序，此过程会自动安装pgbench工具。

创建要执行的sql文件

all.sql

\set scale 11424
\set nbranches 1 * :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
\setrandom bid 1 :nbranches
\setrandom tid 1 :ntellers
\setrandom delta -5000 5000

BEGIN;
 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;

insert.sql

\set scale 11424
\set nbranches 1 * :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
\setrandom bid 1 :nbranches
\setrandom tid 1 :ntellers
\setrandom delta -5000 5000
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

select.sql

\set scale 11424
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts

SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

update.sql

\set scale 11424
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
\setrandom delta -5000 5000
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

执行以下脚本创建表和生成数据

生成四张测试表

# 其中-F参数就是上文说的装填因子，-s值得是比例因子
./pgbench -i -F 100 -s 400 -h ip -p port -U user_name db

pgbench_accounts
pgbench_branches
pgbench_history
pgbench_tellers

测试数据执行的结果

#-c指定了连接数据库client数量，-j指定了建立连接使用的线程数量，推荐将两者设置成一样
 -T指定了测试执行时间，单位为秒
./pgbench -h con_addr -p port -r -n -c 96 -j 96 -T 10 -f all.sql -U user_name db_name

结果内容

transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 96
number of threads: 96
duration: 10 s
number of transactions actually processed: 52913
latency average: 18.337 ms
tps = 5235.329097 (including connections establishing)
tps = 5368.106329 (excluding connections establishing)