MYSQL performance_schema 监控系统更容易与慢查询DUMP SLOW LOG

最新推荐文章于 2024-09-18 14:05:15 发布

原创最新推荐文章于 2024-09-18 14:05:15 发布 · 672 阅读

CC 4.0 BY-SA版权

本文探讨了从MySQL 5.6开始引入的性能监控方式的变化，特别是performance_schema在监控和反映系统状况中的关键作用。文章详细介绍了如何启用和使用performance_schema中的表，如events_waits_summary_by系列，以及如何利用这些表来分析系统等待时间、慢查询和线程状态，从而优化数据库性能。

从MYSQL 5.6 开始MYSQL的监控方式已经有了改变，可能给人的印象是越来越和ORACLE 长得一样了，以后是不是也的来一个 AWR 那是不置可否。。那到底怎么近似了，又怎么监控变化了。如果是从MYSQL 5.5 及其以前用过MYSQL的同学来说，performance_schema是从陌生到熟悉的过程，从原来不不敢打开，到现在的MYSQL5.7 基本都打开的状态，performance_schema 越来对于监控和反应系统的状况重要了。

另外之前一直使用的慢查询，也慢慢的转移到了 performance_schema上，所以有的时候来说改变还是蛮大的。

首先如果使用MYSQL 5.7 percona版本的，performance_schema 基本上是打开的。但如何验证收集信息的模式是否打开。

你可以打开 performance_schema.setup_instruments 和 performance_schema.setup_consumers 这两个表看看所对应的监控项目的是处于打开还是关闭的状态。

 UPDATE performance_schema.setup_instruments
       SET ENABLED = 'YES', TIMED = 'YES';

 UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES';

在打开收集器后，下面就开始今天的正题，performance_schema 中的那些表到底有什么作用，怎么用

先从events_waits_summary_by 开始

| events_waits_summary_by_account_by_event_name

| events_waits_summary_by_host_by_event_name

| events_waits_summary_by_instance

| events_waits_summary_by_thread_by_event_name

| events_waits_summary_by_user_by_event_name

| events_waits_summary_global_by_event_name

上面的6张表 events_wait_summary_global_by_event_name

select * from events_waits_summary_global_by_event_name;

通过上面的语句可以查到所有的等待项目的开始时间，总的等待时间，平均等待时间和最大和最小的等待时间。

根据不同的项目可以看到等待的时间单位皮秒，相关的不少一体化的监控都是从这里边取数，通过这些数据来了解某个项目的运行情况。

例如通过相关的项目获知某些项目的性能情况

Table lock wait/lock/table/%

Network IO wait/io/socket/%

Table IO wait/io/table/%

File IO wait/io/file/%

Mutexes wait/synch/mutex/%

SQL Statements statement/sql/%

COM Commands statement/com/%

当然也可以这些信息中已经有一些表对系统进行了分类，通过分类来更快的对系统的一些状态有了解。

例如那些索引可能建立后从来没有使用

select object_name, index_name from performance_schema.table_io_waits_summary_by_index_usage where index_name is not null and count_star = 0 and index_name != 'primary';

也可以通过下面的语句来获知

select Thread_id,name,type,processlist_id,processlist_host,processlist_db,processlist_state,processlist_info from threads where processlist_state <> 'sleep';

当前的线程中工作状态

下面举一些例子：

慢查询的列子

SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history_long WHERE timer_wait > 100000000000;

我们要查找系统执行语句中慢过0.1秒的语句

基本上通过一条语句就可以查出来历史记录中是否有类似的记录。或者通过模糊查询查看系统中是否有某些语句

例如：你想知道最近运行的SQL中是否有 Duration 的字段的语句

通过上面的语句是可以很快的获得一些慢查询语句。并且不需要设置限定的值是多少，而是通过查询来查找你需要看到的慢查询语句。

但以上的方法也是有缺陷的如果你的系统比较繁忙执行的语句比较多，很可能你的系统中记录的语句会被后面的语句覆盖掉。这里可以采用建立一个表来定时承接系统的记录。

CREATE TABLE IF NOT EXISTS SQL_history

(schema_name varchar(64) DEFAULT NULL,

digest varchar(32) DEFAULT NULL,

sql_text varchar(1024) DEFAULT NULL,

PRIMARY KEY USING BTREE (schema_name,digest))；

INSERT IGNORE INTO SQL_history SELECT CURRENT_SCHEMA, DIGEST, SQL_TEXT FROM performance_schema.events_statements_history WHERE DIGEST IS NOT NULL GROUP BY current_schema, digest ;

通过这样的方式来记录系统中记录的语句，并且自己可以调节插入和轮训的速度，满足系统繁忙度的要求。

通过下面的语句可以进行一个历史方面的语句执行时间的记录展示统计每个语句的执行时间以及平均时间等

SELECT

s.SCHEMA_NAME,

s.SQL_TEXT,

ROUND(d.SUM_TIMER_WAIT / 1000000000000, 6) as EXECUTION_TIME,

ROUND(d.AVG_TIMER_WAIT / 1000000000000, 6) as AVERAGE_TIME,

COUNT_STAR

FROM performance_schema.events_statements_summary_by_digest d

LEFT JOIN SQL_history s USING (digest)

WHERE s.SCHEMA_NAME IS NOT NULL

GROUP BY s.digest