小内存服务器（4G）运行MySQL 8.0应该如何调优？-PHPWP博客

在 4GB 内存的服务器上运行 MySQL 8.0，核心原则是“保守配置”：必须为操作系统、其他服务（如 Nginx/PHP）和缓存预留足够空间，防止触发 OOM Killer 导致服务崩溃。

以下是针对 4GB 内存环境的详细调优指南：

1. 核心参数调整 (`my.cnf` / `mysqld.cnf`)

这是最关键的一步。请修改 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf。

[mysqld]
# ==========================================
# 内存相关 (最关键)
# ==========================================
# 最大连接数：根据业务量调整，但单连接占用内存较大时不宜过高
# 建议：4G 服务器通常设置在 150-200 之间，避免并发高时内存爆炸
max_connections = 150

# 连接缓冲区大小：每个连接建立时的初始缓冲
# 建议：256K - 512K
net_buffer_length = 512k

# 关键：InnoDB 缓冲池大小
# 公式：总内存 - (OS + 其他进程 + 临时表 + 排序缓冲区等)
# 4G 机器建议设置为 1.5G ~ 2.0G (不要超过 50%)
# 注意：如果开启了 swap，可以稍微大一点，但物理内存紧张时务必保守
innodb_buffer_pool_size = 1610M  # 约 1.6GB，留出约 2.4GB 给系统和应用

# InnoDB 日志文件大小
# 默认值通常较小，可适当增大以减少刷盘频率，提升性能
innodb_log_file_size = 256M
innodb_log_files_in_group = 2

# 双写缓冲 (Doublewrite Buffer)
# 4G 机器建议开启，保证数据一致性，虽然消耗少量内存但值得
innodb_doublewrite = ON

# 线程缓存 (Thread Cache)
# 减少创建/销毁线程的开销
thread_cache_size = 32

# ==========================================
# 临时表与排序 (防止磁盘 IO 飙升)
# ==========================================
# tmp_table_size 和 max_heap_table_size 决定内存中临时表的最大值
# 建议：设为 32M - 64M，超过则自动转磁盘
tmp_table_size = 32M
max_heap_table_size = 32M

# 排序缓冲区 (Sort Buffer Size)
# 这是一个 per-connection 参数！如果设置过大且连接数多，会瞬间吃光内存
# 建议：1M - 2M，仅在查询需要大量排序时生效
sort_buffer_size = 2M
read_rnd_buffer_size = 2M

# 读取缓冲区 (Read Buffer Size)
# 同样是 per-connection 参数
read_buffer_size = 2M

# ==========================================
# 其他优化
# ==========================================
# 字符集 (推荐 utf8mb4)
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 慢查询日志 (用于排查问题，生产环境可限制时间)
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# 关闭不必要的功能以节省资源
skip-name-resolve = 1  # 禁用 DNS 解析，加快连接速度并避免反向解析卡顿
log_error_verbosity = 2

2. 计算逻辑与内存分配模型

在 4GB 机器上，内存分配必须遵循以下模型：

操作系统 (OS): 至少预留 512MB – 768MB (用于内核、文件系统缓存)。
Web 服务 (Nginx/PHP-FPM): 假设运行 PHP，每个进程可能占用 50MB-100MB。如果有 10-20 个并发请求，需预留 512MB – 1GB。
MySQL 自身: 剩余部分。
- innodb_buffer_pool_size: 1.5GB – 1.8GB (占比 40%-50%)。
- innodb_buffer_pool_instances: 如果 buffer_pool_size > 1GB，建议设置为 4 或 8 以减少锁竞争。
```
innodb_buffer_pool_instances = 4
```
- Per-Connection 变量总和: 假设 max_connections=150，每个连接平均额外占用 sort_buffer_size + read_buffer_size + join_buffer_size ≈ 4MB。
  - 潜在风险：$150 times 4MB = 600MB$。
  - 重要提示：这些内存只有在执行复杂查询时才分配，但如果所有连接同时跑复杂查询，内存会瞬间爆满。因此 sort_buffer_size 等参数必须设得很小。

3. 系统级优化 (Linux Kernel)

除了 MySQL 配置，操作系统层面的设置对稳定性至关重要。

A. 开启 Swap (虚拟内存)

虽然 Swap 会降低性能，但在 4GB 内存下，它是防止 OOM Killer 杀掉 MySQL 进程的最后一道防线。

创建一个 2GB – 4GB 的 Swap 分区或文件。

调整 vm.swappiness：让系统更倾向于使用物理内存，只有物理内存满了才用 Swap。

# 查看当前值
cat /proc/sys/vm/swappiness

# 临时设置为 10 (推荐值)
sudo sysctl vm.swappiness=10

# 永久生效，写入 /etc/sysctl.conf
echo "vm.swappiness=10" | sudo tee -a /etc/sysctl.conf

B. 调整 HugePages (可选，进阶)

对于 InnoDB 缓冲池较大的场景，HugePages 可以减少 TLB Miss，提升性能。

如果 innodb_buffer_pool_size 设置为 1.6GB，可以分配 2GB 的大页。
注意：这需要重启系统并修改 /etc/default/grub，操作有风险，若不确定可暂不启用。

C. 文件系统挂载选项

确保 MySQL 数据目录所在的分区使用了 noatime 选项，减少元数据写入开销。

# 编辑 /etc/fstab，在对应分区后添加 noatime
/dev/sdaX  /var/lib/mysql  ext4  defaults,noatime,nodiratime  0 0

4. SQL 层面优化 (代码层)

硬件受限的情况下，SQL 质量就是生命线。

*避免 `SELECT `**：只查询需要的字段，减少网络传输和内存占用。
索引优化：
- 检查 EXPLAIN 输出，确保 type 不是 ALL (全表扫描)。
- 利用覆盖索引 (Covering Index)，避免回表。
避免大事务：长事务会持有锁并占用 Undo Log，导致缓冲池碎片化。
定期清理：删除历史数据，保持表紧凑。

5. 监控与验证

调优后，必须观察实际效果。

监控工具：安装 htop 或 Prometheus + Grafana。
- 观察 free 命令中的 buff/cache 是否被频繁交换。
- 观察 MySQL 的 Threads_connected 和 Threads_running。
关键指标检查：
- Innodb_buffer_pool_read_requests vs Innodb_buffer_pool_reads：命中率应大于 99%。
- Created_tmp_disk_tables：如果这个值很高，说明 tmp_table_size 太小，或者 SQL 查询效率低。
- Aborted_connects：如果连接失败，可能是 max_connections 不够或系统负载过高。

总结建议

在 4GB 服务器上：

首选策略：将 innodb_buffer_pool_size 锁定在 1.6GB 左右。
安全底线：设置较小的 sort_buffer_size (2M) 和 max_connections (150)，防止并发攻击或复杂查询拖垮服务器。
兜底措施：必须开启 Swap，并设置 swappiness=10。
持续优化：通过慢查询日志找出低效 SQL，比单纯调整参数更有效。

如果业务增长导致 4GB 内存长期处于瓶颈状态（Swap 使用率持续较高），最经济的方案通常是增加内存或进行读写分离架构拆分，而非继续压榨单机性能。

1. 核心参数调整 (my.cnf / mysqld.cnf)