PostgreSQL压测工具pgbench

PGbench默认是一种宽松场景的基于TPC-B的测试工具。压测主要关注压测TPS、数据量及延迟。

环境介绍：

RHEL 7.5
PG 11.5

环境准备：

PG数据库初始化完成(Port:5435)，创建压测用户(pgbench)、表空间(pgbtbs)、数据库(pgbench)。

[postgres@rhel75 ~]$mkdir /postgres/pgbtbs

postgres=# create user pgbench password 'pgbench';

postgres=# CREATE TABLESPACE pgbtbs
postgres-#   OWNER pgbench
postgres-#   LOCATION '/postgres/pgbtbs';
CREATE TABLESPACE

postgres=# CREATE DATABASE pgbench
postgres-#   WITH OWNER = pgbench
postgres-#        ENCODING = 'UTF8'
postgres-#        TABLESPACE = pgbtbs;
CREATE DATABASE

PGbench参数说明：

[postgres@rhel75 ~]$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")
                           run selected initialization steps
  -F, --fillfactor=NUM     set fill factor
  -n, --no-vacuum          do not run VACUUM during initialization
  -q, --quiet              quiet logging (one message each 5 seconds)
  -s, --scale=NUM          scaling factor
  --foreign-keys           create foreign key constraints between tables
  --index-tablespace=TABLESPACE
                           create indexes in the specified tablespace
  --tablespace=TABLESPACE  create tables in the specified tablespace
  --unlogged-tables        create tables as unlogged tables

Options to select what to run:
  -b, --builtin=NAME[@W]   add builtin script NAME weighted at W (default: 1)
                           (use "-b list" to list available scripts)
  -f, --file=FILENAME[@W]  add script FILENAME weighted at W (default: 1)
  -N, --skip-some-updates  skip updates of pgbench_tellers and pgbench_branches
                           (same as "-b simple-update")
  -S, --select-only        perform SELECT-only transactions
                           (same as "-b select-only")

Benchmarking options:
  -c, --client=NUM         number of concurrent database clients (default: 1)
  -C, --connect            establish new connection for each transaction
  -D, --define=VARNAME=VALUE
                           define variable for use by custom script
  -j, --jobs=NUM           number of threads (default: 1)
  -l, --log                write transaction times to log file
  -L, --latency-limit=NUM  count transactions lasting more than NUM ms as late
  -M, --protocol=simple|extended|prepared
                           protocol for submitting queries (default: simple)
  -n, --no-vacuum          do not run VACUUM before tests
  -P, --progress=NUM       show thread progress report every NUM seconds
  -r, --report-latencies   report average latency per command
  -R, --rate=NUM           target rate in transactions per second
  -s, --scale=NUM          report this scale factor in output
  -t, --transactions=NUM   number of transactions each client runs (default: 10)
  -T, --time=NUM           duration of benchmark test in seconds
  -v, --vacuum-all         vacuum all four standard tables before tests
  --aggregate-interval=NUM aggregate data over NUM seconds
  --log-prefix=PREFIX      prefix for transaction time log file
                           (default: "pgbench_log")
  --progress-timestamp     use Unix epoch timestamps for progress
  --random-seed=SEED       set random seed ("time", "rand", integer)
  --sampling-rate=NUM      fraction of transactions to log (e.g., 0.01 for 1%)

Common options:
  -d, --debug              print debugging output
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -U, --username=USERNAME  connect as specified database user
  -V, --version            output version information, then exit
  - , --help               show this help, then exit

Report bugs to <pgsql-bugs@postgresql.org>.

初始化压测数据

At the default “scale factor” of 1, the tables initially contain this many rows:

table                   # of rows
---------------------------------
pgbench_branches        1
pgbench_tellers         10
pgbench_accounts        100000
pgbench_history         0
You can (and, for most purposes, probably should) increase the number of rows by using the -s (scale factor) option. The -F (fillfactor) option might also be used at this point.

Once you have done the necessary setup, you can run your benchmark with a command that doesn't include -i, that is

测试环境scale factor是10，fillfactor是50，生产环境根据实际配置响应调大

