Linux MariaDB性能调优实战

Linux MariaDB性能调优实战

一 基线评估与监控

• 明确目标：以业务关键路径的P95/P99 延迟、QPS/TPS、错误率为度量，先建立可复现的基准。
• 系统层监控：用 systemctl status mariadb 查看服务状态；用 mysqladmin -u root -p status 获取 Uptime、Threads、Questions；用 SHOW STATUS 与 SHOW PROCESSLIST 观察连接与运行状态；配合 top/htop、iostat、ss 观察 CPU、I/O、网络与连接数。
• 慢查询定位：开启 slow_query_log，设置 long_query_time（如 1 秒），用 EXPLAIN 分析执行计划，优先优化高成本语句与缺失索引场景。
• 可视化与告警：部署 Prometheus + Grafana 或 Zabbix，对连接数、缓冲池命中率、慢查询数、磁盘 IOPS/延迟等建立面板与阈值告警。

二 硬件与系统层优化

• 存储优先：使用 SSD/NVMe，并尽量将 数据目录（datadir）与日志目录（如 innodb_log_group_home_dir） 分别放在不同磁盘，降低写放大与 I/O 争用。
• 内存与 CPU：为数据库预留充足内存，优先保障 InnoDB 缓冲池；多核 CPU 有助于并发处理与后台任务并行。
• 文件系统与挂载：优先 XFS/ext4/Btrfs；使用 noatime 挂载减少元数据写入；合理设置 I/O 调度器（SSD 常用 none/mq-deadline）。
• 内核与资源：适度降低 vm.swappiness（如 10）、调整 vm.vfs_cache_pressure（如 50）；提升 文件描述符限制（ulimit -n 65535） 与内核网络/端口范围，避免连接瓶颈。

三 配置参数优化

• InnoDB 缓冲池：专用数据库服务器将 innodb_buffer_pool_size 设为物理内存的 50%–75%（常见取 70%），减少磁盘读。
• 日志与提交策略：适度增大 innodb_log_file_size（如 256M） 提升批量写入吞吐；权衡一致性与性能时，将 innodb_flush_log_at_trx_commit=2（每秒落盘，宕机可能丢失最近 1 秒事务），强一致场景保持 1。
• 并发与连接：根据内存与负载设置 max_connections（避免过大导致上下文切换与内存压力）；提高 thread_cache_size 降低线程创建开销，命中率可用公式：100 - (Threads_created/Connections)*100%。
• 临时表与内存：将 tmp_table_size 与 max_heap_table_size 设为相同值（如 256M），减少磁盘临时表。
• 查询缓存：在 MariaDB 10.1+ 已移除查询缓存，如需缓存采用 Redis/Memcached 或应用层缓存。
• 名称解析：在 [mysqld] 下设置 skip-name-resolve，避免 DNS 反向解析带来的连接延迟。
• 数据包与超时：适当增大 max_allowed_packet（如 64M–128M）；缩短 wait_timeout（如 60 秒）回收空闲连接，防止连接风暴。

四 索引与 SQL 优化

• 索引策略：为高频过滤/排序/关联字段建立合适索引；长字符串使用前缀索引；多条件组合使用联合索引并遵循最左前缀；避免过度索引以降低写入与维护成本。
• 语句与访问路径：用 EXPLAIN 检查是否走索引、是否出现 filesort/temporary；避免 *SELECT ，只取必要列；分页深翻使用游标/键集分页替代大 OFFSET；必要时用 JOIN 替代复杂子查询。
• 维护与重建：数据大幅变更后评估 OPTIMIZE TABLE 或重组索引，减少碎片；定期清理无用索引与冗余数据。

五 维护与持续优化

• 变更流程：任何配置调整先在测试环境验证，变更后观察 错误日志、状态变量与监控指标，必要时回滚。
• 日志与空间：定期归档/清理 慢查询日志、二进制日志；监控 磁盘使用率 与 InnoDB 表空间。
• 自动化巡检：使用 MySQLTuner 定期输出优化建议，并结合业务特点人工决策。
• 连接治理：设置合理 max_connections 与 wait_timeout，应用侧使用连接池与重试/熔断，避免连接泄漏与雪崩。