PostgreSQL pgbench 基准测试工具

pgbench

pgbench是PostgreSQL自带的一个基准测试工具，用于模拟数据库负载、测试数据库性能和进行基准测试。它可以用来执行各种类型的压力测试，以评估PostgreSQL数据库在不同情况下的表现。

初始化数据

• -U：指定连接数据库用户。
• -i：表示初始化数据库。
• -s 1：表示缩放因子（scale factor）。1表示生成标准数量的数据行 （大约100000行）。
• 最后的关键字是指数据初始化在哪个数据库。

pgbench -U postgres -i -s 1 postgres

输出

$ pgbench -U postgres -i postgres
Password:
dropping old tables...
creating tables...
generating data (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.50 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 1.04 s (drop tables 0.01 s, create tables 0.03 s, client-side generate 0.55 s, vacuum 0.24 s, primary keys 0.21 s).

运行基准测试

• -c 10：表示使用10个并发连接。
• -T 60：表示测试持续时间为60秒。
• 最后的关键字是指数据初始化在哪个数据库。

pgbench -U postgres -c 10 -T 60 postgres

输出

$ pgbench -U postgres -c 10 -T 60 postgres
Password:
pgbench (15.3)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
duration: 60 s
number of transactions actually processed: 52968
latency average = 11.217 ms
initial connection time = 597.902 ms
tps = 891.502626 (without initial connection time)

更多测试

创建2000000数据，并指定pgbench_accounts测试表，创建10个分区。

pgbench -U postgres -i -s 20 --partitions=10 postgres

输出

$ pgbench -U postgres -i -s 20 --partitions=10 postgres
Password:
dropping old tables...
creating tables...
creating 10 partitions...
generating data (client-side)...
2000000 of 2000000 tuples (100%) done (elapsed 11.94 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 19.60 s (drop tables 0.03 s, create tables 0.06 s, client-side generate 12.09 s, vacuum 3.88 s, primary keys 3.53 s).

查看数据表

postgres=# \d+
                                                 List of relations
 Schema |        Name         |       Type        |  Owner   | Persistence | Access method |  Size   | Description 
--------+---------------------+-------------------+----------+-------------+---------------+---------+-------------
 public | pgbench_accounts    | partitioned table | postgres | permanent   |               | 0 bytes | 
 public | pgbench_accounts_1  | table             | postgres | permanent   | heap          | 26 MB   | 
 public | pgbench_accounts_10 | table             | postgres | permanent   | heap          | 26 MB   | 
 public | pgbench_accounts_2  | table             | postgres | permanent   | heap          | 26 MB   | 
 public | pgbench_accounts_3  | table             | postgres | permanent   | heap          | 26 MB   | 
 public | pgbench_accounts_4  | table             | postgres | permanent   | heap          | 26 MB   | 
 public | pgbench_accounts_5  | table             | postgres | permanent   | heap          | 26 MB   | 
 public | pgbench_accounts_6  | table             | postgres | permanent   | heap          | 26 MB   | 
 public | pgbench_accounts_7  | table             | postgres | permanent   | heap          | 26 MB   | 
 public | pgbench_accounts_8  | table             | postgres | permanent   | heap          | 26 MB   | 
 public | pgbench_accounts_9  | table             | postgres | permanent   | heap          | 26 MB   | 
 public | pgbench_branches    | table             | postgres | permanent   | heap          | 40 kB   | 
 public | pgbench_history     | table             | postgres | permanent   | heap          | 0 bytes | 
 public | pgbench_tellers     | table             | postgres | permanent   | heap          | 48 kB   | 
(14 rows)

运行测试，-P 5：每隔5秒输出一次当前结果。

pgbench -U postgres -c 10 -j 10 -T 60 -P 5 postgres 

输出

$ pgbench -U postgres -c 10 -j 10 -T 60 -P 5 postgres
Password:
pgbench (15.3)
starting vacuum...end.
progress: 5.0 s, 1792.5 tps, lat 5.441 ms stddev 2.744
progress: 10.0 s, 1684.2 tps, lat 5.935 ms stddev 1.334
progress: 15.0 s, 1720.5 tps, lat 5.807 ms stddev 1.169
progress: 20.0 s, 1644.2 tps, lat 6.082 ms stddev 1.319
progress: 25.0 s, 1527.8 tps, lat 6.540 ms stddev 5.503
progress: 30.0 s, 1653.2 tps, lat 6.048 ms stddev 1.352
progress: 35.0 s, 1648.0 tps, lat 6.066 ms stddev 2.170
progress: 40.0 s, 1671.4 tps, lat 5.981 ms stddev 1.238
progress: 45.0 s, 1536.0 tps, lat 6.507 ms stddev 7.110
progress: 50.0 s, 1562.4 tps, lat 6.398 ms stddev 3.328
progress: 55.0 s, 1665.7 tps, lat 6.000 ms stddev 2.336
progress: 60.0 s, 1682.5 tps, lat 5.941 ms stddev 1.237
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 20
partition method: range
partitions: 10
query mode: simple
number of clients: 10
number of threads: 10
duration: 60 s
number of transactions actually processed: 98947
latency average = 6.049 ms
latency stddev = 3.095 ms
initial connection time = 123.589 ms
tps = 1652.276839 (without initial connection time)

官方文档

https://www.postgresql.org/docs/17/pgbench.html