PostgreSQL 基准测试与压测方法论

基准测试（Benchmark）  固定负载，测系统的能力上限与配置差异
负载测试（Load）       逐步加压到目标业务量，验证是否满足 SLA
压力测试（Stress）     超过预期负载，找出崩溃点与降级行为
稳定性测试（Soak）     长时间中等负载运行，暴露内存泄漏与连接耗尽

1. 明确问题：要验证什么？扩容？改参数？换硬件？
2. 定义指标：tps、P95 延迟、错误率、资源利用率
3. 构造数据：数据量与分布要接近生产
4. 预热：让缓存与连接池进入稳态
5. 执行：至少三轮，取中位数
6. 分析：结合数据库与系统两层指标定位瓶颈
7. 复现：记录完整配置，确保他人能重现

# 初始化：-s 是缩放因子，1 单位约等于 10 万行
pgbench -i -s 100 -F 90 -U postgres mydb

# 含义
# -s 100  -> pgbench_accounts 约 1000 万行，约 1.35GB
# -F 90   -> fillfactor = 90，减少更新时的页分裂
# -i 会重建 pgbench_accounts / branches / tellers / history

-- 确认数据量与分布
SELECT count(*) FROM pgbench_accounts;   -- 期望 100 * 100000
SELECT count(*) FROM pgbench_branches;   -- 期望 100
SELECT pg_size_pretty(pg_total_relation_size('pgbench_accounts'));

# tpcb-like：TPC-B 风格，含 SELECT + UPDATE + INSERT + UPDATE
pgbench -b tpcb-like -c 32 -j 8 -T 300 mydb

# simple-update：去掉 SELECT，纯 UPDATE
pgbench -b simple-update -c 32 -j 8 -T 300 mydb

# select-only：只读
pgbench -b select-only -c 32 -j 8 -T 300 mydb

# 混合比例（多脚本按权重）
pgbench -b select-only@8 -b tpcb-like@2 -c 32 -j 8 -T 300 mydb

pgbench -c 32 -j 8 -T 300 mydb

-c  并发客户端数（相当于并发连接数）
-j  工作线程数，通常取 CPU 核数，且 j <= c
-T  持续秒数（与 -t 事务数二选一）

-- bench.sql
\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set delta random(-5000, 5000)

BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
    VALUES (:bid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;

pgbench -f bench.sql -c 32 -j 8 -T 300 mydb

random(1, :scale * 100000)                  均匀分布，命中均匀
random_gaussian(1, 100000, 2.0)             高斯分布，模拟热点
random_exponential(1, 100000, 2.0)          指数分布，长尾

-- 脚本内可写多个事务，用 \set 决定走哪条分支
\set r random(1, 100)
\if :r <= 80
    SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
\else
    UPDATE pgbench_accounts SET abalance = abalance + 1 WHERE aid = :aid;
\endif

pgbench -c 32 -j 8 -T 300 -M prepared -P 10 --rate 1000 \
        --latency-limit 50 -n -U postgres mydb

-c, --client=NUM       并发客户端数
-j, --jobs=NUM         工作线程数
-t, --transactions=NUM 每客户端执行的事务数
-T, --time=NUM         持续秒数
-M, --protocol=MODE    simple / extended / prepared（默认 simple）
-f, --file=FILENAME    自定义脚本
-b, --builtin=NAME     内置脚本
-n, --no-vacuum        跳过初始化前的 VACUUM
-P, --progress=NUM     每 N 秒打印进度
--rate=NUM             目标 tps，限流模式（泊松到达）
--latency-limit=NUM    超过此毫秒数的延迟不计入统计
--max-tries=NUM        失败事务的重试次数

simple     每个事务一次简单查询协议，含解析开销
extended   扩展查询协议，每次重新解析
prepared   使用预备语句，跳过解析，最接近生产 ORM 的行为

transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: prepared
number of clients: 32
number of threads: 8
duration: 300 s
number of transactions actually processed: 4821356
latency average = 1.988 ms
latency stddev = 3.121 ms
initial connection time = 214.335 ms
tps = 16068.918250 (without initial connection time)

# 拿到分位数（需要 --progress 之外的额外工具或解析）
pgbench -c 32 -j 8 -T 300 -P 10 --rate 1000 --latency-limit 50 mydb
# 输出中的 "number of transactions skipped" 表示超时被丢弃的事务数

1. 客户端瓶颈：pgbench 自己 CPU 打满，测出的是客户端上限
   -> 用多个客户端机器，或观察 pgbench 进程的 CPU 占用
2. 缓存假象：数据全在 shared_buffers 或 OS page cache 中
   -> 用足够大的 -s，或压测前清空缓存（重启实例）
3. 参数宽松：生产用 fsync=on，压测却关了
   -> 压测配置必须与生产一致
4. 只测只读：生产是读写混合，只读测试高估容量
   -> 按真实读写比构造脚本
5. 忽略连接建立：连接池预热后测得的 tps 远高于冷启动
   -> 报告要注明是否预热
6. 单轮结论：一次运行受随机波动影响
   -> 至少三轮取中位数

-- 检查压测期间是否真的在读磁盘（blks_read 增长说明没全命中缓存）
SELECT blks_read, blks_hit,
       round(100.0 * blks_hit / greatest(blks_hit + blks_read, 1), 2) AS hit_pct
FROM pg_stat_database WHERE datname = 'mydb';

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, mean_exec_time, total_exec_time,
       rows, shared_blks_read, shared_blks_hit
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;

-- 压测中另开一个会话采样
SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active' AND backend_type = 'client backend'
GROUP BY 1, 2 ORDER BY 3 DESC;

LWLock / WALWriteLock        写 WAL 竞争，检查 checkpoint 与 synchronous_commit
Lock / transactionid         行锁或事务 ID 等待，检查长事务与热点行
IO / DataFileRead            磁盘读等待，缓存不足或索引缺失
Client / ClientRead          客户端处理慢，可能是客户端瓶颈
CPU（wait_event 为空）        纯计算，考虑优化 SQL 或加 CPU

iostat -x 1 10
# 关注 %util（设备饱和度）、await（平均 IO 等待毫秒）、r/s 与 w/s

vmstat 1
# 关注 r（运行队列长度，超过核数说明 CPU 饱和）、si/so（换页）

1. 压测机与数据库机分离，避免互相争抢 CPU
2. 关闭其他负载：备份、autovacuum 大表、监控采集降频
3. 固定配置：记录 postgresql.conf 的全部非默认项
4. 固定数据：同一份初始化数据与同一随机种子
5. 预热：先跑 30 秒预热，再开始计时
6. 记录硬件：CPU 型号核数、内存、磁盘类型与 RAID 级别

# 保存完整配置快照
psql -c "SELECT name, setting, unit FROM pg_settings WHERE source <> 'default';" \
     > config-snapshot.txt

=== PostgreSQL 压测报告 ===
日期 / 执行人：
目标问题：

[环境]
CPU / 内存 / 磁盘 / 文件系统：
PostgreSQL 版本 / 关键参数（shared_buffers, work_mem, fsync, wal_level）：
pgbench 版本 / 客户端机器配置：

[数据集]
缩放因子 -s：        实际行数：
数据总量：           是否预热：

[负载]
脚本 / 并发 -c / 线程 -j / 时长 -T / 协议 -M：
读写比：             限流 --rate：

[结果]
tps（三轮）：        中位数：
latency average / stddev：
错误 / 超时事务数：

[瓶颈分析]
数据库层：           系统层：
结论与建议：

# ========== 1. 初始化数据集 ==========
pgbench -i -s 100 -F 90 -U postgres mydb
psql -c "SELECT count(*) FROM pgbench_accounts;"
psql -c "SELECT pg_size_pretty(pg_total_relation_size('pgbench_accounts'));"

# ========== 2. 内置脚本基准测试（三轮取中位数） ==========
for i in 1 2 3; do
  pgbench -b tpcb-like -c 32 -j 8 -T 300 -M prepared -P 30 mydb
done

# ========== 3. 混合读写（80% 只读） ==========
pgbench -b select-only@8 -b tpcb-like@2 -c 32 -j 8 -T 300 -M prepared mydb

# ========== 4. 限流负载测试 ==========
pgbench -f bench.sql -c 32 -j 8 -T 300 -M prepared \
        --rate 1000 --latency-limit 50 -P 30 mydb

# ========== 5. 系统层采样（另开终端） ==========
iostat -x 1 30 > iostat.log &
vmstat 1 30 > vmstat.log &

-- ========== 6. 数据库层采样 ==========
SELECT blks_read, blks_hit,
       round(100.0 * blks_hit / greatest(blks_hit + blks_read, 1), 2) AS hit_pct
FROM pg_stat_database WHERE datname = 'mydb';

SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active' AND backend_type = 'client backend'
GROUP BY 1, 2 ORDER BY 3 DESC;

SELECT query, calls, mean_exec_time, shared_blks_read
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

-- ========== 7. 保存配置快照 ==========
SELECT name, setting, unit FROM pg_settings WHERE source <> 'default';