MySQL内置数据库performance_schema详解(二)：等待事件记录表介绍

一、performanceschema 简介

performance_schema 是 MySQL 数据库中的一个内置的系统数据库，最早从MySQL5.5版本产生，这个数据库主要用于收集和存储与数据库性能相关的统计信息和指标。

使用它可以帮助运维人员对数据库性能监控、调优和故障排查提供依据。

二、performanceschema 特点

• performanceschema数据库是mysql5.5及后续的版本才会有，并且在MySQL5.7当中默认启用，可以在MySQL配置参数里面关闭，可以节约一部分性能的消耗。
• performanceschema数据库中的表使用performanceschema存储引擎管理，主要是监控数据库运行过程中的性能数据的收集。
• performanceschema通过监视server的事件（函数调用、操作系统等待、SQL语句执行阶段等）来实现监视server内部运行情况。
• performanceschema中的事件与写入二进制日志中的事件、事件计划调度程序，事件记录的是server执行某些活动对某些资源的消耗、耗时、这些活动执行的次数等情况。
• performanceschema中的事件只记录在本地server的performanceschema中，表中数据发生变化时不会被写入binlog中，也不会通过复制机制被复制到其他server中。
• performanceschema存储引擎使用server源代码中的“检测点”来实现事件数据的收集。

收集的事件数据存储在performanceschema数据库的表中，支持select进行查询。

• performanceschema表数据非持久化存储，而是保存在内存当中，MySQL服务重启，该数据库所有的表数据库也会被清空。

三、等待事件记录表介绍

3.1 等待事件的数据表列表

 show tables like '%wait%';

查询数据表如下：

events_waits_current
events_waits_history
events_waits_history_long
events_waits_summary_by_account_by_event_name
events_waits_summary_by_host_by_event_name
events_waits_summary_by_instance
events_waits_summary_by_thread_by_event_name
events_waits_summary_by_user_by_event_name
events_waits_summary_global_by_event_name
table_io_waits_summary_by_index_usage
table_io_waits_summary_by_table
table_lock_waits_summary_by_table

3.2 开启等待事件数据的插入功能

默认等待事件的功能是未启用状态，大家可以按照下面的方法启动。

首先确保 MySQL 实例已启用 Performance Schema。在 MySQL 配置文件（如 my.cnf 或 my.ini）中，确保以下行未被注释（如果被注释，请取消注释并重启 MySQL）

performance_schema=ON

首先开启events_waits_current启用状态

#先开启events_waits_current的状态
SELECT * from  setup_consumers where name = 'events_waits_current';
UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'events_waits_current';
--查看等待事件的启用状态
SELECT * from  setup_consumers where name = 'events_waits_history';

说明：其他等待事件的数据表开启方式类似。

注意事项：开启这个功能主要是为了测试使用，启用 Performance Schema 会增加系统的性能开销。因此，建议仅在需要进行性能分析和瓶颈排查时启用它。在完成性能分析后，可以考虑禁用 Performance Schema 以减少性能开销。

3.3 events_waits_current 表

events_waits_current表主当前正在等待的事件数量。这些事件可能是等待锁、等待 I/O 操作完成或等待其他资源。

查询示例

select * from events_waits_current; 

输出

THREAD_ID	EVENT_ID	END_EVENT_ID	EVENT_NAME	SOURCE	TIMER_START	TIMER_END	TIMER_WAIT	SPINS	OBJECT_SCHEMA	OBJECT_NAME	INDEX_NAME	OBJECT_TYPE	OBJECT_INSTANCE_BEGIN	NESTING_EVENT_ID	NESTING_EVENT_TYPE	OPERATION	NUMBER_OF_BYTES	FLAGS
27	159	159	idle		1530341778300000	1530346529100000	4750800000						0			idle		

字段说明

• THREAD_ID，EVENT_ID：线程ID和当前事件ID。THREAD_ID和EVENT_ID两个字段确定唯一一条数据记录。
• END_EVENT_ID：当一个事件正在执行时该列值为NULL，当事件执行结束时把该事件的ID更新到该列
• EVENT_NAME：产生事件的instruments名称，字段来源setup_instruments表的NAME字段值
• SOURCE：产生该事件的instruments所在的源文件名称以及检测到该事件发生点的代码行号。
• TIMER_START，TIMER_END，TIMER_WAIT：事件的开始、结束、经过时间，单位为皮秒， 1,000,000,000 皮秒 = 1毫秒。
• SPINS：互斥量和自旋次数。如果该列值为NULL，则表示代码中没有使用自旋或自旋没有被监控
• OBJECT_SCHEMA，OBJECT_NAME，OBJECT_TYPE：正在被执行的对象的schema、名称和类型
• OBJECT_INSTANCE_BEGIN：内存中的地址
• NESTING_EVENT_ID：表示父事件的EVENT_ID
• NESTING_EVENT_TYPE：父事件的事件类型，有效值有：TRANSACTION，STATEMENT，STAGE或WAIT。
• OPERATION：执行的操作类型，如：lock、read、write、timed_wait
• NUMBER_OF_BYTES：操作读取或写入的字节数或行数。对于文件IO等待，该值表示字节数；对于表I/O等待（wait/io/table/sql/handler instruments事件），该值表示行数。
• FLAGS：暂无介绍

