postgres压测工具pgbench

1简介

PG数据库提供了一款轻量级的压力测试工具叫pgbench, 测试模型是TPC-B.

2安装

略

3.参数介绍

[postgres@localhost  bin]$ pgbench --help

pgbench is a benchmarking tool for PostgreSQL. Usage: pgbench [OPTION]... [DBNAME] Initialization options: -i, --initialize             invokes initialization mode(调用初始化模式) -F, --fillfactor=NUM        set fill factor(填充因子-对表的填充因子，类似oracle的pctfree作用) -n, --no-vacuum          do not run VACUUM after initialization(初始化完成后不VACUUM) -q, --quiet                quiet logging (one message each 5 seconds) -s, --scale=NUM           scaling factor（比例因子-控制表的数据大小） --foreign-keys             create foreign key constraints between tables(表之间创建外键约束) --index-tablespace=TABLESPACE  create indexes in the specified tablespace(索引创建在指定表空间) --tablespace=TABLESPACE    create tables in the specified tablespace(表创建在指定表空间) --unlogged-tables           create tables as unlogged tables(创建表为unlogged表) Benchmarking options: -c, --client=NUM           number of concurrent database clients (default: 1) 客户端个数(默认: 1) -C, --connect              establish new connection for each transaction为每一个事务建立一个新的连接 -D, --define=VARNAME=VALUE   define variable for use by custom script通过客户脚本为用户定义变量 -f, --file=FILENAME         read transaction script from FILENAME指定测试脚本的位置，不指定为自带的默认脚本 -j, --jobs=NUM           number of threads (default: 1) 启动线程数(default: 1) -l, --log                 write transaction times to log file向日志文件中输入每个事务时间 -L, --latency-limit=NUM    count transactions lasting more than NUM ms as late -M, --protocol=simple|extended|prepared                 protocol for submitting queries (default: simple) 向服务器提交查询的协议 (default: simple) -n, --no-vacuum          do not run VACUUM before tests测试前不执行VACUUM -N, --skip-some-updates    skip updates of pgbench_tellers and pgbench_branches不执行update操作 -P, --progress=NUM        show thread progress report every NUM seconds -r, --report-latencies        report average latency per command报告中显示每个命令的平均延迟 -R, --rate=NUM           target rate in transactions per second -s, --scale=NUM           report this scale factor in output报告中显示每个命令的平均延迟 -S, --select-only           perform SELECT-only transactions只执行查询的事务 -t, -transactions=NUM     number of transactions each client runs (default: 10) 每个客户端执行的事务次数(default: 10) -T, --time=NUM            duration of benchmark test in seconds测试时间，单位为秒 -v, --vacuum-all            vacuum all four standard tables before tests测试前对库中默认的4个基准表进行vacuum操作 --aggregate-interval=NUM   aggregate data over NUM seconds --sampling-rate=NUM       fraction of transactions to log (e.g. 0.01 for 1%) Common options:   -d, --debug                print debugging output   -h, --host=HOSTNAME       database server host or socket directory   -p, --port=PORT            database server port number   -U, --username=USERNAME  connect as specified database user   -V, --version               output version information, then exit   -?, --help                 show this help, then exit

4测试方式

1采用默认基准表默认脚本测试

(1)创建名为pgbench数据库:create  database pgbench;

(2)初始化测试数据[postgres@pg2 ~]$pgbench  -i  -F 100 -s 500  -h  localhost -U  dba   -d  pgbench

(3)检查初始化之后的pgbench数据库

pgbench=# select count(1) from pgbench_accounts; pgbench=# select count(1) from pgbench_branches; pgbench=# select count(1) from pgbench_history; pgbench=# select count(1) from pgbench_tellers; pgbench=# \d+ pgbench_accounts pgbench=# \d+ pgbench_branches pgbench=# \d+ pgbench_history pgbench=# \d+ pgbench_tellers 查数据库大小：pgbench=# select pg_database_size('pgbench')/1024/1024||'M';

(4)利用基准表，默认脚本测试

