概述
某DBA大师曾说过，一个DBA要像了解自己的老婆一样了解自己管理的数据库，个人认为包含了两个方面的了解：
- 1、在稳定性层面来说，更多的是关注高可用、读写分离、负载均衡，灾备管理等等high level层面的措施（就好比要保证生活的稳定性）
- 2、在实例级别的来说，需要关注内存、IO、网络，热点表，热点索引，top sql，死锁，阻塞，历史上执行异常的SQL（好比生活品质细节）
一、MySQL数据库相关配置
my.cnf参考如下配置：
performance_schema_max_digest_length用于控制performance_schema在做sql统计分析时，可以保留的sql语句的最大长度，官方文档上此值范围在0-1048576之间。
#监控
performance_schema=on
performance-schema-instrument='wait/io/file/%=on'
performance-schema-instrument='wait/io/table/%=on'
performance-schema-instrument='statement/com/%=on'
performance-schema-instrument='statement/sql/%=on'
performance-schema-instrument='stage/sql/%=on'
performance-schema-instrument='memory/%=on'
# 注：只有current=on，performance_schema才会检查是否需要填充到history和long history中，后两者没有关系，均依赖于current，因此current必须无条件启用
performance-schema-consumer-events_stages_current=on
# performance-schema-consumer-events_stages_history=on
# performance_schema_events_stages_history_size=30
performance-schema-consumer-events_stages_history_long=on
performance_schema_events_statements_history_long_size=10000
performance-schema-consumer-events_statements_current=on
# performance-schema-consumer-events_statements_history=on
# performance_schema_events_statements_history_size=30
performance-schema-consumer-events_statements_history_long=on
performance_schema_events_statements_history_long_size=10000
performance-schema-consumer-events_waits_current=on
# performance-schema-consumer-events_waits_history=on
# performance_schema_events_statements_history_size=30
performance-schema-consumer-events_waits_history_long=on
performance_schema_events_waits_history_long_size=10000
performance-schema-consumer-statements_digest=on
#sql语句长度
max_digest_length=1048500
performance_schema_max_digest_length=1048500二、MySQL performance sql计算方式（累计值）
1、Top 10 Event Summary
通过events_waits_summary_global_by_event_name表，可以获取到系统运行到现在，消耗时间最多的事件，当然还可以根据其它维度排序，比如平均等待时间，从结果来看wait/io/table/sql/handler这个事件消耗的累计时间最长。
SELECT
ews.EVENT_NAME "等待事件",
ews.COUNT_STAR "等待次数",
round( ews.SUM_TIMER_WAIT /1000000 ) "总时长(ms)",
round( ews.AVG_TIMER_WAIT / 1000000 ) "平均等待时间(ms)",
round(ews.MIN_TIMER_WAIT/1000000) MIN_TIMER_WAIT,
round(ews.MAX_TIMER_WAIT/1000000) MAX_TIMER_WAIT
FROM
PERFORMANCE_SCHEMA.events_waits_summary_global_by_event_name ews
WHERE
ews.count_star > 0
AND ews.event_name != 'idle'
ORDER BY
sum_timer_wait DESC;
2、Top 10 SQL
关于SQL维度的统计信息主要集中在events_statements_summary_by_digest表中，通过将SQL语句抽象出digest，可以统计某类SQL语句在各个维度的统计信息(比如：执行次数，排序次数，使用临时表等)
需要注意的是，这个统计是按照MySQL执行一个事务消耗的资源做统计的，而不是一个语句。这个系统表是为数不多的支持truncate的，当然它在内部，也是在不断收集的一个过程。
select ews.SCHEMA_NAME "数据库",
ews.DIGEST "sql_id",
ews.DIGEST_TEXT "sql语句",
ews.COUNT_STAR "执行次数",
round(ews.SUM_TIMER_WAIT/1000000) SUM_TIMER_WAIT,
round(ews.MIN_TIMER_WAIT/1000000) MIN_TIMER_WAIT,
round(ews.AVG_TIMER_WAIT/1000000) AVG_TIMER_WAIT,
round(ews.MAX_TIMER_WAIT/1000000) MAX_TIMER_WAIT,
round(ews.SUM_LOCK_TIME/1000000) SUM_LOCK_TIME,
ews.SUM_ERRORS,
ews.SUM_WARNINGS,
ews.SUM_ROWS_AFFECTED,
ews.SUM_ROWS_SENT,
ews.SUM_ROWS_EXAMINED,
ews.SUM_CREATED_TMP_DISK_TABLES,
ews.SUM_CREATED_TMP_TABLES,
ews.SUM_SELECT_FULL_JOIN,
ews.SUM_SELECT_FULL_RANGE_JOIN,
ews.SUM_SELECT_RANGE,
ews.SUM_SELECT_RANGE_CHECK,
ews.SUM_SELECT_SCAN,
ews.SUM_SORT_MERGE_PASSES,
ews.SUM_SORT_RANGE,
ews.SUM_SORT_ROWS,
ews.SUM_SORT_SCAN,
ews.SUM_NO_INDEX_USED,
ews.SUM_NO_GOOD_INDEX_USED,
ews.FIRST_SEEN,
ews.LAST_SEEN
from performance_schema.events_statements_summary_by_digest ews
where ews.SCHEMA_NAME not in ('perf_stat','mysql','sys','performance_schema','information_schema')
and ews.count_star > 0
and ews.digest_text not like 'SHOW%'
and ews.digest_text not like 'USE%'
and ews.digest_text not like 'SET%'
and ews.digest_text not like 'SELECT @%'
and ews.digest_text not like 'EXPLAIN%'
and ews.digest_text not like 'DROP PROCEDURE%'
and ews.digest_text not like 'CREATE PROCEDURE%'
order by SUM_TIMER_WAIT desc;
--AND last_seen >= CURRENT_TIMESTAMP - INTERVAL 15 MINUTEFIRST_SEEN和LAST_SEEN分别显示了语句第一次执行和最后一次执行的时间点，代码中连续查询两次结果得出差值即以下结果
3、Top 10 Table By LOGICAL IO Wait
select
ews.OBJECT_TYPE,
ews.OBJECT_SCHEMA,
ews.OBJECT_NAME,
ews.COUNT_STAR,
round(ews.SUM_TIMER_WAIT/1000000) SUM_TIMER_WAIT,
round(ews.MIN_TIMER_WAIT/1000000) MIN_TIMER_WAIT,
round(ews.AVG_TIMER_WAIT/1000000) AVG_TIMER_WAIT,
round(ews.MAX_TIMER_WAIT/1000000) MAX_TIMER_WAIT,
ews.COUNT_READ,
round(ews.SUM_TIMER_READ/1000000) SUM_TIMER_READ,
round(ews.MIN_TIMER_READ/1000000) MIN_TIMER_READ,
round(ews.AVG_TIMER_READ/1000000) AVG_TIMER_READ,
round(ews.MAX_TIMER_READ/1000000) MAX_TIMER_READ,
ews.COUNT_WRITE,
round(ews.SUM_TIMER_WRITE/1000000) SUM_TIMER_WRITE,
round(ews.MIN_TIMER_WRITE/1000000) MIN_TIMER_WRITE,
round(ews.AVG_TIMER_WRITE/1000000) AVG_TIMER_WRITE,
round(ews.MAX_TIMER_WRITE/1000000) MAX_TIMER_WRITE,
ews.COUNT_FETCH,
round(ews.SUM_TIMER_FETCH/1000000) SUM_TIMER_FETCH,
round(ews.MIN_TIMER_FETCH/1000000) MIN_TIMER_FETCH,
round(ews.AVG_TIMER_FETCH/1000000) AVG_TIMER_FETCH,
round(ews.MAX_TIMER_FETCH/1000000) MAX_TIMER_FETCH,
ews.COUNT_INSERT,
round(ews.SUM_TIMER_INSERT/1000000) SUM_TIMER_INSERT,
round(ews.MIN_TIMER_INSERT/1000000) MIN_TIMER_INSERT,
round(ews.AVG_TIMER_INSERT/1000000) AVG_TIMER_INSERT,
round(ews.MAX_TIMER_INSERT/1000000) MAX_TIMER_INSERT,
ews.COUNT_UPDATE,
round(ews.SUM_TIMER_UPDATE/1000000) SUM_TIMER_UPDATE,
round(ews.MIN_TIMER_UPDATE/1000000) MIN_TIMER_UPDATE,
round(ews.AVG_TIMER_UPDATE/1000000) AVG_TIMER_UPDATE,
round(ews.MAX_TIMER_UPDATE/1000000) MAX_TIMER_UPDATE,
ews.COUNT_DELETE,
round(ews.SUM_TIMER_DELETE/1000000) SUM_TIMER_DELETE,
round(ews.MIN_TIMER_DELETE/1000000) MIN_TIMER_DELETE,
round(ews.AVG_TIMER_DELETE/1000000) AVG_TIMER_DELETE,
round(ews.MAX_TIMER_DELETE/1000000) MAX_TIMER_DELETE
from performance_schema.table_io_waits_summary_by_table ews
where ews.OBJECT_SCHEMA not in ('perf_stat','mysql','sys','performance_schema','information_schema')
and ews.count_star > 04、Top 10 Index By LOGICAL IO Wait
通过table_io_waits_summary_by_index_usage表，可以获得系统运行到现在，哪个表的具体哪个索引(包括主键索引，二级索引)使用最多。
select
ews.OBJECT_TYPE,
ews.OBJECT_SCHEMA,
ews.OBJECT_NAME,
ews.INDEX_NAME,
ews.COUNT_STAR,
round(ews.SUM_TIMER_WAIT/1000000) SUM_TIMER_WAIT,
round(ews.MIN_TIMER_WAIT/1000000) MIN_TIMER_WAIT,
round(ews.AVG_TIMER_WAIT/1000000) AVG_TIMER_WAIT,
round(ews.MAX_TIMER_WAIT/1000000) MAX_TIMER_WAIT,
ews.COUNT_READ,
round(ews.SUM_TIMER_READ/1000000) SUM_TIMER_READ,
round(ews.MIN_TIMER_READ/1000000) MIN_TIMER_READ,
round(ews.AVG_TIMER_READ/1000000) AVG_TIMER_READ,
round(ews.MAX_TIMER_READ/1000000) MAX_TIMER_READ,
ews.COUNT_WRITE,
round(ews.SUM_TIMER_WRITE/1000000) SUM_TIMER_WRITE,
round(ews.MIN_TIMER_WRITE/1000000) MIN_TIMER_WRITE,
round(ews.AVG_TIMER_WRITE/1000000) AVG_TIMER_WRITE,
round(ews.MAX_TIMER_WRITE/1000000) MAX_TIMER_WRITE,
ews.COUNT_FETCH,
round(ews.SUM_TIMER_FETCH/1000000) SUM_TIMER_FETCH,
round(ews.MIN_TIMER_FETCH/1000000) MIN_TIMER_FETCH,
round(ews.AVG_TIMER_FETCH/1000000) AVG_TIMER_FETCH,
round(ews.MAX_TIMER_FETCH/1000000) MAX_TIMER_FETCH,
ews.COUNT_INSERT,
round(ews.SUM_TIMER_INSERT/1000000) SUM_TIMER_INSERT,
round(ews.MIN_TIMER_INSERT/1000000) MIN_TIMER_INSERT,
round(ews.AVG_TIMER_INSERT/1000000) AVG_TIMER_INSERT,
round(ews.MAX_TIMER_INSERT/1000000) MAX_TIMER_INSERT,
ews.COUNT_UPDATE,
round(ews.SUM_TIMER_UPDATE/1000000) SUM_TIMER_UPDATE,
round(ews.MIN_TIMER_UPDATE/1000000) MIN_TIMER_UPDATE,
round(ews.AVG_TIMER_UPDATE/1000000) AVG_TIMER_UPDATE,
round(ews.MAX_TIMER_UPDATE/1000000) MAX_TIMER_UPDATE,
ews.COUNT_DELETE,
round(ews.SUM_TIMER_DELETE/1000000) SUM_TIMER_DELETE,
round(ews.MIN_TIMER_DELETE/1000000) MIN_TIMER_DELETE,
round(ews.AVG_TIMER_DELETE/1000000) AVG_TIMER_DELETE,
round(ews.MAX_TIMER_DELETE/1000000) MAX_TIMER_DELETE
from performance_schema.table_io_waits_summary_by_index_usage ews
where ews.OBJECT_SCHEMA not in ('perf_stat','mysql','sys','performance_schema','information_schema')
and ews.count_star > 05、Top 10 Table By LOCKS WAITS
select
ews.OBJECT_TYPE,
ews.OBJECT_SCHEMA,
ews.OBJECT_NAME,
ews.COUNT_STAR,
round(ews.SUM_TIMER_WAIT/1000000) SUM_TIMER_WAIT,
round(ews.MIN_TIMER_WAIT/1000000) MIN_TIMER_WAIT,
round(ews.AVG_TIMER_WAIT/1000000) AVG_TIMER_WAIT,
round(ews.MAX_TIMER_WAIT/1000000) MAX_TIMER_WAIT,
ews.COUNT_READ,
round(ews.SUM_TIMER_READ/1000000) SUM_TIMER_READ,
round(ews.MIN_TIMER_READ/1000000) MIN_TIMER_READ,
round(ews.AVG_TIMER_READ/1000000) AVG_TIMER_READ,
round(ews.MAX_TIMER_READ/1000000) MAX_TIMER_READ,
ews.COUNT_WRITE,
round(ews.SUM_TIMER_WRITE/1000000) SUM_TIMER_WRITE,
round(ews.MIN_TIMER_WRITE/1000000) MIN_TIMER_WRITE,
round(ews.AVG_TIMER_WRITE/1000000) AVG_TIMER_WRITE,
round(ews.MAX_TIMER_WRITE/1000000) MAX_TIMER_WRITE,
ews.COUNT_READ_NORMAL,
round(ews.SUM_TIMER_READ_NORMAL/1000000) SUM_TIMER_READ_NORMAL,
round(ews.MIN_TIMER_READ_NORMAL/1000000) MIN_TIMER_READ_NORMAL,
round(ews.AVG_TIMER_READ_NORMAL/1000000) AVG_TIMER_READ_NORMAL,
round(ews.MAX_TIMER_READ_NORMAL/1000000) MAX_TIMER_READ_NORMAL,
ews.COUNT_READ_WITH_SHARED_LOCKS,
round(ews.SUM_TIMER_READ_WITH_SHARED_LOCKS/1000000) SUM_TIMER_READ_WITH_SHARED_LOCKS,
round(ews.MIN_TIMER_READ_WITH_SHARED_LOCKS/1000000) MIN_TIMER_READ_WITH_SHARED_LOCKS,
round(ews.AVG_TIMER_READ_WITH_SHARED_LOCKS/1000000) AVG_TIMER_READ_WITH_SHARED_LOCKS,
round(ews.MAX_TIMER_READ_WITH_SHARED_LOCKS/1000000) MAX_TIMER_READ_WITH_SHARED_LOCKS,
ews.COUNT_READ_NO_INSERT,
round(ews.SUM_TIMER_READ_NO_INSERT/1000000) SUM_TIMER_READ_NO_INSERT,
round(ews.MIN_TIMER_READ_NO_INSERT/1000000) MIN_TIMER_READ_NO_INSERT,
round(ews.AVG_TIMER_READ_NO_INSERT/1000000) AVG_TIMER_READ_NO_INSERT,
round(ews.MAX_TIMER_READ_NO_INSERT/1000000) MAX_TIMER_READ_NO_INSERT,
ews.COUNT_READ_EXTERNAL,
round(ews.SUM_TIMER_READ_EXTERNAL/1000000) SUM_TIMER_READ_EXTERNAL,
round(ews.MIN_TIMER_READ_EXTERNAL/1000000) MIN_TIMER_READ_EXTERNAL,
round(ews.AVG_TIMER_READ_EXTERNAL/1000000) AVG_TIMER_READ_EXTERNAL,
round(ews.MAX_TIMER_READ_EXTERNAL/1000000) MAX_TIMER_READ_EXTERNAL,
ews.COUNT_WRITE_ALLOW_WRITE,
round(ews.SUM_TIMER_WRITE_ALLOW_WRITE/1000000) SUM_TIMER_WRITE_ALLOW_WRITE,
round(ews.MIN_TIMER_WRITE_ALLOW_WRITE/1000000) MIN_TIMER_WRITE_ALLOW_WRITE,
round(ews.AVG_TIMER_WRITE_ALLOW_WRITE/1000000) AVG_TIMER_WRITE_ALLOW_WRITE,
round(ews.MAX_TIMER_WRITE_ALLOW_WRITE/1000000) MAX_TIMER_WRITE_ALLOW_WRITE,
ews.COUNT_WRITE_CONCURRENT_INSERT,
round(ews.SUM_TIMER_WRITE_CONCURRENT_INSERT/1000000) SUM_TIMER_WRITE_CONCURRENT_INSERT,
round(ews.MIN_TIMER_WRITE_CONCURRENT_INSERT/1000000) MIN_TIMER_WRITE_CONCURRENT_INSERT,
round(ews.AVG_TIMER_WRITE_CONCURRENT_INSERT/1000000) AVG_TIMER_WRITE_CONCURRENT_INSERT,
round(ews.MAX_TIMER_WRITE_CONCURRENT_INSERT/1000000) MAX_TIMER_WRITE_CONCURRENT_INSERT,
ews.COUNT_WRITE_NORMAL,
round(ews.SUM_TIMER_WRITE_NORMAL/1000000) SUM_TIMER_WRITE_NORMAL,
round(ews.MIN_TIMER_WRITE_NORMAL/1000000) MIN_TIMER_WRITE_NORMAL,
round(ews.AVG_TIMER_WRITE_NORMAL/1000000) AVG_TIMER_WRITE_NORMAL,
round(ews.MAX_TIMER_WRITE_NORMAL/1000000) MAX_TIMER_WRITE_NORMAL,
ews.COUNT_WRITE_EXTERNAL,
round(ews.SUM_TIMER_WRITE_EXTERNAL/1000000) SUM_TIMER_WRITE_EXTERNAL,
round(ews.MIN_TIMER_WRITE_EXTERNAL/1000000) MIN_TIMER_WRITE_EXTERNAL,
round(ews.AVG_TIMER_WRITE_EXTERNAL/1000000) AVG_TIMER_WRITE_EXTERNAL,
round(ews.MAX_TIMER_WRITE_EXTERNAL/1000000) MAX_TIMER_WRITE_EXTERNAL
from performance_schema.table_lock_waits_summary_by_table ews
where ews.OBJECT_SCHEMA not in ('perf_stat','mysql','sys','performance_schema','information_schema')
and ews.count_star > 06、Top 10 Table By PHYSICAL IO Wait
通过file_summary_by_instance表，可以获得系统运行到现在，哪个文件(表)物理IO最多，这可能意味着这个表经常需要访问磁盘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;
7、哪个索引从来没有使用过？
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索引从来没有被使用过。
三、技术实现
以上的sql计算方式均为累计值，如果需计算某段时间的故障差值是很难算的，考虑用函数定期取值存进中间表定期将累计值的结果存入A表，按sql_id分组查A表，筛选最后执行时间，然后直接最大值减最小值就可以实现需求了。
1、创建中间表
-- 报错记录error_log
CREATE TABLE sys.error_log (
name varchar(400) NOT NULL COMMENT '错误信息',
row int(11) DEFAULT NULL COMMENT '行数',
del_time timestamp NULL DEFAULT NULL COMMENT '删除时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报错记录表';
-- 中间表ews_event(取PERFORMANCE_SCHEMA.events_waits_summary_global_by_event_name:按照账户、主机、用户或线程统计的等待事件统计表)
CREATE TABLE sys.ews_event(
EVENT_NAME varchar(128) NOT NULL,
COUNT_STAR bigint(20) unsigned NOT NULL,
SUM_TIMER_WAIT bigint(20) unsigned NOT NULL,
MIN_TIMER_WAIT bigint(20) unsigned NOT NULL,
AVG_TIMER_WAIT bigint(20) unsigned NOT NULL,
MAX_TIMER_WAIT bigint(20) unsigned NOT NULL,
insert_date timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='按照账户、主机、用户或线程统计的等待事件统计表';
-- 中间表ews_sql(取performance_schema.events_statements_summary_by_digest:按照事件的语句进行聚合,抓取每条标准化语句有关的延迟、错误和查询量信息)
CREATE TABLE sys.ews_sql (
SCHEMA_NAME varchar(64) DEFAULT NULL,
DIGEST varchar(32) DEFAULT NULL,
DIGEST_TEXT longtext,
COUNT_STAR bigint(20) unsigned NOT NULL,
SUM_TIMER_WAIT bigint(20) unsigned NOT NULL,
MIN_TIMER_WAIT bigint(20) unsigned NOT NULL,
AVG_TIMER_WAIT bigint(20) unsigned NOT NULL,
MAX_TIMER_WAIT bigint(20) unsigned NOT NULL,
SUM_LOCK_TIME bigint(20) unsigned NOT NULL,
SUM_ERRORS bigint(20) unsigned NOT NULL,
SUM_WARNINGS bigint(20) unsigned NOT NULL,
SUM_ROWS_AFFECTED bigint(20) unsigned NOT NULL,
SUM_ROWS_SENT bigint(20) unsigned NOT NULL,
SUM_ROWS_EXAMINED bigint(20) unsigned NOT NULL,
SUM_CREATED_TMP_DISK_TABLES bigint(20) unsigned NOT NULL,
SUM_CREATED_TMP_TABLES bigint(20) unsigned NOT NULL,
SUM_SELECT_FULL_JOIN bigint(20) unsigned NOT NULL,
SUM_SELECT_FULL_RANGE_JOIN bigint(20) unsigned NOT NULL,
SUM_SELECT_RANGE bigint(20) unsigned NOT NULL,
SUM_SELECT_RANGE_CHECK bigint(20) unsigned NOT NULL,
SUM_SELECT_SCAN bigint(20) unsigned NOT NULL,
SUM_SORT_MERGE_PASSES bigint(20) unsigned NOT NULL,
SUM_SORT_RANGE bigint(20) unsigned NOT NULL,
SUM_SORT_ROWS bigint(20) unsigned NOT NULL,
SUM_SORT_SCAN bigint(20) unsigned NOT NULL,
SUM_NO_INDEX_USED bigint(20) unsigned NOT NULL,
SUM_NO_GOOD_INDEX_USED bigint(20) unsigned NOT NULL,
FIRST_SEEN timestamp NOT NULL DEFAULT NOW(),
LAST_SEEN timestamp NOT NULL DEFAULT NOW()
) ENGINE=innodb DEFAULT CHARSET=utf8 COMMENT='按照事件的语句进行聚合,抓取每条标准化语句有关的延迟、错误和查询量信息';
-- 中间表ews_table_iowaits(取performance_schema.table_io_waits_summary_by_table:按照每个表进行统计的表I/O等待事件)
CREATE TABLE sys.ews_table_iowaits (
OBJECT_TYPE varchar(64) DEFAULT NULL,
OBJECT_SCHEMA varchar(64) DEFAULT NULL,
OBJECT_NAME varchar(64) DEFAULT NULL,
COUNT_STAR bigint(20) unsigned NOT NULL,
SUM_TIMER_WAIT bigint(20) unsigned NOT NULL,
MIN_TIMER_WAIT bigint(20) unsigned NOT NULL,
AVG_TIMER_WAIT bigint(20) unsigned NOT NULL,
MAX_TIMER_WAIT bigint(20) unsigned NOT NULL,
COUNT_READ bigint(20) unsigned NOT NULL,
SUM_TIMER_READ bigint(20) unsigned NOT NULL,
MIN_TIMER_READ bigint(20) unsigned NOT NULL,
AVG_TIMER_READ bigint(20) unsigned NOT NULL,
MAX_TIMER_READ bigint(20) unsigned NOT NULL,
COUNT_WRITE bigint(20) unsigned NOT NULL,
SUM_TIMER_WRITE bigint(20) unsigned NOT NULL,
MIN_TIMER_WRITE bigint(20) unsigned NOT NULL,
AVG_TIMER_WRITE bigint(20) unsigned NOT NULL,
MAX_TIMER_WRITE bigint(20) unsigned NOT NULL,
COUNT_FETCH bigint(20) unsigned NOT NULL,
SUM_TIMER_FETCH bigint(20) unsigned NOT NULL,
MIN_TIMER_FETCH bigint(20) unsigned NOT NULL,
AVG_TIMER_FETCH bigint(20) unsigned NOT NULL,
MAX_TIMER_FETCH bigint(20) unsigned NOT NULL,
COUNT_INSERT bigint(20) unsigned NOT NULL,
SUM_TIMER_INSERT bigint(20) unsigned NOT NULL,
MIN_TIMER_INSERT bigint(20) unsigned NOT NULL,
AVG_TIMER_INSERT bigint(20) unsigned NOT NULL,
MAX_TIMER_INSERT bigint(20) unsigned NOT NULL,
COUNT_UPDATE bigint(20) unsigned NOT NULL,
SUM_TIMER_UPDATE bigint(20) unsigned NOT NULL,
MIN_TIMER_UPDATE bigint(20) unsigned NOT NULL,
AVG_TIMER_UPDATE bigint(20) unsigned NOT NULL,
MAX_TIMER_UPDATE bigint(20) unsigned NOT NULL,
COUNT_DELETE bigint(20) unsigned NOT NULL,
SUM_TIMER_DELETE bigint(20) unsigned NOT NULL,
MIN_TIMER_DELETE bigint(20) unsigned NOT NULL,
AVG_TIMER_DELETE bigint(20) unsigned NOT NULL,
MAX_TIMER_DELETE bigint(20) unsigned NOT NULL,
INSERT_DATE TIMESTAMP NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='按照每个表进行统计的表I/O等待事件';
-- 中间表ews_index_iowaits(取performance_schema.table_io_waits_summary_by_index_usage:按照每个索引进行统计的表I/O等待事件)
CREATE TABLE sys.ews_index_iowaits (
OBJECT_TYPE varchar(64) DEFAULT NULL,
OBJECT_SCHEMA varchar(64) DEFAULT NULL,
OBJECT_NAME varchar(64) DEFAULT NULL,
INDEX_NAME varchar(64) DEFAULT NULL,
COUNT_STAR bigint(20) unsigned NOT NULL,
SUM_TIMER_WAIT bigint(20) unsigned NOT NULL,
MIN_TIMER_WAIT bigint(20) unsigned NOT NULL,
AVG_TIMER_WAIT bigint(20) unsigned NOT NULL,
MAX_TIMER_WAIT bigint(20) unsigned NOT NULL,
COUNT_READ bigint(20) unsigned NOT NULL,
SUM_TIMER_READ bigint(20) unsigned NOT NULL,
MIN_TIMER_READ bigint(20) unsigned NOT NULL,
AVG_TIMER_READ bigint(20) unsigned NOT NULL,
MAX_TIMER_READ bigint(20) unsigned NOT NULL,
COUNT_WRITE bigint(20) unsigned NOT NULL,
SUM_TIMER_WRITE bigint(20) unsigned NOT NULL,
MIN_TIMER_WRITE bigint(20) unsigned NOT NULL,
AVG_TIMER_WRITE bigint(20) unsigned NOT NULL,
MAX_TIMER_WRITE bigint(20) unsigned NOT NULL,
COUNT_FETCH bigint(20) unsigned NOT NULL,
SUM_TIMER_FETCH bigint(20) unsigned NOT NULL,
MIN_TIMER_FETCH bigint(20) unsigned NOT NULL,
AVG_TIMER_FETCH bigint(20) unsigned NOT NULL,
MAX_TIMER_FETCH bigint(20) unsigned NOT NULL,
COUNT_INSERT bigint(20) unsigned NOT NULL,
SUM_TIMER_INSERT bigint(20) unsigned NOT NULL,
MIN_TIMER_INSERT bigint(20) unsigned NOT NULL,
AVG_TIMER_INSERT bigint(20) unsigned NOT NULL,
MAX_TIMER_INSERT bigint(20) unsigned NOT NULL,
COUNT_UPDATE bigint(20) unsigned NOT NULL,
SUM_TIMER_UPDATE bigint(20) unsigned NOT NULL,
MIN_TIMER_UPDATE bigint(20) unsigned NOT NULL,
AVG_TIMER_UPDATE bigint(20) unsigned NOT NULL,
MAX_TIMER_UPDATE bigint(20) unsigned NOT NULL,
COUNT_DELETE bigint(20) unsigned NOT NULL,
SUM_TIMER_DELETE bigint(20) unsigned NOT NULL,
MIN_TIMER_DELETE bigint(20) unsigned NOT NULL,
AVG_TIMER_DELETE bigint(20) unsigned NOT NULL,
MAX_TIMER_DELETE bigint(20) unsigned NOT NULL,
INSERT_DATE TIMESTAMP NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='按照每个索引进行统计的表I/O等待事件';
-- 中间表sys.ews_table_lockwaits(取performance_schema.table_lock_waits_summary_by_table：按照每个表进行统计的表锁等待事件)
CREATE TABLE sys.ews_table_lockwaits (
OBJECT_TYPE varchar(64) DEFAULT NULL,
OBJECT_SCHEMA varchar(64) DEFAULT NULL,
OBJECT_NAME varchar(64) DEFAULT NULL,
COUNT_STAR bigint(20) unsigned NOT NULL,
SUM_TIMER_WAIT bigint(20) unsigned NOT NULL,
MIN_TIMER_WAIT bigint(20) unsigned NOT NULL,
AVG_TIMER_WAIT bigint(20) unsigned NOT NULL,
MAX_TIMER_WAIT bigint(20) unsigned NOT NULL,
COUNT_READ bigint(20) unsigned NOT NULL,
SUM_TIMER_READ bigint(20) unsigned NOT NULL,
MIN_TIMER_READ bigint(20) unsigned NOT NULL,
AVG_TIMER_READ bigint(20) unsigned NOT NULL,
MAX_TIMER_READ bigint(20) unsigned NOT NULL,
COUNT_WRITE bigint(20) unsigned NOT NULL,
SUM_TIMER_WRITE bigint(20) unsigned NOT NULL,
MIN_TIMER_WRITE bigint(20) unsigned NOT NULL,
AVG_TIMER_WRITE bigint(20) unsigned NOT NULL,
MAX_TIMER_WRITE bigint(20) unsigned NOT NULL,
COUNT_READ_NORMAL bigint(20) unsigned NOT NULL,
SUM_TIMER_READ_NORMAL bigint(20) unsigned NOT NULL,
MIN_TIMER_READ_NORMAL bigint(20) unsigned NOT NULL,
AVG_TIMER_READ_NORMAL bigint(20) unsigned NOT NULL,
MAX_TIMER_READ_NORMAL bigint(20) unsigned NOT NULL,
COUNT_READ_WITH_SHARED_LOCKS bigint(20) unsigned NOT NULL,
SUM_TIMER_READ_WITH_SHARED_LOCKS bigint(20) unsigned NOT NULL,
MIN_TIMER_READ_WITH_SHARED_LOCKS bigint(20) unsigned NOT NULL,
AVG_TIMER_READ_WITH_SHARED_LOCKS bigint(20) unsigned NOT NULL,
MAX_TIMER_READ_WITH_SHARED_LOCKS bigint(20) unsigned NOT NULL,
COUNT_READ_NO_INSERT bigint(20) unsigned NOT NULL,
SUM_TIMER_READ_NO_INSERT bigint(20) unsigned NOT NULL,
MIN_TIMER_READ_NO_INSERT bigint(20) unsigned NOT NULL,
AVG_TIMER_READ_NO_INSERT bigint(20) unsigned NOT NULL,
MAX_TIMER_READ_NO_INSERT bigint(20) unsigned NOT NULL,
COUNT_READ_EXTERNAL bigint(20) unsigned NOT NULL,
SUM_TIMER_READ_EXTERNAL bigint(20) unsigned NOT NULL,
MIN_TIMER_READ_EXTERNAL bigint(20) unsigned NOT NULL,
AVG_TIMER_READ_EXTERNAL bigint(20) unsigned NOT NULL,
MAX_TIMER_READ_EXTERNAL bigint(20) unsigned NOT NULL,
COUNT_WRITE_ALLOW_WRITE bigint(20) unsigned NOT NULL,
SUM_TIMER_WRITE_ALLOW_WRITE bigint(20) unsigned NOT NULL,
MIN_TIMER_WRITE_ALLOW_WRITE bigint(20) unsigned NOT NULL,
AVG_TIMER_WRITE_ALLOW_WRITE bigint(20) unsigned NOT NULL,
MAX_TIMER_WRITE_ALLOW_WRITE bigint(20) unsigned NOT NULL,
COUNT_WRITE_CONCURRENT_INSERT bigint(20) unsigned NOT NULL,
SUM_TIMER_WRITE_CONCURRENT_INSERT bigint(20) unsigned NOT NULL,
MIN_TIMER_WRITE_CONCURRENT_INSERT bigint(20) unsigned NOT NULL,
AVG_TIMER_WRITE_CONCURRENT_INSERT bigint(20) unsigned NOT NULL,
MAX_TIMER_WRITE_CONCURRENT_INSERT bigint(20) unsigned NOT NULL,
COUNT_WRITE_NORMAL bigint(20) unsigned NOT NULL,
SUM_TIMER_WRITE_NORMAL bigint(20) unsigned NOT NULL,
MIN_TIMER_WRITE_NORMAL bigint(20) unsigned NOT NULL,
AVG_TIMER_WRITE_NORMAL bigint(20) unsigned NOT NULL,
MAX_TIMER_WRITE_NORMAL bigint(20) unsigned NOT NULL,
COUNT_WRITE_EXTERNAL bigint(20) unsigned NOT NULL,
SUM_TIMER_WRITE_EXTERNAL bigint(20) unsigned NOT NULL,
MIN_TIMER_WRITE_EXTERNAL bigint(20) unsigned NOT NULL,
AVG_TIMER_WRITE_EXTERNAL bigint(20) unsigned NOT NULL,
MAX_TIMER_WRITE_EXTERNAL bigint(20) unsigned NOT NULL,
INSERT_DATE TIMESTAMP NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='按照每个表进行统计的表锁等待事件';
-- 中间表sys.ews_file_io(取performance_schema.file_summary_by_instance:按照每个文件实例(对应具体的磁盘文件)进行统计的文件I/0等待事件)
CREATE TABLE sys.ews_file_io (
FILE_NAME varchar(512) NOT NULL,
EVENT_NAME varchar(128) NOT NULL,
COUNT_STAR bigint(20) unsigned NOT NULL,
SUM_NUMBER_OF_BYTES_READ bigint(20) NOT NULL,
SUM_NUMBER_OF_BYTES_WRITE bigint(20) NOT NULL,
INSERT_DATE TIMESTAMP NOT NULL
) ENGINE=innodb DEFAULT CHARSET=utf8 COMMENT='按照每个文件实例(对应具体的磁盘文件)进行统计的文件I/0等待事件';2、自定义存储过程
这里用存储过程来定期存数据及删除过期数据。
use sys;
drop procedure if exists ews;
DELIMITER //
CREATE DEFINER = root @'%' PROCEDURE ews( ) BEGIN
DECLARE
affect_rows INT;
DECLARE
del_day INT;-- 长日志存留期
DECLARE
v_commit INT DEFAULT 2;-- 定义事务用，1为正常，-10为失败
DECLARE
msg text;-- 记录错误信息
-- 异常的时候msg捕获报错信息
DECLARE
CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
get diagnostics CONDITION 1 msg = message_text;
SET v_commit = - 10;
END;
SET @del_day = 8;
START TRANSACTION;-- 设置事务
-- Top 10 Event Summary
INSERT INTO sys.ews_event SELECT
ews.EVENT_NAME,
ews.COUNT_STAR,
round( ews.SUM_TIMER_WAIT / 1000000 ) SUM_TIMER_WAIT,
round( ews.AVG_TIMER_WAIT / 1000000 ) AVG_TIMER_WAIT,
round( ews.MIN_TIMER_WAIT / 1000000 ) MIN_TIMER_WAIT,
round( ews.MAX_TIMER_WAIT / 1000000 ) MAX_TIMER_WAIT,
now( )
FROM
PERFORMANCE_SCHEMA.events_waits_summary_global_by_event_name ews
WHERE
ews.count_star > 0
AND ews.event_name != 'idle';
-- TOP 10 SQL
INSERT INTO sys.ews_sql select ews.SCHEMA_NAME,
ews.DIGEST,
ews.DIGEST_TEXT,
ews.COUNT_STAR,
round( ews.SUM_TIMER_WAIT / 1000000 ) SUM_TIMER_WAIT,
round( ews.MIN_TIMER_WAIT / 1000000 ) MIN_TIMER_WAIT,
round( ews.AVG_TIMER_WAIT / 1000000 ) AVG_TIMER_WAIT,
round( ews.MAX_TIMER_WAIT / 1000000 ) MAX_TIMER_WAIT,
round( ews.SUM_LOCK_TIME / 1000000 ) SUM_LOCK_TIME,
ews.SUM_ERRORS,
ews.SUM_WARNINGS,
ews.SUM_ROWS_AFFECTED,
ews.SUM_ROWS_SENT,
ews.SUM_ROWS_EXAMINED,
ews.SUM_CREATED_TMP_DISK_TABLES,
ews.SUM_CREATED_TMP_TABLES,
ews.SUM_SELECT_FULL_JOIN,
ews.SUM_SELECT_FULL_RANGE_JOIN,
ews.SUM_SELECT_RANGE,
ews.SUM_SELECT_RANGE_CHECK,
ews.SUM_SELECT_SCAN,
ews.SUM_SORT_MERGE_PASSES,
ews.SUM_SORT_RANGE,
ews.SUM_SORT_ROWS,
ews.SUM_SORT_SCAN,
ews.SUM_NO_INDEX_USED,
ews.SUM_NO_GOOD_INDEX_USED,
ews.FIRST_SEEN,
ews.LAST_SEEN
FROM
PERFORMANCE_SCHEMA.events_statements_summary_by_digest ews
WHERE
ews.SCHEMA_NAME NOT IN ( 'perf_stat', 'mysql', 'sys', 'performance_schema', 'information_schema' )
AND ews.count_star > 0
AND ews.digest_text NOT LIKE 'SHOW%'
AND ews.digest_text NOT LIKE 'USE%'
AND ews.digest_text NOT LIKE 'SET%'
AND ews.digest_text NOT LIKE 'SELECT @%'
AND ews.digest_text NOT LIKE 'EXPLAIN%'
AND ews.digest_text NOT LIKE 'DROP PROCEDURE%'
AND ews.digest_text NOT LIKE 'CREATE PROCEDURE%';
-- Top 10 Table By LOGICAL IO Wait
INSERT INTO sys.ews_table_iowaits select
ews.OBJECT_TYPE,
ews.OBJECT_SCHEMA,
ews.OBJECT_NAME,
ews.COUNT_STAR,
round(ews.SUM_TIMER_WAIT/1000000) SUM_TIMER_WAIT,
round(ews.MIN_TIMER_WAIT/1000000) MIN_TIMER_WAIT,
round(ews.AVG_TIMER_WAIT/1000000) AVG_TIMER_WAIT,
round(ews.MAX_TIMER_WAIT/1000000) MAX_TIMER_WAIT,
ews.COUNT_READ,
round(ews.SUM_TIMER_READ/1000000) SUM_TIMER_READ,
round(ews.MIN_TIMER_READ/1000000) MIN_TIMER_READ,
round(ews.AVG_TIMER_READ/1000000) AVG_TIMER_READ,
round(ews.MAX_TIMER_READ/1000000) MAX_TIMER_READ,
ews.COUNT_WRITE,
round(ews.SUM_TIMER_WRITE/1000000) SUM_TIMER_WRITE,
round(ews.MIN_TIMER_WRITE/1000000) MIN_TIMER_WRITE,
round(ews.AVG_TIMER_WRITE/1000000) AVG_TIMER_WRITE,
round(ews.MAX_TIMER_WRITE/1000000) MAX_TIMER_WRITE,
ews.COUNT_FETCH,
round(ews.SUM_TIMER_FETCH/1000000) SUM_TIMER_FETCH,
round(ews.MIN_TIMER_FETCH/1000000) MIN_TIMER_FETCH,
round(ews.AVG_TIMER_FETCH/1000000) AVG_TIMER_FETCH,
round(ews.MAX_TIMER_FETCH/1000000) MAX_TIMER_FETCH,
ews.COUNT_INSERT,
round(ews.SUM_TIMER_INSERT/1000000) SUM_TIMER_INSERT,
round(ews.MIN_TIMER_INSERT/1000000) MIN_TIMER_INSERT,
round(ews.AVG_TIMER_INSERT/1000000) AVG_TIMER_INSERT,
round(ews.MAX_TIMER_INSERT/1000000) MAX_TIMER_INSERT,
ews.COUNT_UPDATE,
round(ews.SUM_TIMER_UPDATE/1000000) SUM_TIMER_UPDATE,
round(ews.MIN_TIMER_UPDATE/1000000) MIN_TIMER_UPDATE,
round(ews.AVG_TIMER_UPDATE/1000000) AVG_TIMER_UPDATE,
round(ews.MAX_TIMER_UPDATE/1000000) MAX_TIMER_UPDATE,
ews.COUNT_DELETE,
round(ews.SUM_TIMER_DELETE/1000000) SUM_TIMER_DELETE,
round(ews.MIN_TIMER_DELETE/1000000) MIN_TIMER_DELETE,
round(ews.AVG_TIMER_DELETE/1000000) AVG_TIMER_DELETE,
round(ews.MAX_TIMER_DELETE/1000000) MAX_TIMER_DELETE,
now( )
from performance_schema.table_io_waits_summary_by_table ews
where ews.OBJECT_SCHEMA not in ('perf_stat','mysql','sys','performance_schema','information_schema')
and ews.count_star > 0;
-- Top 10 Index By LOGICAL IO Wait
INSERT INTO SYS.ews_index_iowaits select
ews.OBJECT_TYPE,
ews.OBJECT_SCHEMA,
ews.OBJECT_NAME,
ews.INDEX_NAME,
ews.COUNT_STAR,
round(ews.SUM_TIMER_WAIT/1000000) SUM_TIMER_WAIT,
round(ews.MIN_TIMER_WAIT/1000000) MIN_TIMER_WAIT,
round(ews.AVG_TIMER_WAIT/1000000) AVG_TIMER_WAIT,
round(ews.MAX_TIMER_WAIT/1000000) MAX_TIMER_WAIT,
ews.COUNT_READ,
round(ews.SUM_TIMER_READ/1000000) SUM_TIMER_READ,
round(ews.MIN_TIMER_READ/1000000) MIN_TIMER_READ,
round(ews.AVG_TIMER_READ/1000000) AVG_TIMER_READ,
round(ews.MAX_TIMER_READ/1000000) MAX_TIMER_READ,
ews.COUNT_WRITE,
round(ews.SUM_TIMER_WRITE/1000000) SUM_TIMER_WRITE,
round(ews.MIN_TIMER_WRITE/1000000) MIN_TIMER_WRITE,
round(ews.AVG_TIMER_WRITE/1000000) AVG_TIMER_WRITE,
round(ews.MAX_TIMER_WRITE/1000000) MAX_TIMER_WRITE,
ews.COUNT_FETCH,
round(ews.SUM_TIMER_FETCH/1000000) SUM_TIMER_FETCH,
round(ews.MIN_TIMER_FETCH/1000000) MIN_TIMER_FETCH,
round(ews.AVG_TIMER_FETCH/1000000) AVG_TIMER_FETCH,
round(ews.MAX_TIMER_FETCH/1000000) MAX_TIMER_FETCH,
ews.COUNT_INSERT,
round(ews.SUM_TIMER_INSERT/1000000) SUM_TIMER_INSERT,
round(ews.MIN_TIMER_INSERT/1000000) MIN_TIMER_INSERT,
round(ews.AVG_TIMER_INSERT/1000000) AVG_TIMER_INSERT,
round(ews.MAX_TIMER_INSERT/1000000) MAX_TIMER_INSERT,
ews.COUNT_UPDATE,
round(ews.SUM_TIMER_UPDATE/1000000) SUM_TIMER_UPDATE,
round(ews.MIN_TIMER_UPDATE/1000000) MIN_TIMER_UPDATE,
round(ews.AVG_TIMER_UPDATE/1000000) AVG_TIMER_UPDATE,
round(ews.MAX_TIMER_UPDATE/1000000) MAX_TIMER_UPDATE,
ews.COUNT_DELETE,
round(ews.SUM_TIMER_DELETE/1000000) SUM_TIMER_DELETE,
round(ews.MIN_TIMER_DELETE/1000000) MIN_TIMER_DELETE,
round(ews.AVG_TIMER_DELETE/1000000) AVG_TIMER_DELETE,
round(ews.MAX_TIMER_DELETE/1000000) MAX_TIMER_DELETE,
now( )
from performance_schema.table_io_waits_summary_by_index_usage ews
where ews.OBJECT_SCHEMA not in ('perf_stat','mysql','sys','performance_schema','information_schema')
and ews.count_star > 0;
-- Top 10 Table By LOCKS WAITS
INSERT INTO sys.ews_table_lockwaits select
ews.OBJECT_TYPE,
ews.OBJECT_SCHEMA,
ews.OBJECT_NAME,
ews.COUNT_STAR,
round(ews.SUM_TIMER_WAIT/1000000) SUM_TIMER_WAIT,
round(ews.MIN_TIMER_WAIT/1000000) MIN_TIMER_WAIT,
round(ews.AVG_TIMER_WAIT/1000000) AVG_TIMER_WAIT,
round(ews.MAX_TIMER_WAIT/1000000) MAX_TIMER_WAIT,
ews.COUNT_READ,
round(ews.SUM_TIMER_READ/1000000) SUM_TIMER_READ,
round(ews.MIN_TIMER_READ/1000000) MIN_TIMER_READ,
round(ews.AVG_TIMER_READ/1000000) AVG_TIMER_READ,
round(ews.MAX_TIMER_READ/1000000) MAX_TIMER_READ,
ews.COUNT_WRITE,
round(ews.SUM_TIMER_WRITE/1000000) SUM_TIMER_WRITE,
round(ews.MIN_TIMER_WRITE/1000000) MIN_TIMER_WRITE,
round(ews.AVG_TIMER_WRITE/1000000) AVG_TIMER_WRITE,
round(ews.MAX_TIMER_WRITE/1000000) MAX_TIMER_WRITE,
ews.COUNT_READ_NORMAL,
round(ews.SUM_TIMER_READ_NORMAL/1000000) SUM_TIMER_READ_NORMAL,
round(ews.MIN_TIMER_READ_NORMAL/1000000) MIN_TIMER_READ_NORMAL,
round(ews.AVG_TIMER_READ_NORMAL/1000000) AVG_TIMER_READ_NORMAL,
round(ews.MAX_TIMER_READ_NORMAL/1000000) MAX_TIMER_READ_NORMAL,
ews.COUNT_READ_WITH_SHARED_LOCKS,
round(ews.SUM_TIMER_READ_WITH_SHARED_LOCKS/1000000) SUM_TIMER_READ_WITH_SHARED_LOCKS,
round(ews.MIN_TIMER_READ_WITH_SHARED_LOCKS/1000000) MIN_TIMER_READ_WITH_SHARED_LOCKS,
round(ews.AVG_TIMER_READ_WITH_SHARED_LOCKS/1000000) AVG_TIMER_READ_WITH_SHARED_LOCKS,
round(ews.MAX_TIMER_READ_WITH_SHARED_LOCKS/1000000) MAX_TIMER_READ_WITH_SHARED_LOCKS,
ews.COUNT_READ_NO_INSERT,
round(ews.SUM_TIMER_READ_NO_INSERT/1000000) SUM_TIMER_READ_NO_INSERT,
round(ews.MIN_TIMER_READ_NO_INSERT/1000000) MIN_TIMER_READ_NO_INSERT,
round(ews.AVG_TIMER_READ_NO_INSERT/1000000) AVG_TIMER_READ_NO_INSERT,
round(ews.MAX_TIMER_READ_NO_INSERT/1000000) MAX_TIMER_READ_NO_INSERT,
ews.COUNT_READ_EXTERNAL,
round(ews.SUM_TIMER_READ_EXTERNAL/1000000) SUM_TIMER_READ_EXTERNAL,
round(ews.MIN_TIMER_READ_EXTERNAL/1000000) MIN_TIMER_READ_EXTERNAL,
round(ews.AVG_TIMER_READ_EXTERNAL/1000000) AVG_TIMER_READ_EXTERNAL,
round(ews.MAX_TIMER_READ_EXTERNAL/1000000) MAX_TIMER_READ_EXTERNAL,
ews.COUNT_WRITE_ALLOW_WRITE,
round(ews.SUM_TIMER_WRITE_ALLOW_WRITE/1000000) SUM_TIMER_WRITE_ALLOW_WRITE,
round(ews.MIN_TIMER_WRITE_ALLOW_WRITE/1000000) MIN_TIMER_WRITE_ALLOW_WRITE,
round(ews.AVG_TIMER_WRITE_ALLOW_WRITE/1000000) AVG_TIMER_WRITE_ALLOW_WRITE,
round(ews.MAX_TIMER_WRITE_ALLOW_WRITE/1000000) MAX_TIMER_WRITE_ALLOW_WRITE,
ews.COUNT_WRITE_CONCURRENT_INSERT,
round(ews.SUM_TIMER_WRITE_CONCURRENT_INSERT/1000000) SUM_TIMER_WRITE_CONCURRENT_INSERT,
round(ews.MIN_TIMER_WRITE_CONCURRENT_INSERT/1000000) MIN_TIMER_WRITE_CONCURRENT_INSERT,
round(ews.AVG_TIMER_WRITE_CONCURRENT_INSERT/1000000) AVG_TIMER_WRITE_CONCURRENT_INSERT,
round(ews.MAX_TIMER_WRITE_CONCURRENT_INSERT/1000000) MAX_TIMER_WRITE_CONCURRENT_INSERT,
ews.COUNT_WRITE_NORMAL,
round(ews.SUM_TIMER_WRITE_NORMAL/1000000) SUM_TIMER_WRITE_NORMAL,
round(ews.MIN_TIMER_WRITE_NORMAL/1000000) MIN_TIMER_WRITE_NORMAL,
round(ews.AVG_TIMER_WRITE_NORMAL/1000000) AVG_TIMER_WRITE_NORMAL,
round(ews.MAX_TIMER_WRITE_NORMAL/1000000) MAX_TIMER_WRITE_NORMAL,
ews.COUNT_WRITE_EXTERNAL,
round(ews.SUM_TIMER_WRITE_EXTERNAL/1000000) SUM_TIMER_WRITE_EXTERNAL,
round(ews.MIN_TIMER_WRITE_EXTERNAL/1000000) MIN_TIMER_WRITE_EXTERNAL,
round(ews.AVG_TIMER_WRITE_EXTERNAL/1000000) AVG_TIMER_WRITE_EXTERNAL,
round(ews.MAX_TIMER_WRITE_EXTERNAL/1000000) MAX_TIMER_WRITE_EXTERNAL,
now( )
from performance_schema.table_lock_waits_summary_by_table ews
where ews.OBJECT_SCHEMA not in ('perf_stat','mysql','sys','performance_schema','information_schema')
and ews.count_star > 0;
-- TOP 10 Table By PHYSICAL IO Wait
INSERT INTO sys.ews_file_io SELECT
file_name,
event_name,
COUNT_STAR,
SUM_NUMBER_OF_BYTES_READ,
SUM_NUMBER_OF_BYTES_WRITE,
NOW( )
FROM performance_schema.file_summary_by_instance;
COMMIT;-- 异常回滚且记录日志
START TRANSACTION;-- 设置删除事务
delete from sys.ews_event where insert_date < DATE_SUB(CURDATE(),INTERVAL @del_day DAY);
select ROW_COUNT() into @affect_rows;
insert into sys.error_log values('删除sys.ews_event成功',@affect_rows,now());
delete from sys.ews_sql where last_seen < DATE_SUB(CURDATE(),INTERVAL @del_day DAY);
select ROW_COUNT() into @affect_rows;
insert into sys.error_log values('删除sys.ews_sql成功',@affect_rows,now());
delete from sys.ews_table_iowaits where insert_date < DATE_SUB(CURDATE(),INTERVAL @del_day DAY);
select ROW_COUNT() into @affect_rows;
insert into sys.error_log values('删除sys.ews_table_iowaits成功',@affect_rows,now());
delete from sys.ews_index_iowaits where insert_date < DATE_SUB(CURDATE(),INTERVAL @del_day DAY);
select ROW_COUNT() into @affect_rows;
insert into sys.error_log values('删除sys.ews_index_iowaits成功',@affect_rows,now());
delete from sys.ews_table_lockwaits where insert_date < DATE_SUB(CURDATE(),INTERVAL @del_day DAY);
select ROW_COUNT() into @affect_rows;
insert into sys.error_log values('删除sys.ews_table_lockwaits成功',@affect_rows,now());
delete from sys.ews_file_io where insert_date < DATE_SUB(CURDATE(),INTERVAL @del_day DAY);
select ROW_COUNT() into @affect_rows;
insert into sys.error_log values('删除sys.ews_file_io成功',@affect_rows,now());
COMMIT;-- 异常回滚且记录日志
IF
v_commit = - 10 THEN
ROLLBACK;
INSERT INTO sys.error_log VALUES ( msg, 0, now( ) );
END IF;
END //
DELIMITER;3、定时任务
-- 定时执行存储过程
USE SYS;
DROP EVENT IF EXISTS ews;
CREATE EVENT ews ON SCHEDULE EVERY 10 MINUTE DO
CALL ews ( );4、创建索引
考虑到后面查询基本是按日期做筛选来查的，所以建这个索引。
create index idx_date1 on ews_event(insert_date);
create index idx_date2 on ews_sql(last_seen);
create index idx_date3 on ews_table_iowaits(insert_date);
create index idx_date4 on ews_index_iowaits(insert_date);
create index idx_date5 on ews_table_lockwaits(insert_date);
create index idx_date6 on ews_file_io(insert_date);5、数据查询
5.1、最近30分钟排名前十的等待事件
SELECT
event_name "等待事件",
max( count_star ) - min( count_star)+1 "等待次数",
round( ( max( sum_timer_wait ) - min( sum_timer_wait ) ) / 1000 ) "总时长(s)↓",
concat(
round(
(max( sum_timer_wait ) - min( sum_timer_wait ))/(
SELECT sum(ev) FROM
( SELECT (max(sum_timer_wait) - min( sum_timer_wait ) ) ev FROM sys.ews_event
WHERE insert_date >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE GROUP BY event_name ) c
) * 100,
2),
'%') "总时长占比",
max( avg_timer_wait ) - min( avg_timer_wait ) "平均等待时间(ms)"
FROM sys.ews_event
WHERE insert_date >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE
GROUP BY event_name
ORDER BY max( sum_timer_wait ) - min( sum_timer_wait ) DESC LIMIT 10;
5.2、最近30分钟总耗时排名前十的sql
SELECT
DIGEST "sql_id",
SCHEMA_NAME "数据库",
max( COUNT_STAR ) - min( COUNT_STAR )+1 "执行次数",
round( ( max( sum_timer_wait ) - min( sum_timer_wait ) ) / 1000 ) "总时长(s)↓",
concat(
round(
(max( sum_timer_wait ) - min( sum_timer_wait ))/(
SELECT sum(ev) FROM
( SELECT (max(sum_timer_wait) - min( sum_timer_wait ) ) ev FROM sys.ews_sql
WHERE last_seen >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE GROUP BY DIGEST) c
) * 100,
2),
'%') "总时长占比",
max( avg_timer_wait ) - min( avg_timer_wait ) "平均时长(ms)",
max( SUM_ROWS_EXAMINED ) - min( SUM_ROWS_EXAMINED ) "总检查行数",
max( LAST_SEEN ) "最后执行时间",
DIGEST_TEXT "sql语句"
FROM sys.ews_sql
WHERE last_seen >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE
GROUP BY DIGEST
ORDER BY max( sum_timer_wait ) - min( sum_timer_wait ) DESC LIMIT 10;
5.3、最近30分钟执行次数排名前十的sql
SELECT
SCHEMA_NAME "数据库",
DIGEST "sql_id",
max( COUNT_STAR ) - min( COUNT_STAR ) "执行次数↓",
concat(
round(
(max( COUNT_STAR ) - min( COUNT_STAR ))/(
SELECT sum(ev) FROM
( SELECT (max(COUNT_STAR) - min( COUNT_STAR ) ) ev FROM sys.ews_sql
WHERE last_seen >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE GROUP BY DIGEST) c
) * 100,
2),
'%') "执行次数占比",
max( sum_timer_wait ) - min( sum_timer_wait ) "总时长(ms)",
max( avg_timer_wait ) - min( avg_timer_wait ) "平均时长(ms)",
max( SUM_ROWS_EXAMINED ) - min( SUM_ROWS_EXAMINED ) "总检查行数",
max( LAST_SEEN ) "最后执行时间",
DIGEST_TEXT "sql语句"
FROM sys.ews_sql
WHERE last_seen >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE
GROUP BY DIGEST
ORDER BY max( COUNT_STAR ) - min( COUNT_STAR ) DESC LIMIT 10;
5.4、最近30分钟平均耗时最长的前十sql语句
SELECT
DIGEST "sql_id",
SCHEMA_NAME "数据库",
max( COUNT_STAR ) - min( COUNT_STAR ) "执行次数",
round( ( max( sum_timer_wait ) - min( sum_timer_wait ) ) / 1000 ) "总时长(s)",
max( avg_timer_wait ) - min( avg_timer_wait ) "平均时长(ms)↓",
concat(
round(
(max( avg_timer_wait ) - min( avg_timer_wait ))/(
SELECT sum(ev) FROM
( SELECT (max(avg_timer_wait) - min( avg_timer_wait ) ) ev FROM sys.ews_sql
WHERE last_seen >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE GROUP BY DIGEST) c
) * 100,
2),
'%') "平均时长占比",
max( SUM_ROWS_EXAMINED ) - min( SUM_ROWS_EXAMINED ) "总检查行数",
max( LAST_SEEN ) "最后执行时间",
DIGEST_TEXT "sql语句"
FROM sys.ews_sql
WHERE last_seen >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE
GROUP BY DIGEST
ORDER BY max( avg_timer_wait ) - min( avg_timer_wait ) DESC LIMIT 10;
5.5、最近30分钟逻辑IO总延时排名前十的表
SELECT
object_type "类型",
object_schema "数据库",
object_name "对象名",
max( count_star ) - min( count_star ) "执行总次数",
max( sum_timer_wait ) - min( sum_timer_wait ) "总延时(ms)↓",
concat(
round(
(max( sum_timer_wait ) - min( sum_timer_wait ))/(
SELECT sum(ev) FROM
( SELECT (max(sum_timer_wait) - min( sum_timer_wait ) ) ev FROM sys.ews_table_iowaits
WHERE insert_date >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE GROUP BY object_schema, object_name,object_type ) c
) * 100,
2),
'%') "总延时占比",
max( avg_timer_wait ) - min( avg_timer_wait ) "平均延时(ms)",
max( count_read ) - min( count_read ) "读次数",
max( sum_timer_read ) - min( sum_timer_read ) "读延时(ms)",
max( avg_timer_read ) - min( avg_timer_read ) "平均读延时(ms)",
max( count_write ) - min( count_write ) "写次数",
max( sum_timer_write ) - min( sum_timer_write ) "写延时(ms)",
max( avg_timer_write ) - min( avg_timer_write ) "平均写延时(ms)"
FROM sys.ews_table_iowaits
WHERE insert_date >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE
GROUP BY object_schema, object_name, object_type
ORDER BY max( sum_timer_wait ) - min( sum_timer_wait ) DESC LIMIT 10;
5.6、最近30分钟逻辑IO总延时排名前十的索引
SELECT
object_type "类型",
object_schema "数据库",
object_name "对象名称",
index_name "索引名称",
max( count_star ) - min( count_star ) "执行总次数",
max( sum_timer_wait ) - min( sum_timer_wait ) "总延时(ms)↓",
concat(
round(
(max( sum_timer_wait ) - min( sum_timer_wait ))/(
SELECT sum(ev) FROM
( SELECT (max(sum_timer_wait) - min( sum_timer_wait ) ) ev FROM sys.ews_index_iowaits
WHERE insert_date >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE and index_name is not null GROUP BY object_schema, object_name,index_name, object_type ) c
) * 100,
2),
'%') "总延时占比",
max( avg_timer_wait ) - min( avg_timer_wait ) "平均延时(ms)",
max( count_read ) - min( count_read ) "读次数",
max( sum_timer_read ) - min( sum_timer_read ) "读延时(ms)",
max( avg_timer_read ) - min( avg_timer_read ) "平均读延时(ms)",
max( count_write ) - min( count_write ) "写次数",
max( sum_timer_write ) - min( sum_timer_write ) "写延时(ms)",
max( avg_timer_write ) - min( avg_timer_write ) "平均写延时(ms)"
FROM sys.ews_index_iowaits
WHERE insert_date >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE and index_name is not null
GROUP BY object_schema, object_name, index_name, object_type
ORDER BY max( sum_timer_wait ) - min( sum_timer_wait ) DESC limit 10;
5.7、最近30分钟表锁耗时排名前十的表
SELECT
object_type "类型",
object_schema "数据库",
object_name "对象名称",
max( count_star ) - min( count_star ) "执行总次数",
max( sum_timer_wait ) - min( sum_timer_wait ) "总延时(ms)↓",
concat(
round(
(max( sum_timer_wait ) - min( sum_timer_wait ))/(
SELECT sum(ev) FROM
( SELECT (max(sum_timer_wait) - min( sum_timer_wait ) ) ev FROM sys.ews_table_lockwaits
WHERE insert_date >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE GROUP BY object_schema,object_name,object_type ) c
) * 100,
2),
'%') "总延时占比",
max( avg_timer_wait ) - min( avg_timer_wait ) "平均延时(ms)",
max( count_read ) - min( count_read ) "读次数",
max( sum_timer_read ) - min( sum_timer_read ) "读延时(ms)",
max( avg_timer_read ) - min( avg_timer_read ) "平均读延时(ms)",
max( count_write ) - min( count_write ) "写次数",
max( sum_timer_write ) - min( sum_timer_write ) "写延时(ms)",
max( avg_timer_write ) - min( avg_timer_write ) "平均写延时(ms)"
FROM sys.ews_table_lockwaits
WHERE insert_date >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE
GROUP BY object_schema, object_name, object_type
ORDER BY max( sum_timer_wait ) - min( sum_timer_wait ) DESC LIMIT 10;
5.8、最近30分钟物理IO排名前十的表
SELECT
FILE_NAME "文件名",
EVENT_NAME "等待事件",
max( COUNT_STAR ) - min( COUNT_STAR ) "文件I/0操作数量",
round( ( max( sum_number_of_bytes_read ) - min( sum_number_of_bytes_read ) ) / 1024 ) "文件读I/0数据量(KB)",
round( ( max( sum_number_of_bytes_write ) - min( sum_number_of_bytes_write ) ) / 1024 ) "文件写I/0数据量(KB)",
concat(
round(
(max( SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE ) - min( SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE ))/(
SELECT sum(ev) FROM
( SELECT (max( SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE ) - min( SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE )) ev FROM sys.ews_file_io
WHERE insert_date >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE GROUP BY FILE_NAME,EVENT_NAME)c
) * 100,
2),
'%') "文件I/0占比↓"
FROM sys.ews_file_io
WHERE insert_date >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE
GROUP BY FILE_NAME, EVENT_NAME
ORDER BY max( SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE ) - min( SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE ) DESC LIMIT 10;
5.9、未使用的索引
通过table_io_waits_summary_by_index_usage表可以获得系统运行到现在，哪些索引从来没有被用过。由于索引也会占用大量的空间，可以利用这个统计信息，结合一定的时间策略将无用的索引删除。
SELECT
OBJECT_SCHEMA "数据库",
OBJECT_NAME "对象名称",
INDEX_NAME "索引名称"
FROM
performance_schema.table_io_waits_summary_by_index_usage ews
WHERE
INDEX_NAME IS NOT NULL
AND COUNT_STAR = 0
AND OBJECT_SCHEMA not in ('perf_stat','mysql','sys','performance_schema','information_schema');
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
