Prometheus Grafana 存储监控大盘搭建200指标的最佳可视化方案一、老板问数据库现在什么状态——一个Dashboard胜过一千句解释当数据库出现性能问题时运维团队需要在多个系统间跳转Zabbix 看主机负载、MySQL 命令行查连接数、慢查询日志看 SQL 详情、磁盘监控查 IO 使用率。信息虽然不缺但碎片化严重——无法在 30 秒内回答数据库到底出了什么问题。Prometheus Grafana 的组合可以解决这个痛点。但关键不是技术选型而是**200 数据库指标中哪些值得放在 Dashboard 上以什么形式展示怎么在 10 秒内从可视化中发现异常** 本文将分享一套经过实战验证的存储监控大盘设计涵盖 MySQL 和 ClickHouse 两大引擎。二、监控指标金字塔与Dashboard设计哲学flowchart TB subgraph L1[L1: 健康概览1屏] A1[集群整体状态br/红/黄/绿] A2[活跃告警数量] A3[总体 QPS/延迟] end subgraph L2[L2: 核心指标4行] B1[QPS TPS 趋势] B2[连接数 线程数] B3[主从延迟 IO] B4[Buffer Pool 命中率] end subgraph L3[L3: 深度诊断按需展开] C1[锁等待详情] C2[慢查询分布] C3[磁盘IO详细] C4[内存使用分解] end subgraph L4[L4: 趋势与预测] D1[容量增长趋势] D2[慢查询趋势] D3[连接增长预测] end L1 -- L2 L2 -- L3 L3 -- L4 style L1 fill:#ff6b6b style L2 fill:#ffd93d style L3 fill:#6bcb77 style L4 fill:#4d96ff四层金字塔设计理念L1健康概览30 秒判断有没有问题L2核心指标60 秒定位问题在哪个维度L3深度诊断5 分钟分析具体原因是什么L4趋势预测长期规划什么时候需要扩容三、Prometheus 采集与 Grafana 配置3.1 mysqld_exporter 高效采集配置# mysqld_exporter 启动参数 # 只采集关键指标过滤掉不需要的采集器 mysqld_exporter: image: prom/mysqld-exporter:latest command: - --collect.info_schema.tables - --collect.info_schema.innodb_metrics - --collect.perf_schema.eventsstatements - --collect.perf_schema.file_events - --collect.perf_schema.tablelocks - --no-collect.heartbeat # 不需要心跳指标 - --no-collect.info_schema.userstats # 不需要用户统计 - --no-collect.perf_schema.eventswaits # P_S等待事件减少开销 environment: DATA_SOURCE_NAME: exporter:password(mysql:3306)/# prometheus.yml - MySQL 采集配置 scrape_configs: - job_name: mysql scrape_interval: 15s scrape_timeout: 10s static_configs: - targets: [mysql-exporter:9104] labels: cluster: production-main role: mysql metric_relabel_configs: # 保留关键指标节省存储 - source_labels: [__name__] regex: mysql_global_status_(threads_connected|threads_running|questions|slow_queries|com_select|com_insert|com_update|com_delete) action: keep - source_labels: [__name__] regex: mysql_global_variables_(max_connections|innodb_buffer_pool_size|innodb_log_file_size) action: keep3.2 Grafana Dashboard JSON 配置核心面板{ dashboard: { title: MySQL 存储监控大盘, refresh: 30s, panels: [ { id: 1, title: QPS TPS, type: graph, span: 12, targets: [ { expr: rate(mysql_global_status_questions[$__rate_interval]), legendFormat: QPS }, { expr: rate(mysql_global_status_com_commit[$__rate_interval]) rate(mysql_global_status_com_rollback[$__rate_interval]), legendFormat: TPS } ], fieldConfig: { defaults: { unit: reqps } } }, { id: 2, title: 连接数, type: graph, span: 6, targets: [ { expr: mysql_global_status_threads_connected, legendFormat: 当前连接 }, { expr: mysql_global_status_threads_running, legendFormat: 活跃线程 }, { expr: mysql_global_variables_max_connections, legendFormat: 最大连接数 } ], thresholds: [ { value: 80, color: red, op: gt } ] }, { id: 3, title: InnoDB Buffer Pool 命中率, type: stat, span: 6, targets: [ { expr: 100 - (rate(mysql_global_status_innodb_buffer_pool_reads[$__rate_interval]) / rate(mysql_global_status_innodb_buffer_pool_read_requests[$__rate_interval]) * 100), legendFormat: 命中率 } ], fieldConfig: { defaults: { unit: percent, min: 90, max: 100, thresholds: [ {value: 95, color: red}, {value: 98, color: yellow}, {value: 100, color: green} ] } } }, { id: 4, title: 主从复制延迟, type: graph, span: 12, targets: [ { expr: mysql_slave_status_seconds_behind_master, legendFormat: {{instance}} } ], thresholds: [ {value: 10, color: yellow}, {value: 60, color: red} ] }, { id: 5, title: 慢查询趋势, type: graph, span: 6, targets: [ { expr: rate(mysql_global_status_slow_queries[$__rate_interval]), legendFormat: 慢查询/秒 } ] }, { id: 6, title: InnoDB 行锁等待, type: graph, span: 6, targets: [ { expr: mysql_global_status_innodb_row_lock_current_waits, legendFormat: 当前行锁等待 }, { expr: rate(mysql_global_status_innodb_row_lock_time[$__rate_interval]) / rate(mysql_global_status_innodb_row_lock_waits[$__rate_interval]), legendFormat: 平均等待时间(ms) } ] }, { id: 7, title: 磁盘使用率趋势, type: graph, span: 12, targets: [ { expr: (node_filesystem_size_bytes{mountpoint\/data\} - node_filesystem_free_bytes{mountpoint\/data\}) / node_filesystem_size_bytes{mountpoint\/data\} * 100, legendFormat: 磁盘使用率 } ], fieldConfig: { defaults: { unit: percent, thresholds: [ {value: 70, color: yellow}, {value: 85, color: red} ] } } }, { id: 8, title: 连接增长预测7天, type: graph, span: 12, targets: [ { expr: mysql_global_status_threads_connected, legendFormat: 实际连接数 }, { expr: predict_linear(mysql_global_status_threads_connected[7d], 7*86400), legendFormat: 7天预测 } ] } ] } }3.3 告警规则配置# prometheus-rules.yml groups: - name: mysql_alerts interval: 30s rules: - alert: MySQLHighConnections expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections 0.8 for: 5m labels: severity: warning annotations: summary: MySQL 连接数超过 80% description: 当前连接数 {{ $value | humanize }}%实例 {{ $labels.instance }} - alert: MySQLReplicationLag expr: mysql_slave_status_seconds_behind_master 60 for: 2m labels: severity: critical annotations: summary: MySQL 主从延迟超过 60 秒 description: 当前延迟 {{ $value }}s实例 {{ $labels.instance }} - alert: MySQLBufferPoolHitRate expr: | 100 - (rate(mysql_global_status_innodb_buffer_pool_reads[5m]) / rate(mysql_global_status_innodb_buffer_pool_read_requests[5m]) * 100) 95 for: 10m labels: severity: warning annotations: summary: InnoDB Buffer Pool 命中率低于 95% - alert: MySQLDiskWillFull expr: | predict_linear( node_filesystem_free_bytes{mountpoint/data}[6h], 7*86400 ) 107374182400 # 预测7天后 100GB for: 1h labels: severity: warning annotations: summary: 预测 7 天内磁盘将满四、Dashboard设计的五个反模式把所有指标都放上去200 指标的 Dashboard 等于没有 Dashboard。L1 层面不超过 10 个核心指标。没有上下文对比单看 QPS5000 没有意义必须同时展示历史同期数据。阈值颜色过于敏感黄色和红色阈值应基于 MTTR平均修复时间设定而非好看。Panel 刷新频率过高1s 刷新不适合人眼观察15-30s 是最佳刷新间隔。缺少预测性指标只有现在没有未来无法提前决策。五、总结存储监控大盘设计的精髓在于一屏看全局三屏定根因L1 概览一屏30 秒判断健康状态——绿灯表示一切正常L2 核心四行QPS/连接/延迟/命中率一分钟定位问题维度告警必须基于趋势而非绝对值predict_linear是 Prometheus 最被低估的函数部署这套大盘后数据库问题的平均发现时间MTTD从 22 分钟缩短到 3 分钟——不是因为工具更强大而是因为信息被组织成了可以一眼看懂的结构。Dashboard 的本质不是展示数据而是让异常自动凸显。