Debian MariaDB资源占用高的定位与优化
一 快速判断与定位
- 先看系统层资源：用 top/htop 观察 mysqld 的 RES、CPU 占用；用 vmstat 1 观察 si/so（换入换出）、用 iostat -x 1 观察 await、svctm、util（I/O 压力）。持续高 si/so 多为内存不足，高 await/svctm 多为磁盘瓶颈。
- 再看 MariaDB 层内存构成：MariaDB 内存分为 Engine 层与 Server 层。Engine 层以 InnoDB Buffer Pool 为主，属于常驻内存；Server 层包含 Thread Cache、Binlog Cache、Sort Buffer、Read Buffer、Join Buffer 等，多为非驻留，随连接关闭释放。实例内存使用率在 80% 左右常见，低于 90% 通常无需过度关注。
- 关键指标与 SQL：
- 缓冲池命中率 ≈ 1 − Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests；低于 95% 说明缓冲池偏小或存在大量物理读。
- 连接与线程：Threads_connected、Threads_running、Max_used_connections；Threads_running 长期接近 max_connections 说明并发过高。
- 慢查询：开启 slow_query_log，long_query_time 设为 1–2 秒，用 pt-query-digest 分析。
- InnoDB 状态：SHOW ENGINE INNODB STATUS\G 查看缓冲池、锁、I/O 等；SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_read%’; 监控缓冲池命中与读写情况。
二 内存占用高的优化
- 调整 InnoDB 缓冲池：将 innodb_buffer_pool_size 设为物理内存的 50%–70%（专用数据库可上探至 70%–80%），并按每 1GB 缓冲池配置 1 个 innodb_buffer_pool_instances（如 8GB → 8 个实例），减少锁争用并提升并发。
- 控制连接与线程开销：合理设置 max_connections（避免过大），适度提高 thread_cache_size 复用线程；缩短 wait_timeout / interactive_timeout（如 300–600 秒）回收空闲连接；必要时用应用侧连接池限制峰值连接。
- 降低 Server 层瞬时内存峰值：减少 per-connection 的 sort_buffer_size、join_buffer_size、read_buffer_size 等（仅在确有排序/连接/扫描瓶颈的会话按需设置），避免“上千连接 × 大 buffer”导致内存激增。
- 日志与持久化策略（权衡性能与安全）：非严格强一致场景可将 innodb_flush_log_at_trx_commit=2、sync_binlog=100–1000，显著降低刷盘频率；严格 ACID 场景保持默认 1/1。
- 版本特性提示：MariaDB 10.1+ 已移除查询缓存（query_cache_* 参数无效），无需为缓存占用担忧。
三 CPU 或 I/O 高的优化
- 抓慢查询并优化执行计划：启用慢查询日志，long_query_time=1–2 秒；用 EXPLAIN/optimizer trace 检查是否缺失索引、是否全表扫描、是否 filesort/临时表过多；为高频条件建立合适索引，避免 SELECT *。
- 减少锁争用：缩短事务范围、避免长事务；热点行更新考虑拆分或队列化；合理选择隔离级别。
- 提升 I/O 能力：使用 SSD、合理分区/分表、归档历史数据；检查 InnoDB 日志与临时表空间是否频繁扩展。
- 连接治理：应用侧使用连接池（如 HikariCP、ProxySQL），避免频繁建连/断连与连接风暴。
四 Debian 上的配置与维护步骤
- 配置文件路径：编辑 /etc/mysql/mariadb.conf.d/50-server.cnf（或 /etc/mysql/my.cnf），修改后执行 systemctl restart mariadb 生效。
- 建议的基础模板（按内存与负载调整）：
[mysqld]
innodb_buffer_pool_size = 4G # 物理内存的50%–70%，专用库可更高
innodb_buffer_pool_instances = 4 # 每1GB缓冲池配1个实例
innodb_log_file_size = 256M # 视写入强度适当增大
innodb_flush_log_at_trx_commit = 2 # 非强一致场景可放宽
sync_binlog = 1000 # 非强一致场景可放宽
max_connections = 200–500 # 结合应用与内存评估
thread_cache_size = 16–32
table_open_cache = 2000–4000
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
仅在确有需要的会话按需设置：sort_buffer_size / join_buffer_size / read_buffer_size
- 维护与清理：定期用 pt-query-digest 分析慢日志；必要时执行 OPTIMIZE TABLE / ANALYZE TABLE；配置 logrotate 轮转慢查询与错误日志，避免磁盘被撑满。
五 何时扩容与进一步排查
- 何时扩容：若实例规格偏小或业务持续增长，优先扩容内存/实例规格；在云上，RDS 提供内存使用率与性能指标视图，持续高位且影响业务时应升级规格。
- 进一步排查：若内存曲线长期平稳且低于 90% 通常无需处理；若发现疑似内存泄漏或异常增长，结合监控与错误日志，必要时在维护窗口重启实例，并抓取 SHOW ENGINE INNODB STATUS、SHOW GLOBAL STATUS、SHOW PROCESSLIST 等现场信息进一步分析。
