pgbench(postgresql压力测试工具)

pgbench(postgresql压力测试工具)
简介
postgresql自带提供了一款轻量级的压力测试工具：pgbench
可自行编写脚本，按自己的需求对数据库进行性能压力测试。
且postgresql论坛上有众多网友分享自己编写的针对pgbench的拓展工具，极大的增强了他的功能。

测试环境
操作系统：Ubuntu 15.10
数据库：postgresql 9.4
虚拟机：VMware Workstation 12
内存：2G
硬盘：40G

此数据库搭建了pgpool主备集群模式：
pgpool： port=9999
master: port=5433
slave: port=5434

参数介绍
输入：

pgbench –help
1
返回详细信息：

pgbench is a benchmarking tool for PostgreSQL.

Usage:
pgbench [OPTIONS]… [DBNAME]

Initialization options:
-i invokes initialization mode 初始化模式
-F NUM fill factor fillfactor
-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 .
构建测试环境
通过pgpool端进入postgresql：

psql –p 9999 –U postgres
创建测试用户：
CREATE ROLE pg_bench LOGIN
ENCRYPTED PASSWORD ‘oracle’
SUPERUSER INHERIT CREATEDB NOCREATEROLE REPLICATION;
创建测试表空间
使用root用户创建表空间目录：

mkdir /home/ptbs
授予postgres用户操作权限：

chown –R postgres /home/ptbs
创建表空间tbs:

CREATE TABLESPACE tbs
OWNER pg_bench
LOCATION ‘/home/ptbs’;
创建测试库pg_bench：
CREATE DATABASE pg_bench
WITH OWNER = pg_bench
ENCODING = ‘UTF8’
TABLESPACE = tbs;
初始化测试环境
使用命令：pgbench -i [ other-options ] dbname
如：

pgbench –p 9999 –U postgres –I pg_bench
执行完成后，进入测试库pg_bench，发现创建了4个表：

psql –p 9999 –U postgres –d pg_bench
由于使用的是默认参数创造的测试表，各表数据量如下：（history表是记录表，创建时为空）

name size
pgbench_accounts 100000
pg_branches 1
pg_tellers 10
在创建测试表的时候可以设置测试数据的量如：

pgbench –p 9999 –U postgres –i –F 100 –s 500 pg_bench

会将测试数据扩大500倍，即：

name size
pgbench_accounts 50000000
pg_branches 500
pg_tellers 5000
测试脚本
pg_bench工具自带一个测试脚本
脚本如下：

static char *tpc_b = {
“\\set nbranches :scale\n”
“\\set ntellers 10 * :scale\n”
“\\set naccounts 100000 * :scale\n”
“\\setrandom aid 1 :naccounts\n”
“\\setrandom bid 1 :nbranches\n”
“\\setrandom tid 1 :ntellers\n”
“\\setrandom delta -5000 5000\n”
“BEGIN;\n”
“UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;\n”
“SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n”
“UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;\n”
“UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;\n”
“INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n”
“END;\n”
};
/* -N case */
static char *simple_update = {
“\\set nbranches :scale\n”
“\\set ntellers 10 * :scale\n”
“\\set naccounts 100000 * :scale\n”
“\\setrandom aid 1 :naccounts\n”
“\\setrandom bid 1 :nbranches\n”
“\\setrandom tid 1 :ntellers\n”
“\\setrandom delta -5000 5000\n”
“BEGIN;\n”
“UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;\n”
“SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n”
“INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n”
“END;\n”
};
/* -S case */
static char *select_only = {
“\\set naccounts 100000 * :scale\n”
“\\setrandom aid 1 :naccounts\n”
“SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n”
};

此脚本分别执行了insert,update,select,可有效测试数据库吞吐能力，和并发效率

压力测试
集群上1个session：

pgbench –p 9999 –U pg_bench –c 1 –T 20 –r pg_bench

集群上30个session：

pgbench –p 9999 –U pg_bench –c 30 –T 20 –r pg_bench

直接在单节点，5433上执行：

pgbench –p 5433 –U pg_bench –c 30 –T 20 –r pg_bench

对比tps可明显发现单机环境下tps高于集群环境

自编脚本
只读脚本
编写脚本：
切换到postgres用户
创建只读脚本readonly.sql
执行：

vim readonly.sql
1
写入：

\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

使用命令进行压力测试：

pgbench –p 9999 –U pg_bench –c 1 –T 20 –f /var/lib/postgresql/pgbench_script/readonly.sql
读写脚本
具体使用方法同上
脚本：

\set nbranches :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, CLOCK_TIMESTAMP());
END;

函数脚本
# 主要是为了对比调用函数和直接使用SQL做同样的事情的性能差别
# 创建如下函数
create or replace function pgbench(i_aid int,i_bid int,i_tid int,i_delta int) returns setof int as $BODY$
declare
begin
UPDATE pgbench_accounts SET abalance = abalance + i_delta WHERE aid = i_aid;
UPDATE pgbench_tellers SET tbalance = tbalance + i_delta WHERE tid = i_tid;
UPDATE pgbench_branches SET bbalance = bbalance + i_delta WHERE bid = i_bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (i_tid, i_bid, i_aid, i_delta, CURRENT_TIMESTAMP);
return query SELECT abalance FROM pgbench_accounts WHERE aid = i_aid;
end;
$BODY$ language plpgsql;

脚本内容：

\set nbranches :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
select pgbench(:aid,:bid,:tid,:delta);

使用方法同上

优劣势
经过测试，发现pg_bench有以下优劣点：
优势：
1.系统自带，与postgresql兼容性好，且配置方便
2.工具小，执行速度快
3.开源软件，可在网上找到各种功能插件

劣势：
1.测量结果浮动较大，就多次实验来看，测量结果从40tps到400tps都出现过，统计后发现绝大多数执行结果落在350~400区间。故使用时须多次执行，取合适的值。
2.无法中断。执行过程中无法中断测试操作，就算kill了pg_bench进程，他的脚本也已经进入postgresql，postgresql依然会继续跑测试。只能等着他跑完。

参考：
PostgreSQL 9.4.17 Documentation
一天学会PostgreSQL应用开发与管理 – 8 PostgreSQL 管理