MySQL日常监控及sys库的使用【转】

阅读量：4977 次

发布时间：2019-06-12

本文共 5809 字，大约阅读时间需要 19 分钟。

一、统计信息(SQL维度)

关于SQL维度的统计信息主要集中在events_statements_summary_by_digest表中，通过将SQL语句抽象出digest，可以统计某类SQL语句在各个维度的统计信息(比如：执行次数，排序次数，使用临时表等)

1、执行次数最多的SQL

SELECT  DIGEST_TEXT,  COUNT_STAR,  FIRST_SEEN,  LAST_SEEN FROM  `performance_schema`.events_statements_summary_by_digest ORDER BY  COUNT_STAR DESC;

可以看到执行次数最多的SQL是INSERT INTO `t_ocs_group_production_temporary` VALUES (...) ，FIRST_SEEN和LAST_SEEN分别显示了语句第一次执行和最后一次执行的时间点。

2、平均响应时间最多的sql

SELECT  DIGEST_TEXT,  AVG_TIMER_WAIT,  COUNT_STAR,  FIRST_SEEN,  LAST_SEEN FROM  `performance_schema`.events_statements_summary_by_digest ORDER BY  AVG_TIMER_WAIT DESC;

可以看到平均响应时间最长的sql是SELECT * FROM `lcp_mq_record` ，这个是一张日志表。

3、排序记录数最多的sql

SELECT  DIGEST_TEXT, SUM_SORT_ROWS,  COUNT_STAR,  FIRST_SEEN,  LAST_SEEN FROM  `performance_schema`.events_statements_summary_by_digest ORDER BY  SUM_SORT_ROWS DESC;

4、扫描记录数最多的sql

SELECT  DIGEST_TEXT,  SUM_ROWS_EXAMINED,  COUNT_STAR,  FIRST_SEEN,  LAST_SEEN FROM  `performance_schema`.events_statements_summary_by_digest ORDER BY  SUM_ROWS_EXAMINED DESC;

5、使用临时表最多的sql

SELECT  DIGEST_TEXT,  SUM_CREATED_TMP_TABLES,  SUM_CREATED_TMP_DISK_TABLES,  COUNT_STAR,  FIRST_SEEN,  LAST_SEEN FROM  `performance_schema`.events_statements_summary_by_digest ORDER BY  SUM_CREATED_TMP_TABLES desc,SUM_CREATED_TMP_DISK_TABLES desc

6、返回结果集最多的SQL

SELECT  DIGEST_TEXT,  SUM_ROWS_SENT,  COUNT_STAR,  FIRST_SEEN,  LAST_SEEN FROM  `performance_schema`.events_statements_summary_by_digest ORDER BY  SUM_ROWS_SENT desc;

通过上述指标我们可以间接获得某类SQL的逻辑IO(SUM_ROWS_EXAMINED)，CPU消耗(SUM_SORT_ROWS)，网络带宽(SUM_ROWS_SENT)的对比，但还无法得到某类SQL的物理IO消耗，以及某类SQL访问数据的buffer命中率。

二、统计信息(对象维度)

1、哪个表物理IO最多？

SELECT  file_name,  event_name,  SUM_NUMBER_OF_BYTES_READ,  SUM_NUMBER_OF_BYTES_WRITE FROM  `performance_schema`.file_summary_by_instance ORDER BY  SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC;

通过file_summary_by_instance表，可以获得系统运行到现在，哪个文件(表)物理IO最多，这可能意味着这个表经常需要访问磁盘IO，从结果来看perf_stat库里面的is_global_variables的数据文件访问最多。

2、哪个表逻辑IO最多?

SELECT  object_schema,  object_name,  COUNT_READ,  COUNT_WRITE,  COUNT_FETCH,  SUM_TIMER_WAIT FROM  `performance_schema`.table_io_waits_summary_by_table ORDER BY  sum_timer_wait DESC;

通过table_io_waits_summary_by_table表，可以获得系统运行到现在，哪个表逻辑IO最多，亦即最“热”的表，从结果来看fsl_prod库里面的t_ocs_employee_attendace表访问次数最多。

3、哪个索引访问最多？

SELECT  OBJECT_SCHEMA,  OBJECT_NAME,  INDEX_NAME,  COUNT_FETCH,  COUNT_INSERT,  COUNT_UPDATE,  COUNT_DELETE FROM  `performance_schema`.table_io_waits_summary_by_index_usage ORDER BY  SUM_TIMER_WAIT DESC;

通过table_io_waits_summary_by_index_usage表，可以获得系统运行到现在，哪个表的具体哪个索引(包括主键索引，二级索引)使用最多，从结果来看，我们知道t_ocs_employee_attendace表访问最多，并且都是通过t_ocs_production_lines_n1索引访问。