[postgres@rhel75 ~]$pgbench -i -F 10 -s 50 -h localhost -U pgbench -d pgbench
dropping old tables...
creating tables...
generating data...
100000 of 5000000 tuples (2%) done (elapsed 0.07 s, remaining 3.47 s)
......
5000000 of 5000000 tuples (100%) done (elapsed 37.26 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.
查看压测数据库大小：
pgbench=> select pg_database_size('pgbench')/1024/1024||'M'
;
  column  
----------
 6627M
(1 row)

--readonly测试场景
nohup pgbench -c 5 -j 2 -M prepared -n  -s 500 -S -T 120 -r -h localhost  -p 5435 -U pgbench -d pgbench > readonly.out  2>&1
......
client 2 receiving
pghost: localhost pgport: 5435 nclients: 5 duration: 120 dbName: pgbench
transaction type: <builtin: select only>
scaling factor: 50
query mode: prepared
number of clients: 5
number of threads: 2
duration: 120 s
number of transactions actually processed: 700065
latency average = 0.857 ms
tps = 5833.808371 (including connections establishing)
tps = 5834.011213 (excluding connections establishing)
statement latencies in milliseconds:
         0.009  \set aid random(1, 100000 * :scale)
         0.848  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

--update\select\insert测试场景
nohup pgbench -c 5 -j 2 -M prepared -n -s 500 -T 120 -r -h localhost -p 5435 -U pgbench -d pgbench> all.out 2>&1
......
pghost: localhost pgport: 5435 nclients: 5 duration: 120 dbName: pgbench
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: prepared
number of clients: 5
number of threads: 2
duration: 120 s
number of transactions actually processed: 160509
latency average = 3.738 ms
tps = 1337.438196 (including connections establishing)
tps = 1337.477417 (excluding connections establishing)
statement latencies in milliseconds:
         0.008  \set aid random(1, 100000 * :scale)
         0.004  \set bid random(1, 1 * :scale)
         0.004  \set tid random(1, 10 * :scale)
         0.004  \set delta random(-5000, 5000)
         0.359  BEGIN;
         0.731  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.517  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.414  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.476  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.440  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         0.780  END;
--减少更新比例
nohup pgbench -c 5 -j 2 -M prepared -n -N -s 500 -T 120 -r -h localhost -p 5435 -U pgbench -d pgbench> noupdate.out 2>&1
pghost: localhost pgport: 5435 nclients: 5 duration: 120 dbName: pgbench
transaction type: <builtin: simple update>
scaling factor: 50
query mode: prepared
number of clients: 5
number of threads: 2
duration: 120 s
number of transactions actually processed: 189086
latency average = 3.174 ms
tps = 1575.493301 (including connections establishing)
tps = 1575.549160 (excluding connections establishing)
statement latencies in milliseconds:
         0.008  \set aid random(1, 100000 * :scale)
         0.005  \set bid random(1, 1 * :scale)
         0.004  \set tid random(1, 10 * :scale)
         0.004  \set delta random(-5000, 5000)
         0.394  BEGIN;
         0.926  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.550  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.439  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         0.843  END;

--可以将默认压测结果后面的sql语句写入到一个sql文件中，进行相关调整及测试，例如自定义只读脚本
[postgres@rhel75 ~]$cat read.sql 
\set aid random(1, 100000 * :scale)
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
nohup pgbench -c 5 -j 2 -M prepared -n  -s 500 -S -T 120 -r  -f /home/postgres/read.sql -h localhost  -p 5435 -U pgbench -d pgbench > self_readonly.out  2>&1
......
pghost: localhost pgport: 5435 nclients: 5 duration: 120 dbName: pgbench
transaction type: multiple scripts
scaling factor: 50
query mode: prepared
number of clients: 5
number of threads: 2
duration: 120 s
number of transactions actually processed: 386732
latency average = 1.552 ms
tps = 3222.238267 (including connections establishing)
tps = 3222.372968 (excluding connections establishing)
SQL script 1: <builtin: select only>
 - weight: 1 (targets 50.0% of total)
 - 193222 transactions (50.0% of total, tps = 1609.919330)
 - latency average = 1.544 ms
 - latency stddev = 4.794 ms
 - statement latencies in milliseconds:
         0.009  \set aid random(1, 100000 * :scale)
         1.535  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
SQL script 2: /home/postgres/read.sql
 - weight: 1 (targets 50.0% of total)
 - 193510 transactions (50.0% of total, tps = 1612.318937)
 - latency average = 1.559 ms
 - latency stddev = 5.488 ms
 - statement latencies in milliseconds:
         0.009  \set aid random(1, 100000 * :scale)
         1.550  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

--测试读写函数性能
create or replace function pgbench(i_aid int,i_bid int,i_tid int,i_delta int) returns setof int as $$
declare
begin
UPDATE pgbench_accounts SET abalance = abalance + i_delta WHERE aid = i_aid;
UPDATE pgbench_tellers SET tbalance = tbalance + i_delta WHERE tid = i_tid;
UPDATE pgbench_branches SET bbalance = bbalance + i_delta WHERE bid = i_bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (i_tid, i_bid, i_aid, i_delta, CURRENT_TIMESTAMP);
return query SELECT abalance FROM pgbench_accounts WHERE aid = i_aid;
end;
$$ language plpgsql;

[postgres@rhel75 ~]$cat readwritef.sql 
\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
select pgbench(:aid,:bid,:tid,:delta);

[postgres@rhel75 ~]$nohup pgbench -c 5 -j 2 -M prepared -n  -s 500 -S -T 120 -r  -f /home/postgres/readwritef.sql -h localhost  -p 5435 -U pgbench -d pgbench > self_readw.out 2>&1
......
pghost: localhost pgport: 5435 nclients: 5 duration: 120 dbName: pgbench
transaction type: multiple scripts
scaling factor: 50
query mode: prepared
number of clients: 5
number of threads: 2
duration: 120 s
number of transactions actually processed: 309075
latency average = 1.942 ms
tps = 2575.112761 (including connections establishing)
tps = 2575.187960 (excluding connections establishing)
SQL script 1: <builtin: select only>
 - weight: 1 (targets 50.0% of total)
 - 154687 transactions (50.0% of total, tps = 1288.801966)
 - latency average = 1.245 ms
 - latency stddev = 2.064 ms
 - statement latencies in milliseconds:
         0.009  \set aid random(1, 100000 * :scale)
         1.236  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
SQL script 2: /home/postgres/readwritef.sql
 - weight: 1 (targets 50.0% of total)
 - 154388 transactions (50.0% of total, tps = 1286.310795)
 - latency average = 2.639 ms
 - latency stddev = 2.867 ms
 - statement latencies in milliseconds:
         0.009  \set aid random(1, 100000 * :scale)
         0.005  \set bid random(1, 1 * :scale)
         0.005  \set tid random(1, 10 * :scale)
         0.005  \set delta random(-5000, 5000)
         2.615  select pgbench(:aid,:bid,:tid,:delta);

参考文档：
https://www.postgresql.org/docs/11/pgbench.html
https://blog.csdn.net/arcticJian/article/details/83272068