基准测试工具 pgbench 之一初步测试

pgbench是一种在PostgreSQL上运行基准测试的简单程序。它可能在并发的数据库会话中一遍一遍地运行相同序列的 SQL 命令，并且计算平均事务率（每秒的事务数）。默认情况下，pgbench会测试一种基于 TPC-B 但是要更宽松的场景，其中在每个事务中涉及五个SELECT、UPDATE以及INSERT命令。但是，通过编写自己的事务脚本文件很容易用来测试其他情况。

创建数据库

$ psql
create database pgbenchdb 

准备命令

$ pgbench -i -s 20 pgbenchdb 

NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 2000000 tuples (5%) done (elapsed 0.25 s, remaining 4.70 s)
200000 of 2000000 tuples (10%) done (elapsed 0.53 s, remaining 4.73 s)
300000 of 2000000 tuples (15%) done (elapsed 0.80 s, remaining 4.51 s)
400000 of 2000000 tuples (20%) done (elapsed 1.02 s, remaining 4.09 s)
500000 of 2000000 tuples (25%) done (elapsed 1.24 s, remaining 3.71 s)
600000 of 2000000 tuples (30%) done (elapsed 1.53 s, remaining 3.58 s)
700000 of 2000000 tuples (35%) done (elapsed 1.84 s, remaining 3.42 s)
800000 of 2000000 tuples (40%) done (elapsed 2.07 s, remaining 3.10 s)
900000 of 2000000 tuples (45%) done (elapsed 2.38 s, remaining 2.90 s)
1000000 of 2000000 tuples (50%) done (elapsed 2.71 s, remaining 2.71 s)
1100000 of 2000000 tuples (55%) done (elapsed 2.92 s, remaining 2.39 s)
1200000 of 2000000 tuples (60%) done (elapsed 3.24 s, remaining 2.16 s)
1300000 of 2000000 tuples (65%) done (elapsed 3.58 s, remaining 1.93 s)
1400000 of 2000000 tuples (70%) done (elapsed 3.84 s, remaining 1.64 s)
1500000 of 2000000 tuples (75%) done (elapsed 4.18 s, remaining 1.39 s)
1600000 of 2000000 tuples (80%) done (elapsed 4.45 s, remaining 1.11 s)
1700000 of 2000000 tuples (85%) done (elapsed 4.83 s, remaining 0.85 s)
1800000 of 2000000 tuples (90%) done (elapsed 5.17 s, remaining 0.57 s)
1900000 of 2000000 tuples (95%) done (elapsed 5.42 s, remaining 0.29 s)
2000000 of 2000000 tuples (100%) done (elapsed 5.73 s, remaining 0.00 s)
vacuum...
set primary keys...
done.

测试命令

$ pgbench -r -j2 -c4 -T60 pgbenchdb 

starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 20
query mode: simple
number of clients: 4
number of threads: 2
duration: 60 s
number of transactions actually processed: 80976
latency average = 2.964 ms
tps = 1349.466570 (including connections establishing)
tps = 1349.564883 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
         0.003  set aid random(1, 100000 * :scale)
         0.001  set bid random(1, 1 * :scale)
         0.001  set tid random(1, 10 * :scale)
         0.001  set delta random(-5000, 5000)
         0.073  BEGIN;
         0.207  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.193  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.233  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.332  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.173  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         1.746  END;

tps = 1349.466570 (including connections establishing)
tps = 1349.564883 (excluding connections establishing)

$ pgbench -r -j2 -c10 -T60 pgbenchdb 
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 20
query mode: simple
number of clients: 10
number of threads: 2
duration: 60 s
number of transactions actually processed: 116734
latency average = 5.141 ms
tps = 1945.088417 (including connections establishing)
tps = 1945.243545 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
         0.002  set aid random(1, 100000 * :scale)
         0.001  set bid random(1, 1 * :scale)
         0.001  set tid random(1, 10 * :scale)
         0.001  set delta random(-5000, 5000)
         0.122  BEGIN;
         0.211  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.197  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.321  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         1.121  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.189  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         2.974  END;

tps = 1945.088417 (including connections establishing)
tps = 1945.243545 (excluding connections establishing)

-i 要求调用初始化模式。

-s 在pgbench的输出中报告指定的比例因子。对于内建测试，这并非必需；正确的比例因子将通过对pgbench_branches表中的行计数来检测。不过，当只测试自定义基准（-f选项）时，比例因子将被报告为 1（除非使用了这个选项）。

-r 在基准结束后，报告平均的每个命令的每语句等待时间（从客户端的角度来说是执行时间）。

-j pgbench中的工作者线程数量。在多 CPU 机器上使用多于一个线程会有用。客户端会尽可能均匀地分布到可用的线程上。默认为 1。

-c 模拟的客户端数量，也就是并发数据库会话数量。默认为 1。

-T 运行测试这么多秒，而不是为每个客户端运行固定数量的事务。-t和-T是互斥的。