自带脚本 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; 测试语句 1只读 nohup  pgbench -c 50 -j 10 -M prepared -n  -s 500 -S -T 120 -r -h localhost  -p 5432 -U dba -d pgbench> /pgdb/pgbenchtest/readonly.out  2>&1 2更新，查询，插入 nohup  pgbench -c 50 -j 10 -M prepared -n  -s 500 -T 120  -r -h localhost  -p 5432 -U dba -d pgbench> /pgdb/pgbenchtest/all.out  2>&1 3不执行更新 nohup  pgbench -c 50 -j 10 -M prepared -n  -N  -s 500 -T 120  -r -h localhost  -p 5432 -U dba -d pgbench> /pgdb/pgbenchtest/noupdate.out  2>&1 说明：我们主要关心的是最后的输出报告中的两个TPS值,一个是包含网络开销(including),另一个是不包含网络开销的 (excluding),这个值是反映的是每秒处理的事务数，反过来也可以查出每个事务数所消耗的平均时间，一般认为能将硬件用到极致，速度越快越好。

2利用默认基准表，自定义脚本测试

分别测试3种脚本：(只读，读写，读写函数)

2.1只读测试

--脚本内容 vi  /pgdb/pgbenchtest/read.sql \set naccounts 100000 * :scale \setrandom aid 1 :naccounts SELECT abalance FROM pgbench_accounts WHERE aid = :aid; --测试时间段内的操作系统负载 Linux命令top --测试语句 [postgres@pg2 ~]$pgbench -c 50 -j 10 -M prepared -n -s 500 -T 120 -f /pgdb/pgbenchtest/read.sql -h localhost -p 5432 -U dba -d pgbench>/pgdb/pgbenchtest/readonly.out  2>&1

2.2读写测试

--脚本内容 vi  /pgdb/pgbenchtest/readwrite.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; --测试时间段内的操作系统负载 Linux命令top --测试语句 [postgres@pg2 ~]$pgbench -c 50 -j 10 -M prepared -n -s 500 -T 120 -f /pgdb/pgbenchtest/readwrite.sql -h localhost -p 5432 -U dba -d pgbench>/pgdb/pgbenchtest/readwrite.out  2>&1

2.3读写函数的测试

--主要是为了对比调用函数和直接使用SQL的性能差别 --创建如下函数 pgbench=# 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; --脚本内容 vi  /pgdb/pgbenchtest/readwritefunction.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 select pgbench(:aid,:bid,:tid,:delta); --测试时间段内的操作系统负载 Linux命令top --测试语句 [postgres@pg2 ~]$ pgbench -c 50 -j 10 -M prepared -n -s 500 -T 120 -f /pgdb/pgbenchtest/readwritefunction.sql -h localhost -p 5432 -U dba -d pgbench>/pgdb/pgbenchtest/readwritefunction.out  2>&1

3自定义测试表，数据与脚本

3.1测试查询

--表准备 pgbench=#create table testtb1(id int,name text); pgbench=#insert into testtb1(id,name) select n,n||'_test' from generate_series(1,5000000) n; pgbench=#alter table testtb1 add primary key(id); --测试脚本 SQL [postgres@pg2 ~]$ cat  /pgdb/pgbenchtest/testtb1.sql \setrandom v_id 1 5000000 select name from testtb1 where id=:v_id; --测试语句 pgbench -c 2 -T 20 -n -M prepared -d pgbench  -U dba -f /pgdb/pgbenchtest/ testtb1.sql >/pgdb/pgbenchtest/testtb1.out 2>&1

2测试插入

--表准备 pgbench=#create table testintodata(data text); --测试脚本 SQL内容： [postgres@pg2 ~]$ cat  /pgdb/pgbenchtest/testintodata.sql insert into testintodata (data) values (repeat('xyz',:scale)); --测试语句 pgbench -s 100 -c 50 -j 10 -M prepared -T 120 -f /pgdb/pgbenchtest/testintodata.sql  -h localhost  -p 5432 -U dba -d pgbench>/pgdb/pgbenchtest/testintodata.out  2>&1

 完成。