前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MYSQL 8 从PS说起,但不止于PS , 不在使用淘汰的慢查询日志,那我怎么查慢查询(6)

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

作者头像
AustinDatabases
发布2022-04-05 14:17:16
1.3K0
发布2022-04-05 14:17:16
举报
文章被收录于专栏:AustinDatabasesAustinDatabases

这是关于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;

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

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;

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

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

下面我们就看看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

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

select * from events_statements_history_long;

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

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

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

select * from events_statements_history_long;

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%';

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

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

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;

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

;

上面由于要有展示,但此时系统中并未有符合语句所以将 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

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2022-03-04,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 AustinDatabases 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档