PostgreSQL 18 从新手到大师：实战指南 - 5.4 高级性能调优

一、高级性能调优概述

在数据库管理中，性能调优是一个持续的过程，从基础的参数调整到深入的内核调优，需要不断地监控、分析和优化。PostgreSQL 18提供了丰富的性能调优选项，能够处理大规模并发和复杂的工作负载。

1.1 高级性能调优的目标

高级性能调优的主要目标是：

• 解决复杂的性能瓶颈
• 处理大规模并发请求
• 优化资源利用率
• 提高系统的可扩展性
• 确保系统在高负载下的稳定性

1.2 高级性能调优的范围

高级性能调优涵盖以下几个方面：

二、锁争用解决

锁争用是高并发环境下常见的性能瓶颈，当多个事务同时访问相同的数据时，会产生锁竞争，导致事务等待，降低系统吞吐量。

2.1 锁的类型

PostgreSQL支持多种锁类型，主要包括：

2.2 锁争用识别

2.2.1 使用pg_locks视图

— 查看当前锁信息
SELECT
locktype, database, relation::regclass, page, tuple, virtualxid, transactionid,
classid, objid, objsubid,
pid, mode, granted,
query
FROM pg_locks
JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
WHERE NOT granted; — 只显示等待的锁

2.2.2 使用pg_stat_activity视图

— 查看当前活动会话和锁等待情况
SELECT
pid, datname, usename, application_name, client_addr,
backend_start, query_start, state, wait_event_type, wait_event,
query
FROM pg_stat_activity
WHERE state = 'active' OR wait_event IS NOT NULL;

2.2.3 使用pg_blocking_pids函数

— 查看阻塞其他会话的PID
SELECT pid, pg_blocking_pids(pid) AS blocking_pids, query
FROM pg_stat_activity
WHERE pg_blocking_pids(pid)::text != '{}';

2.3 锁争用解决策略

2.3.1 优化事务设计

• 保持事务简短，减少事务持有锁的时间
• 尽量在事务末尾执行写操作
• 避免在事务中等待用户输入
• 使用合适的事务隔离级别

2.3.2 优化查询语句

• 避免长时间运行的查询
• 优化查询计划，减少锁持有时间
• 避免全表扫描，使用索引
• 合理使用锁级别，避免过度锁定

2.3.3 使用乐观锁

对于并发更新频繁的表，可以使用乐观锁机制：

— 添加版本列
ALTER TABLE products ADD COLUMN version INT DEFAULT 0;

— 更新时检查版本
UPDATE products
SET price = 100, version = version + 1
WHERE id = 1 AND version = 0;

— 检查更新是否成功
IF FOUND THEN
— 更新成功
ELSE
— 版本冲突，需要重试
END IF;

2.3.4 使用行级锁

• 使用SELECT FOR UPDATE/SHARE只锁定需要的行
• 避免使用LOCK TABLE语句
• 合理使用NOWAIT选项，避免长时间等待

2.3.5 分区表

将大表拆分为多个分区表，减少锁的粒度：

— 创建分区表
CREATE TABLE orders (
id SERIAL,
order_date DATE,
...
) PARTITION BY RANGE (order_date);

— 创建分区
CREATE TABLE orders_2023 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

2.4 锁争用监控

使用pg_stat_statements扩展监控锁等待：

— 查看锁等待时间最长的查询
SELECT
query, calls, total_time,
mean_time, max_time,
rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY max_time DESC
LIMIT 10;

三、大规模并发处理

处理大规模并发请求是高负载系统的关键挑战之一，需要从多个方面进行优化。

3.1 连接管理

3.1.1 使用连接池

连接池可以减少创建和销毁连接的开销，提高系统处理并发连接的能力。常用的连接池包括：

• PgBouncer：轻量级连接池，支持三种模式（会话、事务、语句）
• Pgpool-II：功能丰富的连接池，支持负载均衡、高可用等

3.1.2 PgBouncer配置

[databases]
* = host=localhost port=5432

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 10
reserve_pool_timeout = 5

