pgbench

pgbench是pg自带的，类似于TPC-B的一个基准测试工具（包括select/update/insert），可以用于测试pg库的吞吐量（throughput），延迟（latency）,并发量等

• 通用参数：

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

用于连接数据库，指定debug模式输出详细信息等

• 初始化参数：

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

用于生成初始化表结构和数据，可指定数据存储表空间，初始化数据的规模（-s），填充因子、初始化完成是否执行vacuum以及静默模式等

• benchmark参数：

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%)

用于指定运行测试的各个参数，如并发连接数，延迟限制等

• 其它可选参数：

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")

用于添加和查看，设置内置的测试脚本

• 用例

• 初始化：

[postgres@pg93 ~]$ pgbench -i -F 90 -n -q -s 20 -U fin_er findb
dropping old tables...
creating tables...
generating data...
2000000 of 2000000 tuples (100%) done (elapsed 3.85 s, remaining 0.00 s)
creating primary keys...
done.

以上通过fin_er用户登录到findb数据库，以90的填充因子，no-vacuum和静默模式，创建了scale为20的初始化数据（未创建外键约束）

• 内置测试脚本：

[postgres@pg93 ~]$ pgbench -b list
Available builtin scripts:
        tpcb-like
        simple-update
        select-only
--可以pgbench测试中用-b指定使用哪个脚本来跑测试，默认是使用tpcb-like
也可以用-f指定自定义的脚本文件，可以@数字指定不同的权重

• benchmark测试

[postgres@pg93 ~]$ pgbench -n -b simple-update@2 -b select-only@8 -b tpcb@1 -c 10 -j 4 -T 60 -P 5 -U fin_er findb
progress: 5.0 s, 3375.1 tps, lat 2.905 ms stddev 9.901
progress: 10.0 s, 1280.0 tps, lat 7.905 ms stddev 28.248
progress: 15.0 s, 1423.3 tps, lat 6.975 ms stddev 24.955
progress: 20.0 s, 1957.8 tps, lat 5.135 ms stddev 18.538
progress: 25.0 s, 1233.9 tps, lat 8.099 ms stddev 28.875
progress: 30.0 s, 1337.2 tps, lat 7.435 ms stddev 27.373
progress: 35.0 s, 1968.1 tps, lat 4.941 ms stddev 20.398
progress: 40.0 s, 2719.7 tps, lat 3.764 ms stddev 19.946
progress: 45.0 s, 2842.0 tps, lat 3.533 ms stddev 16.449
progress: 50.0 s, 2600.6 tps, lat 3.841 ms stddev 16.897
progress: 55.0 s, 3154.5 tps, lat 3.136 ms stddev 13.296
progress: 60.0 s, 1100.6 tps, lat 9.102 ms stddev 34.015
transaction type: multiple scripts
scaling factor: 20
query mode: simple
number of clients: 10
number of threads: 4
duration: 60 s
number of transactions actually processed: 124980
latency average = 4.791 ms
latency stddev = 20.278 ms
tps = 2082.687007 (including connections establishing)
tps = 2083.029731 (excluding connections establishing)
SQL script 1: <builtin: simple update>
 - weight: 2 (targets 18.2% of total)
 - 22740 transactions (18.2% of total, tps = 378.943051)
 - latency average = 14.226 ms
 - latency stddev = 35.193 ms
SQL script 2: <builtin: select only>
 - weight: 8 (targets 72.7% of total)
 - 90495 transactions (72.4% of total, tps = 1508.023369)
 - latency average = 0.846 ms
 - latency stddev = 4.494 ms
SQL script 3: <builtin: TPC-B (sort of)>
 - weight: 1 (targets 9.1% of total)
 - 11364 transactions (9.1% of total, tps = 189.371541)
 - latency average = 17.327 ms
 - latency stddev = 37.401 ms

-b指定脚本，@后面的数字为比重，后面的脚本名字在不重复的情况下可以简写，-j指定cpu数，-c为client数，-T为持续时间，单位s，-t为要执行的总事务数
还可以用R指定固定的运行事务速率（如果超过最大可运行的则无效），还可以用-L指定最大延迟（单位为ms），对超过该值的事务进行独立报告和计数，可以用-l指定输出到日志，默认名字pgbench_log.pid