PostgreSQL教程FG090-PG配置调优：内存/IO/连接核心参数优化
本文档风哥主要介绍PostgreSQL的配置调优，包括内存、IO和连接等核心参数的优化方法。风哥教程参考PostgreSQL官方文档Server Administration内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
学习交流加群风哥QQ113257174
Part01-基础概念与理论知识
1.1 PostgreSQL配置调优的概念
PostgreSQL配置调优是指通过调整PostgreSQL的配置参数，以提高数据库的性能和稳定性。配置调优是数据库管理的重要组成部分，可以根据系统硬件和工作负载特点，优化数据库的资源使用，提高查询性能，减少响应时间。更多视频教程www.fgedu.net.cn
- 提高查询性能：减少查询响应时间
- 提高系统稳定性：减少系统崩溃和错误
- 优化资源使用：合理利用内存、CPU和磁盘资源
- 提高并发处理能力：支持更多并发连接
- 减少系统负载：降低CPU和IO使用率
1.2 PostgreSQL核心参数
PostgreSQL的核心参数包括：
– 内存参数：控制内存使用
– IO参数：控制磁盘IO操作
– 连接参数：控制连接数和连接管理
– 查询优化参数：控制查询执行计划
– 写入优化参数：控制写入操作
– 日志参数：控制日志记录
– 安全参数：控制安全设置
# 内存参数
– shared_buffers：共享内存缓冲区大小
– work_mem：工作内存大小
– maintenance_work_mem：维护操作内存大小
– effective_cache_size：有效缓存大小
– wal_buffers：WAL缓冲区大小
# IO参数
– random_page_cost：随机页面访问成本
– sequential_page_cost：顺序页面访问成本
– effective_io_concurrency：有效IO并发数
– wal_writer_delay：WAL写入延迟
– checkpoint_timeout：检查点超时时间
– max_wal_size：最大WAL大小
– min_wal_size：最小WAL大小
# 连接参数
– max_connections：最大连接数
– superfgedu_reserved_connections：超级用户预留连接数
– idle_in_transaction_session_timeout：空闲事务会话超时时间
– tcp_keepalives_idle：TCP保持连接空闲时间
– tcp_keepalives_interval：TCP保持连接间隔时间
– tcp_keepalives_count：TCP保持连接尝试次数
1.3 PostgreSQL配置调优原则
PostgreSQL配置调优的基本原则包括：
- 根据硬件配置调优：根据服务器的CPU、内存、磁盘等硬件配置调整参数
- 根据工作负载调优：根据数据库的工作负载特点调整参数
- 逐步调整：每次只调整一个参数，观察效果后再调整其他参数
- 测试验证：在测试环境中测试调整效果，再应用到生产环境
- 监控反馈：监控系统性能，根据反馈调整参数
- 保持平衡：平衡内存、IO和连接参数，避免顾此失彼
- 文档记录：记录参数调整的原因和效果，便于后续参考
Part02-生产环境规划与建议
2.1 内存参数调优规划
内存参数调优规划要点：
1. 分析系统内存：了解服务器的总内存大小
2. 分配内存比例：根据工作负载分配内存比例
3. 调整核心内存参数：设置合适的shared_buffers、work_mem等参数
4. 测试内存使用：监控内存使用情况，避免内存不足
5. 优化内存配置：根据测试结果调整内存参数
# 内存分配建议
– shared_buffers：建议设置为总内存的25%，最大不超过16GB
– work_mem：根据并发连接数和查询复杂度设置，一般为总内存的1-2%
– maintenance_work_mem：建议设置为总内存的5-10%，最大不超过1GB
– effective_cache_size：建议设置为总内存的50-75%
– wal_buffers：建议设置为16MB或更大
# 内存调优考虑因素
– 系统内存大小：内存越大，可分配的参数值越大
– 并发连接数：并发连接数越多，work_mem需要越小
– 查询复杂度：复杂查询需要更大的work_mem
– 维护操作频率：维护操作频繁需要更大的maintenance_work_mem
– 其他应用：考虑服务器上其他应用的内存需求
2.2 IO参数调优规划
IO参数调优规划要点：
1. 分析存储系统：了解存储系统的类型和性能
2. 测量IO性能：测试存储系统的IOPS和延迟
3. 调整IO参数：设置合适的random_page_cost、sequential_page_cost等参数
4. 优化写入操作：调整WAL相关参数
5. 监控IO性能：监控IO使用率和延迟，避免IO瓶颈
# IO参数建议
– random_page_cost：机械硬盘建议设置为4.0，SSD建议设置为1.0-2.0
– sequential_page_cost：机械硬盘建议设置为1.0，SSD建议设置为0.5-1.0
– effective_io_concurrency：机械硬盘建议设置为2-4，SSD建议设置为100-200
– wal_writer_delay：建议设置为200-500ms
– checkpoint_timeout：建议设置为15-30分钟
– max_wal_size：建议设置为1-2GB
– min_wal_size：建议设置为80MB
# IO调优考虑因素
– 存储类型：机械硬盘、SSD或NVMe
– RAID级别：RAID 0、1、5、10等
– 存储控制器：缓存大小和类型
– 工作负载类型：读密集型或写密集型
– IO调度器：cfq、deadline、noop等
2.3 连接参数调优规划
连接参数调优规划要点：
1. 分析连接需求：了解应用的连接需求和峰值连接数
2. 计算连接数：根据应用需求计算合适的max_connections值
3. 调整连接参数：设置合适的max_connections、idle_in_transaction_session_timeout等参数
4. 配置连接池：考虑使用连接池减少连接数
5. 监控连接状态：监控连接数和连接状态，避免连接泄露
# 连接参数建议
– max_connections：根据应用需求和系统资源设置，一般为100-500
– superfgedu_reserved_connections：建议设置为3-5
– idle_in_transaction_session_timeout：建议设置为30000-60000ms
– tcp_keepalives_idle：建议设置为60-120s
– tcp_keepalives_interval：建议设置为10-30s
– tcp_keepalives_count：建议设置为3-5
# 连接调优考虑因素
– 应用连接需求：应用的并发连接数
– 系统资源：服务器的CPU和内存资源
– 连接池使用：是否使用连接池
– 连接模式：短连接或长连接
– 安全考虑：防止连接滥用
Part03-生产环境项目实施方案
3.1 内存参数调优
3.1.1 调整shared_buffers参数
$ sudo -u pgsql psql -c “SHOW shared_buffers;”
shared_buffers
—————-
128MB
(1 row)
# 调整shared_buffers值为总内存的25%
$ sudo vi /postgresql/data/postgresql.conf
# 修改shared_buffers参数
shared_buffers = 4GB
# 保存并退出
# 重新启动PostgreSQL服务
$ sudo systemctl restart postgresql
# 验证修改结果
$ sudo -u pgsql psql -c “SHOW shared_buffers;”
shared_buffers
—————-
4GB
(1 row)
3.1.2 调整work_mem参数
$ sudo -u pgsql psql -c “SHOW work_mem;”
work_mem
———-
4MB
(1 row)
# 调整work_mem值
$ sudo vi /postgresql/data/postgresql.conf
# 修改work_mem参数
work_mem = 16MB
# 保存并退出
# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”
pg_reload_conf
—————-
t
(1 row)
# 验证修改结果
$ sudo -u pgsql psql -c “SHOW work_mem;”
work_mem
———-
16MB
(1 row)
3.1.3 调整其他内存参数
$ sudo vi /postgresql/data/postgresql.conf
# 修改maintenance_work_mem参数
maintenance_work_mem = 512MB
# 调整effective_cache_size参数
effective_cache_size = 12GB
# 调整wal_buffers参数
wal_buffers = 16MB
# 保存并退出
# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”
# 验证修改结果
$ sudo -u pgsql psql -c “SHOW maintenance_work_mem;”
maintenance_work_mem
———————-
512MB
(1 row)
$ sudo -u pgsql psql -c “SHOW effective_cache_size;”
effective_cache_size
———————-
12GB
(1 row)
$ sudo -u pgsql psql -c “SHOW wal_buffers;”
wal_buffers
————
16MB
(1 row)
3.2 IO参数调优
3.2.1 调整IO成本参数
$ sudo -u pgsql psql -c “SHOW random_page_cost;”
random_page_cost
——————
4
(1 row)
$ sudo -u pgsql psql -c “SHOW sequential_page_cost;”
sequential_page_cost
———————
1
(1 row)
# 调整IO成本参数（针对SSD存储）
$ sudo vi /postgresql/data/postgresql.conf
# 修改IO成本参数
random_page_cost = 1.1
sequential_page_cost = 1.0
# 保存并退出
# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”
# 验证修改结果
$ sudo -u pgsql psql -c “SHOW random_page_cost;”
random_page_cost
——————
1.1
(1 row)
$ sudo -u pgsql psql -c “SHOW sequential_page_cost;”
sequential_page_cost
———————
1.0
(1 row)
3.2.2 调整WAL参数
$ sudo -u pgsql psql -c “SHOW wal_writer_delay;”
wal_writer_delay
——————
200ms
(1 row)
$ sudo -u pgsql psql -c “SHOW checkpoint_timeout;”
checkpoint_timeout
——————–
5min
(1 row)
$ sudo -u pgsql psql -c “SHOW max_wal_size;”
max_wal_size
————–
1GB
(1 row)
# 调整WAL参数
$ sudo vi /postgresql/data/postgresql.conf
# 修改WAL参数
wal_writer_delay = 200ms
checkpoint_timeout = 15min
max_wal_size = 2GB
min_wal_size = 80MB
# 保存并退出
# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”
# 验证修改结果
$ sudo -u pgsql psql -c “SHOW wal_writer_delay;”
wal_writer_delay
——————
200ms
(1 row)
$ sudo -u pgsql psql -c “SHOW checkpoint_timeout;”
checkpoint_timeout
——————–
15min
(1 row)
$ sudo -u pgsql psql -c “SHOW max_wal_size;”
max_wal_size
————–
2GB
(1 row)
3.2.3 调整effective_io_concurrency参数
$ sudo -u pgsql psql -c “SHOW effective_io_concurrency;”
effective_io_concurrency
————————–
1
(1 row)
# 调整effective_io_concurrency参数（针对SSD存储）
$ sudo vi /postgresql/data/postgresql.conf
# 修改effective_io_concurrency参数
effective_io_concurrency = 100
# 保存并退出
# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”
# 验证修改结果
$ sudo -u pgsql psql -c “SHOW effective_io_concurrency;”
effective_io_concurrency
————————–
100
(1 row)
3.3 连接参数调优
3.3.1 调整max_connections参数
$ sudo -u pgsql psql -c “SHOW max_connections;”
max_connections
—————–
100
(1 row)
# 调整max_connections参数
$ sudo vi /postgresql/data/postgresql.conf
# 修改max_connections参数
max_connections = 200
# 保存并退出
# 重新启动PostgreSQL服务
$ sudo systemctl restart postgresql
# 验证修改结果
$ sudo -u pgsql psql -c “SHOW max_connections;”
max_connections
—————–
200
(1 row)
3.3.2 调整空闲事务超时参数
$ sudo -u pgsql psql -c “SHOW idle_in_transaction_session_timeout;”
idle_in_transaction_session_timeout
———————————-
0
(1 row)
# 调整idle_in_transaction_session_timeout参数
$ sudo vi /postgresql/data/postgresql.conf
# 修改idle_in_transaction_session_timeout参数
idle_in_transaction_session_timeout = 60000
# 保存并退出
# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”
# 验证修改结果
$ sudo -u pgsql psql -c “SHOW idle_in_transaction_session_timeout;”
idle_in_transaction_session_timeout
———————————-
60000
(1 row)
3.3.3 调整TCP参数
$ sudo -u pgsql psql -c “SHOW tcp_keepalives_idle;”
tcp_keepalives_idle
——————-
0
(1 row)
$ sudo -u pgsql psql -c “SHOW tcp_keepalives_interval;”
tcp_keepalives_interval
———————-
0
(1 row)
$ sudo -u pgsql psql -c “SHOW tcp_keepalives_count;”
tcp_keepalives_count
——————–
0
(1 row)
# 调整TCP参数
$ sudo vi /postgresql/data/postgresql.conf
# 修改TCP参数
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 3
# 保存并退出
# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”
# 验证修改结果
$ sudo -u pgsql psql -c “SHOW tcp_keepalives_idle;”
tcp_keepalives_idle
——————-
60
(1 row)
$ sudo -u pgsql psql -c “SHOW tcp_keepalives_interval;”
tcp_keepalives_interval
———————-
10
(1 row)
$ sudo -u pgsql psql -c “SHOW tcp_keepalives_count;”
tcp_keepalives_count
——————–
3
(1 row)
3.4 其他参数调优
3.4.1 调整查询优化参数
$ sudo vi /postgresql/data/postgresql.conf
# 修改查询优化参数
random_page_cost = 1.1
sequential_page_cost = 1.0
effective_cache_size = 12GB
# 保存并退出
# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”
3.4.2 调整写入优化参数
$ sudo vi /postgresql/data/postgresql.conf
# 修改写入优化参数
wal_buffers = 16MB
wal_writer_delay = 200ms
checkpoint_timeout = 15min
max_wal_size = 2GB
min_wal_size = 80MB
# 保存并退出
# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”
3.4.3 调整日志参数
$ sudo vi /postgresql/data/postgresql.conf
# 修改日志参数
log_destination = ‘csvlog’
logging_collector = on
log_directory = ‘log’
log_filename = ‘postgresql-%Y-%m-%d.log’
log_rotation_age = ‘1d’
log_rotation_size = 100MB
log_line_prefix = ‘%t [%p]: [%l-1] fgedu=%u,db=%d,fgapp=%a,client=%h ‘
log_statement = ‘mod’
log_connections = on
log_disconnections = on
log_error_verbosity = ‘verbose’
# 保存并退出
# 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”
Part04-生产案例与实战讲解
4.1 内存参数调优案例
4.1.1 案例描述
场景：一个拥有16GB内存的服务器，运行PostgreSQL数据库，需要优化内存参数以提高查询性能。
4.1.2 实施方案
$ free -h
total used free shared buff/cache available
Mem: 15Gi 2.0Gi 10Gi 100Mi 3.0Gi 13Gi
Swap: 8.0Gi 0B 8.0Gi
# 2. 调整内存参数
$ sudo vi /postgresql/data/postgresql.conf
# 修改内存参数
shared_buffers = 4GB # 总内存的25%
work_mem = 16MB # 并发连接数为100时，总使用1.6GB
maintenance_work_mem = 512MB # 总内存的3%
effective_cache_size = 10GB # 总内存的62.5%
wal_buffers = 16MB # 默认值的4倍
# 保存并退出
# 3. 重新启动PostgreSQL服务
$ sudo systemctl restart postgresql
# 4. 验证内存参数
$ sudo -u pgsql psql -c “SHOW shared_buffers;”
shared_buffers
—————-
4GB
(1 row)
$ sudo -u pgsql psql -c “SHOW work_mem;”
work_mem
———-
16MB
(1 row)
$ sudo -u pgsql psql -c “SHOW maintenance_work_mem;”
maintenance_work_mem
———————-
512MB
(1 row)
$ sudo -u pgsql psql -c “SHOW effective_cache_size;”
effective_cache_size
———————-
10GB
(1 row)
$ sudo -u pgsql psql -c “SHOW wal_buffers;”
wal_buffers
————
16MB
(1 row)
# 5. 测试查询性能
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE salary > 8000 ORDER BY hire_date;”
# 6. 监控内存使用
$ top -p $(pgrep -f postgres)
# 7. 调整work_mem参数
# 根据并发连接数调整work_mem
# 假设最大并发连接数为200
# 200 * 16MB = 3.2GB，加上其他内存使用，总内存使用约8GB，在16GB内存中是合理的
4.2 IO参数调优案例
4.2.1 案例描述
场景：一个使用SSD存储的服务器，运行PostgreSQL数据库，需要优化IO参数以提高读写性能。
4.2.2 实施方案
$ fio –name=random-read –ioengine=libaio –rw=randread –bs=4k –size=1G –numjobs=4 –iodepth=32 –runtime=60 –time_based –group_reporting
# 2. 调整IO参数
$ sudo vi /postgresql/data/postgresql.conf
# 修改IO参数
random_page_cost = 1.1 # SSD存储的随机页面访问成本
sequential_page_cost = 1.0 # SSD存储的顺序页面访问成本
effective_io_concurrency = 100 # SSD存储的有效IO并发数
wal_writer_delay = 200ms # WAL写入延迟
checkpoint_timeout = 15min # 检查点超时时间
max_wal_size = 2GB # 最大WAL大小
min_wal_size = 80MB # 最小WAL大小
# 保存并退出
# 3. 重新加载配置
$ sudo -u pgsql psql -c “SELECT pg_reload_conf();”
# 4. 验证IO参数
$ sudo -u pgsql psql -c “SHOW random_page_cost;”
random_page_cost
——————
1.1
(1 row)
$ sudo -u pgsql psql -c “SHOW sequential_page_cost;”
sequential_page_cost
———————
1.0
(1 row)
$ sudo -u pgsql psql -c “SHOW effective_io_concurrency;”
effective_io_concurrency
————————–
100
(1 row)
$ sudo -u pgsql psql -c “SHOW wal_writer_delay;”
wal_writer_delay
——————
200ms
(1 row)
$ sudo -u pgsql psql -c “SHOW checkpoint_timeout;”
checkpoint_timeout
——————–
15min
(1 row)
$ sudo -u pgsql psql -c “SHOW max_wal_size;”
max_wal_size
————–
2GB
(1 row)
# 5. 测试IO性能
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_fgfgfgfgsales WHERE amount > 10000;”
# 6. 监控IO使用
$ iostat -x 1
4.3 连接参数调优案例
4.3.1 案例描述
场景：一个Web应用，需要支持200个并发连接，运行PostgreSQL数据库，需要优化连接参数以提高并发处理能力。
4.3.2 实施方案
# 查看当前连接数
$ sudo -u pgsql psql -c “SELECT count(*) FROM pg_stat_activity;”
count
——-
10
(1 row)
# 2. 调整连接参数
$ sudo vi /postgresql/data/postgresql.conf
# 修改连接参数
max_connections = 200 # 最大连接数
superfgedu_reserved_connections = 5 # 超级用户预留连接数
idle_in_transaction_session_timeout = 60000 # 空闲事务超时时间
tcp_keepalives_idle = 60 # TCP保持连接空闲时间
tcp_keepalives_interval = 10 # TCP保持连接间隔时间
tcp_keepalives_count = 3 # TCP保持连接尝试次数
# 保存并退出
# 3. 重新启动PostgreSQL服务
$ sudo systemctl restart postgresql
# 4. 验证连接参数
$ sudo -u pgsql psql -c “SHOW max_connections;”
max_connections
—————–
200
(1 row)
$ sudo -u pgsql psql -c “SHOW superfgedu_reserved_connections;”
superfgedu_reserved_connections
——————————-
5
(1 row)
$ sudo -u pgsql psql -c “SHOW idle_in_transaction_session_timeout;”
idle_in_transaction_session_timeout
———————————-
60000
(1 row)
# 5. 配置连接池
# 安装pgBouncer
$ sudo apt-get 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 [pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 10
reserve_pool_timeout = 5
# 启动pgBouncer
$ sudo systemctl start pgbouncer
# 6. 测试连接性能
# 使用pgbench测试连接性能
$ pgbench -h localfgedu.net.cn -p 6432 -U pgsql -d fgedu_production -c 100 -j 10 -T 60
# 7. 监控连接状态
$ sudo -u pgsql psql -c “SELECT state, count(*) FROM pg_stat_activity GROUP BY state;”
Part05-风哥经验总结与分享
5.1 PostgreSQL配置调优最佳实践
PostgreSQL配置调优最佳实践：
from oracle:www.itpux.com
- 根据硬件配置调优：根据服务器的CPU、内存、磁盘等硬件配置调整参数
- 根据工作负载调优：根据数据库的工作负载特点调整参数
- 逐步调整：每次只调整一个参数，观察效果后再调整其他参数
- 测试验证：在测试环境中测试调整效果，再应用到生产环境
- 监控反馈：监控系统性能，根据反馈调整参数
- 保持平衡：平衡内存、IO和连接参数，避免顾此失彼
- 文档记录：记录参数调整的原因和效果，便于后续参考
- 定期审查：定期审查配置参数，根据业务需求和系统变化调整
5.2 配置调优常见问题
配置调优常见问题及解决方案：
- 内存不足：调整shared_buffers和work_mem参数，避免内存使用过高
- IO瓶颈：调整IO参数，使用更快的存储设备，优化查询
- 连接数过多：使用连接池，调整max_connections参数
- 查询性能差：优化查询语句，创建索引，调整查询优化参数
- 写入性能差：调整WAL参数，使用更快的存储设备
- 系统不稳定：检查配置参数，确保参数值合理
- 配置参数冲突：确保参数之间的协调一致
- 调优效果不明显：分析工作负载，找出性能瓶颈，针对性调优
5.3 PostgreSQL配置调优技巧
PostgreSQL配置调优技巧：
- 内存调优技巧：
- shared_buffers：设置为总内存的25%，最大不超过16GB
- work_mem：根据并发连接数和查询复杂度设置，一般为总内存的1-2%
- maintenance_work_mem：设置为总内存的5-10%，最大不超过1GB
- effective_cache_size：设置为总内存的50-75%
- IO调优技巧：
- random_page_cost：机械硬盘设置为4.0，SSD设置为1.0-2.0
- sequential_page_cost：机械硬盘设置为1.0，SSD设置为0.5-1.0
- effective_io_concurrency：机械硬盘设置为2-4，SSD设置为100-200
- checkpoint_timeout：设置为15-30分钟，减少检查点频率
- 连接调优技巧：
- max_connections：根据应用需求和系统资源设置，一般为100-500
- 使用连接池：如pgBouncer或Pgpool-II，减少连接数
- idle_in_transaction_session_timeout：设置为30000-60000ms，避免空闲事务
- tcp_keepalives_idle：设置为60-120s，保持连接活跃
- 查询优化技巧：
- enable_seqscan：对于小表，设置为on；对于大表，设置为off
- enable_indexscan：设置为on，启用索引扫描
- enable_bitmapscan：设置为on，启用位图扫描
- random_page_cost：根据存储类型调整，影响索引选择
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
