pgbench

pgbench是内置的基准测试工具

1、初始化测试数据

[pg14@pg_master_1 ~]$ pgbench -i
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 (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.28 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.56 s (drop tables 0.04 s, create tables 0.02 s, client-side generate 0.36 s, vacuum 0.09 s, primary keys 0.05 s).

2、使用内置脚本测试

内置脚本有三种类型tpcb-like、simple-update、select-only。

tpcb-like类型

[pg14@pg_master_1 ~]$ pgbench -b tpcb-like -h pg_master_1 -p 10240 -U pg14 pg14

pgbench (14.6)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
latency average = 1.846 ms
initial connection time = 3.460 ms
tps = 541.682466 (without initial connection time)

simple-update类型
[pg14@pg_master_1 ~]$ pgbench -b simple-update -h pg_master_1 -p 10240 -U pg14 pg14

pgbench (14.6)
starting vacuum...end.
transaction type: <builtin: simple update>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
latency average = 1.717 ms
initial connection time = 3.668 ms
tps = 582.445105 (without initial connection time)

select-only类型

[pg14@pg_master_1 ~]$ pgbench -b select-only -h pg_master_1 -p 10240 -U pg14 pg14
pgbench (14.6)
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
latency average = 0.411 ms
initial connection time = 3.369 ms
tps = 2434.867300 (without initial connection time)

还可以使用三种内置脚本混合进行测试，在脚本名称后面加上@符号，@符号后加一个脚本运行比例的权重的整数值，例如使用simple-update和select-only两种内置脚本，并且以2:8的比例混合进行测试，代码如下所示：

[pg14@pg_master_1 ~]$ pgbench -b simple-update@2 -b select-only@8 -b tpcb@0 -h pg_master_1 -p 10240 -U pg14 pg14
pgbench (14.6)
starting vacuum...end.
transaction type: multiple scripts
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
latency average = 0.623 ms
initial connection time = 3.158 ms
tps = 1604.621309 (without initial connection time)
SQL script 1: <builtin: simple update>
 - weight: 2 (targets 20.0% of total)
 - 2 transactions (20.0% of total, tps = 320.924262)
 - latency average = 2.045 ms
 - latency stddev = 0.851 ms
SQL script 2: <builtin: select only>
 - weight: 8 (targets 80.0% of total)
 - 8 transactions (80.0% of total, tps = 1283.697047)
 - latency average = 0.252 ms
 - latency stddev = 0.103 ms
SQL script 3: <builtin: TPC-B (sort of)>
 - weight: 0 (targets 0.0% of total)
 - 0 transactions (0.0% of total, tps = 0.000000)