2. 首页 > PostgreSQL教程 > 正文

PostgreSQL教程FG098-PG性能调优实战：高并发场景参数优化

教程整理：风哥教程  |  更新时间：2026-01-22  |  教程分类：PostgreSQL教程  |  文档学习：6

本文档风哥主要介绍PostgreSQL在高并发场景下的性能调优参数优化。风哥教程参考PostgreSQL官方文档Server Administration内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

from oracle:www.itpux.com

Part01-基础概念与理论知识

1.1 高并发场景的概念

高并发场景是指数据库同时处理大量并发请求的情况，通常表现为：

  • 大量并发连接
  • 高频率的SQL查询和修改操作
  • 高事务率
  • 高资源使用率

高并发场景常见于：

  • 电商网站的促销活动
  • 在线游戏的高峰期
  • 金融交易系统
  • 实时数据分析系统
高并发场景的特点：

  • 短事务为主：大多数事务执行时间短
  • 读写比例高：读操作远多于写操作
  • 突发流量：流量可能在短时间内剧增
  • 对响应时间要求高：用户体验对响应时间敏感

1.2 高并发场景的挑战

高并发场景面临的挑战包括：

学习交流加群风哥QQ113257174

# 高并发场景的挑战
– 连接管理：大量并发连接会消耗系统资源
– 内存管理：内存使用过高可能导致系统不稳定
– 锁竞争：并发修改操作会导致锁竞争
– IO瓶颈：高并发读写会导致IO瓶颈
– 事务冲突：并发事务可能导致冲突
– 系统负载：高并发会导致系统负载过高
– 响应时间：高并发可能导致响应时间变长
– 扩展性：系统扩展性不足

# 常见性能问题
– 连接池耗尽：连接数超过最大连接数
– 内存不足：内存使用超过系统可用内存
– 死锁：事务相互等待资源
– 慢查询：查询执行时间过长
– IO等待：磁盘IO成为瓶颈
– 锁等待：锁竞争导致事务等待

1.3 性能调优目标

高并发场景下的性能调优目标：

  • 提高并发处理能力：增加系统能同时处理的请求数
  • 降低响应时间：减少每个请求的处理时间
  • 提高资源利用率：合理使用系统资源
  • 增强系统稳定性：避免系统崩溃或性能急剧下降
  • 提高扩展性：支持业务增长和流量变化

Part02-生产环境规划与建议

2.1 高并发场景规划

高并发场景规划要点：

# 高并发场景规划步骤
1. 业务需求分析：了解业务特点和并发需求
2. 系统架构设计：选择合适的系统架构
3. 硬件资源规划：根据并发需求规划硬件资源
4. 数据库设计：优化数据库结构和索引
5. 连接池配置：合理配置连接池
6. 缓存策略：使用缓存减少数据库负载
7. 监控系统：建立完善的监控系统
8. 灾备方案：制定灾备方案确保系统可靠性

# 系统架构建议
– 采用主从架构：主库处理写操作，从库处理读操作
– 使用连接池：减少连接建立和销毁的开销
– 引入缓存：使用Redis等缓存减轻数据库压力
– 负载均衡：使用负载均衡器分发请求
– 水平扩展：通过增加节点提高系统容量

# 硬件资源建议
– CPU：选择多核高性能CPU
– 内存：充足的内存，至少16GB以上
– 存储：使用SSD存储，提高IO性能
– 网络：千兆或万兆网络，确保网络带宽

2.2 参数调优策略

参数调优策略：

  • 渐进式调优：逐步调整参数，观察效果
  • 基于监控：根据监控数据调整参数
  • 分阶段调优：先调优内存参数，再调优IO参数
  • 测试验证：通过性能测试验证调优效果
  • 文档记录：记录调优过程和结果

2.3 系统要求

高并发场景的系统要求：

更多视频教程www.fgedu.net.cn

# 操作系统要求
– Linux：推荐使用CentOS/RHEL 7.x或8.x
– 内核参数：优化内核参数，如文件描述符、网络参数
– 磁盘调度：使用deadline或cfq调度器
– 内存管理：开启大内存页，关闭透明大页
– NUMA：关闭NUMA或合理配置NUMA

# PostgreSQL版本要求
– 推荐使用PostgreSQL 12.0以上版本
– 支持并行查询和JIT编译
– 支持高级索引类型和分区表

# 存储要求
– 使用SSD存储，提高IO性能
– 合理规划RAID级别，如RAID 10
– 足够的磁盘空间，预留20%以上的空闲空间
– 定期清理无用数据，避免磁盘空间不足

风哥提示：高并发场景的性能调优需要综合考虑硬件资源、系统配置和数据库参数，制定合理的调优策略。学习交流加群风哥微信: itpux-com

