Linux中MySQL如何进行性能调优实战

Linux 上 MySQL 性能调优实战

一 基线测量与压测

• 建立可复现的基线，明确优化目标（如降低P95/P99 延迟、提升QPS与缓冲池命中率）。
• 快速采集关键指标脚本（示例）：
  • QPS 计算：QPS = (Questions_t2 − Questions_t1) / Δt
  • 连接与运行线程：Threads_connected、Threads_running
  • InnoDB 命中率：1 − Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests
• 压测工具组合
  • sysbench 准备与混合读写：
    • 准备：sysbench oltp_read_write prepare …
    • 运行：sysbench oltp_read_write --tables=10 --table-size=1000000 --threads=16 --time=300 run
  • mysqlslap 并发模拟：mysqlslap --concurrency=100 --iterations=1000 …
• 慢查询分析
  • mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
  • pt-query-digest /var/log/mysql/slow.log > analysis.txt
• 建议将压测结果与监控指标留存，形成“调参前后”对比曲线，便于评估收益与回滚。

二 Linux 系统层优化

• 内核与网络
  • net.core.somaxconn、net.core.netdev_max_backlog、net.ipv4.tcp_max_syn_backlog 适度增大
  • 开启 tcp_tw_reuse，关闭 tcp_tw_recycle（避免 NAT/负载均衡环境异常）
  • tcp_fin_timeout 适度降低（如 10）
• 内存与虚拟内存
  • vm.swappiness=1（尽量避免 swap）
  • vm.dirty_ratio=15、vm.dirty_background_ratio=5（控制脏页刷盘）
• 文件句柄与进程数
  • fs.file-max 提升（如 1000000）
• 磁盘与文件系统
  • 优先使用 SSD；I/O 调度器：SSD 用 deadline，HDD 用 cfq
  • 挂载参数：ext4 可用 noatime,data=writeback,barrier=0,nobh；XFS 推荐 noatime,attr2,inode64,noquota
• 原则：先确保硬件与 OS 不成为瓶颈，再进入数据库参数调优。

三 MySQL 配置优化要点

• 内存与连接
  • innodb_buffer_pool_size：专用库建议物理内存的50%~70%（独占服务器可到70%~80%）；InnoDB 为主时 key_buffer_size 可小（如 64M）
  • max_connections：结合应用连接池与内存预算设置，避免过高导致内存压力
  • thread_cache_size：建议为 max_connections 的**~10%**或更高（如 100）
• InnoDB 关键参数
  • innodb_buffer_pool_instances：建议与 CPU 核心数匹配（如 8）
  • innodb_log_file_size：1~4G（写密集可适当增大，减少 checkpoint 频率；调整需停库并移走旧日志）
  • innodb_log_buffer_size：如 64M
  • innodb_flush_log_at_trx_commit：1（最安全）/ 2（更高性能，最多丢失 1 秒）/ 0（风险更高）
  • innodb_flush_method：O_DIRECT（避免双重缓冲）
  • innodb_io_capacity / innodb_io_capacity_max：SSD 可 4000~20000，HDD 200~400
  • innodb_read_io_threads / innodb_write_io_threads：如 8/8
  • innodb_thread_concurrency：0（由线程池/系统自适应）
• 查询与临时表
  • tmp_table_size / max_heap_table_size：如 64M~256M（避免磁盘临时表）
  • sort_buffer_size / join_buffer_size：避免过大（默认通常足够）
• 表与元数据缓存
  • table_open_cache、table_definition_cache：如 2048 或更高
• 慢查询与日志
  • slow_query_log=1，long_query_time=1~2 秒；log_queries_not_using_indexes=1（临时排障用）
• 复制与二进制日志
  • log-bin、binlog_format=ROW、max_binlog_size=1G、expire_logs_days=7
  • 从库：slave_parallel_workers、slave_parallel_type=‘LOGICAL_CLOCK’
• 查询缓存
  • MySQL 8.0 已移除 Query Cache；5.7 及更早版本在高并发写场景建议关闭（query_cache_type=0）
• 动态调参与验证
  • 运行时示例：SET GLOBAL innodb_buffer_pool_size=810241024*1024;
  • 每次只改少量参数，观察指标与错误日志后再继续。

四 SQL 与索引优化

• 索引设计
  • 复合索引遵循选择性递减与最左前缀原则
  • 优先使用覆盖索引减少回表
  • 用 EXPLAIN 检查访问类型（system > const > eq_ref > ref > range > index > ALL）、扫描行数、是否出现 Using filesort / Using temporary
• 查询写法
  • 避免 SELECT *，只取必要列
  • 用 JOIN 替代低效子查询；GROUP BY/ORDER BY 配合索引
  • 分页优化（避免大偏移：基于游标的分页/延迟关联）
• 执行计划与诊断
  • EXPLAIN FORMAT=JSON 深入分析
  • 慢查询日志配合 pt-query-digest 找出 Top SQL，优先优化占比最高者。

五 监控 告警 与常见故障处理

• 监控与告警
  • Prometheus + Grafana：MySQL Exporter（如 9104/metrics），关键面板：连接数使用率、缓冲池命中率、慢查询增长率
  • 关键指标公式
    • 连接数使用率：Threads_connected / max_connections
    • 缓冲池命中率：1 − Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests
    • 慢查询增长率：rate(slow_queries[5m])
  • 健康检查脚本：检测服务存活、连接数阈值、复制状态并推送告警
• 常见故障与处置
  • 连接数耗尽（ERROR 1040）
    • 诊断：SHOW PROCESSLIST; SHOW STATUS LIKE ‘Threads_connected’; SHOW VARIABLES LIKE ‘max_connections’;
    • 处置：临时调大 max_connections；优化应用连接池与超时；定位长事务/僵尸连接
  • InnoDB 锁等待超时（ERROR 1205）
    • 诊断：information_schema.innodb_lock_waits 关联 innodb_trx 查阻塞链
    • 处置：KILL 阻塞线程；优化事务范围与隔离级别；减少热点行争用
  • 主从复制延迟
    • 监控：Seconds_Behind_Master
    • 处置：提升从库并行度（slave_parallel_workers/type）、优化大事务、提升 I/O 能力
• 风险提示
  • 生产变更务必先备份 my.cnf 与数据，在灰度/维护窗口执行，变更后持续观察错误日志与监控指标，必要时回滚。