MariaDB内存优化配置方法

MariaDB内存优化配置方法

一 核心原则与快速估算

• 以 InnoDB 为主的专用数据库，将 innodb_buffer_pool_size 设为物理内存的约70%；以 MyISAM 为主可给 key_buffer_size 约**20%**内存；混合负载可按需折中。避免把内存“给满”，需为操作系统、连接栈、临时表等预留空间。
• 警惕“每连接”内存参数（如 sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size、binlog_cache_size、thread_stack）的叠加效应：它们会按并发连接数线性放大峰值内存。
• 快速估算峰值内存（单位换算为GB）：
  SELECT (@@key_buffer_size + @@query_cache_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@max_connections * (@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack + @@tmp_table_size) ) / (102410241024) AS MAX_MEMORY_GB;
  该公式用于评估“最坏情况”内存占用，便于压测与容量规划。

二 关键参数建议与设置方法

• InnoDB 缓冲池
  • innodb_buffer_pool_size：专用库建议设为内存的约70%；非专用库可先设为约1/4，观察再调。
  • innodb_buffer_pool_instances：建议1–16，每个实例≥1GB，提升并发与减少锁争用。
  • 动态性：buffer pool 大小可通过 SET GLOBAL 在线调整（滚动预热），实例数通常需重启。
• MyISAM 索引缓存
  • key_buffer_size：仅当存在 MyISAM 表或内部临时表使用 MyISAM 时调大；纯 InnoDB 场景可保留较小值（如 8–32M）。
  • 调优依据：关注状态变量 Key_read_requests/Key_reads，命中率越高越好，经验上至少 1:100，更优 1:1000。
• 连接与会话
  • max_connections：结合业务并发与内存预算设定，避免过大导致线程栈与每连接缓冲叠加爆炸。
  • thread_cache_size：缓存线程以减少创建/销毁开销；可用命中率公式评估：(Connections − Threads_created) / Connections。
  • 超时收敛：如 wait_timeout、interactive_timeout，避免空闲连接长期占用内存。
• 临时表与排序/连接缓冲
  • tmp_table_size 与 max_heap_table_size：控制内存临时表上限，二者建议一致；若 Created_tmp_disk_tables / Created_tmp_tables > 5%，可适当上调。
  • sort_buffer_size、join_buffer_size：按“会话按需分配”，不宜过大；建议从256K–1M起步，结合压测逐步微调。
• 二进制日志与重做日志
  • binlog_cache_size：大事务较多时适当增大；观察 Binlog_cache_disk_use/Binlog_cache_use。
  • innodb_log_buffer_size：一般 16–64M 足够；事务密集可适度上调。
  • innodb_log_file_size：增大可减少检查点抖动与刷盘频率（需权衡恢复时间）。
• 查询缓存
  • MariaDB 默认启用但命中率常不理想；高并发写场景建议 query_cache_type=0/OFF 以降低开销。
• 运行时修改
  • 多数全局变量可用 SET GLOBAL var=value; 动态生效；涉及缓冲池大小等变更，建议分批、限幅调整并观察稳定性。

三 监控与诊断要点

• InnoDB 缓冲池健康度
  • 命中率：关注 Innodb_buffer_pool_read_requests / Innodb_buffer_pool_reads，长期低于**99%**需排查索引/SQL/缓冲池不足。
  • 脏页与空闲页：通过 SHOW ENGINE INNODB STATUS 观察 Free buffers / Database pages 与脏页比例，脏页过高可能需提升刷盘能力或降低写入强度。
• 连接与线程
  • 连接使用率：Threads_connected / max_connections 建议**<80%；线程缓存命中率目标>90%**。
• 临时表与磁盘溢出
  • 关注 Created_tmp_disk_tables / Created_tmp_tables；若比例偏高，优先优化 SQL（减少大排序/大聚合/笛卡尔积），其次适度上调 tmp_table_size / max_heap_table_size。
• 每连接缓冲叠加风险
  • 结合“最大连接数 × 每连接缓冲”与公式估算峰值内存，防止 OOM 或 swap。
• 内存分配细粒度观测
  • 启用 Performance Schema 内存监控：
    UPDATE performance_schema.setup_instruments SET ENABLED=‘YES’ WHERE NAME LIKE ‘%memory/%’;
    SELECT SUBSTRING_INDEX(EVENT_NAME,‘/’,3) AS Component, ROUND(SUM(CURRENT_NUMBER_OF_BYTES)/1024/1024,2) AS Memory_MB
    FROM performance_schema.memory_summary_global_by_event_name GROUP BY Component ORDER BY Memory_MB DESC LIMIT 10;
• 文件句柄与表缓存
  • 关注 open_files_limit 与 Opened_files/Uptime：若大于5，考虑增大 table_open_cache；过大亦会因线性扫描降低性能。

四 不同内存规模的参考模板

• 说明：以下为“起点值”，需结合业务特征与压测微调；连接相关参数请按并发与内存预算同步评估。
• 场景A：专用数据库，内存 16GB（InnoDB 为主）
  • innodb_buffer_pool_size = 10–12G
  • innodb_buffer_pool_instances = 8（每实例≥1G）
  • key_buffer_size = 16M
  • max_connections = 300–500
  • thread_cache_size = 32
  • tmp_table_size = max_heap_table_size = 64M
  • sort_buffer_size = 256K；join_buffer_size = 256K–1M
  • innodb_log_buffer_size = 32M；innodb_log_file_size = 100–256M
  • query_cache_type = 0
• 场景B：专用数据库，内存 128GB（InnoDB 为主）
  • innodb_buffer_pool_size = 90–100G
  • innodb_buffer_pool_instances = 8–16
  • key_buffer_size = 32M
  • max_connections = 1000–2000（谨慎评估每连接内存叠加）
  • thread_cache_size = 64–128
  • tmp_table_size = max_heap_table_size = 64–128M
  • sort_buffer_size = 256K–512K；join_buffer_size = 1–2M
  • innodb_log_buffer_size = 64M；innodb_log_file_size = 256–512M
  • query_cache_type = 0
• 场景C：混合负载，内存 32GB（InnoDB+MyISAM）
  • innodb_buffer_pool_size = 20–24G
  • key_buffer_size = 1–2G（依据 MyISAM 索引量与 Key_reads/Key_read_requests 调整）
  • 其余参数按并发与 SQL 特征在上述范围内取值并压测验证。