MySQL 性能巡檢 Shell 腳本解析：QPS、TPS、緩存命中率、連接數一鍵獲取

MySQL 性能巡檢 Shell 腳本解析：QPS、TPS、緩存命中率、連接數一鍵獲取

定期巡檢 MySQL 核心指標是 DBA 保障穩定性的基本功。本文解析一款實用的 Shell 巡檢腳本，涵蓋版本、進程數、客戶端連接數、QPS/TPS、Key Buffer/InnoDB Buffer/Query Cache 命中率等關鍵指標，並說明各指標計算邏輯、告警閾值與優化建議。

腳本核心功能模塊

關鍵計算邏輯詳解

# QPS 計算
qps_01="show global status like 'Questions';"
qps_02=$(mysql ... -e "$qps_01" | awk 'NR==2 {print $2}')
qps_03=$(cat /proc/uptime | awk '{print $1}')  # 系統運行秒數
qps_04=$(awk "BEGIN {print $qps_02 / $qps_03}")  # awk 支持浮點

# TPS 計算
tps_01="show global status like 'Com_commit';"
tps_02="show global status like 'Com_rollback';"
tps_sum=$((commit + rollback))
tps_avg=$(awk "BEGIN {print $tps_sum / $qps_03}")

# InnoDB Buffer 讀命中率
innob_01="show global status like 'Innodb_buffer_pool_reads';"
innob_02="show global status like 'Innodb_buffer_pool_read_requests';"
reads=$(mysql ... -e "$innob_01" | awk 'NR==2 {print $2}')
requests=$(mysql ... -e "$innob_02" | awk 'NR==2 {print $2}')
hit_rate=$(awk "BEGIN {print (1 - $reads/$requests) * 100}")

指標異常排查與優化建議

1. InnoDB Buffer 讀命中率 < 99%
   • 檢查 innodb_buffer_pool_size 是否足夠（建議物理內存 60%-75%）
   • 分析熱點表/索引是否全部載入 Buffer Pool
   • 考慮 innodb_buffer_pool_instances 增加並發
2. QPS/TPS 突變
   • 關聯 SHOW PROCESSLIST 找慢查詢
   • 檢查是否有大事務、鎖等待、全表掃描
   • 配合 sys.schema_table_statistics 定位高頻表
3. 連接數逼近上限
   • 檢查應用連接池配置（最大連接數、閒置超時）
   • 排查連接泄漏（未正確關閉）
   • 適當調大 max_connections，但需評估內存壓力
4. Key Buffer 命中率低（MyISAM 表）
   • 增大 key_buffer_size
   • 評估遷移至 InnoDB

生產級巡檢增強建議

• 輸出 JSON/Prometheus 格式，接入監控系統
• 增加慢查詢統計：SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10
• 增加複製延遲檢查：SHOW SLAVE STATUS\G 關注 Seconds_Behind_Master
• 增加磁盤空間、InnoDB 紅日志使用率、臨時表創建數等指標
• 異常時自動觸發 pt-stalk 或 mysql-slow-log-analyzer 采集現場

腳本部署最佳實踐

# 1. 放置路徑
/opt/mysql_monitor/check_mysql_health.sh

# 2. 權限
chmod +x check_mysql_health.sh

# 3. 專用監控用戶（最小權限）
CREATE USER 'monitor'@'%' IDENTIFIED BY 'xxx';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'monitor'@'%';

# 4. 定時任務（每 5 分鐘）
*/5 * * * * /opt/mysql_monitor/check_mysql_health.sh >> /var/log/mysql_health.log 2>&1

# 5. 告警集成（示例：企業微信/鉉釘 Webhook）
if (( $(echo "$hit_rate < 99" | bc -l) )); then
    curl -X POST 'webhook_url' -d '{"msg":"InnoDB Buffer 命中率異常: '$hit_rate'%"}'
fi

進階替代方案

• Prometheus + mysqld_exporter：標準化指標采集、Grafana 開箱即用儀表盤
• Percona Monitoring and Management (PMM)：全鏈路可觀測性、Query Analytics、Advisor 建議
• Datadog / New Relic / 阿里雲 CloudMonitor：SaaS 化、零維護