Skip to content

events_transactions_summary_*表

Jump to bottom
xiaoboluo768 edited this page Jun 8, 2020 · 2 revisions
  • 包含如下几张表，performance_schema在这些表中按照不同的分组列（不同纬度）进行聚合事务事件相关的数据（例如：执行次数，总等待时间，最小、最大、平均等待时间），注意，事务事件instruments只有一个transaction，默认禁用，对应的consumers也默认禁用，所以默认情况下事务事件摘要表中只有对应连接的一个统计行，但是相关聚合字段值都为0，需要启用之后才会进行收集并在摘要表中进行聚合
admin@localhost : performance_schema 06:37:45> show tables like '%events_transactions_summary%';
+--------------------------------------------------------------+
| Tables_in_performance_schema (%events_transactions_summary%) |
+--------------------------------------------------------------+
| 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            |
+--------------------------------------------------------------+
5 rows in set (0.00 sec)
  • 其中，都包含events_transactions_summary_global_by_event_name表的字段，除此之外，events_transactions_summary_by_account_by_event_name表多了USER和HOST字段，events_transactions_summary_by_host_by_event_name表多了HOST字段，events_transactions_summary_by_thread_by_event_name表多了THREAD_ID字段，events_transactions_summary_by_user_by_event_name表多了USER字段
