MYSQL 8 从PS说起,但不止于PS , 不在使用淘汰的慢查询日志,那我怎么查慢查询(6)

时间:2022-09-30 22:57:50

MYSQL 8 从PS说起,但不止于PS , 不在使用淘汰的慢查询日志,那我怎么查慢查询(6)

这是关于MYSQL8 获取信息的方式的第六篇,终于到达了慢日志查询的位置,在MYSQL的DBA 的管理员的心目中,pt-query-digest 和 SLOW QUERY LOG 是分析慢查询的唯一的方式。实际上在MYSQL 8 中这样的慢查询的数据获取方式,已经被淘汰了,或者说不合时宜了。

主要的原因是获取信息的时效性的问题,获取慢查询需要去通过PT工具来读取SLOW LOG文件,并输出超过慢查询时间的语句信息。


SELECT

SCHEMA_NAME,

digest,

digest_text,

round(sum_timer_wait/ 1000000000000, 6) as second,

count_star,

now() as check_time

FROM performance_schema.events_statements_summary_by_digest

where digest_text not like 'CREATE%' and digest_text not like 'ALTER%'

ORDER BY sum_timer_wait DESC LIMIT 10;


MYSQL 8 从PS说起,但不止于PS , 不在使用淘汰的慢查询日志,那我怎么查慢查询(6)


通过查询的方式将慢查询语句摘录出来,但实际上又出现新的问题,就是每次将数据都搜索出来后,而这就会产生两个问题


1  重复数据的问题




​https://blog.monyog.com/troubleshooting-mysql-performance-issues/​


​https://www.liquidweb.com/kb/mysql-performance-identifying-long-queries/​


​https://vladmihalcea.com/mysql-query-profiling-performance-schema/​

2  如何汇总数据的问题


SELECT

SCHEMA_NAME,

digest,

digest_text,

round(sum_timer_wait/ 1000000000000, 6) as second,

count_star,

now() as check_time

FROM performance_schema.events_statements_summary_by_digest

where digest_text not like 'CREATE%' and digest_text not like 'ALTER%' and schema_name <> 'information_schema' and schema_name <> 'sys'

ORDER BY sum_timer_wait DESC LIMIT 10;


MYSQL 8 从PS说起,但不止于PS , 不在使用淘汰的慢查询日志,那我怎么查慢查询(6)


我们通过 digest 的方式进行信息的汇总,因为类似结构的查询语句会产生,  同样的码,在插入时将插入的方式变更为判断并无数据插入数据,有数据更新数据的方式并将这个位置变更为唯一索引。


在我们的performance_schema中 show tables like 'events_statement%',可以看到通过 event_statement 里有很多的从不同的维度对MYSQL 中产生的语句进行分析表,其中主要分为 current,  histogram , history , summary 几大类,他们分别也承载了慢查询中不同的功能。




MYSQL 8 从PS说起,但不止于PS , 不在使用淘汰的慢查询日志,那我怎么查慢查询(6)

下面我们就看看MYSQL 怎么来满足查询的需求


1  实时的观测,MYSQL 系统中语句的查询情况,可以称之为实时语句查询监控

select current_schema,SQL_TEXT,timer_wait/1000000000000 as 等待时间,lock_time/1000000000000 as 锁的时间,errors,rows_sent,rows_examined,select_scan,sort_rows

from events_statements_current

where  SQL_TEXT IS NOT NULL;

MYSQL 8 从PS说起,但不止于PS , 不在使用淘汰的慢查询日志,那我怎么查慢查询(6)

在MYSQL 8 


2  当查询某些表没有数据如

select * from events_statements_history_long;

MYSQL 8 从PS说起,但不止于PS , 不在使用淘汰的慢查询日志,那我怎么查慢查询(6)


这样的情况很可能是是由于你在 setup_consumers 中并没有打开相关的enabled 数据造成的。那么我们需要打开相关表的设置。

update setup_consumers set enabled = 'YES' where name = 'events_statements_history_long' ;


MYSQL 8 从PS说起,但不止于PS , 不在使用淘汰的慢查询日志,那我怎么查慢查询(6)


打开后,相关的表就可以接收到数据了

select * from events_statements_history_long;


MYSQL 8 从PS说起,但不止于PS , 不在使用淘汰的慢查询日志,那我怎么查慢查询(6)


select event_name,timer_wait/1000000000000 as wait_second,sql_text,digest,errors,rows_affected,rows_sent,created_tmp_disk_tables,select_scan

from events_statements_history_long

where current_schema  in ('sys','performance_schema','information_schema') and event_name like 'statement/sql%';


MYSQL 8 从PS说起,但不止于PS , 不在使用淘汰的慢查询日志,那我怎么查慢查询(6)


除此以外,我们还可以通过events_statements_summary_by_digest

来展示目前MYSQL 系统中 TOP 10 最慢的语句有那些。



MYSQL 8 从PS说起,但不止于PS , 不在使用淘汰的慢查询日志,那我怎么查慢查询(6)

select digest_text,count_star,(AVG_TIMER_WAIT/1000000000000) as avg_wait_time_second,(MAX_TIMER_WAIT/1000000000000) as max_timer_wait,SUM_ROWS_SENT,SUM_SELECT_SCAN,SUM_NO_INDEX_USED,fIRST_SEEN,LAST_SEEN,QUERY_SAMPLE_TEXT