Part03-生产环境项目实施方案

3.1 连接参数优化

3.1.1 最大连接数

# 查看当前最大连接数
$ sudo -u pgsql psql -c “SHOW max_connections;”

# 修改最大连接数
$ sudo vi /postgresql/data/postgresql.conf

max_connections = 1000 # 最大连接数，根据系统资源调整

# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”

# 监控连接数
$ sudo -u pgsql psql -c “SELECT count(*) FROM pg_stat_activity;”
$ sudo -u pgsql psql -c “SELECT state, count(*) FROM pg_stat_activity GROUP BY state;”

3.1.2 连接池配置

# 使用pgBouncer作为连接池

# 安装pgBouncer
$ sudo yum install pgBouncer

# 配置pgBouncer
$ sudo vi /etc/pgbouncer/pgbouncer.ini

[fgedudbs]
fgedu_production = fgedu.net.cn=localfgedu.net.cn port=5432 fgedudb=fgedu_production fgedu=pgsql password=your_password

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/fgedulist.txt
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 100
min_pool_size = 20
reserve_pool_size = 50
reserve_pool_timeout = 5.0

# 创建用户认证文件
$ sudo vi /etc/pgbouncer/fgedulist.txt

“postgres” “md5hash”

# 启动pgBouncer
$ sudo systemctl start pgbouncer
$ sudo systemctl enable pgbouncer

# 连接到pgBouncer
$ psql -h localfgedu.net.cn -p 6432 -U pgsql -d fgedu_production

3.2 内存参数优化

3.2.1 共享缓冲区

# 查看当前共享缓冲区设置
$ sudo -u pgsql psql -c “SHOW shared_buffers;”

# 修改共享缓冲区
$ sudo vi /postgresql/data/postgresql.conf

shared_buffers = 8GB # 总内存的25%

# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”

3.2.2 工作内存

# 查看当前工作内存设置
$ sudo -u pgsql psql -c “SHOW work_mem;”

# 修改工作内存
$ sudo vi /postgresql/data/postgresql.conf

work_mem = 32MB # 并发连接数为100时，总使用3.2GB

# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”

3.2.3 维护工作内存

# 查看当前维护工作内存设置
$ sudo -u pgsql psql -c “SHOW maintenance_work_mem;”

# 修改维护工作内存
$ sudo vi /postgresql/data/postgresql.conf

maintenance_work_mem = 1GB # 总内存的5%

# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”

3.2.4 有效缓存大小

# 查看当前有效缓存大小设置
$ sudo -u pgsql psql -c “SHOW effective_cache_size;”

# 修改有效缓存大小
$ sudo vi /postgresql/data/postgresql.conf

effective_cache_size = 24GB # 总内存的75%

# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”

3.3 查询参数优化

3.3.1 随机页面成本

# 查看当前随机页面成本设置
$ sudo -u pgsql psql -c “SHOW random_page_cost;”

# 修改随机页面成本
$ sudo vi /postgresql/data/postgresql.conf

random_page_cost = 1.1 # 使用SSD存储时设置为1.1-1.3

# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”

3.3.2 顺序页面成本

# 查看当前顺序页面成本设置
$ sudo -u pgsql psql -c “SHOW seq_page_cost;”

# 修改顺序页面成本
$ sudo vi /postgresql/data/postgresql.conf

seq_page_cost = 1.0 # 使用SSD存储时设置为1.0

# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”

3.3.3 并行查询参数

# 查看当前并行查询设置
$ sudo -u pgsql psql -c “SHOW max_worker_processes;”
$ sudo -u pgsql psql -c “SHOW max_parallel_workers;”
$ sudo -u pgsql psql -c “SHOW max_parallel_workers_per_gather;”

# 修改并行查询参数
$ sudo vi /postgresql/data/postgresql.conf

max_worker_processes = 8
max_parallel_workers = 4
max_parallel_workers_per_gather = 4
parallel_leader_participation = on
min_parallel_table_scan_size = 8MB
min_parallel_index_scan_size = 5MB

# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”

3.4 IO参数优化

3.4.1 WAL缓冲区

# 查看当前WAL缓冲区设置
$ sudo -u pgsql psql -c “SHOW wal_buffers;”

# 修改WAL缓冲区
$ sudo vi /postgresql/data/postgresql.conf

wal_buffers = 16MB # 默认值的4倍

# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”

3.4.2 写前日志同步

# 查看当前写前日志同步设置
$ sudo -u pgsql psql -c “SHOW synchronous_commit;”

# 修改写前日志同步
$ sudo vi /postgresql/data/postgresql.conf

synchronous_commit = off # 非同步提交，提高性能，有数据丢失风险

# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”

3.4.3 检查点参数