3.1.3 调整max_connections

— 查看当前值
SHOW max_connections;

— 根据系统资源调整
ALTER SYSTEM SET max_connections = 500;

3.2 工作进程优化

3.2.1 调整工作进程参数

— 最大工作进程数
ALTER SYSTEM SET max_worker_processes = 32;

— 最大并行工作进程数
ALTER SYSTEM SET max_parallel_workers = 16;

— 每个Gather节点的最大并行工作进程数
ALTER SYSTEM SET max_parallel_workers_per_gather = 8;

3.3 内存优化

3.3.1 调整shared_buffers

— 设置为系统内存的25%~40%
ALTER SYSTEM SET shared_buffers = '16GB';

3.3.2 调整work_mem

— 根据并发查询数调整
ALTER SYSTEM SET work_mem = '64MB';

3.4 事务管理优化

3.4.1 使用合适的事务隔离级别

— 查看当前值
SHOW default_transaction_isolation;

— 设置为READ COMMITTED（默认值，适合大多数场景）
ALTER SYSTEM SET default_transaction_isolation = 'read committed';

3.4.2 启用异步提交

对于非关键业务，可以启用异步提交以提高性能：

— 查看当前值
SHOW synchronous_commit;

— 设置为off启用异步提交
ALTER SYSTEM SET synchronous_commit = off;

四、内核调优

内核调优是高级性能调优的重要组成部分，通过调整PostgreSQL内核参数，可以显著提高系统性能。

4.1 内核参数分类

PostgreSQL内核参数可以分为以下几类：

4.2 关键内核参数调优

4.2.1 内存参数

— 共享缓冲区
shared_buffers = '16GB' — 系统内存的25%~40%

— 工作内存
work_mem = '64MB' — 根据并发查询数调整

— 维护工作内存
maintenance_work_mem = '2GB' — 系统内存的5%~10%

— 有效缓存大小
effective_cache_size = '48GB' — 系统内存的50%~75%

4.2.2 并发参数

— 最大连接数
max_connections = 500

— 最大工作进程数
max_worker_processes = 32

— 最大并行工作进程数
max_parallel_workers = 16

— 每个Gather节点的最大并行工作进程数
max_parallel_workers_per_gather = 8

4.2.3 I/O参数

— 随机页面成本（SSD设置为1.1）
random_page_cost = 1.1

— 顺序页面成本
seq_page_cost = 1.0

— 有效I/O并发（NVMe SSD设置为300）
effective_io_concurrency = 300

— 维护操作的有效I/O并发
maintenance_io_concurrency = 150

4.2.4 WAL参数

— WAL缓冲区大小
wal_buffers = '32MB' — shared_buffers的3%~5%

— 同步提交级别
synchronous_commit = on — 或local, remote_write, off

— WAL压缩
wal_compression = on

— WAL写入延迟（毫秒）
wal_writer_delay = 200ms

4.2.5 检查点参数

— 检查点超时
checkpoint_timeout = '30min' — 15~30分钟

— 最大WAL大小
max_wal_size = '64GB'

— 最小WAL大小
min_wal_size = '8GB'

— 检查点完成目标
checkpoint_completion_target = 0.9

4.3 内核调优最佳实践

• 根据硬件配置调整参数
• 逐步调整，避免一次调整多个参数
• 在测试环境中验证调优效果
• 监控调优后的系统性能
• 根据工作负载调整参数

五、内存深度优化

内存是数据库性能的关键资源，深度优化内存使用可以显著提高系统性能。

5.1 内存分配策略

对于专用数据库服务器，建议按照以下比例分配内存：

• 操作系统：20%
• shared_buffers：25%~40%
• work_mem：根据并发查询数动态调整
• maintenance_work_mem：5%~10%
• 其他进程和缓存：剩余内存

5.2 内存上下文优化

PostgreSQL使用内存上下文（Memory Context）管理内存分配，优化内存上下文可以减少内存碎片和内存泄漏。

5.2.1 监控内存使用

