TOP SQL 统计
可以按照执行时间,阻塞时间,返回行数等等维度统计top sql。
另外可以按照时间筛选last_seen,可以统计最近某一段时间出现过的top sql
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;SELECTschema_name,
digest_text,
count_star,
avg_timer_wait/1000000000000 ASavg_timer_wait,
max_timer_wait/1000000000000 ASmax_timer_wait,
sum_lock_time/count_star/1000000000000 ASavg_lock_time ,
sum_rows_affected/count_star ASavg_rows_affected,
sum_rows_sent/count_star ASavg_rows_sent ,
sum_rows_examined/count_star ASavg_rows_examined,
sum_created_tmp_disk_tables/count_star ASavg_create_tmp_disk_tables,
sum_created_tmp_tables/count_star ASavg_create_tmp_tables,
sum_select_full_join/count_star ASavg_select_full_join,
sum_select_full_range_join/count_star ASavg_select_full_range_join,
sum_select_range/count_star ASavg_select_range,
sum_select_range_check/count_star ASavg_select_range,
first_seen,
last_seenFROMperformance_schema.events_statements_summary_by_digestWHERE last_seen>date_add(NOW(), interval -1HOUR)ORDER BYmax_timer_wait--avg_timer_wait--sum_rows_affected/count_star--sum_lock_time/count_star--avg_lock_time--avg_rows_sent
DESClimit10;
需要注意的是,这个统计是按照MySQL执行一个事务消耗的资源做统计的,而不是一个语句,笔者一开始懵逼了一阵子,举个简单的例子。
参考如下,这里是循环写个数据的一个存储过程,调用方式就是call create_test_data(N),写入N条测试数据。
比如call create_test_data(1000000)就是写入100W的测试数据,这个执行过程耗费了几分钟的时间,按照笔者的测试实例情况,avg_timer_wait的维度,绝对是一个TOP SQL。
但是在查询的时候,始终没有发现这个存储过程的调用被列为TOP SQL,后面尝试在存储过程内部加了一个事物,然后就顺利地收集到了整个TOP SQL.
因此说performance_schema.events_statements_summary_by_digest里面的统计,是基于事务的,而不是某一个批处理的执行时间的。
CREATE DEFINER=`root`@`%` PROCEDURE`create_test_data`(IN `loopcnt` INT)
LANGUAGE SQLNOTDETERMINISTICCONTAINSSQL
SQL SECURITY DEFINER
COMMENT''
BEGIN-- STARTTRANSACTION;while loopcnt>0doinsert into test_mrr(rand_id,create_date) values (RAND()*100000000,now(6));set loopcnt=loopcnt-1;end while;
--commit;END
另外一点比较有意思的是,这个系统表是为数不多的支持truncate的,当然它在内部,也是在不断收集的一个过程。
执行失败的SQL 统计
一直以为系统不会记录执行失败的\解析错误的SQL,比如想统计因为超时而执行失败的语句,后面才发现,这些信息,MySQL会完整地记录下来
这里会详细记录执行错误的语句,包括最终执行失败(超时之类的),语法错误,执行过程中产生了警告之类的语句。用sum_errors>0 or sum_warnings>0去performance_schema.events_statements_summary_by_digest筛选一下即可。
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;selectschema_name,
digest_text,
count_star,
first_seen,
last_seenfromperformance_schema.events_statements_summary_by_digestwhere sum_errors>0 or sum_warnings>0
order by last_seen desc;
Index使用情况统计
基于performance_schema.table_io_waits_summary_by_index_usage这个系统表,其统计的维度同样是“按照某个索引查询返回的行数的统计”。
可以按照哪些索引使用最多\最少等情况进行统计。
不过这个统计有一个给人潜在一个误区:
count_read,count_write,count_fetch,count_insert,count_update,count_delete统计了某个索引上使用到索引的情况下,受影响的行数,sum_timer_wait是累计在该索引上等待的时间。
如果使用到了该索引,但是没有数据受影响(就是没有DML语句的条件没有命中数据),将count_***不会统计进来,但是sum_timer_wait会统计进来
这就存在一个容易受到误导的地方,这个索引明明没有命中过很多次,但是却产生了大量的timer_wait,索引看到类似的信息,也不能贸然删除索引。
等待事件统计
MySQL数据库中的任何一个动作,都需要等待(一定的时间来完成),一共有超过1000个等待事件,分属不懂的类别,每个版本都不一样,且默认不是所有的等待事件都启用。
个人认为等待事件这个东西,仅做参考,不具备问题的诊断性,即便是再优化或者低负载的数据库,累计一段时间,某些事件仍旧会积累大量的等待事件。
这些事件的等待事件,不一定都是负面性的,比如事物的锁等待,是在并发执行过程中必然会生成的,这个等待事件的统计结果,也是累计的,单纯的看一个直接的值,不具备任何参考意义。
除非定期收集,做差值计算,根据实际情况,才具备参考意义。
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;SELECT SUBSTRING_INDEX(NAME, '/', 1) as wait_type,COUNT(1)FROMperformance_schema.setup_instrumentsGROUP BY 1
ORDER BY 2 DESC;SELECTevent_name,
count_star,
sum_timer_waitFROMperformance_schema.events_waits_summary_global_by_event_nameWHERE event_name != 'idle'
order by sum_timer_wait desclimit100;
最后,需要注意的是,
1,MySQL提供的诸多的系统表(视图)中的数据,单纯的看这个值本身,因为它是一个累计值,个人觉得意义不大,尤其是avg_***,需要结合多方面的综合因素,做参考使用。
2,任何系统表的查询,都可能对系统性能的本身造成一定的影响,不要再对系统可能产生较大负面影响的情况下做数据的统计收集。
参考:
耐克的广告,竟然是这么的煽情
你能从一片空白里,看到可能吗?
有些人要看到证据,等有人做到了才敢出手。
但那些第一个行动的人,他们等过吗?
他们直接出手,不管有没有人做到过。
你能从一片空白里,看到可能吗?
不等别人,出手即证明。