强曰为道

与天地相似，故不违。知周乎万物，而道济天下，故不过。旁行而不流，乐天知命，故不忧.
文档目录

第七章：PostgreSQL 性能测试

第七章：PostgreSQL 性能测试

7.1 概述

PostgreSQL 是另一款广泛使用的开源关系型数据库，以其丰富的功能、ACID 合规性和扩展性著称。本章介绍如何使用 Sysbench 对 PostgreSQL 进行性能测试，并与 MySQL 进行对比。

7.2 PostgreSQL 连接配置

7.2.1 基本连接选项

选项默认值说明
--pgsql-host127.0.0.1PostgreSQL 服务器地址
--pgsql-port5432PostgreSQL 端口
--pgsql-userroot用户名
--pgsql-password(空)密码
--pgsql-dbsbtest测试数据库名

注意：Sysbench 的 PostgreSQL 驱动使用 --pgsql- 前缀（不是 --psql-）。

7.2.2 创建测试用户和数据库

-- 连接到 PostgreSQL
-- psql -U postgres

-- 创建测试用户
CREATE USER sbench WITH PASSWORD 'strong_password_here';

-- 创建测试数据库
CREATE DATABASE sbtest OWNER sbench;

-- 授予权限
GRANT ALL PRIVILEGES ON DATABASE sbtest TO sbench;

7.2.3 pg_hba.conf 配置

确保 pg_hba.conf 允许测试用户连接：

# TYPE  DATABASE  USER   ADDRESS       METHOD
host    sbtest    sbench 127.0.0.1/32  md5
host    sbtest    sbench 192.168.1.0/24 md5

# 修改后重载配置
sudo systemctl reload postgresql
# 或
sudo pg_ctlcluster <version> main reload

7.2.4 postgresql.conf 关键参数

# 连接设置
max_connections = 200
superuser_reserved_connections = 3

# 内存设置
shared_buffers = 4GB              # 类似 MySQL 的 buffer_pool_size
effective_cache_size = 12GB       # 操作系统缓存估算
work_mem = 64MB                   # 每个排序/哈希操作的内存
maintenance_work_mem = 1GB        # VACUUM/CREATE INDEX 的内存

# WAL 设置
wal_level = replica
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB

# 查询优化
random_page_cost = 1.1            # SSD 建议 1.1，HDD 保持默认 4.0
effective_io_concurrency = 200    # SSD 建议 200，HDD 保持默认 1

7.3 Sysbench 编译 PostgreSQL 支持

7.3.1 验证编译选项

# 检查 sysbench 是否支持 PostgreSQL
sysbench --list-tests
# 应该能看到 oltp_read_write 等测试

# 检查驱动
sysbench --db-driver=pgsql --help | grep pgsql

7.3.2 从源码编译（启用 PostgreSQL 支持）

# 安装 PostgreSQL 开发库
# Debian/Ubuntu
sudo apt install -y libpq-dev

# CentOS/RHEL
sudo dnf install -y postgresql-devel

# 编译 sysbench
git clone https://github.com/akopytov/sysbench.git
cd sysbench
./autogen.sh
./configure --with-pgsql --with-mysql --with-lua
make -j$(nproc)
sudo make install

7.4 PostgreSQL 标准测试

7.4.1 OLTP 读写混合测试

# 准备数据
sysbench oltp_read_write \
  --db-driver=pgsql \
  --pgsql-host=127.0.0.1 \
  --pgsql-port=5432 \
  --pgsql-user=sbench \
  --pgsql-password=secret \
  --pgsql-db=sbtest \
  --tables=16 \
  --table-size=1000000 \
  prepare

# 预热
sysbench oltp_read_write \
  --db-driver=pgsql \
  --pgsql-host=127.0.0.1 \
  --pgsql-user=sbench \
  --pgsql-password=secret \
  --pgsql-db=sbtest \
  --tables=16 \
  --table-size=1000000 \
  --threads=16 \
  --time=60 \
  run > /dev/null 2>&1