— 查看内存使用情况
SELECT name, setting, unit FROM pg_settings WHERE category = 'Resource Usage' AND name LIKE '%mem%';

5.3 大页内存支持

使用大页内存可以减少TLB（Translation Lookaside Buffer） misses，提高内存访问性能。

5.3.1 在Linux上配置大页内存

# 查看当前大页设置
grep Huge /proc/meminfo

# 配置大页
echo 2048 > /sys/kernel/mm/hugepages/hugepages-2048kB/nr_hugepages

# 挂载大页内存
mkdir -p /mnt/hugepagesmmount -t hugetlbfs hugetlbfs /mnt/hugepages

5.3.2 配置PostgreSQL使用大页内存

# 在postgresql.conf中添加
shared_memory_type = mmap
large_pages = on

六、I/O深度优化

I/O是数据库性能的重要瓶颈，深度优化I/O操作可以显著提高系统性能。

6.1 存储设备优化

• 使用NVMe SSD替代HDD
• 使用RAID 10提高性能和可靠性
• 分离WAL和数据存储到不同的存储设备

6.2 文件系统优化

6.2.1 Linux文件系统选择

• XFS：适合大型文件系统和高吞吐量
• ext4：稳定可靠，适合大多数场景
• Btrfs：支持高级特性，如快照、校验和等

6.2.2 文件系统挂载选项

# XFS挂载选项
/dev/sdb1 /pgdata xfs noatime,nodiratime,attr2,discard,inode64,logbufs=8,logbsize=256k 0 0

# ext4挂载选项
/dev/sdb1 /pgdata ext4 noatime,nodiratime,barrier=0,data=writeback 0 0

6.3 PostgreSQL I/O优化

6.3.1 使用O_DIRECT

# 在postgresql.conf中添加
data_directory = '/pgdata'
# 使用O_DIRECT绕过操作系统缓存
# wal_buffers需要足够大
wal_buffers = '64MB'

6.3.2 调整bgwriter参数

— 后台写进程延迟（毫秒）
ALTER SYSTEM SET bgwriter_delay = 200ms;

— 每次写入的最大缓冲区数
ALTER SYSTEM SET bgwriter_lru_maxpages = 100;

— 每次写入的目标缓冲区数
ALTER SYSTEM SET bgwriter_lru_multiplier = 2.0;

6.4 表空间优化

将不同的数据库对象存储在不同的存储设备上：

— 创建表空间
CREATE TABLESPACE fast_ssd LOCATION '/mnt/ssd/pgdata';
CREATE TABLESPACE slow_hdd LOCATION '/mnt/hdd/pgdata';

— 将WAL存储在高速存储设备上
ALTER SYSTEM SET wal_directory = '/mnt/ssd/pgwal';

七、高级查询优化

高级查询优化涉及复杂查询的分析和优化，需要深入理解查询优化器的工作原理。

7.1 使用高级索引技术

7.1.1 部分索引

为表的子集创建索引，减少索引大小和维护成本：

— 为活跃用户创建索引
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

7.1.2 表达式索引

为表达式创建索引，提高表达式查询的性能：

— 为lower(email)创建索引
CREATE INDEX idx_users_email_lower ON users((lower(email)));

— 使用索引的查询
SELECT * FROM users WHERE lower(email) = 'example@example.com';

7.1.3 覆盖索引

包含查询所需的所有列，避免回表查询：

— 创建覆盖索引
CREATE INDEX idx_users_name_email ON users(name, email);

— 使用覆盖索引的查询
SELECT name, email FROM users WHERE name = 'John';

7.2 优化复杂查询

7.2.1 重写子查询

将相关子查询重写为连接查询：

— 子查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

— 重写为连接查询
SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.amount > 100;

7.2.2 使用CTE优化复杂查询

使用公共表表达式（CTE）分解复杂查询：

WITH recent_orders AS (
SELECT user_id, COUNT(*) AS order_count
FROM orders
WHERE created_at > NOW() – INTERVAL '30 days'
GROUP BY user_id
)
SELECT u.name, u.email, ro.order_count
FROM users u
JOIN recent_orders ro ON u.id = ro.user_id
ORDER BY ro.order_count DESC;

