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, skippingNOTICE: table "pgbench_branches" does not exist, skippingNOTICE: table "pgbench_history" does not exist, skippingNOTICE: 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: 1query mode: prepared number of clients: 8number of threads: 4duration: 120 s number of transactions actually processed: 27809latency 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;
作者：EthanHe
链接：https://www.jianshu.com/p/cfb3f5a385c3
随时随地看视频