3.4 events_waits_history 表

events_waits_history表包含每个线程最近的N个等待事件。 数据库服务启动时，N的值会自动调整。 也可以在服务启动之前调整系统参数performance_schema_events_waits_history_size的值。 等待事件需要执行结束时才被添加到events_waits_history表中（未结束时保存在events_waits_current表）。当添加新事件到events_waits_history表时，如果该表已满(默认每个线程为10条记录)，则会自动丢弃每个线程较旧的事件记录。

开启events_waits_history的状态

-- 然后开启events_waits_history的状态
SELECT * from  setup_consumers where name = 'events_waits_history';
UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'events_waits_history';
-- 重新查看等待事件的启用状态
SELECT * from  setup_consumers where name = 'events_waits_history';

查询示例

SELECT * from events_waits_history ;

输出

THREAD_ID	EVENT_ID	END_EVENT_ID	EVENT_NAME	SOURCE	TIMER_START	TIMER_END	TIMER_WAIT	SPINS	OBJECT_SCHEMA	OBJECT_NAME	INDEX_NAME	OBJECT_TYPE	OBJECT_INSTANCE_BEGIN	NESTING_EVENT_ID	NESTING_EVENT_TYPE	OPERATION	NUMBER_OF_BYTES	FLAGS
27	179	179	idle		1592433140500000	1592433743900000	603400000						0			idle		
27	181	181	idle		1592434364600000	1673137014700000	80702650100000						0			idle		
27	183	183	idle		1673137201900000	1673137331200000	129300000						0			idle		

字段：参考events_waits_current

3.5 events_waits_history_long 表

events_waits_history_long表包含所有线程中全局已结束的最新等待事件（默认所有线程的总记录数为10000）。等待事件在结束之前不会添加到表中。当表已满时，添加新行时，最旧的行将被丢弃，无论哪个行生成了哪一个线程。

开启events_waits_history_long 状态

UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'events_waits_history_long';

查询示例

select * from events_waits_history_long ; 

输出

3.6 events_waits_summary_by_account_by_event_name

该表的主要作用是以账户、主机、事件名称为维度的等待事件统计数据。

select * from events_waits_summary_by_account_by_event_name ; 

输出

字段说明

• USER：等待事件所属的用户。
• HOST：等待事件的来源主机。
• EVENT_NAME：等待事件的名称，如 'wait/io/file/sql/table_lock'。
• COUNT_STAR：发生该等待事件的次数。
• SUM_TIMER_WAIT：等待事件的总计时器等待时间。
• MIN_TIMER_WAIT：等待事件的最小计时器等待时间。
• AVG_TIMER_WAIT：等待事件的平均计时器等待时间。
• MAX_TIMER_WAIT：等待事件的最大计时器等待时间。

3.7 events_waits_summary_by_host_by_event_name

该表的主要作用是以主机、事件名称为维度的等待事件统计数据。

select * from events_waits_summary_by_host_by_event_name ; 

输出

字段说明

字段说明参考events_waits_summary_by_account_by_event_name表，没有USER字段。

3.8 events_waits_summary_by_instance

该表的主要作用是以等待事件为维度的等待事件统计数据。

select * from events_waits_summary_by_instance ; 

输出

字段说明

• EVENT_NAME：等待事件的名称，如 'wait/io/file/innodb/innodb_data_file'。
• COUNT_STAR：发生该等待事件的次数。
• SUM_TIMER_WAIT：等待事件的总计时器等待时间。
• MIN_TIMER_WAIT：等待事件的最小计时器等待时间。
• AVG_TIMER_WAIT：等待事件的平均计时器等待时间。
• MAX_TIMER_WAIT：等待事件的最大计时器等待时间。

3.9 events_waits_summary_by_thread_by_event_name

该表用于提供按线程和事件名称为维度汇总的等待事件统计信息。

select * from events_waits_summary_by_thread_by_event_name ; 

输出

字段说明

• THREAD_ID：等待事件所属的线程 ID。
• EVENT_NAME：等待事件的名称，如 'wait/io/file/sql/table_lock'。
• COUNT_STAR：发生该等待事件的次数。
• SUM_TIMER_WAIT：等待事件的总计时器等待时间。
• MIN_TIMER_WAIT：等待事件的最小计时器等待时间。
• AVG_TIMER_WAIT：等待事件的平均计时器等待时间。
• MAX_TIMER_WAIT：等待事件的最大计时器等待时间。

