3.1 MySQL性能监控体系搭建：从零开始构建完整的监控平台

3.1 MySQL性能监控体系搭建：从零开始构建完整的监控平台

📚 学习目标

通过本节学习，你将掌握：

• ✅ 监控体系的三层架构（系统级、数据库级、应用级）
• ✅ Prometheus + Grafana监控平台的搭建方法
• ✅ MySQL关键性能指标的监控和告警
• ✅ 监控数据的采集、存储和可视化
• ✅ 生产环境监控体系的最佳实践

🎯 学习收获

学完本节后，你将能够：

1. 平台搭建：从零开始搭建完整的监控平台
2. 指标监控：监控MySQL的关键性能指标
3. 告警配置：建立完善的告警体系
4. 问题诊断：通过监控数据快速定位问题

💡 实际场景引入

场景一：系统突然变慢但不知道原因

问题描述：某系统在业务高峰期突然变慢，但不知道是CPU、内存、磁盘还是数据库的问题。没有监控系统，只能盲目排查，花费了大量时间。

你的任务：如何快速搭建监控系统，快速定位问题？

场景二：需要建立完善的监控体系

问题描述：某公司需要为MySQL数据库建立完善的监控体系，包括性能监控、告警、报表等，但不知道从何开始。

你的任务：如何设计并实施完整的监控方案？

在现代数据库运维中，建立一个全面的性能监控体系是确保系统稳定运行和快速故障排查的关键。没有有效的监控，我们就像在黑暗中驾驶，无法预知前方的障碍。本节将详细介绍如何从零开始构建一个完整的MySQL性能监控平台，涵盖系统级、数据库级和应用级的监控指标。

监控体系的重要性

为什么需要监控？

监控的三个层次

1. 系统级监控：CPU、内存、磁盘、网络等基础设施指标
2. 数据库级监控：连接数、查询性能、缓存命中率等数据库内部指标
3. 应用级监控：业务指标、用户响应时间、错误率等应用层面指标

系统级监控指标

基础资源监控

# CPU使用率监控top-bn1|grep"Cpu(s)"|awk'{print $2}'|sed's/%us,//'# 内存使用情况free-m|awk'NR==2{printf "Memory Usage: %s/%sMB (%.2f%%)\n", $3,$2,$3*100/$2 }'# 磁盘IO监控iostat -x11|grep"nvme0n1"|awk'{print "Disk Utilization: " $10 "%"}'# 网络流量监控cat/proc/net/dev|grepeth0|awk'{print "RX: " $2/1024/1024 "MB, TX: " $10/1024/1024 "MB"}'

MySQL进程监控

# MySQL进程状态检查psaux|grepmysqld|grep-vgrep# MySQL端口监听检查netstat-tlnp|grep:3306# MySQL连接数监控mysql -e"SHOW STATUS LIKE 'Threads_connected';"|grepThreads_connected|awk'{print $2}'

数据库级监控指标

Performance Schema监控

-- 启用Performance SchemaUPDATEperformance_schema.setup_consumersSETENABLED='YES'WHERENAMELIKE'%events_statements%';-- 监控最耗时的SQL语句SELECTDIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT/1000000000ASAVG_TIME_SEC,MAX_TIMER_WAIT/1000000000ASMAX_TIME_SECFROMperformance_schema.events_statements_summary_by_digestORDERBYAVG_TIMER_WAITDESCLIMIT10;-- 监控表I/O等待事件SELECTOBJECT_NAME,COUNT_STAR,AVG_TIMER_WAIT/1000000000ASAVG_TIME_SECFROMperformance_schema.table_io_waits_summary_by_tableORDERBYAVG_TIMER_WAITDESCLIMIT10;

关键性能指标监控

