PostgreSQL DBA - pgbench简介

PostgreSQL DBA(7) - pgbench简介
pgbench是面向PostgreSQL的一个基准测试工具。默认情况下（工具默认提供），pgbench 测试基于TPC-B场景，每个事务包括5个SELECT、UPDATE 和INSERT命令。可以通过编写自己的事务脚本文件按需进行定制化测试。
一、数据初始化
通过-i参数,初始化基表和数据.创建测试基表pgbench_accounts/pgbench_branches/pgbench_history/pgbench_tellers
[atlasdb@localhost tmp]$ createdb pgbench
[atlasdb@localhost tmp]$ pgbench -i -U atlasdb -p 5432 -d pgbench
dropping old tables…
NOTICE: table “pgbench_accounts” does not exist, skipping
NOTICE: table “pgbench_branches” does not exist, skipping
NOTICE: table “pgbench_history” does not exist, skipping
NOTICE: table “pgbench_tellers” does not exist, skipping
creating tables…
generating data…
100000 of 100000 tuples (100%) done (elapsed 0.45 s, remaining 0.00 s)
vacuuming…
creating primary keys…
done.
二、参数说明
通过pgbench --help可以查看完整的参数列表.
其中:
-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
只执行查询语句
[atlasdb@localhost tmp]$ pgbench --help
pgbench is a benchmarking tool for PostgreSQL.

Usage:
pgbench [OPTION]… [DBNAME]

Initialization options:
-i, --initialize invokes initialization mode
-I, --init-steps=[dtgvpf]+ (default “dtgvp”)
…
三、基本使用
执行基准测试
执行以下命令,启动4个工作线程,创建8个客户端连接PG:
pgbench -h 192.168.26.103 -U atlasdb -p 5432 -d pgbench -r -M prepared -v -T 120 -c 8 -j 4 --aggregate-interval=5 -l
以下为执行过程中的部分日志输出:
client 5 receiving
client 2 receiving
client 2 sending INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (2, 1, 39145, -3294, CURRENT_TIMESTAMP);
client 2 receiving
client 4 receiving
client 4 executing script “<builtin: TPC-B (sort of)>”
client 4 executing \set aid
client 4 executing \set bid
client 4 executing \set tid
client 4 executing \set delta
client 4 sending BEGIN;
client 4 receiving
client 2 receiving
client 2 sending END;
client 2 receiving
client 4 receiving
client 4 sending UPDATE pgbench_accounts SET abalance = abalance + 4608 WHERE aid = 80792;
client 4 receiving
client 4 receiving
client 4 sending SELECT abalance FROM pgbench_accounts WHERE aid = 80792;
client 4 receiving
client 2 receiving
下面是结果输出:
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 4
duration: 120 s
number of transactions actually processed: 27809
latency average = 34.537 ms
tps = 231.635472 (including connections establishing)
tps = 231.685954 (excluding connections establishing)
statement latencies in milliseconds:
0.035 \set aid random(1, 100000 * :scale)
0.008 \set bid random(1, 1 * :scale)
0.007 \set tid random(1, 10 * :scale)
0.006 \set delta random(-5000, 5000)
1.588 BEGIN;
1.987 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
1.829 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
9.305 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
15.904 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
1.741 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
2.101 END;
执行查询基准测试
pgbench -h 192.168.26.103 -U atlasdb -p 5432 -d pgbench -S -r -M prepared -v -T 120 -c 8 -j 4 --aggregate-interval=5 -l
最终结果如下:
transaction type: <builtin: select only>
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 4
duration: 120 s
number of transactions actually processed: 418458
latency average = 2.294 ms
tps = 3486.766688 (including connections establishing)
tps = 3487.047954 (excluding connections establishing)
statement latencies in milliseconds:
0.018 \set aid random(1, 100000 * :scale)
2.266 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
四、参考资料
pgbench