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 '%transaction%';

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

第一种：修改MySQL配置

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

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

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

第二种 直接修改配置表

五、事务事件表介绍

events_transactions_current

events_transactions_current表包含当前事务事件信息，每个线程只保留一行最近事务的事务事件 。

查询示例

输出

字段介绍

• 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行事务事件信息。

查询

输出

字段介绍

与events_transactions_current表一致

events_transactions_history_long

events_transactions_history_long主要记录全局最近10000行事务事件信息

查询

输出

字段介绍

与events_transactions_current表一致

events_transactions_summary_by_account_by_event_name（主机+用户+事件）

events_transactions_summary_by_account_by_event_name用于按照列EVENT_NAME、USER、HOST进行分组事件信息。

查询

输出

字段介绍

• 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进行分组事件信息。

查询

输出

字段介绍

• 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 用于按照列线程名称、事件名称进行分组事件信息。

查询

输出

字段介绍

• 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用于按照列用户、事件名称进行分组事件信息。

查询

输出

字段介绍

• 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用于用于对全局的事务事件进行汇总.

查询

输出

字段介绍

• 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：只读计数最长时间

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