Linux MySQL内存配置怎样优化

Linux 上 MySQL 内存配置优化实操指南

一 核心原则与快速估算

• 明确目标：在不触发 OOM 和 Swap 的前提下，尽量提升热点数据与索引的命中率，同时控制并发连接带来的会话级内存膨胀。
• 快速估算公式（按“峰值并发”评估最坏情况）：
  • 总内存 ≈ innodb_buffer_pool_size
    • key_buffer_size（MyISAM 索引）
    • innodb_log_buffer_size
    • query_cache_size（仅 MySQL 5.7 及以下）
    • max_connections ×（sort_buffer_size + read_buffer_size + read_rnd_buffer_size + join_buffer_size + binlog_cache_size + thread_stack）
    • 其他固定/元数据缓存（如 table_definition_cache、Performance Schema 等）
• 关键经验值：
  • innodb_buffer_pool_size：专用数据库服务器可设为物理内存的50%–75%，在内存充裕且主要为 InnoDB 时可上探至70%–80%，需结合负载微调。
  • innodb_log_file_size：通常设为 128M–512M；更大的日志有利于写吞吐，但会增加恢复时间。
  • max_connections：避免盲目放大，结合连接池与业务并发；连接越多，线程独享内存成倍放大。
  • query_cache_size：MySQL 8.0 已移除；5.7 及以下如启用需谨慎评估竞争与命中率。

二 关键参数建议与适用场景

三 诊断与监控步骤

• 基线采集
  • 系统层：free -m、vmstat 1、top/htop、/proc/meminfo；关注 Swap 与 si/so。
  • MySQL 层：SHOW VARIABLES LIKE ‘%buffer%’;、SHOW STATUS LIKE ‘Threads_connected’;、SHOW ENGINE INNODB STATUS\G；慢查询日志与 EXPLAIN 分析。
• 命中率与瓶颈识别
  • InnoDB 缓冲池命中率 ≈ (Innodb_buffer_pool_read_requests − Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests × 100%；长期低于**95%**可考虑增大缓冲池或优化访问模式。
  • 临时表：监控 Created_tmp_disk_tables / Created_tmp_tables；若磁盘临时表比例超过10%，适度上调 tmp_table_size / max_heap_table_size 并优化 SQL（索引、覆盖索引、避免大字段参与排序/分组）。
  • 连接与线程：关注 Threads_connected、连接耗时、线程创建频率；结合应用连接池配置（最小/最大连接、超时、回收策略）。

四 示例配置与动态调整

• 示例（仅供起步参考，需按实际调优）：
  • 场景：16GB 内存、以 InnoDB 为主、中等写入、连接池管理连接
  • 建议片段（my.cnf/mysqld 段）：
    • innodb_buffer_pool_size = 10G
    • innodb_log_file_size = 256M
    • innodb_log_buffer_size = 16M
    • innodb_flush_log_at_trx_commit = 2
    • innodb_file_per_table = 1
    • key_buffer_size = 32M
    • query_cache_type = 0（MySQL 5.7 及以下；8.0 删除）
    • tmp_table_size = 128M
    • max_heap_table_size = 128M
    • sort_buffer_size = 8M
    • join_buffer_size = 64M
    • read_buffer_size = 2M
    • read_rnd_buffer_size = 2M
    • binlog_cache_size = 4M
    • max_connections = 500
    • thread_cache_size = 100
    • table_open_cache = 2000
    • table_definition_cache = 1000
    • performance_schema_max_table_instances = 400
• 动态调整（无需重启，视版本/变量支持情况）
  • SET GLOBAL innodb_buffer_pool_size = 1210241024*1024;
  • SET GLOBAL max_connections = 800;
  • SET GLOBAL tmp_table_size = 25610241024;
  • SET GLOBAL max_heap_table_size = 25610241024;
• 变更要点
  • 涉及 innodb_log_file_size 的变更需先干净关闭、替换/调整日志文件后启动；其他多数变量可按需动态生效。
  • 任何调整前先备份配置，低峰期灰度，变更后持续观察命中率、连接、临时表与慢查询。

五 系统与稳定性设置

• 避免 OOM
  • 合理规划 MySQL 与其他服务的内存配额；必要时通过 /proc/<mysqld_pid>/oom_score_adj 降低 MySQL 被 OOM Killer 选中的概率（如设为 -20），并优化应用与 SQL 减少异常内存占用。
• 内存过度提交策略
  • 结合 /proc/sys/vm/overcommit_memory 与 overcommit_ratio 设置（如 2 模式配合合理比率），避免系统层面过度承诺导致分配失败或突发 OOM。
• 其他
  • 避免“通用大配置”生搬硬套，优先基于业务特征与监控数据定制；定期审计并清理冗余索引与低效 SQL。