CREATE TABLE `events_transactions_summary_global_by_event_name` (
  `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,
  `COUNT_READ_WRITE` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_READ_WRITE` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_READ_WRITE` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_READ_WRITE` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_READ_WRITE` bigint(20) unsigned NOT NULL,
  `COUNT_READ_ONLY` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_READ_ONLY` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_READ_ONLY` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_READ_ONLY` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_READ_ONLY` bigint(20) unsigned NOT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

  • 每个事务事件摘要表都有一个或多个分组列，以确定如何聚合事件信息。事件名称指的是setup_instruments表中instruments名称：

    • events_transactions_summary_by_account_by_event_name表：按照USER、HOST、EVENT_NAME列进行分组
    • events_transactions_summary_by_host_by_event_name表：按照HOST、EVENT_NAME列进行分组
    • events_transactions_summary_by_thread_by_event_name表：按照THREAD_ID、EVENT_NAME列进行分组
    • events_transactions_summary_by_user_by_event_name表：按照USER、EVENT_NAME列进行分组
    • events_transactions_summary_global_by_event_name表：按照EVENT_NAME列进行分组

  • 每个事务摘要表都包含如下聚合值字段：

    • COUNT_STAR，SUM_TIMER_WAIT，MIN_TIMER_WAIT，AVG_TIMER_WAIT，MAX_TIMER_WAIT：这些列含义类似于等待事件摘要表中相同名称的列，但事务事件摘要表聚合信息来自events_transactions_current表而不是events_waits_current的表
    • COUNT_READ_WRITE，SUM_TIMER_READ_WRITE，MIN_TIMER_READ_WRITE，AVG_TIMER_READ_WRITE，MAX_TIMER_READ_WRITE：这些类似于COUNT_STAR和xxx_TIMER_WAIT列含义，但这些列只针对读写事务进行聚合
    • COUNT_READ_ONLY，SUM_TIMER_READ_ONLY，MIN_TIMER_READ_ONLY，AVG_TIMER_READ_ONLY，MAX_TIMER_READ_ONLY：这些类似于COUNT_STAR和xxx_TIMER_WAIT列含义，但这些列只针对只读事务进行聚合

  • 事务摘要表允许使用TRUNCATE TABLE语句。执行truncate语句时有如下行为：

    • 对于未按照帐户、主机、用户聚合的摘要表，truncate语句会将聚合列值重置为零，而不是删除行
    • 对于按照帐户、主机、用户聚合的摘要表，truncate语句会删除已开端连接的帐户，主机或用户对应的行，并将其他有连接的行的摘要列值重置为零（实测跟未按照帐号、主机、用户聚合的摘要表一样，只会被重置不会被删除）

  • 此外，按照帐户、主机、用户、线程聚合的每个语句事件摘要表或者events_transactions_summary_global_by_event_name表，如果依赖的连接表(accounts、hosts、users表)执行truncate时，那么依赖这些连接表数据的摘要表会同时被隐式truncate

  • 事务聚合规则

    • 事务事件的收集不考虑隔离级别，访问模式或自动提交模式
    • 读写事务通常比只读事务占用更多资源，因此事务摘要表包含了用于读写和只读事务的单独聚合列
    • 事务所占用的资源需求多少也可能会因事务隔离级别有所差异(例如：锁资源)。但是：每个server可能是使用相同的隔离级别，所以不单独提供隔离级别相关的聚合列

  • 表记录内容示例

# events_transactions_summary_by_account_by_event_name表
admin@localhost : performance_schema 10:52:09> select * from events_transactions_summary_by_account_by_event_name where COUNT_STAR!=0 limit 1\G;
*************************** 1. row ***************************
                USER: admin
                HOST: localhost
          EVENT_NAME: transaction
          COUNT_STAR: 3
      SUM_TIMER_WAIT: 141585000
      MIN_TIMER_WAIT: 141585000
      AVG_TIMER_WAIT: 47195000
      MAX_TIMER_WAIT: 141585000
    COUNT_READ_WRITE: 3
SUM_TIMER_READ_WRITE: 141585000
MIN_TIMER_READ_WRITE: 141585000
AVG_TIMER_READ_WRITE: 47195000
MAX_TIMER_READ_WRITE: 141585000
    COUNT_READ_ONLY: 0
SUM_TIMER_READ_ONLY: 0
MIN_TIMER_READ_ONLY: 0
AVG_TIMER_READ_ONLY: 0
MAX_TIMER_READ_ONLY: 0
1 row in set (0.00 sec)

# events_transactions_summary_by_host_by_event_name表
admin@localhost : performance_schema 10:52:27> select * from events_transactions_summary_by_host_by_event_name where COUNT_STAR!=0 limit 1\G
*************************** 1. row ***************************
                HOST: localhost
          EVENT_NAME: transaction
          COUNT_STAR: 3
      SUM_TIMER_WAIT: 141585000
      MIN_TIMER_WAIT: 141585000
      AVG_TIMER_WAIT: 47195000
      MAX_TIMER_WAIT: 141585000
    COUNT_READ_WRITE: 3
SUM_TIMER_READ_WRITE: 141585000
MIN_TIMER_READ_WRITE: 141585000
AVG_TIMER_READ_WRITE: 47195000
MAX_TIMER_READ_WRITE: 141585000
    COUNT_READ_ONLY: 0
SUM_TIMER_READ_ONLY: 0
MIN_TIMER_READ_ONLY: 0
AVG_TIMER_READ_ONLY: 0
MAX_TIMER_READ_ONLY: 0
1 row in set (0.00 sec)

# events_transactions_summary_by_thread_by_event_name表
admin@localhost : performance_schema 10:53:57> select * from events_transactions_summary_by_thread_by_event_name where SUM_TIMER_WAIT!=0\G
*************************** 1. row ***************************
          THREAD_ID: 47
          EVENT_NAME: transaction
          COUNT_STAR: 2
      SUM_TIMER_WAIT: 141585000
      MIN_TIMER_WAIT: 141585000
      AVG_TIMER_WAIT: 70792000
      MAX_TIMER_WAIT: 141585000
    COUNT_READ_WRITE: 2
SUM_TIMER_READ_WRITE: 141585000
MIN_TIMER_READ_WRITE: 141585000
AVG_TIMER_READ_WRITE: 70792000
MAX_TIMER_READ_WRITE: 141585000
    COUNT_READ_ONLY: 0
SUM_TIMER_READ_ONLY: 0
MIN_TIMER_READ_ONLY: 0
AVG_TIMER_READ_ONLY: 0
MAX_TIMER_READ_ONLY: 0
1 row in set (0.00 sec)

# events_transactions_summary_by_user_by_event_name表
admin@localhost : performance_schema 10:54:16> select * from events_transactions_summary_by_user_by_event_name where SUM_TIMER_WAIT!=0\G
*************************** 1. row ***************************
                USER: admin
          EVENT_NAME: transaction
          COUNT_STAR: 3
      SUM_TIMER_WAIT: 141585000
      MIN_TIMER_WAIT: 141585000
      AVG_TIMER_WAIT: 47195000
      MAX_TIMER_WAIT: 141585000
    COUNT_READ_WRITE: 3
SUM_TIMER_READ_WRITE: 141585000
MIN_TIMER_READ_WRITE: 141585000
AVG_TIMER_READ_WRITE: 47195000
MAX_TIMER_READ_WRITE: 141585000
    COUNT_READ_ONLY: 0
SUM_TIMER_READ_ONLY: 0
MIN_TIMER_READ_ONLY: 0
AVG_TIMER_READ_ONLY: 0
MAX_TIMER_READ_ONLY: 0
1 row in set (0.00 sec)

# events_transactions_summary_global_by_event_name表
admin@localhost : performance_schema 10:54:37> select * from events_transactions_summary_global_by_event_name where SUM_TIMER_WAIT!=0\G
*************************** 1. row ***************************
          EVENT_NAME: transaction
          COUNT_STAR: 3
      SUM_TIMER_WAIT: 141585000
      MIN_TIMER_WAIT: 141585000
      AVG_TIMER_WAIT: 47195000
      MAX_TIMER_WAIT: 141585000
    COUNT_READ_WRITE: 3
SUM_TIMER_READ_WRITE: 141585000
MIN_TIMER_READ_WRITE: 141585000
AVG_TIMER_READ_WRITE: 47195000
MAX_TIMER_READ_WRITE: 141585000
    COUNT_READ_ONLY: 0
SUM_TIMER_READ_ONLY: 0
MIN_TIMER_READ_ONLY: 0
AVG_TIMER_READ_ONLY: 0
MAX_TIMER_READ_ONLY: 0
1 row in set (0.00 sec)

上一篇： events_statements_summary_*表与prepared_statements_instances表 | 下一篇： objects_summary_global_by_type表

  • 验证、测试、整理：罗小波
  • QQ：309969177
  • 提示：本系列文章的主体结构遵循Oracle MySQL 官方 5.7 手册中，关于information_schema、mysql schema、performance_schema、sys schema的章节结构体系，并额外添加了一些验证、测试数据。鉴于本人精力和能力有限，难免出现一些纰漏，欢迎大家踊跃指正！
Clone this wiki locally