# 正式测试
sysbench oltp_read_write \
  --db-driver=pgsql \
  --pgsql-host=127.0.0.1 \
  --pgsql-user=sbench \
  --pgsql-password=secret \
  --pgsql-db=sbtest \
  --tables=16 \
  --table-size=1000000 \
  --threads=32 \
  --time=300 \
  --histogram \
  --percentile=99 \
  run

# 清理
sysbench oltp_read_write \
  --db-driver=pgsql \
  --pgsql-host=127.0.0.1 \
  --pgsql-user=sbench \
  --pgsql-password=secret \
  --pgsql-db=sbtest \
  --tables=16 \
  cleanup

7.4.2 只读测试

sysbench oltp_read_only \
  --db-driver=pgsql \
  --pgsql-host=127.0.0.1 \
  --pgsql-user=sbench \
  --pgsql-password=secret \
  --pgsql-db=sbtest \
  --tables=16 \
  --table-size=1000000 \
  --threads=32 \
  --time=300 \
  run

7.4.3 点查询测试

sysbench oltp_point_select \
  --db-driver=pgsql \
  --pgsql-host=127.0.0.1 \
  --pgsql-user=sbench \
  --pgsql-password=secret \
  --pgsql-db=sbtest \
  --tables=16 \
  --table-size=1000000 \
  --threads=64 \
  --time=120 \
  run

7.4.4 写入测试

sysbench oltp_write_only \
  --db-driver=pgsql \
  --pgsql-host=127.0.0.1 \
  --pgsql-user=sbench \
  --pgsql-password=secret \
  --pgsql-db=sbtest \
  --tables=16 \
  --table-size=1000000 \
  --threads=32 \
  --time=300 \
  run

7.5 PostgreSQL 特有配置测试

7.5.1 shared_buffers 调优

shared_buffers 是 PostgreSQL 最重要的内存参数，类似 MySQL 的 innodb_buffer_pool_size。

#!/bin/bash
# pg_shared_buffers_test.sh

HOST="127.0.0.1"
USER="sbench"
PASS="secret"
TABLES=16
TABLE_SIZE=1000000

# 准备数据
sysbench oltp_read_write \
  --db-driver=pgsql --pgsql-host=$HOST --pgsql-user=$USER --pgsql-password=$PASS \
  --tables=$TABLES --table-size=$TABLE_SIZE prepare

for size in "256MB" "512MB" "1GB" "2GB" "4GB" "8GB"; do
  echo "=== shared_buffers = $size ==="
  
  # 修改 postgresql.conf（需要重启）
  sudo -u postgres sed -i "s/^shared_buffers = .*/shared_buffers = '$size'/" \
    /etc/postgresql/*/main/postgresql.conf
  sudo systemctl restart postgresql
  sleep 10
  
  # 预热
  sysbench oltp_read_write \
    --db-driver=pgsql --pgsql-host=$HOST --pgsql-user=$USER --pgsql-password=$PASS \
    --tables=$TABLES --table-size=$TABLE_SIZE \
    --threads=16 --time=30 run > /dev/null 2>&1
  
  # 正式测试
  sysbench oltp_read_write \
    --db-driver=pgsql --pgsql-host=$HOST --pgsql-user=$USER --pgsql-password=$PASS \
    --tables=$TABLES --table-size=$TABLE_SIZE \
    --threads=16 --time=300 \
    run 2>&1 | grep -E "(transactions|95th)"
  
  sleep 5
done

# 清理
sysbench oltp_read_write \
  --db-driver=pgsql --pgsql-host=$HOST --pgsql-user=$USER --pgsql-password=$PASS \
  --tables=$TABLES cleanup

7.5.2 WAL 设置测试

#!/bin/bash
# pg_wal_test.sh - WAL 配置对写入性能的影响

# 测试不同的 wal_level
for level in minimal replica logical; do
  echo "=== wal_level = $level ==="
  sudo -u postgres sed -i "s/^wal_level = .*/wal_level = $level/" \
    /etc/postgresql/*/main/postgresql.conf
  sudo systemctl restart postgresql
  sleep 10
  
  sysbench oltp_write_only \
    --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-user=sbench --pgsql-password=secret \
    --tables=16 --table-size=1000000 --threads=32 --time=120 \
    run 2>&1 | grep -E "(transactions|95th)"