# 查看当前检查点参数设置
$ sudo -u pgsql psql -c “SHOW checkpoint_timeout;”
$ sudo -u pgsql psql -c “SHOW max_wal_size;”
$ sudo -u pgsql psql -c “SHOW min_wal_size;”

# 修改检查点参数
$ sudo vi /postgresql/data/postgresql.conf

checkpoint_timeout = 30min # 检查点超时时间
max_wal_size = 2GB # 最大WAL大小
min_wal_size = 1GB # 最小WAL大小

# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”

3.5 WAL参数优化

3.5.1 WAL写入模式

# 查看当前WAL写入模式设置
$ sudo -u pgsql psql -c “SHOW wal_sync_method;”

# 修改WAL写入模式
$ sudo vi /postgresql/data/postgresql.conf

wal_sync_method = fdatasync # 根据操作系统选择合适的同步方法

# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”

3.5.2 WAL压缩

# 查看当前WAL压缩设置
$ sudo -u pgsql psql -c “SHOW wal_compression;”

# 修改WAL压缩
$ sudo vi /postgresql/data/postgresql.conf

wal_compression = on # 开启WAL压缩

# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”

Part04-生产案例与实战讲解

4.1 高并发场景调优案例

4.1.1 案例描述

场景：一个电商网站的PostgreSQL数据库，在促销活动期间面临高并发访问，需要进行性能调优以应对流量高峰。

4.1.2 实施方案

# 1. 分析当前性能状况

$ sudo -u pgsql psql -c “SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted FROM pg_stat_fgedudb;”

$ sudo -u pgsql psql -c “SELECT pid, usename, datname, state, query_start, now() – query_start as duration, query FROM pg_stat_activity WHERE state = ‘active’ ORDER BY duration DESC;”

$ sudo -u pgsql psql -c “SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;”

# 2. 调整连接参数

$ sudo vi /postgresql/data/postgresql.conf

max_connections = 1000

# 3. 调整内存参数

$ sudo vi /postgresql/data/postgresql.conf

shared_buffers = 8GB
work_mem = 32MB
maintenance_work_mem = 1GB
effective_cache_size = 24GB

# 4. 调整查询参数

$ sudo vi /postgresql/data/postgresql.conf

random_page_cost = 1.1
seq_page_cost = 1.0
max_worker_processes = 8
max_parallel_workers = 4
max_parallel_workers_per_gather = 4

# 5. 调整IO参数

$ sudo vi /postgresql/data/postgresql.conf

wal_buffers = 16MB
synchronous_commit = off
checkpoint_timeout = 30min
max_wal_size = 2GB
min_wal_size = 1GB

# 6. 配置连接池

$ sudo vi /etc/pgbouncer/pgbouncer.ini

[fgedudbs]
fgedu_production = fgedu.net.cn=localfgedu.net.cn port=5432 fgedudb=fgedu_production fgedu=pgsql password=your_password

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/fgedulist.txt
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 100
min_pool_size = 20
reserve_pool_size = 50

# 7. 重启服务

$ sudo systemctl restart postgresql
$ sudo systemctl restart pgbouncer

# 8. 验证调优效果

$ sudo -u pgsql psql -c “SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted FROM pg_stat_fgedudb;”

$ sudo -u pgsql psql -c “SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;”

4.2 性能测试与验证

4.2.1 性能测试工具

# 使用pgbench进行性能测试

# 准备测试数据
$ sudo -u pgsql pgbench -i -s 100 fgedu_production

# 运行简单SELECT测试
$ sudo -u pgsql pgbench -c 100 -j 4 -T 60 fgedu_production

# 运行混合测试（SELECT/UPDATE/INSERT/DELETE）
$ sudo -u pgsql pgbench -c 100 -j 4 -T 60 -b simple-update fgedu_production

# 运行自定义测试
$ sudo vi test.sql

BEGIN;
SELECT * FROM fgedu_employees WHERE id = random() * 1000000;
UPDATE fgedu_employees SET salary = salary * 1.01 WHERE id = random() * 1000000;
COMMIT;

$ sudo -u pgsql pgbench -c 100 -j 4 -T 60 -f test.sql fgedu_production

# 分析测试结果
$ sudo -u pgsql pgbench -c 100 -j 4 -T 60 -r fgedu_production

4.2.2 性能测试结果分析

# 测试结果示例

starting vacuum…end.
transaction type:
scaling factor: 100
query mode: simple
number of clients: 100
number of threads: 4
duration: 60 s
number of transactions actually processed: 120000
transaction rate: 2000.00 txns/s
latency average = 50.000 ms
latency stddev = 10.000 ms
initial connection time = 1000.000 ms
tps = 2000.000 (including connections establishing)
tps = 2000.000 (excluding connections establishing)

