基于MySQL5.7存储过程实现myawr平台的top sql功能

最新推荐文章于 2023-10-01 02:30:42 发布
最新推荐文章于 2023-10-01 02:30:42 发布
阅读量345 收藏
点赞数
分类专栏： # MySQL监控调优
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_28721869/article/details/115829463
版权

概述

某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 MINUTE

FIRST_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 > 0

4、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 > 0

5、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 > 0

6、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');

在这里插入图片描述

淡定波007
关注
专栏目录
MySQL5.7中的sql_mode默认值带来的坑及解决方法
09-09
MySQL5.7版本引入了一些新的默认`sql_mode`设置，这些设置可能会对现有的应用程序或数据库架构产生影响。本文主要讨论了其中两个常见的问题及其解决方法。 首先，我们来看`ONLY_FULL_GROUP_BY`模式。在MySQL5.7之前...
centos7 安装mysql_基于centos7系统安装部署mysql-awr开源监控工具--第一部分
weixin_39998859的博客
11-28 209
概述今天主要分享一款开源的mysql工具--myawr。myawr以mysql instance 为单位，每隔一段时间进行采样，然后把数据保存到数据库，以便分析。目前myawr脚本收集的信息包括5个部分：1)系统方面的：负载、cpu、io、网络、swap、磁盘空间2)Mysql状态方面的：具体到一些QPS、TPS、key Buffer 命中率、InnoDB Buffer命中率、Query Cach...
awr报告分析 mysql_myawr : mysql性能监控
weixin_42501033的博客
12-24 288
myawr以mysql instance 为单位，每隔一段时间进行采样，然后把数据保存到数据库，以便分析。目前myawr脚本收集的信息包括5个部分：1 系统方面的：负载、cpu、io、网络、swap、磁盘空间2 Mysql状态方面的：Mysql整体的一些指标；Myisam 引擎的一些指标；Innodb 引擎的一些指标。具体到一些QPS、TPS、key Buffer 命中率、InnoDB Buff...
mysql5.7Performance Schema 二
LOST_9的博客
04-29 911
25.8 性能模式原子和分子事件 25.8 性能模式原子和分子事件 对于表 I/O 事件，通常有两行events_waits_current，而不是一行。例如，行提取可能会产生如下行： Row# EVENT_NAME TIMER_START TIMER_END ---- ---------- ----------- --------- 1 wait/io/file/myisam/dfile 10001 1...
mysql myawr 死锁_myawr部署注意事项
weixin_42188533的博客
01-18 183
官方版的部署流程中有几个需要注意的点，在此补充一下部署master第一步，yum安装perl-DBD-mysql即可，当然源码编译安装也可以。第二步，修改myawr.sql，create table前加上use myawr;并修改账号的密码。然后导入至mysql。第三步，INSERT INTO `myawr_host`(id,host_name,ip_addr,port,db_role,versi...
performance_schema 笔记 实际应用（一）—— 等待事件，多线程复制报错查看
Hehuyi_In的博客
03-29 1732
1.利用等待事件排查MySQL性能问题 通常，在生产服务器上线之前， 我们会对数据库服务器的硬件进行IO基准测试，对数据库进行增删改查的基准测试，建立基线参考数据，以便日后的服务器扩容或架构升级提供数据支撑。在基准测试规划时，我们通常需要选择一款基准测试软件（IO基准测试通常选择fio和iozone，MySQL数据库基准测试通常选择sysbench、tpcc-mysql、workbench等），...
Mysql5.7.37-rpm安装包（aarch64平台）
01-23
通过官方源码包重新打包为aarch64平台下的rpm格式，源代码未进行任何修改，在麒麟v10服务器系统上测试通过 文件列表： mysql-community-client-5.7.37-1.ky10.ky10.aarch64.rpm mysql-community-common-5.7.37-1.ky...
MySQL5.7安装过程并重置root密码的方法（shell 脚本）
09-09
然后解压并使用`yum`命令安装MySQL 5.7及其依赖包： ```bash tar xvf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar yum install -y mysql-* ``` 安装完成后，你需要初始化MySQL服务： ```bash service mysqld start...
MySQL 5.7双主同步部分表的实现过程详解
01-21
安装MySQL5.7 推荐直接进官网 https://dev.mysql.com 下载yum源的方式安装，毕竟安全又省事。 wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm yum -y install mysql57-community-...
银河麒麟v10安装MySQL5.7 含教程
03-31
银河麒麟v10是一款基于Linux的国产操作系统，适用于arm架构的aarch64平台。由于我们提到的是免编译安装，这意味着我们将使用预编译的二进制包来简化流程，而不是从源代码进行编译。 ### 一、准备工作 1. **更新...
mysql sum 和 count_基于MySQL实现myawr平台top sql功能（上）
weixin_39877805的博客
11-20 173
概述某DBA大师曾说过，一个DBA要像了解自己的老婆一样了解自己管理的数据库，个人认为包含了两个方面的了解：1，在稳定性层面来说，更多的是关注高可用、读写分离、负载均衡，灾备管理等等high level层面的措施(就好比要保证生活的稳定性)2，在实例级别的来说，需要关注内存、IO、网络，热点表，热点索引，top sql，死锁，阻塞，历史上执行异常的SQL(好比生活品质细节)写这个的初衷是因为mys...
MySQL优化工具AWR，精准定位数据库瓶颈！
最新发布
MbfrDeveloper的博客
10-01 433
总结起来，MySQL优化工具AWR是一个强大的性能分析工具，可以帮助开发人员定位数据库瓶颈并提供相应的优化建议。通过启用AWR并使用相应的查询语句，开发人员可以获取关于锁等待时间、IO负载和查询响应时间等方面的性能数据。AWR是一个强大的性能分析工具，它可以收集数据库的大量性能数据，并将其存储在特定的存储库中。通过分析这些数据，开发人员可以了解数据库的IO瓶颈，并采取相应的优化措施。通过使用以上示例中的查询语句，开发人员可以根据自己的需求和数据库的实际情况，进一步优化数据库性能，并解决潜在的瓶颈问题。
mysql awr flush_配置MYSQL 的AWR快照采集
weixin_34475951的博客
01-19 190
一：在储存采集信息的库上执行快速安装配置：1，执行SQL脚本的内容：myawr.sql二：在需要采集的数据库上首先检查是否安装 perl-DBD-MySQLrpm -qa perl-DBD-MySQL*没有安装的话直接 yum install perl-DBD-MySQL* 即可。1。检查安装模块：[root@oel58 aaa]# cat listmodle.pl#!usr/bin/perlus...
mysql awr报告详解_AWR报告的使用
weixin_42299391的博客
01-28 899
[Oracle@localhost admin]$ pwd /home/oracle/oracle/product/10.2.0/db_1/rdbms/admin [oracle@localhost admin]$ sqlplus sys/[Oracle@localhost admin]$ pwd/home/oracle/oracle/product/10.2.0/db_1/rdbms/admin...
mysql的awr生成语句_Oracle数据库生成AWR报告
weixin_39804603的博客
02-28 191
通常DBA在管理数据库的时候，可以通过分析AWR(Automatic WorkloadRepository)报告来诊断数据库的性能问题，熟练掌握AWR报告，是DBA运维的必备技能之一。AWR报告的原理是基于Oracle数据库的定时镜像功能。默认情况下，Oracle数据库后台进程会以一定间隔(一小时)收集系统当前状态镜像，并且保存在数据库中。生成AWR报告时，只需要指定进行分析的时间段(开始镜像编号...
mysql数据库新建一个递增的_如何在mysql数据库搭建一个类似Oracle awr类似的分析平台？...
weixin_36360005的博客
11-26 146
概述继续前面的内容，这里整理一下mysqlawr的被监控端部署及测试部分。一、被监控端部署1、安装与Perl相关的模块1.1、安装perl(注意和监控端安装的版本要保持一致)rpm -ivh mysql-community-libs-compat-5.7.26-1.el7.x86_64.rpm rpm -ivh perl-DBD-MySQL-4.023-5.el7.x86_64.rpm 1.2、安...
SQL优化不会？推荐4 款工具
大数据星球-浪尖
09-17 563
转自：jianshu.com/p/cb2be017d5a9对于正在运行的mysql性能如何？参数设置的是否合理？账号设置的是否存在安全隐患？是否了然于胸？俗话说工欲善其事，必先利其器，...
mysql配置内核参数_MySQL核心参数优化（IO优化）配置示例
weixin_28864167的博客
01-19 592
1、测算单位时间内产生的redo log大小先执行压测[root@cnbugs1 ~]# cd /usr/local/tpcc-mysql/[root@cnbugs1 tpcc-mysql]# ./tpcc_start -h127.0.0.1 -P 3306 -d tpcc -u root -p Aa123456 -w 10 -c 1 -r 300 -l 200测试结果 (all must be ...
会话和锁信息查询视图 | 全方位认识 sys 系统库
老叶茶馆
11-30 467
在上一篇《等待事件统计视图 | 全方位认识 sys 系统库》中，我们介绍了sys 系统库中的等待事件统计视图，本期的内容先给大家介绍会话信息和锁等待信息查询视图，通过这些视图我们可以清晰...
MySQL 5.7版Windows平台安装包
资源摘要信息:"MySQL是一个广泛使用的开源关系型数据库管理系统（RDBMS），由瑞典MySQL AB公司开发，后来被Sun ...通过解压缩此文件，用户可以开始MySQL 5.7的安装过程，设置数据库服务器，以及进行后续的配置和使用。
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

淡定波007

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值