6.7.5 内存与缓存参数调优

内存与缓存参数调优#

目标与原则
- 保障热数据驻留内存，降低磁盘IO与抖动
- 依据业务负载与数据规模分配内存，避免过度配置导致Swap
- “先度量、再调整、再验证”，以指标驱动调优

原理草图：MySQL内存与缓存路径

关键参数与建议（含示例）
1. InnoDB Buffer Pool（核心热数据缓存）
- innodb_buffer_pool_size：专用DB建议物理内存的 60%–80%
- innodb_buffer_pool_instances：每实例 1–8GB，实例数适配CPU与并发

# /etc/my.cnf
[mysqld]
innodb_buffer_pool_size=8G
innodb_buffer_pool_instances=4

2. InnoDB Log Buffer（写入缓存）
   - innodb_log_buffer_size：写入多时适当增大

# /etc/my.cnf
[mysqld]
innodb_log_buffer_size=128M

3. 表缓存与文件句柄
   - table_open_cache / table_open_cache_instances
   - open_files_limit 需与系统限制一致

# /etc/my.cnf
[mysqld]
table_open_cache=4096
table_open_cache_instances=8
open_files_limit=65535

4. 线程与连接相关缓存
   - thread_cache_size：减少线程创建销毁

# /etc/my.cnf
[mysqld]
thread_cache_size=128

5. 临时表与内存临时空间
   - tmp_table_size / max_heap_table_size 取小值

# /etc/my.cnf
[mysqld]
tmp_table_size=256M
max_heap_table_size=256M

6. 排序与连接缓冲（每连接分配）
   - sort_buffer_size、join_buffer_size、read_buffer_size

# /etc/my.cnf
[mysqld]
sort_buffer_size=4M
join_buffer_size=4M
read_buffer_size=2M
read_rnd_buffer_size=4M

7. 性能模式与系统开销
   - performance_schema 适度开启并控制开销

# /etc/my.cnf
[mysqld]
performance_schema=ON
performance_schema_digests_size=10000

安装与生效步骤（含命令解释）

# 1) 修改配置文件
sudo vi /etc/my.cnf

# 2) 验证配置文件语法（不同发行版可用 mysqld --help --verbose 检查）
mysqld --verbose --help | head -n 20

# 3) 重启MySQL服务使配置生效
sudo systemctl restart mysqld

# 4) 查看关键参数是否生效
mysql -uroot -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -uroot -p -e "SHOW VARIABLES LIKE 'tmp_table_size';"

度量与验证示例

-- Buffer Pool命中率
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

-- 临时表落盘比例
SHOW GLOBAL STATUS LIKE 'Created_tmp%';

-- 线程创建与连接关系
SHOW GLOBAL STATUS LIKE 'Threads_created';
SHOW GLOBAL STATUS LIKE 'Connections';

-- 排序与读随机
SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';
SHOW GLOBAL STATUS LIKE 'Handler_read_rnd_next';

调优计算示例（防止内存超配）

总内存消耗 ≈ 全局缓存 + (连接级缓存 * 并发连接数)

示例：
全局缓存: buffer_pool 8G + log_buffer 128M ≈ 8.125G
连接级缓存: sort 4M + join 4M + read 2M + read_rnd 4M ≈ 14M
并发连接数: 200
连接级总量: 14M * 200 = 2.8G
合计: 10.9G  (需小于物理内存并保留系统与OS缓存)

常见问题与排错步骤
1. 内存不足或Swap频繁

# 观察系统内存与Swap
free -h
vmstat 1 5

# 处理思路：
# - 降低每连接缓冲
# - 下调 max_connections
# - 减小 tmp_table_size

2. 临时表落盘比例高

SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';

-- 处理思路：
-- - 增大 tmp_table_size / max_heap_table_size
-- - 优化SQL避免大排序与大GROUP BY

3. Opened_tables过高

SHOW GLOBAL STATUS LIKE 'Opened_tables';
SHOW GLOBAL STATUS LIKE 'Open_tables';

-- 处理思路：
-- - 增大 table_open_cache
-- - 排查应用短连接过多

4. Buffer Pool命中率低

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';

-- 处理思路：
-- - 扩大 innodb_buffer_pool_size
-- - 优化热点SQL与索引

练习与实践
1. 将 innodb_buffer_pool_size 从 4G 调到 8G，记录调前调后：
- QPS、P95响应时间、Buffer Pool命中率
2. 增大 tmp_table_size 至 256M，观察 Created_tmp_disk_tables 比例变化
3. 将 sort_buffer_size 从 2M 调到 4M，比较 Sort_merge_passes 变化
4. 通过 max_connections 控制并发上限，验证Swap是否消失