Ubuntu MySQL内存占用高怎么优化

Ubuntu 上 MySQL 内存占用高的系统化优化

一 快速定位占用来源

• 先看操作系统层真实占用：用 top/htop 找到 mysqld 的 PID，再查看 /proc//status 的 VmRSS（实际物理内存），确认是否异常增长。
• 查看 MySQL 关键指标：
  • InnoDB 缓冲池：SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
  • 当前连接：SHOW STATUS LIKE ‘Threads_connected’;
  • 临时表行为：SHOW GLOBAL STATUS LIKE ‘Created_tmp%tables’;（比值越小越好）
• 打开内存仪表盘定位热点：在 performance_schema 中启用内存统计（如 memory_summary_global_by_event_name），找出占用最高的事件与模块。
• 关注常见诱因：连接数突增、复杂 SQL（大量 JOIN/ORDER BY）、一次性执行大量语句（multiple statements）、以及 InnoDB Buffer Pool 命中率低导致的频繁磁盘读。

二 核心参数优化

• 控制 InnoDB 缓冲池大小：将 innodb_buffer_pool_size 设为物理内存的约 50%–70%（专用数据库可更高，需为系统和其他进程预留内存）；若实例内存很大（如 >16GB），可适当提高并配合 innodb_buffer_pool_instances=8/16 降低锁竞争。
• 限制会话级缓冲区：会话级参数（如 sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size、binlog_cache_size）默认较小且按连接分配，避免设得过大；并发高时尤需谨慎。
• 管理临时表内存：将 tmp_table_size 与 max_heap_table_size 设为一致且不过大；在 MySQL 8.0 还需关注 temptable_max_ram（默认 1GB），超出会转磁盘临时表。
• 连接数治理：合理设置 max_connections，避免空闲连接堆积；结合应用连接池的空闲超时与最大连接上限。
• 其他引擎与日志：若仍使用 MyISAM，将 key_buffer_size 设为 MyISAM 索引总量的合理比例（如约 1/4，视数据而定）；innodb_log_file_size 通常设为 256M 起步，过大不利于恢复，过小增加检查点压力。

三 查询与连接治理

• 避免 multiple statements 大包：一次性发送大量语句会导致执行过程中多类内存累积，建议拆分执行或改为批量小事务。
• 优化慢 SQL：用 EXPLAIN 检查是否出现全表扫描、临时表、文件排序等；对大 JOIN/聚合/排序场景，优先通过索引与 SQL 改写降低内存与 I/O。
• 控制连接与突发流量：当 QPS/连接数 上升时，优先释放空闲连接、限流异常 SQL，必要时扩容实例规格。

四 系统与内存分配器优化

• 为系统预留内存：给 操作系统和其他服务 留出足够 RAM，避免将 InnoDB 缓冲池设得过高导致换页与 OOM。
• 使用 jemalloc 降低碎片：glibc 在高并发分配/释放下可能出现内存碎片，安装 libjemalloc1 并在 systemd 中预加载（Environment=“LD_PRELOAD=/usr/lib/x86_64-linux-gnu/libjemalloc.so.1”），重启后通过 lsof 验证是否生效。
• 监控与告警：开启 performance_schema 内存监控，持续观察内存使用与热点；为实例设置 内存使用率告警阈值不低于 90%，超过阈值可先重启实例或主备切换进行快速止损。

五 安全调整与验证步骤

• 编辑配置文件：常见路径为 /etc/mysql/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf，在 [mysqld] 段落按需调整上述参数。
• 动态与持久化：部分参数支持 SET GLOBAL 动态生效，但为持久化请在配置文件写入并重启；变更前建议备份。
• 重启与验证：
  • 重启：sudo systemctl restart mysql
  • 验证：SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’; SHOW STATUS LIKE ‘Threads_connected’;
• 回归观测：观察 VmRSS、连接数、慢日志与 performance_schema 内存摘要，确认内存曲线趋于平稳。