4、哪个索引从来没有使用过？

SELECT  OBJECT_SCHEMA,  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 OBJECT_SCHEMA <> 'mysql' ORDER BY  OBJECT_SCHEMA,  OBJECT_NAME;

通过table_io_waits_summary_by_index_usage表，我们还可以获得系统运行到现在，哪些索引从来没有被用过。由于索引也会占用大量的空间，我们可以利用这个统计信息，结合一定的时间策略将无用的索引删除。上面的结果显示，fsl_prod库act_hi_actinst表的ACT_IDX_HI_ACT_INST_END索引从来没有被使用过。

三、统计信息(等待事件维度)

1、哪个等待事件消耗的时间最多？

SELECT  EVENT_NAME,  COUNT_STAR,  SUM_TIMER_WAIT,  AVG_TIMER_WAIT FROM  `performance_schema`.events_waits_summary_global_by_event_name WHERE  event_name != 'idle' ORDER BY  SUM_TIMER_WAIT DESC;

通过events_waits_summary_global_by_event_name表，可以获取到系统运行到现在，消耗时间最多的事件，当然还可以根据其它维度排序，比如平均等待时间，从结果来看wait/io/table/sql/handler这个事件消耗的累计时间最长。

概述

MySQL5.7的新特性中，非常突出的特性之一就是sys库，不仅可以通过sys库完成MySQL信息的收集，还可以用来监控和排查问题。下面介绍一些常用的用法。

一、用户、连接类

1、查看每个客户端IP过来的连接消耗资源情况。

select * from sys.host_summary;

2、查看每个用户消耗资源情况

select * from sys.user_summary;

3、查看当前连接情况（有多少连接就应该有多少行）

select host,current_connections,statements from sys.host_summary;

4、查看当前正在执行的SQL

和执行show full processlist的结果差不多

select conn_id,pid,user,db,command,current_statement,last_statement,time,lock_latency from sys.session

二、SQL 和io类

1、查看发生IO请求前5名的文件。

select * from sys.io_global_by_file_by_bytes order by total limit 5;

三、buffer pool 、内存

1、查看总共分配了多少内存

select * from sys.memory_global_total; select * from sys.memory_global_by_current_bytes;

2、每个库（database）占用多少buffer pool

select * from sys.innodb_buffer_stats_by_schema order by allocated desc;

pages是指在buffer pool中的page数量；pages_old指在LUR 列表中处于后37%位置的page。

当出现buffer page不够用时，就会征用这些page所占的空间。37%是默认位置，具体可以自定义。

3、统计每张表具体在InnoDB中具体的情况，比如占多少页？

注意和前面的pages的总数都是相等的，也可以借用sum（pages）运算验证一下。

select * from sys.innodb_buffer_stats_by_table;

4、查询每个连接分配了多少内存

利用session表和memory_by_thread_by_current_bytes分配表进行关联查询。

SELECT  b.USER,  current_count_used,  current_allocated,  current_avg_alloc,  current_max_alloc,  total_allocated,  current_statement FROM  sys.memory_by_thread_by_current_bytes a,  sys.SESSION b WHERE  a.thread_id = b.thd_id;

四、字段、索引、锁

1、查看表自增字段最大值和当前值，有时候做数据增长的监控，可以作为参考。

select * from sys.schema_auto_increment_columns;

2、MySQL索引使用情况统计

select * from sys.schema_index_statistics order by rows_selected desc;

3、MySQL中有哪些冗余索引和无用索引

若库中展示没有冗余索引，则没有数据；当有联合索引idx_abc(a,b,c)和idx_a(a)，那么idx_a就算冗余索引了。

select * from sys.schema_redundant_indexes;

4、查看INNODB 锁信息

在未来的版本将被移除，可以采用其他方式

select * from sys.innodb_lock_waits

5、查看库级别的锁信息，这个需要先打开MDL锁的监控：

--打开MDL锁监控 update performance_schema.setup_instruments set enabled='YES',TIMED='YES' where name='wait/lock/metadata/sql/mdl'; select * from sys.schema_table_lock_waits;

五、线程类

1、MySQL内部有多个线程在运行，线程类型及数量

select user,count(*) from sys.`processlist` group by user;

六、主键自增

查看MySQL自增id的使用情况

SELECT  table_schema,  table_name,  ENGINE,  Auto_increment FROM  information_schema.TABLES WHERE  TABLE_SCHEMA NOT IN ( "INFORMATION_SCHEMA", "PERFORMANCE_SCHEMA", "MYSQL", "SYS" )