MySQL 内存使用优化指南

内存使用概览

MySQL 使用内存主要分为以下几部分： - 全局缓冲池 (InnoDB Buffer Pool) - 线程级内存 (Thread Buffers) - 查询缓存 (Query Cache) - 排序缓冲区和临时表 - 其他内存区域

关键优化点

1. InnoDB Buffer Pool 优化

-- 查看当前设置
SHOW VARIABLES LIKE 'innodb_buffer_pool%';

-- 建议设置为可用物理内存的50-80%
SET GLOBAL innodb_buffer_pool_size=8G;

优化建议： - 设置为服务器物理内存的50-80% - 考虑使用innodb_buffer_pool_instances(通常设置为4-8)来减少争用 - 启用innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup加速预热

2. 线程级内存优化

-- 查看线程相关内存设置
SHOW VARIABLES LIKE '%thread%';
SHOW VARIABLES LIKE '%sort%';
SHOW VARIABLES LIKE '%join%';

关键参数： - sort_buffer_size (建议2-8MB) - join_buffer_size (建议2-8MB) - read_buffer_size (建议1-4MB) - read_rnd_buffer_size (建议1-4MB) - thread_stack (默认256KB通常足够)

3. 查询缓存优化

-- 查询缓存状态
SHOW VARIABLES LIKE 'query_cache%';
SHOW STATUS LIKE 'Qcache%';

优化建议： - 对于写密集型应用，考虑禁用查询缓存(query_cache_size=0) - 如果使用，query_cache_size通常不超过256MB

4. 临时表和内存表

SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

优化建议： - 将tmp_table_size和max_heap_table_size设置为相同值(通常32-64MB) - 监控Created_tmp_disk_tables和Created_tmp_tables状态

5. 连接数管理

SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';

优化建议： - 合理设置max_connections(通常100-300) - 使用连接池减少连接数 - 监控Threads_connected和Aborted_connects

监控工具

1. 内置命令：

SHOW ENGINE INNODB STATUS;
SHOW STATUS;
SHOW VARIABLES;

2. 性能模式：

-- 启用性能模式
SELECT * FROM performance_schema.setup_instruments;
SELECT * FROM performance_schema.memory_summary_global_by_event_name;

3. 外部工具：

- MySQL Workbench - Percona PMM - Prometheus + Grafana

高级优化技巧

1. 内存分配策略：

   • 考虑使用jemalloc或tcmalloc替代默认的内存分配器

2. NUMA架构优化：

   # 启动MySQL时使用numactl
   numactl --interleave=all mysqld
   

3. 交换空间预防：

   # 确保swappiness设置为低值
   echo 1 > /proc/sys/vm/swappiness
   

4. 透明大页禁用：

   # 在/etc/rc.local中添加
   echo never > /sys/kernel/mm/transparent_hugepage/enabled
   

常见问题排查

1. 内存泄漏：

   • 监控resident memory随时间增长情况
   • 检查是否有未关闭的连接或游标

2. OOM Killer杀死MySQL：

   • 检查系统日志/var/log/messages
   • 适当降低内存配置或增加服务器内存

3. 性能突然下降：

   • 检查是否有大查询耗尽内存
   • 监控sort_merge_passes和created_tmp_disk_tables

通过以上优化措施，可以显著提高MySQL的内存使用效率，减少磁盘I/O，提升整体性能。