done

# 测试 synchronous_commit 设置
for val in "on" "off" "remote_write"; do
  echo "=== synchronous_commit = $val ==="
  sudo -u postgres psql -c "ALTER SYSTEM SET synchronous_commit = '$val';"
  sudo -u postgres psql -c "SELECT pg_reload_conf();"
  sleep 5
  
  sysbench oltp_write_only \
    --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-user=sbench --pgsql-password=secret \
    --tables=16 --table-size=1000000 --threads=32 --time=120 \
    run 2>&1 | grep -E "(transactions|95th)"
done

# 恢复默认
sudo -u postgres psql -c "ALTER SYSTEM RESET ALL;"
sudo systemctl restart postgresql

7.5.3 checkpoint 设置测试

#!/bin/bash
# pg_checkpoint_test.sh

# checkpoint_completion_target：控制检查点写入的速度分布
for val in 0.1 0.5 0.9; do
  echo "=== checkpoint_completion_target = $val ==="
  sudo -u postgres psql -c "ALTER SYSTEM SET checkpoint_completion_target = $val;"
  sudo -u postgres psql -c "SELECT pg_reload_conf();"
  sleep 5
  
  sysbench oltp_write_only \
    --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-user=sbench --pgsql-password=secret \
    --tables=16 --table-size=1000000 --threads=32 --time=300 \
    run 2>&1 | grep -E "(transactions|95th|max:)"
done

# max_wal_size：更大的值可以减少检查点频率
for size in "1GB" "2GB" "4GB" "8GB"; do
  echo "=== max_wal_size = $size ==="
  sudo -u postgres psql -c "ALTER SYSTEM SET max_wal_size = '$size';"
  sudo -u postgres psql -c "SELECT pg_reload_conf();"
  sleep 5
  
  sysbench oltp_write_only \
    --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-user=sbench --pgsql-password=secret \
    --tables=16 --table-size=1000000 --threads=32 --time=300 \
    run 2>&1 | grep -E "(transactions|95th|max:)"
done

sudo -u postgres psql -c "ALTER SYSTEM RESET ALL;"
sudo systemctl restart postgresql

7.5.4 work_mem 测试

#!/bin/bash
# pg_work_mem_test.sh - work_mem 对复杂查询的影响

for size in "4MB" "16MB" "64MB" "256MB" "1GB"; do
  echo "=== work_mem = $size ==="
  sudo -u postgres psql -c "ALTER SYSTEM SET work_mem = '$size';"
  sudo -u postgres psql -c "SELECT pg_reload_conf();"
  sleep 5
  
  # oltp_read_only 包含 ORDER BY 和 DISTINCT 查询，受益于 work_mem
  sysbench oltp_read_only \
    --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-user=sbench --pgsql-password=secret \
    --tables=16 --table-size=1000000 --threads=32 --time=120 \
    run 2>&1 | grep -E "(transactions|95th)"
done

sudo -u postgres psql -c "ALTER SYSTEM RESET ALL;"
sudo systemctl restart postgresql

7.6 PostgreSQL 高级测试

7.6.1 连接池测试（PgBouncer）

#!/bin/bash
# pgbouncer_test.sh - 对比直连和连接池

# 直连 PostgreSQL
echo "=== 直连 PostgreSQL ==="
sysbench oltp_read_write \
  --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-port=5432 \
  --pgsql-user=sbench --pgsql-password=secret \
  --tables=16 --table-size=1000000 --threads=64 --time=300 \
  run 2>&1 | grep -E "(transactions|95th)"