from events_statements_summary_by_digest

where (AVG_TIMER_WAIT/1000000000000) > 0.1 and digest_text not like 'CREATE%' and digest_text not like 'ALTER%'

ORDER BY LAST_SEEN limit 10;


以上的信息足以满足日常的慢查询分析的需求。除此以外,我们还可以衍生出更多与监控有关的信息集合


1   通过performance_schema  中查询的信息,来发现MYSQL 系统中未使用的索引信息。这样就可以查看索引的使用情况,来判断我们建立的索引在使用中是否存在根本就用不到的情况。


SELECT DISTINCT s.table_schema, s.table_name, s.index_name

--     , i.count_star

  FROM information_schema.statistics AS s

  LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage AS i

         ON (s.table_schema = i.object_schema AND s.table_name = i.object_name AND s.index_name = i.index_Name)

 WHERE s.table_schema NOT IN ('mysql', 'performance_schema', 'sys', 'information_schema')

   AND s.index_name != 'PRIMARY'

   AND i.count_star = 0

 ORDER BY s.table_schema, s.table_name, s.index_name;


MYSQL 8 从PS说起,但不止于PS , 不在使用淘汰的慢查询日志,那我怎么查慢查询(6)


2  查询MYSQL 查询中出现生成物理磁盘查询的情况


SELECT schema_name, digest_text AS statement, count_star AS cnt

     , sum_created_tmp_disk_tables AS tmp_disk_tables, sum_created_tmp_tables AS tmp_tables

  FROM performance_schema.events_statements_summary_by_digest

 WHERE sum_created_tmp_disk_tables >= 0 and schema_name not in ('sys','information_schema','performance_schema','mysql')  and schema_name is not null

    ;


MYSQL 8 从PS说起,但不止于PS , 不在使用淘汰的慢查询日志,那我怎么查慢查询(6)


    

上面由于要有展示,但此时系统中并未有符合语句所以将 sum_created_tmp_disk_tables  设置成 >= 0 实际上可以改成下面的写法。


SELECT schema_name, digest_text AS statement, count_star AS cnt

     , sum_created_tmp_disk_tables AS tmp_disk_tables, sum_created_tmp_tables AS tmp_tables

  FROM performance_schema.events_statements_summary_by_digest

 WHERE sum_created_tmp_disk_tables > 0 and schema_name not in ('sys','information_schema','performance_schema','mysql')  and schema_name is not null and 

 digest_text not like 'CREATE%' and  digest_text not like 'ALTER%' and digest_text not like 'explain%' and digest_text not like 'set%' 

    ;


通过以上的一些语句完全可以摆脱之前的SLOW LOG的工作模式,实际上其他的数据库本身也并没有SLOW LOG ,都是通过系统表的方式来获取慢查询语句,如 SQL SERVER ,ORACLE 等。


MYSQL 作为开源流行的数据库,从MYSQL 8 后,完全可以不在使用原有的方式去捕捉慢查询数据。至少不会为设置某些过滤语句的时间在去犯难,所有运行语句的时间都会被记录,我们所做的只是需要过滤这些语句即可。


最后下面的SQL 可以统计整体预计执行的信息并且以,从最慢的SQL 开始,包含各种语句的维度信息


SELECT left(digest_text, 64)

     , ROUND(SUM(timer_end-timer_start)/1000000000, 1) AS tot_exec_ms

     , ROUND(SUM(timer_end-timer_start)/1000000000/COUNT(*), 1) AS avg_exec_ms

     , ROUND(MIN(timer_end-timer_start)/1000000000, 1) AS min_exec_ms

     , ROUND(MAX(timer_end-timer_start)/1000000000, 1) AS max_exec_ms

     , ROUND(SUM(timer_wait)/1000000000, 1) AS tot_wait_ms

     , ROUND(SUM(timer_wait)/1000000000/COUNT(*), 1) AS avg_wait_ms

     , ROUND(MIN(timer_wait)/1000000000, 1) AS min_wait_ms

     , ROUND(MAX(timer_wait)/1000000000, 1) AS max_wait_ms

     , ROUND(SUM(lock_time)/1000000000, 1) AS tot_lock_ms

     , ROUND(SUM(lock_time)/1000000000/COUNT(*), 1) AS avglock_ms

     , ROUND(MIN(lock_time)/1000000000, 1) AS min_lock_ms

     , ROUND(MAX(lock_time)/1000000000, 1) AS max_lock_ms

     , MIN(LEFT(DATE_SUB(NOW(), INTERVAL (isgs.VARIABLE_VALUE - TIMER_START*10e-13) second), 19)) AS first_seen

     , MAX(LEFT(DATE_SUB(NOW(), INTERVAL (isgs.VARIABLE_VALUE - TIMER_START*10e-13) second), 19)) AS last_seen

     , COUNT(*) as cnt

  FROM performance_schema.events_statements_history_long

  JOIN performance_schema.global_status AS isgs

 WHERE isgs.variable_name = 'UPTIME'

 GROUP BY LEFT(digest_text,64)

 ORDER BY tot_exec_ms DESC


MYSQL 8 从PS说起,但不止于PS , 不在使用淘汰的慢查询日志,那我怎么查慢查询(6)

MYSQL 8 从PS说起,但不止于PS , 不在使用淘汰的慢查询日志,那我怎么查慢查询(6)