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服务重启，该数据库所有的表数据库也会被清空。

三、阶段事件记录表

阶段事件记录表，记录语句执行的阶段事件的表 show tables like '%stage%';

events_stages_current
events_stages_history
events_stages_history_long
events_stages_summary_by_account_by_event_name
events_stages_summary_by_host_by_event_name
events_stages_summary_by_thread_by_event_name
events_stages_summary_by_user_by_event_name
events_stages_summary_global_by_event_name

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

第一种：修改MySQL配置

首先确保 MySQL 实例已启用 Performance Schema。在 MySQL 配置文件（如 my.cnf 或 my.ini）中,增加如下行：

performance-schema-instrument='stage/%=ON'
performance-schema-consumer-events-stages-current=ON
performance-schema-consumer-events-stages-history=ON
performance-schema-consumer-events-stages-history-long=ON

如果需要禁用的话把ON 修改为 OFF,或者直接注释掉，具体如下：

performance-schema-instrument='stage/%=OFF'
performance-schema-consumer-events-stages-current=OFF
performance-schema-consumer-events-stages-history=OFF
performance-schema-consumer-events-stages-history-long=OFF

注意：如果通过配置文件修改的话，当MySQL服务正常启动后，默认就是开启状态，大家可以可以通过修改配置表手动去关闭。

第二种：直接更新配置表

更新setup_instruments和setup_consumers表 ，启用日志，就会实时生效。

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME like 'stage/%';
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE 'events_stages%';

禁用的话把YES修改为NO，具体如下：

UPDATE performance_schema.setup_instruments
SET ENABLED = 'NO', TIMED = 'NO'
WHERE NAME like 'stage/%';
UPDATE performance_schema.setup_consumers
SET ENABLED = 'NO'
WHERE NAME LIKE 'events_stages%';

注意：开启这个功能主要是为了测试使用，启用后会影响数据库的性能，建议生产环境不要开启，如果是排查问题的话，开启后，请及时关闭。

五、等待事件表介绍

5.1 events_stages_current

events_stages_current主要记录当前正在执行的语句的执行过程中所经过的各个阶段的统计信息。

查询示例

SELECT * from events_stages_current ;

输出结果

-- 输出结果
THREAD_ID	EVENT_ID	END_EVENT_ID	EVENT_NAME	SOURCE	TIMER_START	TIMER_END	TIMER_WAIT	WORK_COMPLETED	WORK_ESTIMATED	NESTING_EVENT_ID	NESTING_EVENT_TYPE
29	465		stage/sql/Sending data		882707998400000	882708021000000	22600000			455	STATEMENT

字段介绍

• THREAD_ID：执行当前语句的线程 ID。
• EVENT_ID：当前阶段的事件 ID。
• END_EVENT_ID：当前阶段结束时的事件 ID。
• EVENT_NAME：当前阶段的名称。
• SOURCE：当前阶段的源。
• TIMER_START：当前阶段开始时的时间戳。
• TIMER_END：当前阶段结束时的时间戳。
• TIMER_WAIT：当前阶段等待的时间。
• LOCK_TIME：当前阶段持有锁的时间。
• SQL_TEXT：当前语句的 SQL 文本。
• DIGEST：当前语句的摘要。
• DIGEST_TEXT：当前语句的摘要文本。
• CURRENT_SCHEMA：当前执行的语句所在的数据库名称。
• OBJECT_TYPE：当前语句操作的对象类型。
• OBJECT_SCHEMA：当前语句操作的对象所在的数据库名称。
• OBJECT_NAME：当前语句操作的对象名称。
• OBJECT_INSTANCE_BEGIN：当前语句操作的对象实例的起始位置。
• MYSQL_ERRNO：当前语句执行过程中出现的错误码。
• RETURNED_SQLSTATE：当前语句执行过程中返回的 SQL 状态。

5.2 events_stages_history

events_stages_history主要记录每个线程已结束的最新阶段事件个数默认为10,个数可自定义。

查询示例

 SELECT * from events_stages_history ;

输出结果

THREAD_ID	EVENT_ID	END_EVENT_ID	EVENT_NAME	SOURCE	TIMER_START	TIMER_END	TIMER_WAIT	WORK_COMPLETED	WORK_ESTIMATED	NESTING_EVENT_ID	NESTING_EVENT_TYPE
28	76	76	stage/sql/checking permissions		876268077700000	876268084900000	7200000			39	STATEMENT
28	77	77	stage/sql/Sending data		876268084900000	876268139300000	54400000			39	STATEMENT
28	78	78	stage/sql/end		876268139300000	876268139900000	600000			39	STATEMENT
29	595	595	stage/sql/executing		1113927994300000	1113927996400000	2100000			586	STATEMENT

字段介绍

字段参考events_stages_current介绍。

5.3 events_stages_history_long

events_stages_history_long表主要记录所有线程中全局已结束的最新阶段事件N条记录（可自定义）。阶段事件在结束之前不会添加到表中。当表已满时，添加新行时，最旧的行将被丢弃，无论哪个行生成了哪一个线程。

查询示例

SELECT * from events_stages_history_long ;

输出结果

THREAD_ID	EVENT_ID	END_EVENT_ID	EVENT_NAME	SOURCE	TIMER_START	TIMER_END	TIMER_WAIT	WORK_COMPLETED	WORK_ESTIMATED	NESTING_EVENT_ID	NESTING_EVENT_TYPE
0	0	34	stage/sql/updating		191081264800000	191081284400000	19600000			34	STATEMENT
27	35	35	stage/sql/end		191081284400000	191081287600000	3200000			34	STATEMENT
27	36	36	stage/sql/query end		191081287600000	191081289800000	2200000			34	STATEMENT