# 通过 PgBouncer 连接
echo "=== PgBouncer (port 6432) ==="
sysbench oltp_read_write \
  --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-port=6432 \
  --pgsql-user=sbench --pgsql-password=secret \
  --tables=16 --table-size=1000000 --threads=64 --time=300 \
  run 2>&1 | grep -E "(transactions|95th)"

7.6.2 并行查询测试

# PostgreSQL 14+ 支持并行查询
# 测试不同 max_parallel_workers_per_gather 设置

for val in 0 1 2 4 8; do
  echo "=== max_parallel_workers_per_gather = $val ==="
  sudo -u postgres psql -c "ALTER SYSTEM SET max_parallel_workers_per_gather = $val;"
  sudo -u postgres psql -c "SELECT pg_reload_conf();"
  sleep 5
  
  sysbench oltp_read_only \
    --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-user=sbench \
    --tables=16 --table-size=1000000 --threads=8 --time=120 \
    run 2>&1 | grep -E "(transactions|95th)"
done

sudo -u postgres psql -c "ALTER SYSTEM RESET ALL;"
sudo systemctl restart postgresql

7.6.3 vacuum 和 analyze 影响测试

#!/bin/bash
# pg_vacuum_test.sh - 测试 VACUUM 对性能的影响

# 准备数据并修改大量行（产生死元组）
sysbench oltp_write_only \
  --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-user=sbench \
  --tables=16 --table-size=1000000 --threads=32 --time=120 \
  prepare run

# 测试 VACUUM 前的性能
echo "=== Before VACUUM ==="
sysbench oltp_read_write \
  --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-user=sbench \
  --tables=16 --table-size=1000000 --threads=16 --time=60 \
  run 2>&1 | grep -E "(transactions|95th)"

# 执行 VACUUM
echo "Running VACUUM ANALYZE..."
sudo -u postgres psql sbtest -c "
  SELECT 'VACUUM ANALYZE sbtest' || i || ';' 
  FROM generate_series(1, 16) AS i;
" | sudo -u postgres psql sbtest

# 测试 VACUUM 后的性能
echo "=== After VACUUM ==="
sysbench oltp_read_write \
  --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-user=sbench \
  --tables=16 --table-size=1000000 --threads=16 --time=60 \
  run 2>&1 | grep -E "(transactions|95th)"

7.7 PostgreSQL vs MySQL 对比测试

7.7.1 标准化对比脚本

#!/bin/bash
# pg_vs_mysql.sh - PostgreSQL vs MySQL 对比测试

TABLES=16
TABLE_SIZE=1000000
DURATION=300
THREADS=32

# --- MySQL ---
echo "============================================"
echo "MySQL Test"
echo "============================================"
sysbench oltp_read_write \
  --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=secret \
  --tables=$TABLES --table-size=$TABLE_SIZE prepare

sysbench oltp_read_write \
  --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=secret \
  --tables=$TABLES --table-size=$TABLE_SIZE \
  --threads=$THREADS --time=$DURATION \
  --json=mysql_result.json run

sysbench oltp_read_write \
  --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=secret \
  --tables=$TABLES cleanup

# --- PostgreSQL ---
echo "============================================"
echo "PostgreSQL Test"
echo "============================================"
sysbench oltp_read_write \
  --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-user=sbench --pgsql-password=secret \
  --tables=$TABLES --table-size=$TABLE_SIZE prepare

sysbench oltp_read_write \
  --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-user=sbench --pgsql-password=secret \
  --tables=$TABLES --table-size=$TABLE_SIZE \
  --threads=$THREADS --time=$DURATION \
  --json=pg_result.json run

sysbench oltp_read_write \
  --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-user=sbench --pgsql-password=secret \
  --tables=$TABLES cleanup