7.2.3 使用物化视图

对于频繁执行的复杂查询，可以使用物化视图：

— 创建物化视图
CREATE MATERIALIZED VIEW daily_sales AS
SELECT
DATE(created_at) AS sale_date,
SUM(amount) AS total_sales,
COUNT(*) AS order_count
FROM orders
GROUP BY DATE(created_at);

— 刷新物化视图
REFRESH MATERIALIZED VIEW daily_sales;

— 查询物化视图
SELECT * FROM daily_sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31';

7.3 查询优化工具

7.3.1 使用EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'example@example.com';

7.3.2 使用pg_stat_statements

— 查看慢查询
SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

7.3.3 使用pg_hint_plan扩展

— 提示使用特定索引
/*+ IndexScan(users idx_users_email) */
SELECT * FROM users WHERE email = 'example@example.com';

八、监控与诊断

高级性能调优需要强大的监控和诊断工具，以便及时发现和解决性能问题。

8.1 内置监控视图

PostgreSQL提供了丰富的内置监控视图：

• pg_stat_activity：当前活动会话
• pg_stat_database：数据库统计信息
• pg_stat_user_tables：用户表统计信息
• pg_stat_user_indexes：用户索引统计信息
• pg_stat_bgwriter：后台写进程统计信息
• pg_stat_wal：WAL统计信息
• pg_stat_checkpointer：检查点统计信息
• pg_locks：锁信息

8.2 监控工具

8.2.1 Prometheus + Grafana

使用Prometheus收集PostgreSQL指标，使用Grafana可视化监控数据。

PostgreSQL Exporter配置：

scrape_configs:
– job_name: 'postgresql'
static_configs:
– targets: ['localhost:9187']
metrics_path: '/metrics'
params:
format: ['prometheus']

8.2.2 pgAdmin

pgAdmin提供了直观的图形界面，用于监控PostgreSQL服务器。

8.2.3 pganalyze

pganalyze是一个商业监控工具，提供高级的查询分析和性能优化建议。

8.3 诊断工具

8.3.1 使用strace

跟踪PostgreSQL进程的系统调用：

strace -p <pid> -f -e trace=open,read,write,close

8.3.2 使用perf

分析PostgreSQL的CPU使用情况：

perf record -p <pid> -g
perf report

8.3.3 使用gdb

调试PostgreSQL进程：

gdb -p <pid>

九、实战案例：深度性能调优

9.1 案例描述

假设我们有一个大型电商网站的PostgreSQL数据库，数据量超过1TB，并发用户数超过5000，遇到了性能瓶颈。

9.2 性能分析

通过监控发现以下问题：

9.3 调优方案

9.3.1 锁争用解决

9.3.2 内存优化

9.3.3 I/O优化

9.3.4 查询优化

9.4 调优效果

通过以上调优措施，预期可以实现：

• 锁等待时间减少80%以上
• 内存使用率降低30%以上
• I/O等待时间减少60%以上
• 查询响应时间减少50%以上
• 系统吞吐量提高40%以上

十、总结

高级性能调优是一个复杂的系统工程，涉及多个方面，包括锁争用解决、大规模并发处理、内核调优、内存深度优化、I/O深度优化和高级查询优化等。

高级性能调优的主要原则是：

通过深入理解PostgreSQL的工作原理，结合实际的硬件环境和工作负载，进行有针对性的调优，可以显著提高PostgreSQL数据库的性能和可靠性。

高级性能调优需要丰富的经验和深入的知识，需要不断学习和实践。随着PostgreSQL版本的更新，新的性能特性和调优选项不断出现，需要关注新版本的变化，充分利用新特性来提高系统性能。

通过本章节的学习，读者应该掌握PostgreSQL高级性能调优的核心原理和方法，能够识别和解决复杂的性能问题，优化系统以处理大规模并发请求，提高系统的性能和可靠性。