2G 内存跑 MySQL,核心思路不是“参数越激进越快”,而是:
- 别让 MySQL 频繁 swap
- 把有限内存优先给 InnoDB Buffer Pool
- 控制连接数和单连接缓冲
- 先把慢 SQL 和索引问题解决
- 看云盘、CPU、iowait,不只看内存
如果这台机器还跑着 Nginx、PHP-FPM、Java、Redis,那 MySQL 的配置还要再保守一些。
一、先判断这台 2G 机器是不是专跑 MySQL
如果是:
free -m
top
ps aux --sort=-%mem | head
如果 MySQL 独占 2G,可以给 innodb_buffer_pool_size 多一点;如果同机还有 Web 服务,建议 MySQL 内存控制在 800M~1.2G 以内。
一般经验:
| 场景 | innodb_buffer_pool_size 建议 |
|---|---|
| 2G 内存,MySQL 独占 | 768M~1G |
| 2G 内存,同机跑 Web/PHP | 384M~640M |
| 数据量远大于内存 | 不要盲目加大,优先看磁盘和 SQL |
二、推荐一份偏保守的 my.cnf
适合 2G 内存、 small/medium 站点,MySQL 5.7 或 8.0 都可参考。
[mysqld]
# ========== InnoDB 核心 ==========
innodb_buffer_pool_size = 768M
innodb_buffer_pool_instances = 1
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 8M
# MySQL 5.7 用这个
innodb_log_file_size = 128M
# MySQL 8.0.30+ 用这个,二选一,不要和上面混用
# innodb_redo_log_capacity = 256M
# ========== 连接数 ==========
max_connections = 80
thread_cache_size = 8
skip-name-resolve
# ========== 表缓存 ==========
table_open_cache = 512
table_definition_cache = 512
# 如果不用 MyISAM,给小一点即可
key_buffer_size = 16M
# ========== 单连接缓冲,别设太大 ==========
sort_buffer_size = 256K
read_buffer_size = 128K
read_rnd_buffer_size = 256K
join_buffer_size = 256K
tmp_table_size = 32M
max_heap_table_size = 32M
# ========== 慢查询日志 ==========
slow_query_log = ON
long_query_time = 1
MySQL 5.7 如果开启了 Query Cache,建议关掉:
query_cache_type = 0
query_cache_size = 0
MySQL 8.0 已经移除 Query Cache,不要写这两个参数,否则可能启动报错。
三、几个关键参数为什么这么设
1. innodb_buffer_pool_size
这是 InnoDB 最重要的内存参数,用来缓存表数据和索引。
2G 机器不要设太大,比如直接设 1.5G,很容易导致系统 swap。MySQL 一旦 swap,性能会断崖式下降。
如果 MySQL 独占机器,可以先试:
innodb_buffer_pool_size = 768M
观察一段时间,如果内存稳定、swap 几乎不动,可以试到:
innodb_buffer_pool_size = 896M
甚至 1G。
2. max_connections 不要盲目调大
很多人一看连接不够就改成 500、1000,这在 2G 机器上很危险。
MySQL 的每个连接都可能占用:
- sort buffer
- join buffer
- read buffer
- tmp table
- 排序临时空间
- 会话状态内存
虽然这些不是连接建立后立即全部占满,但高并发下很容易把内存打爆。
2G 机器建议:
max_connections = 50~100
如果是 PHP 站点,还要看 PHP-FPM:
pm.max_children
PHP-FPM 子进程数和 MySQL max_connections 要匹配。比如 PHP-FPM 有 100 个子进程,每个都可能连 MySQL,那 MySQL 设置 50 就没有意义,反而会报连接过多。
3. 单连接 buffer 不要设太大
下面这些参数不要看到别人推荐 4M、8M 就照抄:
sort_buffer_size
join_buffer_size
read_buffer_size
read_rnd_buffer_size
这些是“每连接/每查询相关”的缓冲,设大了对个别复杂查询可能有帮助,但并发一上来内存会炸。
2G 机器建议:
sort_buffer_size = 256K
join_buffer_size = 256K
read_buffer_size = 128K
read_rnd_buffer_size = 256K
如果确实有少量报表查询需要更大 sort buffer,最好在会话级别单独设置,不要全局拉高。
4. 日志落盘参数要按业务取舍
默认数据安全配置:
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
这是最稳的,但写性能压力较大。
如果是不太重要的日志、缓存类数据,可以考虑:
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0
或者:
sync_binlog = 1000
但要注意:宕机时可能丢失最近一小段时间的事务。订单、支付、账户类业务不要乱改。
四、系统层也要调
1. 降低 swap 倾向
MySQL 服务器不建议完全依赖 swap,但也不建议直接关掉 swap。可以降低 swappiness:
sysctl -w vm.swappiness=10
持久化:
echo "vm.swappiness = 10" >> /etc/sysctl.d/99-mysql.conf
sysctl --system
如果机器把 MySQL 的 InnoDB buffer pool swap 出去,性能会非常差。
2. 文件系统和磁盘
如果用 ext4/xfs,挂载参数可以考虑 noatime:
UUID=xxx / ext4 defaults,noatime 0 1
如果在国内云厂商的云主机上,还要看:
- 云盘是不是 SSD
- 是否突发性能实例
- CPU 积分是否耗尽
- 云盘 IOPS 是否打满
- iowait 是否长期偏高
很多时候 2G 机器慢,不是 MySQL 参数问题,而是云盘 IOPS 或 CPU 积分限制。
3. 文件描述符
连接数上来后,文件描述符不够也会出问题。
systemd 服务可以加:
[Service]
LimitNOFILE=65535
然后:
systemctl daemon-reload
systemctl restart mysqld
五、SQL 和索引比调参更重要
2G 内存下,慢 SQL 的破坏力更明显。一个没索引的查询,可能把 buffer pool 冲掉,拖慢整个实例。
建议打开慢查询日志:
slow_query_log = ON
long_query_time = 1
然后重点看:
EXPLAIN SELECT ...
常见优化点:
- 给 WHERE、ORDER BY、JOIN 字段建合适索引
- 避免
SELECT * - 避免在索引字段上使用函数,比如
DATE(create_time) = ... - 大分页避免
LIMIT 100000, 20 - JOIN 字段类型和字符集保持一致
- InnoDB 表尽量有显式主键
- 不要大量使用 MyISAM,尽量统一 InnoDB
六、怎么判断还要不要继续加 buffer pool
看这几个状态:
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
重点看:
Innodb_buffer_pool_read_requests
Innodb_buffer_pool_reads
粗略命中率:
1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests
如果命中率长期低于 99%,并且磁盘读很高,同时系统 swap 不明显,可以适当加大 buffer pool。
如果 swap 已经出现,优先降低 buffer pool 或控制连接数,不要继续加。
再看:
vmstat 1
iostat -x 1
如果 si/so 长期非零,说明在 swap;如果 %util 长期接近 100%,就是磁盘瓶颈。
七、不要做的事
这几件事在 2G 机器上很容易翻车:
max_connections直接设 500、1000innodb_buffer_pool_size设到 1.5G 以上sort_buffer_size、join_buffer_size全局设 8M、16M- 开启 Query Cache
- 不看慢查询,只靠 my.cnf 硬调
- 频繁执行
OPTIMIZE TABLE - 把临时表目录放到很小的
tmpfs上
八、实际建议路线
如果是我接手一台 2G 的 MySQL,会按这个顺序来:
- 看
free -m、top、iostat,确认内存和磁盘瓶颈 - 开慢查询日志,抓 1~2 天慢 SQL
- 控制
max_connections - 设置
innodb_buffer_pool_size为 512M~768M 起步 - 把单连接 buffer 降下来
- 改索引和 SQL
- 观察 swap、buffer pool 命中率、Threads_running、慢查询数量
- 如果业务继续增长,优先加内存或把数据库独立部署
2G 内存现在确实偏小,调参能改善,但上限有限。真正的瓶颈如果是数据量、并发、索引设计或者云盘 IOPS,光靠 MySQL 参数是救不回来的。
云服务器