PostgreSQL性能测试实战：pgbench压测教程（TPS优化+最大并发测试）

pgbench是PostgreSQL官方自带的免费性能压测工具，无需额外安装，是数据库运维和开发者测试PostgreSQL吞吐量的首选工具。本文通过在2核i5-4260U服务器上的真实压测数据，详解TPS指标含义、pgbench核心参数、不同并发下的性能表现，帮你精准找到数据库的最佳并发数和最大TPS峰值，同时解决压测过程中常见的"too many clients already"报错问题。

一、TPS 概念解释

tps：transactions per second（每秒事务数）的缩写在pgbench的测试结果中，tps表示数据库每秒钟能够完成多少个事务

对于只读测试，一个“事务”就是一条select查询语句
对于默认的tpc-B测试（读写混合），一个事务是一个完整的业务单元：begin–>更新账户–>查询余额–>更新出纳员–>更新分行–>插入历史–>commit tps是衡量数据库吞吐量（处理能力）最核心的指标，tps越高，代表数据库在单位时间内能处理的请求越多，性能越好。

二、pgbench 测试参数解释

常用测试命令示例

pgbench -S -c 16 -j 4 -T 60 -M prepared -r pgbench_test
pgbench -c 16 -j 4 -T 60 -M prepared -r pgbench_test

只读模式（-S），并发16，线程4，持续60s

postgres@lzy:~$ pgbench -S -c 16 -j 4 -T 60 -M prepared -r pgbench_test
pgbench (16.13 (Ubuntu 16.13-0ubuntu0.24.04.1))
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 16
number of threads: 4
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 2262366
number of failed transactions: 0 (0.000%)
latency average = 0.424 ms
initial connection time = 21.638 ms
tps = 37719.421242 (without initial connection time)
statement latencies in milliseconds and failures:
         0.001           0  \set aid random(1, 100000 * :scale)
         0.420           0  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

读写混合模式，并发16，线程4，持续60s