-- 创建监控视图CREATEVIEWmysql_performance_metricsASSELECT-- 连接相关指标(SELECTVARIABLE_VALUEFROMinformation_schema.GLOBAL_STATUSWHEREVARIABLE_NAME='Threads_connected')ASthreads_connected,(SELECTVARIABLE_VALUEFROMinformation_schema.GLOBAL_STATUSWHEREVARIABLE_NAME='Threads_running')ASthreads_running,(SELECTVARIABLE_VALUEFROMinformation_schema.GLOBAL_STATUSWHEREVARIABLE_NAME='Connections')AStotal_connections,-- 查询相关指标(SELECTVARIABLE_VALUEFROMinformation_schema.GLOBAL_STATUSWHEREVARIABLE_NAME='Queries')AStotal_queries,(SELECTVARIABLE_VALUEFROMinformation_schema.GLOBAL_STATUSWHEREVARIABLE_NAME='Slow_queries')ASslow_queries,-- 缓存相关指标(SELECTVARIABLE_VALUEFROMinformation_schema.GLOBAL_STATUSWHEREVARIABLE_NAME='Innodb_buffer_pool_read_requests')ASbuffer_pool_read_requests,(SELECTVARIABLE_VALUEFROMinformation_schema.GLOBAL_STATUSWHEREVARIABLE_NAME='Innodb_buffer_pool_reads')ASbuffer_pool_reads,-- 复制相关指标(SELECTVARIABLE_VALUEFROMinformation_schema.GLOBAL_STATUSWHEREVARIABLE_NAME='Seconds_Behind_Master')ASseconds_behind_master;-- 定期查询监控指标SELECTthreads_connected,threads_running,total_connections,total_queries,slow_queries,ROUND((buffer_pool_reads/buffer_pool_read_requests*100),2)ASbuffer_pool_hit_ratio,seconds_behind_masterFROMmysql_performance_metrics;

InnoDB存储引擎监控

-- InnoDB关键指标监控SELECTNAME,COUNT,SUM_NUMBER_OF_BYTES_USED/1024/1024ASMB_USEDFROMinformation_schema.INNODB_METRICSWHERENAMEIN('buffer_pool_pages_total','buffer_pool_pages_free','buffer_pool_pages_data','buffer_pool_pages_dirty')ANDSTATUS='enabled';-- 事务监控SELECTtrx_id,trx_state,trx_started,trx_mysql_thread_id,trx_queryFROMinformation_schema.INNODB_TRXORDERBYtrx_started;

应用级监控指标

业务指标监控

-- 订单处理监控SELECTDATE(created_at)asorder_date,COUNT(*)astotal_orders,SUM(amount)astotal_amount,AVG(amount)asavg_amount,MAX(created_at)aslast_order_timeFROMordersWHEREcreated_at>=DATE_SUB(NOW(),INTERVAL1HOUR)GROUPBYDATE(created_at)ORDERBYorder_dateDESC;-- 用户活跃度监控SELECTCOUNT(DISTINCTuser_id)asdau,COUNT(*)astotal_actions,AVG(session_duration)asavg_session_durationFROMuser_sessionsWHEREsession_start>=DATE_SUB(NOW(),INTERVAL1HOUR);

错误和异常监控

-- 错误日志监控SHOWENGINEINNODBSTATUS\G-- 慢查询日志分析SELECTDIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT/1000000000ASavg_exec_time_sec,FIRST_SEEN,LAST_SEENFROMperformance_schema.events_statements_summary_by_digestWHEREAVG_TIMER_WAIT/1000000000>5-- 平均执行时间超过5秒ORDERBYLAST_SEENDESCLIMIT10;

监控平台搭建

使用Prometheus + Grafana

Prometheus配置

# prometheus.ymlglobal:scrape_interval:15sevaluation_interval:15sscrape_configs:-job_name:'mysql'static_configs:-targets:['localhost:9104']metrics_path:/metricsscrape_interval:15s# MySQL Exporter配置# my.cnf中添加监控用户CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'password'; GRANT PROCESS,REPLICATION CLIENT,SELECT ON*.*TO 'exporter'@'localhost'; FLUSH PRIVILEGES;

Grafana仪表板配置

{"dashboard":{"id":null,"title":"MySQL Performance Dashboard","tags":["mysql","performance"],"timezone":"browser","schemaVersion":16,"version":0,"panels":[{"type":"graph","title":"MySQL Connections","targets":[{"expr":"mysql_global_status_threads_connected","legendFormat":"Connected"},{"expr":"mysql_global_status_threads_running","legendFormat":"Running"}]},{"type":"graph","title":"Query Performance","targets":[{"expr":"rate(mysql_global_status_queries[5m])","legendFormat":"Queries/sec"},{"expr":"rate(mysql_global_status_slow_queries[5m])",