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