mysql -监控数据以及配置参数-02

一、监控数据来源

1. 信息模式库（information_schema）

作用：提供数据库元数据访问，包含数据库、表、列等配置信息

(1) 查看配置

-- 查看所有全局变量配置
SHOW GLOBAL VARIABLES;

 

(2) 查看状态

-- 查看所有全局状态
SHOW GLOBAL STATUS;

-- 查看连接相关状态
SHOW GLOBAL STATUS LIKE 'Threads_%';

 

2. 性能模式库（performance_schema）

作用：提供服务器运行时性能数据，用于监控数据库活动

常用表：

• events_statements_summary_by_digest：SQL语句摘要统计

• events_waits_current：当前等待事件

• file_summary_by_event_name：文件I/O统计

-- 查看最高耗时的SQL类型
SELECT digest_text, count_star, avg_timer_wait/1000000000 as avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC LIMIT 10;

 

二、慢SQL监控详解

1. 慢查询配置参数

(1) 查看默认配置

SHOW VARIABLES LIKE '%query%';

　　

关键参数：

• slow_query_log：是否开启慢查询日志（默认OFF）

• long_query_time：慢查询阈值（秒，默认10）

• slow_query_log_file：慢查询日志文件路径

• log_queries_not_using_indexes：是否记录未使用索引的查询（默认OFF）

 

(2) 开启慢查询日志

临时开启（无需重启）：

SET GLOBAL slow_query_log = 'ON';  -- 开启日志
SET GLOBAL long_query_time = 0.1;  -- 设置阈值为100ms
SET GLOBAL slow_query_log_file = '/mysql/logs/slow.log';  -- 设置日志路径

 永久开启（需修改配置文件）：

[mysqld]
slow_query_log = 1
long_query_time = 0.1
slow_query_log_file = /mysql/logs/slow.log
log_queries_not_using_indexes = 1  # 可选：记录未使用索引的查询

[root@demo mysql]# pwd 
/mysql
[root@demo mysql]# vim my.cnf

　　

 

生效方式：需要重启MySQL服务

 

 

2. 慢查询日志分析

(1) 日志格式示例

# Time: 2023-10-25T06:12:33.123456Z
# User@Host: root[root] @ localhost []  Id:    15
# Query_time: 0.234567  Lock_time: 0.000123 Rows_sent: 100  Rows_examined: 10000
SET timestamp=1666663953;
SELECT * FROM large_table WHERE non_indexed_column = 'value';

(2) 日志分析工具

• mysqldumpslow：MySQL自带工具

  bash

  mysqldumpslow -s t /mysql/logs/slow.log  # 按总时间排序

• pt-query-digest（Percona Toolkit）

 

         bash 

 

     pt-query-digest /mysql/logs/slow.log

 

永久设置日志
slow_query_log_file=/mysql/logs/slow.log

查看慢sql日志 tail -f /mysql/logs/slow.log    每次跑压测前，清理这个log

(3) 压测前清理日志

> /mysql/logs/slow.log  # 清空日志文件
或
rm /mysql/logs/slow.log && touch /mysql/logs/slow.log

 

3. 配置验证

修改配置后检查是否生效：

SHOW VARIABLES LIKE 'slow_query_log';  -- 确认是否开启
SHOW VARIABLES LIKE 'long_query_time'; -- 确认阈

　　注意：long_query_time的新值需要新建立的会话才能生效，当前会话可能仍显示旧值

-------------------

三、监控可视化方案

1. 命令行工具

• mysqladmin：实时状态查看

  bash

  mysqladmin -uroot -p ext -i1  # 每秒刷新状态

• innotop：InnoDB监控

  bash

  innotop -u root -p

2. 可视化监控系统

(1) Prometheus + Grafana

• Exporter：使用mysqld_exporter采集数据

• 关键指标：

  • 查询吞吐量

  • 连接数

  • 缓冲池命中率

  • 锁等待

(2) Percona Monitoring and Management (PMM)

• 专为MySQL设计的开源监控平台

• 提供查询分析、性能仪表板

(3) MySQL Enterprise Monitor

• Oracle官方商业监控工具

• 提供高级诊断和顾问功能

四、监控指标详解

1. 关键性能指标（KPIs）

 

指标类别	关键指标	健康参考值
连接	Threads_connected	< max_connections的80%
	Threads_running	< CPU核心数的2倍
查询	Questions	根据业务基准
	Slow_queries	< 总查询量的1%
缓冲池	Innodb_buffer_pool_hit_ratio	> 95%
锁	Innodb_row_lock_waits	持续增长需关注
复制	Seconds_behind_master (主从)	< 60秒

2. 重要状态变量监控

sql

-- 计算缓冲池命中率
SELECT (1 - (SELECT variable_value FROM performance_schema.global_status 
WHERE variable_name = 'Innodb_buffer_pool_reads') / 
(SELECT variable_value FROM performance_schema.global_status 
WHERE variable_name = 'Innodb_buffer_pool_read_requests')) * 100 
AS buffer_pool_hit_ratio;

五、高级监控技巧

1. 性能模式深度使用

sql

-- 开启所有检测点（生产环境慎用）
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES';

-- 查看等待事件
SELECT event_name, count_star, sum_timer_wait/1000000000 as total_sec
FROM performance_schema.events_waits_summary_global_by_event_name
ORDER BY sum_timer_wait DESC LIMIT 10;

2. sys模式库（MySQL 5.7+）

sql

-- 查看最耗资源的SQL
SELECT * FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 10;

-- 查看表IO情况
SELECT * FROM sys.io_by_table_by_bytes LIMIT 10;

通过以上监控体系，可以全面掌握MySQL的运行状态，及时发现性能瓶颈，为优化提供数据支持。建议根据业务特点建立定期监控机制和告警规则。