3.10 events_waits_summary_by_user_by_event_name

该表用于提供按用户和事件名称为维度汇总的等待事件统计信息。

select * from events_waits_summary_by_user_by_event_name ; 

输出

字段说明

• USER：等待事件所属的用户。
• EVENT_NAME：等待事件的名称，如 'wait/io/file/sql/table_lock'。
• COUNT_STAR：发生该等待事件的次数。
• SUM_TIMER_WAIT：等待事件的总计时器等待时间。
• MIN_TIMER_WAIT：等待事件的最小计时器等待时间。
• AVG_TIMER_WAIT：等待事件的平均计时器等待时间。
• MAX_TIMER_WAIT：等待事件的最大计时器等待时间。

3.11 events_waits_summary_global_by_event_name

提供了按事件名称分类的全局等待事件的汇总信息。

select * from events_waits_summary_global_by_event_name ; 

输出

字段说明

• EVENT_NAME：等待事件的名称，如 'wait/io/file/sql/table_lock'。
• COUNT_STAR：发生该等待事件的次数。
• SUM_TIMER_WAIT：等待事件的总计时器等待时间。
• MIN_TIMER_WAIT：等待事件的最小计时器等待时间。
• AVG_TIMER_WAIT：等待事件的平均计时器等待时间。
• MAX_TIMER_WAIT：等待事件的最大计时器等待时间。

3.12 table_io_waits_summary_by_index_usage

该表保存了关于每个表和索引的I/O等待事件的统计信息。

select * from table_io_waits_summary_by_index_usage ;

输出

字段说明

• OBJECT_SCHEMA: 表所属的数据库模式（schema）名称。
• OBJECT_NAME: 表的名称。
• INDEX_NAME: 索引的名称。如果等待事件与表而不是特定索引相关，则该字段的值为NULL。
• COUNT_STAR: 等待事件的总次数。
• SUM_TIMER_WAIT: 等待事件的总等待时间。
• MIN_TIMER_WAIT: 等待事件的最小等待时间。
• AVG_TIMER_WAIT: 等待事件的平均等待时间。
• MAX_TIMER_WAIT: 等待事件的最大等待时间。
• COUNT_READ: 读取等待事件的总次数。
• SUM_TIMER_READ: 读取等待事件的总等待时间。
• MIN_TIMER_READ: 读取等待事件的最小等待时间。
• AVG_TIMER_READ: 读取等待事件的平均等待时间。
• MAX_TIMER_READ: 读取等待事件的最大等待时间。
• COUNT_WRITE: 写入等待事件的总次数。
• SUM_TIMER_WRITE: 写入等待事件的总等待时间。
• MIN_TIMER_WRITE: 写入等待事件的最小等待时间。
• AVG_TIMER_WRITE: 写入等待事件的平均等待时间。
• MAX_TIMER_WRITE: 写入等待事件的最大等待时间。

3.13 table_io_waits_summary_by_table

该表主要提供了按表分类的I/O等待事件的汇总信息。这个对于查询比较慢表数据表来说还是非常实用的，数据库可以通过这个查询找到瓶颈的数据表。

select * from table_io_waits_summary_by_table ; 

输出

字段说明

• OBJECT_SCHEMA: 表所属的数据库模式（schema）名称。
• OBJECT_NAME: 表的名称。
• COUNT_STAR: 等待事件的总次数。
• SUM_TIMER_WAIT: 等待事件的总等待时间。
• MIN_TIMER_WAIT: 等待事件的最小等待时间。
• AVG_TIMER_WAIT: 等待事件的平均等待时间。
• MAX_TIMER_WAIT: 等待事件的最大等待时间。
• COUNT_READ: 读取等待事件的总次数。
• SUM_TIMER_READ: 读取等待事件的总等待时间。
• MIN_TIMER_READ: 读取等待事件的最小等待时间。
• AVG_TIMER_READ: 读取等待事件的平均等待时间。
• MAX_TIMER_READ: 读取等待事件的最大等待时间。
• COUNT_WRITE: 写入等待事件的总次数。
• SUM_TIMER_WRITE: 写入等待事件的总等待时间。
• MIN_TIMER_WRITE: 写入等待事件的最小等待时间。
• AVG_TIMER_WRITE: 写入等待事件的平均等待时间。
• MAX_TIMER_WRITE: 写入等待事件的最大等待时间。

3. 14 table_lock_waits_summary_by_table

该表主要提供了按表分类的表锁等待事件的汇总信息。通过该表你可以获取按表分类的表锁等待事件的汇总信息，以了解哪些表在数据库访问中导致了最多的表锁等待。

select * from table_lock_waits_summary_by_table ; 

输出

我正在参与2023腾讯技术创作特训营第四期有奖征文，快来和我瓜分大奖！