概述
众所周知，MySQL数据库中的performance_schema的事件统计表中的统计数据计算的是累计值，如果想要计算某段时间的TOP SQL是不行的，这里考虑用函数定期取值存进中间表定期将累计值的结果存入A表，按sql_id分组查A表，筛选最后执行时间，然后直接最大值减最小值来实现需求了。
以下针对MySQL数据库8.0版本。
1、创建中间表
-- 报错记录error_log
CREATE TABLE sys.error_log (
ERROR varchar(128) NOT NULL,
AFFECT_ROWS bigint(20) unsigned NOT NULL,
del_time datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 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 datetime 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 datetime NOT NULL DEFAULT NOW(),
LAST_SEEN datetime 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 datetime 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 datetime 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 datetime 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 datetime 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');
431
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
