奋斗
努力

在2G内存的Linux服务器上如何优化MySQL性能?

云计算

2G 内存跑 MySQL,核心思路不是“参数越激进越快”,而是:

  1. 别让 MySQL 频繁 swap
  2. 把有限内存优先给 InnoDB Buffer Pool
  3. 控制连接数和单连接缓冲
  4. 先把慢 SQL 和索引问题解决
  5. 看云盘、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、1000
  • innodb_buffer_pool_size 设到 1.5G 以上
  • sort_buffer_size、join_buffer_size 全局设 8M、16M
  • 开启 Query Cache
  • 不看慢查询,只靠 my.cnf 硬调
  • 频繁执行 OPTIMIZE TABLE
  • 把临时表目录放到很小的 tmpfs 上

八、实际建议路线

如果是我接手一台 2G 的 MySQL,会按这个顺序来:

  1. 看 free -m、top、iostat,确认内存和磁盘瓶颈
  2. 开慢查询日志,抓 1~2 天慢 SQL
  3. 控制 max_connections
  4. 设置 innodb_buffer_pool_size 为 512M~768M 起步
  5. 把单连接 buffer 降下来
  6. 改索引和 SQL
  7. 观察 swap、buffer pool 命中率、Threads_running、慢查询数量
  8. 如果业务继续增长,优先加内存或把数据库独立部署

2G 内存现在确实偏小,调参能改善,但上限有限。真正的瓶颈如果是数据量、并发、索引设计或者云盘 IOPS,光靠 MySQL 参数是救不回来的。

未经允许不得转载:云服务器 » 在2G内存的Linux服务器上如何优化MySQL性能?