# 关键指标分析
– transaction rate: 事务处理速率，越高越好
– latency average: 平均延迟，越低越好
– latency stddev: 延迟标准差，越小越好
– tps: 每秒事务数，越高越好

4.3 高并发问题排查

4.3.1 连接问题排查

# 检查连接数
$ sudo -u pgsql psql -c “SELECT count(*) FROM pg_stat_activity;”
$ sudo -u pgsql psql -c “SELECT state, count(*) FROM pg_stat_activity GROUP BY state;”

# 检查连接池状态
$ psql -h localfgedu.net.cn -p 6432 -U pgsql -c “SHOW POOLS;”

# 检查连接等待
$ sudo -u pgsql psql -c “SELECT pid, usename, datname, state, query_start, now() – query_start as duration, query FROM pg_stat_activity WHERE state = ‘idle in transaction’ ORDER BY duration DESC;”

4.3.2 锁问题排查

# 检查锁等待
$ sudo -u pgsql psql -c “SELECT pid, usename, datname, relation::regclass, mode, granted FROM pg_locks WHERE NOT granted;”

# 检查死锁
$ sudo tail -n 100 /postgresql/data/log/postgresql-$(date +%Y-%m-%d).log | grep -i deadlock

# 查看锁持有情况
$ sudo -u pgsql psql -c “SELECT pid, usename, datname, relation::regclass, mode, granted FROM pg_locks;”

4.3.3 IO问题排查

# 检查IO性能
$ iostat -x 1

# 检查磁盘空间
$ df -h

# 检查WAL日志使用情况
$ sudo -u pgsql psql -c “SELECT * FROM pg_stat_wal;”

# 检查检查点情况
$ sudo -u pgsql psql -c “SELECT * FROM pg_stat_bgwriter;”

风哥教程针对风哥教程针对风哥教程针对生产环境建议：在生产环境中，建议定期监控数据库性能，及时发现和解决性能问题。通过性能测试，可以验证调优效果，确保系统在高并发场景下的稳定性和可靠性。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 高并发场景最佳实践

高并发场景最佳实践：

  • 合理配置连接池：使用连接池管理连接，减少连接建立和销毁的开销
  • 优化内存参数：根据系统内存大小，合理配置共享缓冲区、工作内存等参数
  • 优化查询参数：根据存储类型，调整随机页面成本和顺序页面成本
  • 优化IO参数：调整WAL缓冲区、检查点参数等，提高IO性能
  • 使用SSD存储：SSD存储可以显著提高IO性能，适合高并发场景
  • 合理设计索引：为高频查询创建合适的索引，提高查询性能
  • 使用缓存：使用Redis等缓存减轻数据库压力
  • 监控系统：建立完善的监控系统，及时发现和解决性能问题

5.2 常见问题与解决方案

高并发场景常见问题及解决方案：

  • 连接池耗尽：解决方案：增加连接池大小，优化应用程序连接管理
  • 内存不足：解决方案：增加系统内存，优化内存参数配置
  • 锁竞争：解决方案：优化事务设计，减少锁持有时间，使用合理的隔离级别
  • IO瓶颈：解决方案：使用SSD存储，优化IO参数，合理配置检查点
  • 慢查询：解决方案：优化查询语句，创建合适的索引，使用查询缓存
  • 系统负载过高：解决方案：增加系统资源，使用负载均衡，水平扩展
  • 死锁：解决方案：优化事务顺序，减少事务持有时间，使用合理的隔离级别
  • WAL日志堆积：解决方案：优化WAL参数，确保WAL日志及时归档

5.3 调优技巧

高并发场景调优技巧：

  • 渐进式调优：逐步调整参数，观察效果，避免一次性调整过多参数
  • 基于监控：根据监控数据调整参数，确保调优效果
  • 分阶段调优：先调优内存参数，再调优IO参数，最后调优查询参数
  • 测试验证：通过性能测试验证调优效果，确保系统在高并发场景下的稳定性
  • 文档记录：记录调优过程和结果，便于后续参考和回滚
  • 定期维护：定期执行VACUUM、ANALYZE等维护操作，保持数据库性能
  • 优化应用程序：优化应用程序代码，减少不必要的数据库操作
  • 持续学习：关注PostgreSQL的最新特性和最佳实践，持续优化系统
风哥提示：高并发场景的性能调优需要综合考虑硬件资源、系统配置和数据库参数，制定合理的调优策略。通过定期监控和性能测试，可以确保系统在高并发场景下的稳定性和可靠性。from PostgreSQL:www.itpux.com

持续改进：高并发场景的性能调优是一个持续的过程，需要根据业务需求和系统变化不断调整和优化。建议定期审查和调整调优策略，以保持系统的最佳性能。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息