MySQL 与 PostgreSQL 生产部署：基础调优指南

无论选 MySQL 还是 PostgreSQL，生产部署的核心都是同一件事：让默认配置匹配你的硬件与负载。两者出厂参数都以"兼容优先"而非"性能优先"，尤其是内存类参数普遍偏保守，部署后必须先调。

一、连接数与并发

PostgreSQL：max_connections 默认 100。官方 wiki 指出，好硬件上 PG 能支撑几百个连接，但上千连接时应引入连接池（如 PgBouncer）降低开销。

# postgresql.conf
max_connections = 200

MySQL：max_connections 默认 151。MySQL 每个连接都有一定内存开销，配合 thread_cache_size 复用线程：

# my.cnf
max_connections = 300
thread_cache_size = 64

连接数不是越大越好。MySQL 每个连接要占几 MB 内存，PG 的每个后端进程也会吃内存，盲目把 max_connections 调到几千，机器可能先被连接耗尽。更常见的做法是让应用连到连接池，池子再复用少量真实连接。PG 侧标准方案是 PgBouncer，把 pool_mode 设为 transaction，一般 50~200 个后端连接就能扛住几千个应用连接：

# pgbouncer.ini
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb
[pgbouncer]
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 50

二、内存调优

PostgreSQL 内存参数（官方 wiki 的经典建议）：

参数	建议起始值	说明
`shared_buffers`	物理内存的 25%	数据库共享缓冲区，超过 40% 通常无益
`effective_cache_size`	物理内存的 50%~75%	仅供查询规划器估算，非真实分配
`work_mem`	4MB~64MB	排序/哈希等操作的内存，按并发连接数累加
`maintenance_work_mem`	64MB~1GB	VACUUM、建索引等维护操作使用

# 以 16GB 内存的专用数据库服务器为例
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 64MB
maintenance_work_mem = 1GB

work_mem 是按操作分配的：50MB × 30 个并发用户很快就能吃掉 1.5GB，设置时要乘上并发数再评估。

MySQL InnoDB 内存参数：

# 以 16GB 内存为例
innodb_buffer_pool_size = 8G   # 建议物理内存的 50%~75%
innodb_log_file_size = 1G
innodb_buffer_pool_instances = 8

innodb_buffer_pool_size 是最关键的参数，缓存表与索引数据；innodb_log_file_size 决定 redo log 大小，太小时写入密集场景会出现频繁的 checkpooint。MySQL 8.0.30+ 可用 innodb_redo_log_capacity 统一管理 redo log。

三、WAL 与持久化

PostgreSQL 的 synchronous_commit（默认 on）保证每次提交都刷盘，是 ACID 的基础，生产环境不建议关闭。wal_buffers 默认随 shared_buffers 自动调整，一般无需手动改。

MySQL 的 innodb_flush_log_at_trx_commit：

1（默认）：每次提交都刷盘，最安全
2：每秒刷盘，性能更好但崩溃时可能丢最多 1 秒数据
0：由系统决定，最快但风险最大

对绝大多数生产场景，保持 1，配合 sync_binlog=1 保证复制安全。

调 synchronous_commit 之前先想清楚业务能不能接受“最多丢一秒数据”。很多读多写少的中小站点，把 PG 的 synchronous_commit 设为 off 能明显降低写入延迟，代价是崩溃时可能丢最近提交的事务。对订单、支付这类数据，保持默认 on 不要动。

四、自动维护

PostgreSQL 的 autovacuum 千万不要关。官方 wiki 明确指出："几乎所有 vacuum 问题的答案都是更频繁地 vacuum，而不是更少"。它负责清理死行、防止表膨胀：

autovacuum = on
autovacuum_max_workers = 3

MySQL 的 redo log 与 purge 线程：保持默认通常即可，重点是监控 Innodb_buffer_pool_wait_free 等状态值判断是否存在瓶颈。

五、慢查询日志

两类数据库都支持慢查询日志，是定位性能问题的第一步。

PostgreSQL：

log_min_duration_statement = 1000   # 记录超过 1 秒的查询
log_line_prefix = '%t:%r:%u@%d:[%p]: '

MySQL：

slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log

慢查询日志只是第一步，拿到日志后要学会读执行计划。PG 用 EXPLAIN ANALYZE，MySQL 的 EXPLAIN 默认不真正执行，要看实际成本得用 EXPLAIN ANALYZE（8.0.18+）。读执行计划时重点盯三处：有没有全表扫描（seq scan / type=ALL）、有没有额外排序（Sort / filesort）、有没有该走索引却没走（rows 估算远大于实际命中）。大多数慢查询靠加一条合适的索引就能解决。

六、部署形态与运维

容器化：数据库同样可以用 Docker Compose 部署，数据目录用命名卷持久化，参考Compose LAMP 环境。
备份：生产必备，见备份恢复手册和云服务器备份策略。
监控：用 Prometheus + Grafana 盯连接数、慢查询、缓冲池命中率，告警接入监控告警。
LNMP 集成：PHP 站点可参考LNMP 环境搭建。

一次真实的调优案例

一个以文章为主的站点，晚高峰时数据库 CPU 打满、页面要 3 秒才出。第一件事是开慢查询日志，第二天发现一条按月统计文章数的聚合查询跑了几十秒。分析执行计划后确认它没走 published_at 索引，加索引后单次查询从 8 秒降到 0.3 秒。随后把 shared_buffers 从默认 128MB 提到 4GB，命中率上去，CPU 占用降了一半。整个过程只改了三个参数，每改一个都用压测脚本验证一次——数据库调优很多时候就是“日志 + 索引 + 内存”三板斧。

16IDC 观察

数据库调优的收益在“开头”最明显：把内存参数与硬件匹配、打开慢查询日志、保持自动维护，往往就能解决大部分性能问题，无需深入索引与查询计划层面。关键原则是每次只改一个参数、用真实负载验证、记录变更，避免照搬网上配置。对中小项目，先做好这些基础项，比盲目追求高端特性更重要。

参考：PostgreSQL 调优 wiki https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server ；PG 资源参数 https://www.postgresql.org/docs/current/runtime-config-resource.html ；MySQL InnoDB 参数 https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html ；PgBouncer 配置 https://www.pgbouncer.org/config.html

原文来源：https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server 、https://www.postgresql.org/docs/current/runtime-config-resource.html 、https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html

MySQL 与 PostgreSQL 生产部署：基础调优指南