MySQL内置数据库performance_schema详解(四)：事务事件记录表介绍
原创MySQL内置数据库performance_schema详解(四)：事务事件记录表介绍
原创
IT技术分享社区
发布于 2023-12-24 08:17:18
发布于 2023-12-24 08:17:18
一、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 '%transaction%';
events_transactions_current
events_transactions_history
events_transactions_history_long
events_transactions_summary_by_account_by_event_name
events_transactions_summary_by_host_by_event_name
events_transactions_summary_by_thread_by_event_name
events_transactions_summary_by_user_by_event_name
events_transactions_summary_global_by_event_name四、开启事务事件数据的插入功能
第一种：修改MySQL配置
首先确保 MySQL 实例已启用 Performance Schema。在 MySQL 配置文件（如 my.cnf 或 my.ini）中,增加如下行：
#设置setup_instruments表收集transaction event
performance-schema-instrument='transaction=ON'
#开启events_transactions_current表存储当前连接线程执行的transaction event信息
performance-schema-consumer-events-transactions-current=ON
#开启events_transactions_history表默认存储每个线程最近10条transaction event信息
performance-schema-consumer-events-transactions-history=ON
#开启events_statements_history_long表默认存储最近10000条语句event信息。
performance-schema-consumer-events-transactions-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服务正常启动后，默认就是开启状态，大家可以可以通过修改配置表手动去关闭。
第二种 直接修改配置表
-- 开启
update setup_consumers set ENABLED='YES' where name like 'events_transactions%';
update setup_instruments set ENABLED='YES',TIMED='YES' where name like '%events_transactions%';
-- 禁用
update setup_consumers set ENABLED='NO' where name like 'events_transactions%';
update setup_instruments set ENABLED='NO',TIMED='NO' where name like '%events_transactions%';五、事务事件表介绍
events_transactions_current
events_transactions_current表包含当前事务事件信息，每个线程只保留一行最近事务的事务事件 。
查询示例
SELECT * from events_transactions_current ;输出
THREAD_ID EVENT_ID END_EVENT_ID EVENT_NAME STATE TRX_ID GTID XID_FORMAT_ID XID_GTRID XID_BQUAL XA_STATE SOURCE TIMER_START TIMER_END TIMER_WAIT ACCESS_MODE ISOLATION_LEVEL AUTOCOMMIT NUMBER_OF_SAVEPOINTS NUMBER_OF_ROLLBACK_TO_SAVEPOINT NUMBER_OF_RELEASE_SAVEPOINT OBJECT_INSTANCE_BEGIN NESTING_EVENT_ID NESTING_EVENT_TYPE
1 5 5 transaction COMMITTED 283972571023152 AUTOMATIC 5764005600000 5764024900000 19300000 READ ONLY READ COMMITTED YES 0 0 0 
字段介绍
- THREAD_ID，EVENT_ID：与事件关联的线程号和事件启动时的事件编号，可以使用THREAD_ID和EVENT_ID列值来唯一标识该行，这两行的值作为组合条件时不会出现相同的数据行
- END_EVENT_ID：当一个事件开始执行时，对应行记录的该列值被设置为NULL，当一个事件执行结束时，对应的行记录的该列值被更新为该事件的ID
- EVENT_NAME：收集该事务事件的instruments的名称。来自setup_instruments表的NAME列值
- STATE：当前事务状态。有效值为：ACTIVE(执行了START TRANSACTION或BEGIN语句之后，事务未提交或未回滚之前)、COMMITTED(执行了COMMIT之后)、ROLLED BACK(执行了ROLLBACK语句之后)
- TRX_ID：未使用，字段值总是为NULL
- GTID：包含gtid_next系统变量的值，其值可能是格式为：UUID:NUMBER的GTID，也可能是：ANONYMOUS、AUTOMATIC。对于AUTOMATIC列值的事务事件，GTID列在事务提交和对应事务的GTID实际分配时都会进行更改(如果gtid_mode系统变量为ON或ON_PERMISSIVE，则GTID列将更改为事务的GTID，如果gtid_mode为OFF或OFF_PERMISSIVE，则GTID列将更改为ANONYMOUS)
- XID_FORMAT_ID，XID_GTRID和XID_BQUAL：XA事务标识符的组件。关于XA事务语法详见链接：https://dev.mysql.com/doc/refman/5.7/en/xa-statements.html
- XA_STATE：XA事务的状态。有效值为：ACTIVE(执行了XA START之后，未执行其他后续XA语句之前)、IDLE(执行了XA END语句之后，未执行其他后续XA语句之前)、PREPARED(执行了XA PREPARE语句之后，未执行其他后续XA语句之前)、ROLLED BACK(执行了XA ROLLBACK语句之后，未执行其他后续XA语句之前)、COMMITTED(执行了XA COMMIT语句之后)
- SOURCE：源文件的名称及其用于检测该事件的代码位于源文件中的行号，您可以检查源代码来确定涉及的代码
- TIMER_START，TIMER_END，TIMER_WAIT：事件的时间信息。这些值的单位是皮秒(万亿分之一秒)。 TIMER_START和TIMER_END值表示事件的开始时间和结束时间。TIMER_WAIT是事件执行消耗的时间(持续时间)如果事件未执行完成，则TIMER_END为当前时间，TIMER_WAIT为当前为止所经过的时间(TIMER_END - TIMER_START)
- 如果监视仪器配置表setup_instruments中对应的监视器TIMED字段被设置为 NO，则不会收集该监视器的时间信息，那么对于该事件采集的信息记录中，TIMER_START，TIMER_END和TIMER_WAIT字段值均为NULL
- ACCESS_MODE：事务访问模式。有效值为：READ ONLY或READ WRITE
- ISOLATION_LEVEL：事务隔离级别。有效值为：REPEATABLE READ、READ COMMITTED、READ UNCOMMITTED、SERIALIZABLE
- AUTOCOMMIT：在事务开始时是否启用了自动提交模式，如果启用则为YES，没有启用则为NO
- NUMBER_OF_SAVEPOINTS，NUMBER_OF_ROLLBACK_TO_SAVEPOINT，NUMBER_OF_RELEASE_SAVEPOINT：在事务内执行的SAVEPOINT，ROLLBACK TO SAVEPOINT和RELEASE SAVEPOINT语句的数量
- OBJECT_INSTANCE_BEGIN：未使用，字段值总是为NULL
- NESTING_EVENT_ID：嵌套事务事件的父事件EVENT_ID值
- NESTING_EVENT_TYPE：嵌套事件类型。有效值为：TRANSACTION、STATEMENT、STAGE、WAIT (由于事务无法嵌套，因此该列值不会出现TRANSACTION)
events_transactions_history
events_transactions_history主要记录每个线程最近10行事务事件信息。
查询
SELECT * from events_transactions_history ;输出
THREAD_ID EVENT_ID END_EVENT_ID EVENT_NAME STATE TRX_ID GTID XID_FORMAT_ID XID_GTRID XID_BQUAL XA_STATE SOURCE TIMER_START TIMER_END TIMER_WAIT ACCESS_MODE ISOLATION_LEVEL AUTOCOMMIT NUMBER_OF_SAVEPOINTS NUMBER_OF_ROLLBACK_TO_SAVEPOINT NUMBER_OF_RELEASE_SAVEPOINT OBJECT_INSTANCE_BEGIN NESTING_EVENT_ID NESTING_EVENT_TYPE
1 1 1 transaction COMMITTED 283972571023152 AUTOMATIC 5734174300000 5734208700000 34400000 READ ONLY READ COMMITTED YES 0 0 0
1 2 2 transaction COMMITTED 283972571023152 AUTOMATIC 5740625800000 5741235000000 609200000 READ WRITE REPEATABLE READ YES 0 0 0
1 3 3 transaction COMMITTED 283972571023152 AUTOMATIC 5749747100000 5749792000000 44900000 READ WRITE REPEATABLE READ YES 0 0 0 字段介绍
与events_transactions_current表一致
events_transactions_history_long
events_transactions_history_long主要记录全局最近10000行事务事件信息
查询
SELECT * from events_transactions_history_long ;输出
THREAD_ID EVENT_ID END_EVENT_ID EVENT_NAME STATE TRX_ID GTID XID_FORMAT_ID XID_GTRID XID_BQUAL XA_STATE SOURCE TIMER_START TIMER_END TIMER_WAIT ACCESS_MODE ISOLATION_LEVEL AUTOCOMMIT NUMBER_OF_SAVEPOINTS NUMBER_OF_ROLLBACK_TO_SAVEPOINT NUMBER_OF_RELEASE_SAVEPOINT OBJECT_INSTANCE_BEGIN NESTING_EVENT_ID NESTING_EVENT_TYPE
1 1 1 transaction COMMITTED 283972571023152 AUTOMATIC 5734174300000 5734208700000 34400000 READ ONLY READ COMMITTED YES 0 0 0
1 2 2 transaction COMMITTED 283972571023152 AUTOMATIC 5740625800000 5741235000000 609200000 READ WRITE REPEATABLE READ YES 0 0 0
1 3 3 transaction COMMITTED 283972571023152 AUTOMATIC 5749747100000 5749792000000 44900000 READ WRITE REPEATABLE READ YES 0 0 0
1 4 4 transaction COMMITTED 283972571023152 AUTOMATIC 5760785800000 5760801100000 15300000 READ ONLY READ COMMITTED YES 0 0 0 
字段介绍
与events_transactions_current表一致
events_transactions_summary_by_account_by_event_name（主机+用户+事件）
events_transactions_summary_by_account_by_event_name用于按照列EVENT_NAME、USER、HOST进行分组事件信息。
查询
SELECT * from events_transactions_summary_by_account_by_event_name ;输出
USER HOST EVENT_NAME COUNT_STAR SUM_TIMER_WAIT MIN_TIMER_WAIT AVG_TIMER_WAIT MAX_TIMER_WAIT COUNT_READ_WRITE SUM_TIMER_READ_WRITE MIN_TIMER_READ_WRITE AVG_TIMER_READ_WRITE MAX_TIMER_READ_WRITE COUNT_READ_ONLY SUM_TIMER_READ_ONLY MIN_TIMER_READ_ONLY AVG_TIMER_READ_ONLY MAX_TIMER_READ_ONLY
transaction 5 723100000 15300000 144600000 609200000 2 654100000 44900000 327000000 609200000 3 69000000 15300000 23000000 34400000
root localhost transaction 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0字段介绍
- USER：MySQL 用户名
- HOST：MySQL 主机名。
- EVENT_NAME：事件名称，包括 "transaction"、"commit" 和 "rollback"。
- COUNT_STAR：事件计数。
- SUM_TIMER_WAIT：事件等待时间之和。
- MIN_TIMER_WAIT：事件等待最短时间。
- AVG_TIMER_WAIT：事件等待平均时间。
- MAX_TIMER_WAIT：事件等待最长时间。
- COUNT_READ_WRITE：读写计数
- SUM_TIMER_READ_WRITE：读写计数时间之和
- MIN_TIMER_READ_WRITE：读写计数最短时间
- AVG_TIMER_READ_WRITE：读写计数平均时间
- MAX_TIMER_READ_WRITE：读写计数最长时间
- COUNT_READ_ONLY：只读计数
- SUM_TIMER_READ_ONLY：只读计数时间之和
- MIN_TIMER_READ_ONLY：只读计数最短时间
- AVG_TIMER_READ_ONLY：只读计数平均时间
- MAX_TIMER_READ_ONLY：只读计数最长时间
events_transactions_summary_by_host_by_event_name（主机+事件）
events_transactions_summary_by_account_by_event_name用于按照列EVENT_NAME、HOST进行分组事件信息。
查询
SELECT * from events_transactions_summary_by_host_by_event_name ;输出
HOST EVENT_NAME COUNT_STAR SUM_TIMER_WAIT MIN_TIMER_WAIT AVG_TIMER_WAIT MAX_TIMER_WAIT COUNT_READ_WRITE SUM_TIMER_READ_WRITE MIN_TIMER_READ_WRITE AVG_TIMER_READ_WRITE MAX_TIMER_READ_WRITE COUNT_READ_ONLY SUM_TIMER_READ_ONLY MIN_TIMER_READ_ONLY AVG_TIMER_READ_ONLY MAX_TIMER_READ_ONLY
transaction 5 723100000 15300000 144600000 609200000 2 654100000 44900000 327000000 609200000 3 69000000 15300000 23000000 34400000
localhost transaction 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0字段介绍
- HOST：MySQL 主机名。
- EVENT_NAME：事件名称，包括 "transaction"、"commit" 和 "rollback"。
- COUNT_STAR：事件计数。
- SUM_TIMER_WAIT：事件等待时间之和。
- MIN_TIMER_WAIT：事件等待最短时间。
- AVG_TIMER_WAIT：事件等待平均时间。
- MAX_TIMER_WAIT：事件等待最长时间。
- COUNT_READ_WRITE：读写计数
- SUM_TIMER_READ_WRITE：读写计数时间之和
- MIN_TIMER_READ_WRITE：读写计数最短时间
- AVG_TIMER_READ_WRITE：读写计数平均时间
- MAX_TIMER_READ_WRITE：读写计数最长时间
- COUNT_READ_ONLY：只读计数
- SUM_TIMER_READ_ONLY：只读计数时间之和
- MIN_TIMER_READ_ONLY：只读计数最短时间
- AVG_TIMER_READ_ONLY：只读计数平均时间
- MAX_TIMER_READ_ONLY：只读计数最长时间
events_transactions_summary_by_thread_by_event_name（线程+事件）
events_transactions_summary_by_thread_by_event_name 用于按照列线程名称、事件名称进行分组事件信息。
查询
SELECT * from events_transactions_summary_by_thread_by_event_name;输出
THREAD_ID EVENT_NAME COUNT_STAR SUM_TIMER_WAIT MIN_TIMER_WAIT AVG_TIMER_WAIT MAX_TIMER_WAIT COUNT_READ_WRITE SUM_TIMER_READ_WRITE MIN_TIMER_READ_WRITE AVG_TIMER_READ_WRITE MAX_TIMER_READ_WRITE COUNT_READ_ONLY SUM_TIMER_READ_ONLY MIN_TIMER_READ_ONLY AVG_TIMER_READ_ONLY MAX_TIMER_READ_ONLY
1 transaction 5 723100000 15300000 144600000 609200000 2 654100000 44900000 327000000 609200000 3 69000000 15300000 23000000 34400000
2 transaction 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
3 transaction 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0字段介绍
- THREAD_ID：线程 ID。
- EVENT_NAME：事件名称，包括 "transaction"、"commit" 和 "rollback"。
- COUNT_STAR：事件计数。
- SUM_TIMER_WAIT：事件等待时间之和。
- MIN_TIMER_WAIT：事件等待最短时间。
- AVG_TIMER_WAIT：事件等待平均时间。
- MAX_TIMER_WAIT：事件等待最长时间。
- COUNT_READ_WRITE：读写计数
- SUM_TIMER_READ_WRITE：读写计数时间之和
- MIN_TIMER_READ_WRITE：读写计数最短时间
- AVG_TIMER_READ_WRITE：读写计数平均时间
- MAX_TIMER_READ_WRITE：读写计数最长时间
- COUNT_READ_ONLY：只读计数
- SUM_TIMER_READ_ONLY：只读计数时间之和
- MIN_TIMER_READ_ONLY：只读计数最短时间
- AVG_TIMER_READ_ONLY：只读计数平均时间
- MAX_TIMER_READ_ONLY：只读计数最长时间
events_transactions_summary_by_user_by_event_name（用户+事件）
events_transactions_summary_by_user_by_event_name用于按照列用户、事件名称进行分组事件信息。
查询
SELECT * from events_transactions_summary_by_user_by_event_name;输出
USER EVENT_NAME COUNT_STAR SUM_TIMER_WAIT MIN_TIMER_WAIT AVG_TIMER_WAIT MAX_TIMER_WAIT COUNT_READ_WRITE SUM_TIMER_READ_WRITE MIN_TIMER_READ_WRITE AVG_TIMER_READ_WRITE MAX_TIMER_READ_WRITE COUNT_READ_ONLY SUM_TIMER_READ_ONLY MIN_TIMER_READ_ONLY AVG_TIMER_READ_ONLY MAX_TIMER_READ_ONLY
transaction 5 723100000 15300000 144600000 609200000 2 654100000 44900000 327000000 609200000 3 69000000 15300000 23000000 34400000
root transaction 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0字段介绍
- USER：MySQL 用户名。
- EVENT_NAME：事件名称，包括 "transaction"、"commit" 和 "rollback"。
- COUNT_STAR：事件计数。
- SUM_TIMER_WAIT：事件等待时间之和。
- MIN_TIMER_WAIT：事件等待最短时间。
- AVG_TIMER_WAIT：事件等待平均时间。
- MAX_TIMER_WAIT：事件等待最长时间。
- COUNT_READ_WRITE：读写计数
- SUM_TIMER_READ_WRITE：读写计数时间之和
- MIN_TIMER_READ_WRITE：读写计数最短时间
- AVG_TIMER_READ_WRITE：读写计数平均时间
- MAX_TIMER_READ_WRITE：读写计数最长时间
- COUNT_READ_ONLY：只读计数
- SUM_TIMER_READ_ONLY：只读计数时间之和
- MIN_TIMER_READ_ONLY：只读计数最短时间
- AVG_TIMER_READ_ONLY：只读计数平均时间
- MAX_TIMER_READ_ONLY：只读计数最长时间
events_transactions_summary_global_by_event_name(全局事件)
events_transactions_summary_global_by_event_name用于用于对全局的事务事件进行汇总.
查询
SELECT * from events_transactions_summary_global_by_event_name;输出
EVENT_NAME COUNT_STAR SUM_TIMER_WAIT MIN_TIMER_WAIT AVG_TIMER_WAIT MAX_TIMER_WAIT COUNT_READ_WRITE SUM_TIMER_READ_WRITE MIN_TIMER_READ_WRITE AVG_TIMER_READ_WRITE MAX_TIMER_READ_WRITE COUNT_READ_ONLY SUM_TIMER_READ_ONLY MIN_TIMER_READ_ONLY AVG_TIMER_READ_ONLY MAX_TIMER_READ_ONLY
transaction 5 723100000 15300000 144600000 609200000 2 654100000 44900000 327000000 609200000 3 69000000 15300000 23000000 34400000字段介绍
- EVENT_NAME：事件名称，包括 "transaction"、"commit" 和 "rollback"。
- COUNT_STAR：事件计数。
- SUM_TIMER_WAIT：事件等待时间之和。
- MIN_TIMER_WAIT：事件等待最短时间。
- AVG_TIMER_WAIT：事件等待平均时间。
- MAX_TIMER_WAIT：事件等待最长时间。
- COUNT_READ_WRITE：读写计数
- SUM_TIMER_READ_WRITE：读写计数时间之和
- MIN_TIMER_READ_WRITE：读写计数最短时间
- AVG_TIMER_READ_WRITE：读写计数平均时间
- MAX_TIMER_READ_WRITE：读写计数最长时间
- COUNT_READ_ONLY：只读计数
- SUM_TIMER_READ_ONLY：只读计数时间之和
- MIN_TIMER_READ_ONLY：只读计数最短时间
- AVG_TIMER_READ_ONLY：只读计数平均时间
- MAX_TIMER_READ_ONLY：只读计数最长时间
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。
腾讯云开发者