# --- 结果对比 ---
echo ""
echo "============================================"
echo "结果对比"
echo "============================================"
echo ""
echo "| 指标 | MySQL | PostgreSQL |"
echo "|------|-------|------------|"
mysql_tps=$(jq '.transactions.per_second' mysql_result.json)
pg_tps=$(jq '.transactions.per_second' pg_result.json)
echo "| TPS | $mysql_tps | $pg_tps |"
mysql_p95=$(jq '.latency.percentile_95' mysql_result.json)
pg_p95=$(jq '.latency.percentile_95' pg_result.json)
echo "| P95(ms) | $mysql_p95 | $pg_p95 |"

7.7.2 差异说明

方面MySQL (InnoDB)PostgreSQL
默认隔离级别REPEATABLE READREAD COMMITTED
锁机制行锁 + Gap Lock行锁 + MVCC
VACUUM不需要（Undo Log）需要（MVCC 旧版本清理）
连接模型线程模型进程模型（推荐用连接池）
扩展性良好优秀（支持并行查询）

注意：对比测试时，确保两个数据库使用相同的隔离级别，否则结果不可直接对比。

7.8 PostgreSQL 监控

7.8.1 测试期间监控

-- 查看活跃查询
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle' AND pid != pg_backend_pid()
ORDER BY duration DESC;

-- 查看数据库统计
SELECT datname, numbackends, xact_commit, xact_rollback, 
       blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted
FROM pg_stat_database WHERE datname = 'sbtest';

-- Buffer Cache 命中率
SELECT 
  sum(blks_hit) * 100.0 / (sum(blks_hit) + sum(blks_read)) AS hit_ratio
FROM pg_stat_database 
WHERE datname = 'sbtest';

-- 查看锁等待
SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query,
       blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

7.8.2 使用 pg_stat_statements

-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 查看最耗时的查询
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 重置统计
SELECT pg_stat_statements_reset();

7.9 注意事项

7.9.1 连接开销

PostgreSQL 使用进程模型（每个连接一个进程），连接开销比 MySQL（线程模型）大。在高并发测试时：

# 直连 PostgreSQL，64+ 并发时可能遇到性能瓶颈
# 推荐使用 PgBouncer 或 Pgpool-II

# PgBouncer 配置示例 (/etc/pgbouncer/pgbouncer.ini)
# [databases]
# sbtest = host=127.0.0.1 port=5432 dbname=sbtest
# 
# [pgbouncer]
# listen_port = 6432
# pool_mode = transaction
# max_client_conn = 1000
# default_pool_size = 50

7.9.2 autovacuum 影响

测试期间 autovacuum 可能自动运行，影响结果：

# 临时禁用 autovacuum（不推荐用于生产）
sudo -u postgres psql -c "ALTER SYSTEM SET autovacuum = off;"
sudo -u postgres psql -c "SELECT pg_reload_conf();"

# 测试完成后恢复
sudo -u postgres psql -c "ALTER SYSTEM RESET autovacuum;"
sudo -u postgres psql -c "SELECT pg_reload_conf();"

7.9.3 索引类型

PostgreSQL 支持多种索引类型，可进行对比测试：

-- 默认 B-tree 索引（Sysbench 自动创建）
CREATE INDEX idx_k ON sbtest1(k);

-- Hash 索引（PostgreSQL 10+ 支持 WAL 记录）
CREATE INDEX idx_k_hash ON sbtest1 USING HASH(k);

-- BRIN 索引（适合时序数据）
CREATE INDEX idx_id_brin ON sbtest1 USING BRIN(id);

-- GIN 索引（适合全文搜索/JSON）
-- CREATE INDEX idx_c_gin ON sbtest1 USING GIN(to_tsvector('english', c));

7.10 小结

要点说明
连接选项使用 --pgsql- 前缀，--db-driver=pgsql
关键参数shared_buffers、work_mem、max_wal_size
连接模型进程模型，高并发时使用连接池
VACUUM定期维护，影响写入性能和查询效率
与 MySQL 对比注意隔离级别和锁机制差异

扩展阅读