在 2U2G 服务器资源下优化 MySQL 实例内存占用

2025/06/10 共 1139 字 3 分钟阅读

AI 摘要: 本文针对一台5年使用的低配2C2G CVM服务器，探讨了MySQL在小内存环境下的优化策略。作者指出，默认配置中MySQL会根据物理内存自动分配缓冲区，但在低配服务器上可能导致资源浪费或性能不佳。为提升效率，文章重点介绍了三项核心优化措施：调整`innodb_buffer_pool_size`以合理使用内存，关闭`performance_schema`减少资源占用，以及调优`thread_stack`和`max_connections`，以防止过度占用内存。这些优化点旨在在有限资源下实现MySQL的运行效果最大化，具有一定实践指导价值。

背景

自己有一台订阅了 5 年多 2C2G 的低配 CVM 机器，以前是按默认配置安装的 Mysql 服务，会据物理内存自动分配缓冲区，在小内存机器上非常不合理，简单记录下配置优化点

1
2
3
MiB Mem: 53.6/1771.4
PID    USER   VIRT       RES    %MEM  COMMAND
6551   mysql  1828124  540296  29.8  mysqld

Mysqld 关键配置信息摸底

内存 & 链接参数

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 内存相关参数
SELECT variable_name, variable_value
FROM performance_schema.global_variables
WHERE variable_name IN (
  'innodb_buffer_pool_size',
  'innodb_buffer_pool_instances',
  'innodb_log_buffer_size',
  'key_buffer_size',
  'max_connections',
  'thread_stack',
  'tmp_table_size',
  'max_heap_table_size',
  'sort_buffer_size',
  'join_buffer_size',
  'read_buffer_size',
  'performance_schema'
);

-- 查看实际连接使用情况（用来估算峰值资源占用）
-- 当前活跃连接数
SHOW GLOBAL STATUS LIKE 'Threads_connected';

-- 当前正在执行查询的线程数
SHOW GLOBAL STATUS LIKE 'Threads_running';

-- 历史峰值连接数（重要！）
SHOW GLOBAL STATUS LIKE 'Max_used_connections';

关键参数解读（InnoDB）

字段	含义	优化动作
innodb_buffer_pool_size（最关键）	InnoDB 的数据和索引缓存池，默认机器内存 50%～ 75%，2G 上面自动分配了 512M	将其降到 128MB
performance_schema	内置性能诊断框架，占用 100 ～ 200M,记录等待事件、语句统计、内存分配等细粒度指	可以使用慢日志替代
max_connections × thread_stack（线性累积）	thread_stack 默认 1MB，max_connections 默认 151	thread_stack 降到 256KB，同时把 max_connections 降低到 64
innodb_log_buffer_size	redo log 在内存中的缓冲区大小，默认 16M	小事务、低写入频率的场景，8MB 完全够用。

优化配置方案

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

# 启用慢查询日志
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow-query.log
long_query_time=2  # 记录执行时间超过 2 秒的查询

# 常规日志(包括断开连接事件)
general_log=1
general_log_file = /var/log/mysql/mysql-general.log

# 资源优化
# -------

# 核心大头：
innodb_buffer_pool_size = 128M
innodb_buffer_pool_instances = 1

# 关掉 performance_schema 节省 100-200MB
performance_schema = OFF

# 你贴出来的这些参数优化
innodb_log_buffer_size = 8M      # 16M → 8M，小事务够用
thread_stack = 256K              # 1MB → 256KB × 64 = 约16M（降低了8倍）

# 最大并发连接数上限
max_connections = 64             # 给够缓冲，但压缩理论内存上限

# 加上 wait_timeout 防止空连接长期占用
wait_timeout = 300               # 空闲连接 5 分钟后断开（默认 8 小时）
interactive_timeout = 300

配置完成后，重启 systemctl restart mysqld 重启生效，重新跑 top 和内存相关参数 SQL 查询检测

其他

innodb_flush_log_at_trx_commit = 2 // 控制事务是否实时刷盘
performance_schema 关闭 // 小业务通过 slowlog 够用
“Too many connections” // 适当调大 max_connections 值

担心资源问题，做好性能监控

配置慢查询日志，通过 `mysqldumpslow` 查看

1
2
3
4
5
# 按总耗时排序，找最慢的 10 条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按执行次数排序，找频繁出现的慢查询
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

实时状态诊断

1
2
3
4
5
6
7
show processlist;

--
SELECT id, user, host, db, command, time, state, LEFT(info, 80) AS query
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;

State 列的常见问题信号：

Waiting for table metadata : lock 被长事务锁住找到长事务 -> KILL 掉
Copying to tmp table: 内存临时表不够，写磁盘 -> 优化 SQL，加索引
Sorting result: 排序缓冲不足检查，ORDER BY 是否命中索引
locked: 行锁等待 -> 找持锁连接

计算两个核心比值

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 缓冲池命中率，正常应 > 99%
-- 低于 95% 说明 buffer_pool 太小，大量 I/O 读磁盘
SELECT ROUND(
  (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100, 2
) AS buffer_pool_hit_rate_pct
FROM (
  SELECT
    (SELECT variable_value FROM performance_schema.global_status
     WHERE variable_name = 'Innodb_buffer_pool_reads') AS Innodb_buffer_pool_reads,
    (SELECT variable_value FROM performance_schema.global_status
     WHERE variable_name = 'Innodb_buffer_pool_read_requests') AS Innodb_buffer_pool_read_requests
) t;

-- 临时表落盘率，正常应接近 0%
-- 高说明 tmp_table_size 不够，或 SQL 本身需要优化
SELECT ROUND(
  Created_tmp_disk_tables / Created_tmp_tables * 100, 2
) AS tmp_disk_table_pct
FROM (
  SELECT
    (SELECT variable_value FROM performance_schema.global_status
     WHERE variable_name = 'Created_tmp_disk_tables') AS Created_tmp_disk_tables,
    (SELECT variable_value FROM performance_schema.global_status
     WHERE variable_name = 'Created_tmp_tables') AS Created_tmp_tables
) t;

走 PromQL

这部分在另一篇文章有讲到，后面会补充。

排查决策树小结

应用 SQL 响应变慢

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
应用 SQL 响应变慢
        │
        ├─ Threads_running 异常高？
        │       ├─ 是 → SHOW FULL PROCESSLIST
        │       │         ├─ State = Waiting for lock → 找长事务 KILL
        │       │         └─ State = Copying to tmp table → 慢查询日志 → 加索引
        │       └─ 否 ↓
        │
        ├─ buffer_pool 命中率 < 99%？
        │       └─ 是 → innodb_buffer_pool_size 太小
        │                适当调大（不超过可用内存的 60%）
        │
        ├─ Created_tmp_disk_tables 快速增长？
        │       └─ 是 → 慢查询日志找 Rows_examined 大的 SQL
        │                EXPLAIN 分析，添加合适索引
        │
        └─ 慢查询日志有大量记录？
                └─ 是 → EXPLAIN 分析执行计划
                         重点看 type = ALL（全表扫描）
                         key = NULL（未使用索引）

小结

在 2C2G 低配服务器上，MySQL 内存优化的核心抓手只有三个：

innodb_buffer_pool_size: 从默认自动分配（约 512MB）降到 128MB，单项节省最多。
performance_schema = OFF: 关闭内置诊断框架，节省 100~200MB，用慢查询日志替代。
thread_stack + max_connections: 联动压缩理论内存上限。

背景