1.初始化:
pgbench -i pgbench
如果端口号有变化,需要先手动创建数据库,再执行。
pgbench -i -s 10 -p 5433 pgbench
重点:主要用到两个参数,-i:初始化模式,-s 插入的倍数,默认是1,即插入100000条;也就是执行多少次generate_series(1,100000)。
2.开始测试:
pgbench -c 96 -j 12 -T 20 -r -p 5433 pgbench
-bash-4.1$ pgbench -c 96 -j 12 -T 20 -r -p 5433 pgbench
starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1 query mode: simple number of clients: 96 number of threads: 12 duration: 20 s number of transactions actually processed: 1536 latency average: 1345.443 ms tps = 71.351954 (including connections establishing) tps = 71.400363 (excluding connections establishing) statement latencies in milliseconds: 0.003744 \set nbranches 1 * :scale 0.001051 \set ntellers 10 * :scale 0.000855 \set naccounts 100000 * :scale 0.001345 \setrandom aid 1 :naccounts 0.000850 \setrandom bid 1 :nbranches 0.000680 \setrandom tid 1 :ntellers 0.000797 \setrandom delta -5000 5000 0.425127 BEGIN; 3.685616 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.189167 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 1144.710503 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 121.211975 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 0.194937 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 13.740971 END;
主要参数:
-c 总连接数,创建多少个连接到数据库,一般数据库接受连接数默认为100,其中需要预留3个左右的连接。
-j 进程数量,每个进程创建n个连接,那么就存在如下关系:-c = -j *n。
-T 测试持续时间,指定了-T就不能指定-t,每个连接执行的事物数量。即,要么指定测试多长时间,要么指定测试多少个事物。
-r 显示每一步操作的平均时间。
-f 指定测试脚本,不指定则使用默认脚本。
3.测试结果分析
可以看看设置的参数,也可以看到TPS,当然可以算到QPS,也可以看到系统的读写更新的速度,知道该往哪方面优化。
4.pgbench初始化参数:
pgbench accepts the following command-line initialization arguments: -F fillfactor Create the pgbench_accounts, pgbench_tellers and pgbench_branches tables with the given fillfactor. Default is 100. -i Required to invoke initialization mode. -s scale_factor Multiply the number of rows generated by the scale factor. For example, -s 100 will create 10,000,000 rows in the pgbench_accounts table. Default is 1.
5.pgbench 压力测试参数:
pgbench accepts the following command-line benchmarking arguments: -c clients Number of clients simulated, that is, number of concurrent database sessions. Default is 1. -C Establish a new connection for each transaction, rather than doing it just once per client session. This is useful to measure the connection overhead. -d Print debugging output. -D varname=value Define a variable for use by a custom script (see below). Multiple -D options are allowed. -f filename Read transaction script from filename. See below for details. -N, -S, and -f are mutually exclusive. -j threads Number of worker threads within pgbench. Using more than one thread can be helpful on multi-CPU machines. The number of clients must be a multiple of the number of threads, since each thread is given the same number of client sessions to manage. Default is 1. -l Write the time taken by each transaction to a log file. See below for details. -M querymode Protocol to use for submitting queries to the server: simple: use simple query protocol. extended: use extended query protocol. prepared: use extended query protocol with prepared statements. The default is simple query protocol. (See Chapter 46 for more information.) -n Perform no vacuuming before running the test. This option is necessary if you are running a custom test scenario that does not include the standard tables pgbench_accounts, pgbench_branches, pgbench_history, and pgbench_tellers. -N Do not update pgbench_tellers and pgbench_branches. This will avoid update contention on these tables, but it makes the test case even less like TPC-B. -s scale_factor Report the specified scale factor in pgbench is output. With the built-in tests, this is not necessary; the correct scale factor will be detected by counting the number of rows in the pgbench_branches table. However, when testing custom benchmarks (-f option), the scale factor will be reported as 1 unless this option is used. -S Perform select-only transactions instead of TPC-B-like test. -t transactions Number of transactions each client runs. Default is 10. -T seconds Run the test for this many seconds, rather than a fixed number of transactions per client. -t and -T are mutually exclusive. -v Vacuum all four standard tables before running the test. With neither -n nor -v, pgbench will vacuum the pgbench_tellers and pgbench_branches tables, and will truncate pgbench_history.
6.默认测试脚本:
\set nbranches :scale \set ntellers 10 * :scale \set naccounts 100000 * :scale \setrandom delta -5000 5000 \setrandom aid 1 :naccounts \setrandom bid 1 :nbranches \setrandom tid 1 :ntellers 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; 备注:上面是 pgbench 自带的测试脚本,用户也可以自己编写测试脚本。
补充20190619:
7.在pg11上测试时,\setrandom报错,修改脚本:
\set nbranches :scale \set ntellers 10 * :scale \set naccounts 100000 * :scale \set delta random(-5000,5000) \set aid random(1,:naccounts) \set bid random(1,:nbranches) \set tid random(1,:ntellers) 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)使用pgbench 选用-P 选项,多少s报告一次进程的tps等信息,将这些信息可以放到excel或者Gnuplot、Matlab中绘图。
2)使用pgbench_tools来做综合测试,并给出一个趋势报告图。
下面是对pgbench_tools的介绍和使用:
https://github.com/kuang17/pgbench-tools
需要注意的是:
1)PG10修改了几个函数,例如pg_current_xlog_location变为了pg_current_wal_lsn。
2)在测试脚本中,\setrandom 需要对应的修改为\set xxx random(xxx, xxx)的方式。
3)在绘图时,需要先按照Gnuplot绘图工具及相关的字体。
yum install gnuplot -y
wget http://olea.org/paquetes-rpm//msttcore-fonts-2.0-6.noarch.rpm
rpm -ivh msttcore-fonts-2.0-6.noarch.rpm
修改/etc/profile或~/.bashrc,这样设置可以固定下来。
export GDFONTPATH="/usr/share/fonts/msttcore"
export GNUPLOT_DEFAULT_GDFONT="arial"
. /etc/profile