postgres@lzy:~$ pgbench -c 16 -j 4 -T 60 -M prepared -r pgbench_test
pgbench (16.13 (Ubuntu 16.13-0ubuntu0.24.04.1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: prepared
number of clients: 16
number of threads: 4
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 94767
number of failed transactions: 0 (0.000%)
latency average = 10.258 ms
initial connection time = 20.287 ms
tps = 1559.782572 (without initial connection time)
statement latencies in milliseconds and failures:
         0.002           0  \set aid random(1, 100000 * :scale)
         0.001           0  \set bid random(1, 1 * :scale)
         0.001           0  \set tid random(1, 10 * :scale)
         0.001           0  \set delta random(-5000, 5000)
         0.320           0  BEGIN;
         0.533           0  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.465           0  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.377           0  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.908           0  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.232           0  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         7.273           0  END;

参数说明

-S：只读模式，加上-S时，只执行select查询，不执行更新/插入。不加时执行默认的tpc-B读写混合事务
-c：客户端并发数
-j：工作线程数，使用指定数量的cpu线程来驱动这些客户端。通常设为cpu核心数
-T：测试持续时间，压测持续时间，如果不指定-T或-t，默认只运行10s
-r：在测试结束后，额外输出每条 SQL 语句的平均耗时
-M：查询模式，使用预处理语句，可以减少sql解析和计划开销，使测试更聚焦于数据库执行性能

三、服务器 CPU 信息与 pgbench -j 参数最佳实践

postgres@lzy:~$ lscpu
Architecture:                x86_64
  CPU op-mode(s):            32-bit, 64-bit
  Address sizes:             39 bits physical, 48 bits virtual
  Byte Order:                Little Endian
CPU(s):                      4     逻辑cpu总数
  On-line CPU(s) list:       0-3
Vendor ID:                   GenuineIntel
  Model name:                Intel(R) Core(TM) i5-4260U CPU @ 1.40GHz
    CPU family:              6
    Model:                   69
    Thread(s) per core:      2     物理核心数
    Core(s) per socket:      2    每个核心2个超线程
    Socket(s):               1
    Stepping:                1
    CPU(s) scaling MHz:      52%

结论：根据电脑cpu情况，-j 线程数建议为物理核心数2，让每个核心处理一个工作线程。

四、pgbench 测试结果

测试1：读写混合模式，不同并发（4/8/16/32/64），线程2，持续30s

4.1 并发4，线程2

postgres@lzy:~$ pgbench -c 4 -j 2 -T 30 -M prepared -r pgbench_test
pgbench (16.13 (Ubuntu 16.13-0ubuntu0.24.04.1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: prepared
number of clients: 4
number of threads: 2
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 33938
number of failed transactions: 0 (0.000%)
latency average = 3.535 ms
initial connection time = 8.900 ms
tps = 1131.527031 (without initial connection time)
statement latencies in milliseconds and failures:
         0.002           0  \set aid random(1, 100000 * :scale)
         0.001           0  \set bid random(1, 1 * :scale)
         0.001           0  \set tid random(1, 10 * :scale)
         0.001           0  \set delta random(-5000, 5000)
         0.091           0  BEGIN;
         0.246           0  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.116           0  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.135           0  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.206           0  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.103           0  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         2.634           0  END;

4.2 并发8，线程2

postgres@lzy:~$ pgbench -c 8 -j 2 -T 30 -M prepared -r pgbench_test
pgbench (16.13 (Ubuntu 16.13-0ubuntu0.24.04.1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: prepared
number of clients: 8
number of threads: 2
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 36874
number of failed transactions: 0 (0.000%)
latency average = 6.702 ms
initial connection time = 13.167 ms
tps = 1193.745774 (without initial connection time)
statement latencies in milliseconds and failures:
         0.002           0  \set aid random(1, 100000 * :scale)
         0.001           0  \set bid random(1, 1 * :scale)
         0.001           0  \set tid random(1, 10 * :scale)
         0.001           0  \set delta random(-5000, 5000)
         0.162           0  BEGIN;
         0.342           0  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.236           0  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.232           0  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.388           0  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.155           0  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         5.060           0  END;

4.3 并发16，线程2

postgres@lzy:~$ pgbench -c 16 -j 2 -T 30 -M prepared -r pgbench_test
pgbench (16.13 (Ubuntu 16.13-0ubuntu0.24.04.1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: prepared
number of clients: 16
number of threads: 2
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 71725
number of failed transactions: 0 (0.000%)
latency average = 6.845 ms
initial connection time = 26.046 ms
tps = 2337.479811 (without initial connection time)
statement latencies in milliseconds and failures:
         0.002           0  \set aid random(1, 100000 * :scale)
         0.001           0  \set bid random(1, 1 * :scale)
         0.001           0  \set tid random(1, 10 * :scale)
         0.001           0  \set delta random(-5000, 5000)
         0.296           0  BEGIN;
         0.500           0  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.444           0  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.301           0  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.636           0  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.237           0  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         4.284           0  END;

4.4 并发32，线程2

postgres@lzy:~$ pgbench -c 32 -j 2 -T 30 -M prepared -r pgbench_test
pgbench (16.13 (Ubuntu 16.13-0ubuntu0.24.04.1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: prepared
number of clients: 32
number of threads: 2
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 75520
number of failed transactions: 0 (0.000%)
latency average = 13.173 ms
initial connection time = 51.288 ms
tps = 2429.190715 (without initial connection time)
statement latencies in milliseconds and failures:
         0.002           0  \set aid random(1, 100000 * :scale)
         0.001           0  \set bid random(1, 1 * :scale)
         0.001           0  \set tid random(1, 10 * :scale)
         0.001           0  \set delta random(-5000, 5000)
         0.455           0  BEGIN;
         0.694           0  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.825           0  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.624           0  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         1.855           0  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.495           0  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         7.975           0  END;

4.5 并发64，线程2

postgres@lzy:~$ pgbench -c 64 -j 2 -T 30 -M prepared -r pgbench_test
pgbench (16.13 (Ubuntu 16.13-0ubuntu0.24.04.1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: prepared
number of clients: 64
number of threads: 2
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 74684
number of failed transactions: 0 (0.000%)
latency average = 26.209 ms
initial connection time = 104.197 ms
tps = 2441.905430 (without initial connection time)
statement latencies in milliseconds and failures:
         0.002           0  \set aid random(1, 100000 * :scale)
         0.001           0  \set bid random(1, 1 * :scale)
         0.001           0  \set tid random(1, 10 * :scale)
         0.001           0  \set delta random(-5000, 5000)
         0.846           0  BEGIN;
         1.493           0  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         1.856           0  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         1.884           0  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         6.508           0  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         1.821           0  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
        11.481           0  END;

五、测试结论

并发: 4    8    16   32   64
TPS: 1131 1194 2337 2429 2442

4 → 8 并发：TPS 几乎不变（1131 → 1194），延迟从 3.5ms 涨到 6.7ms。说明并发翻倍但没有带来吞吐量提升，资源开始出现竞争。
8 → 16 并发：TPS 翻倍（1194 → 2337），这是合理的提升。延迟基本持平（6.7ms → 6.8ms）。说明在 16 并发以内，系统还能有效利用并发。
16 → 32 并发：TPS 几乎不再增长（2337 → 2429），仅增加 4%，而延迟翻倍（6.8ms → 13.2ms）。
32 → 64 并发：TPS 完全停滞（2429 → 2442），延迟再次翻倍（13.2ms → 26.2ms）。

核心结论

性能拐点在 16 并发。超过 16 后，增加并发只会让延迟飙升，吞吐量基本不变。最大 TPS 约 2440（在当前硬件和配置下）。当前服务器（2 核 i5-4260U，默认 PostgreSQL 配置）在 pgbench 读写混合测试中，最大 TPS 约 2440，最佳并发 16。性能瓶颈主要是磁盘写入延迟（WAL fsync）和单行表锁竞争，CPU 和内存不是首要限制。

六、常见报错：too many clients already 解决方案

报错信息

postgres@lzy:~$ postgres@lzy:~$ pgbench -c 128 -j 2 -T 30 -M prepared -r pgbench_test
pgbench (16.13 (Ubuntu 16.13-0ubuntu0.24.04.1))
starting vacuum...end.
pgbench: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  sorry, too many clients already
pgbench: error: could not create connection for client 113

排查结果

postgres=# show max_connections;
 max_connections
 100
(1 row)

一、TPS 概念解释#

二、pgbench 测试参数解释#

常用测试命令示例#

只读模式（-S），并发16，线程4，持续60s#

读写混合模式，并发16，线程4，持续60s#

参数说明#

三、服务器 CPU 信息与 pgbench -j 参数最佳实践#

四、pgbench 测试结果#

测试1：读写混合模式，不同并发（4/8/16/32/64），线程2，持续30s#

4.1 并发4，线程2#

4.2 并发8，线程2#

4.3 并发16，线程2#

4.4 并发32，线程2#

4.5 并发64，线程2#

五、测试结论#

核心结论#

六、常见报错：too many clients already 解决方案#

报错信息#

排查结果#

相关阅读#