events_stages_summary_*表 - xiaoboluo768/mysql-system-schema GitHub Wiki
- 包含如下几张表，performance_schema在这些表中按照不同的分组列（不同纬度）进行聚合阶段事件相关的数据（例如：执行次数，总等待时间，最小、最大、平均等待时间），注意，阶段事件有一部分是默认禁用的，阶段事件的instruments也有一部分是开启的，所以events_stages_xxx consumers在未开启的情况下，stage的摘要表中也会有一些stage事件信息
admin@localhost : performance_schema 06:23:02> show tables like '%events_stages_summary%';
+--------------------------------------------------------+
| Tables_in_performance_schema (%events_stages_summary%) |
+--------------------------------------------------------+
| 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 |
+--------------------------------------------------------+
5 rows in set (0.00 sec)
- 其中，所有表都包含events_stages_summary_global_by_event_name 表中的字段，除此之外，events_stages_summary_by_account_by_event_name表中多了USER，HOST字段，events_stages_summary_by_host_by_event_name表中多了HOST字段，events_stages_summary_by_thread_by_event_name表中多了THREAD_ID字段，events_stages_summary_by_user_by_event_name表中多了USER字段
CREATE TABLE `events_stages_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
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
-
每个阶段事件摘要表都有一个或多个分组列，以确定如何聚合事件信息。事件名称指的是setup_instruments表中instruments名称：
- events_stages_summary_by_account_by_event_name表：按照EVENT_NAME、USER、HOST列进行分组
- events_stages_summary_by_host_by_event_name表：按照EVENT_NAME、HOST列进行分组
- events_stages_summary_by_thread_by_event_name表：按照THREAD_ID、EVENT_NAME列进行分组
- events_stages_summary_by_user_by_event_name表：按照EVENT_NAME、USER列进行分组
- events_stages_summary_global_by_event_name表：按照EVENT_NAME列进行分组
-
每个阶段事件的摘要表都有这些聚合列：COUNT_STAR，SUM_TIMER_WAIT，MIN_TIMER_WAIT，AVG_TIMER_WAIT和MAX_TIMER_WAIT。这些列含义与等待事件摘要表中相同名称的列含义相同，除了阶段事件摘要表聚合数据是来自events_stages_current表而不是events_waits_current的表之外
-
阶段事件摘要表允许使用TRUNCATE TABLE语句。执行该语句时有如下行为：
- 对于未按照帐户、主机、用户聚合的摘要表，truncate语句会将聚合列值重置为零，而不是删除行
- 对于按照帐户、主机、用户聚合的摘要表，truncate语句会删除已开端连接的帐户，主机或用户对应的行，并将其他有连接的行的摘要列值重置为零（实测跟未按照帐号、主机、用户聚合的摘要表一样，只会被重置不会被删除）
-
此外，按照帐户、主机、用户、线程聚合的每个阶段事件摘要表或者events_stages_summary_global_by_event_name表，如果依赖的连接表(accounts、hosts、users表)执行truncate时，那么依赖这些连接表数据的摘要表会同时被隐式truncate
-
表记录内容示例
# events_stages_summary_by_account_by_event_name表
admin@localhost : performance_schema 03:34:03> select * from events_stages_summary_by_account_by_event_name where USER is not null limit 1;
+-------+-------------+------------------------+------------+----------------+----------------+----------------+----------------+
| USER | HOST | EVENT_NAME | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+-------+-------------+------------------------+------------+----------------+----------------+----------------+----------------+
| qfsys | 10.10.20.15 | stage/sql/After create | 0 | 0 | 0 | 0 | 0 |
+-------+-------------+------------------------+------------+----------------+----------------+----------------+----------------+
1 row in set (0.00 sec)
# events_stages_summary_by_host_by_event_name表
admin@localhost : performance_schema 03:34:18> select * from events_stages_summary_by_account_by_event_name where COUNT_STAR !=0 limit 1;
Empty set (0.01 sec)
admin@localhost : performance_schema 03:35:10> select * from events_stages_summary_by_host_by_event_name where HOST is not null limit 1;
+-------------+------------------------+------------+----------------+----------------+----------------+----------------+
| HOST | EVENT_NAME | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+-------------+------------------------+------------+----------------+----------------+----------------+----------------+
| 10.10.20.15 | stage/sql/After create | 0 | 0 | 0 | 0 | 0 |
+-------------+------------------------+------------+----------------+----------------+----------------+----------------+
1 row in set (0.00 sec)
# events_stages_summary_by_thread_by_event_name表
admin@localhost : performance_schema 03:35:46> select * from events_stages_summary_by_thread_by_event_name where thread_id is not null limit 1;
+-----------+------------------------+------------+----------------+----------------+----------------+----------------+
| THREAD_ID | EVENT_NAME | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+-----------+------------------------+------------+----------------+----------------+----------------+----------------+
| 1 | stage/sql/After create | 0 | 0 | 0 | 0 | 0 |
+-----------+------------------------+------------+----------------+----------------+----------------+----------------+
1 row in set (0.00 sec)
# events_stages_summary_by_user_by_event_name表
admin@localhost : performance_schema 03:36:04> select * from events_stages_summary_by_user_by_event_name where user is not null limit 1;
+-------+------------------------+------------+----------------+----------------+----------------+----------------+
| USER | EVENT_NAME | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+-------+------------------------+------------+----------------+----------------+----------------+----------------+
| qfsys | stage/sql/After create | 0 | 0 | 0 | 0 | 0 |
+-------+------------------------+------------+----------------+----------------+----------------+----------------+
1 row in set (0.00 sec)
# events_stages_summary_global_by_event_name表
admin@localhost : performance_schema 03:36:18> select * from events_stages_summary_global_by_event_name limit 1;
+------------------------+------------+----------------+----------------+----------------+----------------+
| EVENT_NAME | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+------------------------+------------+----------------+----------------+----------------+----------------+
| stage/sql/After create | 0 | 0 | 0 | 0 | 0 |
+------------------------+------------+----------------+----------------+----------------+----------------+
1 row in set (0.00 sec)
上一篇： events_waits_summary_*表与objects_summary_global_by_type表 | 下一篇： events_statements_summary_*表与prepared_statements_instances表