字段介绍

• USER_VAR_EVENT_ID：当前执行阶段涉及到的用户自定义变量事件 ID。
• TIMER_WAIT_MS：当前执行阶段等待的时间（单位为毫秒）。
• TIMER_READS：当前执行阶段读取的次数。
• TIMER_READS_MS：当前执行阶段读取的时间（单位为毫秒）。
• TIMER_READS_ROWS：当前执行阶段读取的行数。
• TIMER_READS_AVG_US：当前执行阶段每次读取所花费的平均时间（单位为微秒）。
• TIMER_READS_AVG_ROW_LENGTH：当前执行阶段每次读取的平均行长度。
• TIMER_WAITS：当前执行阶段等待的次数。
• TIMER_WAITS_MS：当前执行阶段等待的时间（单位为毫秒）。
• TIMER_WAITS_ROWS：当前执行阶段等待的行数。
• TIMER_WAITS_AVG_US：当前执行阶段每次等待所花费的平均时间（单位为微秒）。
• TIMER_WAITS_AVG_ROW_LENGTH：当前执行阶段每次等待的平均行长度。
• SPINS：当前执行阶段自旋的次数。
• SPINS_MS：当前执行阶段自旋的时间（单位为毫秒）。
• SPINS_AVG_US：当前执行阶段每次自旋所花费的平均时间（单位为微秒）。
• BACKOFFS：当前执行阶段后退的次数。
• BACKOFFS_MS：当前执行阶段后退的时间（单位为毫秒）。
• BACKOFFS_AVG_US：当前执行阶段每次后退所花费的平均时间（单位为微秒）。
• THREADS：当前执行阶段涉及到的线程数。
• THREADS_MS：当前执行阶段涉及到的线程所花费的时间（单位为毫秒）。
• OS_WAITS：当前执行阶段等待操作系统的次数。
• OS_WAITS_MS：当前执行阶段等待操作系统的时间（单位为毫秒）。
• OS_WAITS_AVG_US：当前执行阶段每次等待操作系统所花费的平均时间（单位为微秒）。
• QUERIES：当前执行阶段涉及到的查询数。
• QUERIES_MS：当前执行阶段涉及到的查询所花费的时间（单位为毫秒）。
• NOTES：当前执行阶段的注释。

5.4 events_stages_summary_by_account_by_event_name

用于记录每个用户和事件类型的事件计数和计时信息

查询示例

 SELECT * from events_stages_summary_by_account_by_event_name ;

输出结果

字段介绍

• USER：执行事件的用户。
• HOST：执行事件的主机。
• EVENT_NAME：事件名称。
• COUNT_STAR：事件的计数。
• SUM_TIMER_WAIT：事件的计时总和。
• MIN_TIMER_WAIT：事件的最小计时。
• AVG_TIMER_WAIT：事件的平均计时。
• MAX_TIMER_WAIT：事件的最大计时。

5.5 events_stages_summary_by_host_by_event_name

events_stages_summary_by_host_by_event_name用于记录每个主机和事件类型的事件计数和计时信息

查询示例

 SELECT * from events_stages_summary_by_host_by_event_name ;

输出结果

字段介绍

• HOST：执行事件的主机。
• EVENT_NAME：事件名称。
• COUNT_STAR：事件的计数。
• SUM_TIMER_WAIT：事件的计时总和。
• MIN_TIMER_WAIT：事件的最小计时。
• AVG_TIMER_WAIT：事件的平均计时。
• MAX_TIMER_WAIT：事件的最大计时。

5.6 events_stages_summary_by_thread_by_event_name

用于记录每个线程 ID 和事件类型的事件计数和计时信息.

查询示例

 SELECT * from events_stages_summary_by_thread_by_event_name ;

输出结果

字段介绍

• THREAD_ID：执行事件的线程 ID。
• EVENT_NAME：事件名称。
• COUNT_STAR：事件的计数。
• SUM_TIMER_WAIT：事件的计时总和。
• MIN_TIMER_WAIT：事件的最小计时。
• AVG_TIMER_WAIT：事件的平均计时。
• MAX_TIMER_WAIT：事件的最大计时。

5.7 events_stages_summary_by_user_by_event_name

用于记录每个用户和事件类型的事件计数和计时信息

SELECT * from events_stages_summary_by_user_by_event_name ;

输出结果

字段介绍

• USER：执行事件的用户。
• EVENT_NAME：事件名称。
• COUNT_STAR：事件的计数。
• SUM_TIMER_WAIT：事件的计时总和。
• MIN_TIMER_WAIT：事件的最小计时。
• AVG_TIMER_WAIT：事件的平均计时。
• MAX_TIMER_WAIT：事件的最大计时。

5.8 events_stages_summary_global_by_event_name

用于记录每个事件类型的全局事件计数和计时信息

查询示例

 SELECT * from events_stages_summary_global_by_event_name ;

输出结果

字段介绍

• EVENT_NAME：事件名称。
• COUNT_STAR：事件的计数。
• SUM_TIMER_WAIT：事件的计时总和。
• MIN_TIMER_WAIT：事件的最小计时。
• AVG_TIMER_WAIT：事件的平均计时。
• MAX_TIMER_WAIT：事件的最大计时。

'

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