events_statements_summary_*表与prepared_statements_instances - xiaoboluo768/mysql-system-schema GitHub Wiki

  • 包含如下几张表,performance_schema在这些表中按照不同的分组列(不同纬度)进行聚合语句事件相关的数据(例如:执行次数,总等待时间,最小、最大、平均等待时间),注意,语句事件instruments默认全部开启,但是consumers配置中events_statements_history_long默认关闭,所以,语句事件摘要表中默认会记录所有的语句事件聚合信息
admin@localhost : performance_schema 06:27:58> show tables like '%events_statements_summary%';
+------------------------------------------------------------+
| Tables_in_performance_schema (%events_statements_summary%) |
+------------------------------------------------------------+
| events_statements_summary_by_account_by_event_name        |
| events_statements_summary_by_digest                        |
| events_statements_summary_by_host_by_event_name            |
| events_statements_summary_by_program                      |
| events_statements_summary_by_thread_by_event_name          |
| events_statements_summary_by_user_by_event_name            |
| events_statements_summary_global_by_event_name            |
+------------------------------------------------------------+
7 rows in set (0.00 sec)

admin@localhost : performance_schema 06:28:48> show tables like '%prepare%';
+------------------------------------------+
| Tables_in_performance_schema (%prepare%) |
+------------------------------------------+
| prepared_statements_instances            |
+------------------------------------------+
1 row in set (0.00 sec)
  • 其中,对于events_statements_summary_*表,绝大多数都包含events_statements_summary_global_by_event_name 表中的字段,除此之外,events_statements_summary_by_account_by_event_name表多了USER和HOST字段,events_statements_summary_by_digest表多了SCHEMA_NAME、DIGEST、DIGEST_TEXT、FIRST_SEEN、LAST_SEEN字段但少了EVENT_NAME字段,events_statements_summary_by_host_by_event_name表多了HOST字段,events_statements_summary_by_program表多了OBJECT_TYPE、OBJECT_SCHEMA、OBJECT_NAME、COUNT_STATEMENTS、SUM_STATEMENTS_WAIT、MIN_STATEMENTS_WAIT、AVG_STATEMENTS_WAIT、MAX_STATEMENTS_WAIT但少了EVENT_NAME字段,events_statements_summary_by_thread_by_event_name表多了个THREAD_ID字段,prepared_statements_instances表多了OBJECT_INSTANCE_BEGIN、STATEMENT_ID、STATEMENT_NAME、SQL_TEXT、OWNER_THREAD_ID、OWNER_EVENT_ID、OWNER_OBJECT_TYPE、OWNER_OBJECT_SCHEMA、OWNER_OBJECT_NAME、TIMER_PREPARE、COUNT_REPREPARE、COUNT_EXECUTE、SUM_TIMER_EXECUTE、MIN_TIMER_EXECUTE、AVG_TIMER_EXECUTE、MAX_TIMER_EXECUTE但少了EVENT_NAME、COUNT_STAR、SUM_TIMER_WAIT、MIN_TIMER_WAIT、AVG_TIMER_WAIT、MAX_TIMER_WAIT字段
CREATE TABLE `events_statements_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,
  `SUM_LOCK_TIME` bigint(20) unsigned NOT NULL,
  `SUM_ERRORS` bigint(20) unsigned NOT NULL,
  `SUM_WARNINGS` bigint(20) unsigned NOT NULL,
  `SUM_ROWS_AFFECTED` bigint(20) unsigned NOT NULL,
  `SUM_ROWS_SENT` bigint(20) unsigned NOT NULL,
  `SUM_ROWS_EXAMINED` bigint(20) unsigned NOT NULL,
  `SUM_CREATED_TMP_DISK_TABLES` bigint(20) unsigned NOT NULL,
  `SUM_CREATED_TMP_TABLES` bigint(20) unsigned NOT NULL,
  `SUM_SELECT_FULL_JOIN` bigint(20) unsigned NOT NULL,
  `SUM_SELECT_FULL_RANGE_JOIN` bigint(20) unsigned NOT NULL,
  `SUM_SELECT_RANGE` bigint(20) unsigned NOT NULL,
  `SUM_SELECT_RANGE_CHECK` bigint(20) unsigned NOT NULL,
  `SUM_SELECT_SCAN` bigint(20) unsigned NOT NULL,
  `SUM_SORT_MERGE_PASSES` bigint(20) unsigned NOT NULL,
  `SUM_SORT_RANGE` bigint(20) unsigned NOT NULL,
  `SUM_SORT_ROWS` bigint(20) unsigned NOT NULL,
  `SUM_SORT_SCAN` bigint(20) unsigned NOT NULL,
  `SUM_NO_INDEX_USED` bigint(20) unsigned NOT NULL,
  `SUM_NO_GOOD_INDEX_USED` bigint(20) unsigned NOT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
  • 每个语句事件摘要表都有一个或多个分组列,以确定如何聚合事件信息。事件名称指的是setup_instruments表中instruments名称:

    • events_statements_summary_by_account_by_event_name表:按照EVENT_NAME、USER、HOST列进行分组
    • events_statements_summary_by_digest表:按照SCHEMA_NAME、DIGEST列进行分组 (DIGEST_TEXT列包含相应的规范化语句摘要文本,但该列既不是分组列也不是聚合列),server启动时,表中的最大行数会自动调整。要显式设置此最大值,可以在server启动之前修改系统变量performance_schema_digests_size的值,注意:该表不记录语法错误的语句,但会记录执行错误,产生警告的语句,关于语法错误的语句,需要查询events_statements_xxx表
    • events_statements_summary_by_host_by_event_name表:按照EVENT_NAME、HOST列进行分组
    • events_statements_summary_by_program表:按照OBJECT_TYPE、OBJECT_SCHEMA、OBJECT_NAME列进行分组。每行聚合给定的存储程序(存储过程或函数,触发器或事件)的事件信息
    • events_statements_summary_by_thread_by_event_name表:按照THREAD_ID、EVENT_NAME列进行分组
    • events_statements_summary_by_user_by_event_name表:按照EVENT_NAME、USER列进行分组
    • events_statements_summary_global_by_event_name表:按照EVENT_NAME列进行分组
    • prepared_statements_instances表:有额外的OBJECT_INSTANCE_BEGIN列,按照STATEMENT_NAME列进行分组
  • 每个语句摘要表都包含如下聚合列:

    • COUNT_STAR,SUM_TIMER_WAIT,MIN_TIMER_WAIT,AVG_TIMER_WAIT,MAX_TIMER_WAIT
      • 这些列的含义类似于等待事件摘要表中相同名称的列,除了语句摘要表聚合的事件信息来自events_statements_current而不是events_waits_current表除外
      • PS:prepared_statements_instances表没有这些聚合列
    • SUM_xxx:对应events_statements_*表中相应的xxx列的聚合值。例如:语句摘要表中的SUM_LOCK_TIME和SUM_ERRORS列是对应events_statements_current表中LOCK_TIME和ERRORS列的聚合值
    • events_statements_summary_by_digest表有自己额外的聚合列:
      • FIRST_SEEN,LAST_SEEN:显示某给定语句第一次插入events_statements_summary_by_digest表和最后一次更新该表的时间戳
    • events_statements_summary_by_program表有自己额外的聚合列:
      • COUNT_STATEMENTS,SUM_STATEMENTS_WAIT,MIN_STATEMENTS_WAIT,AVG_STATEMENTS_WAIT,MAX_STATEMENTS_WAIT:关于存储程序执行期间调用的嵌套语句的统计信息
    • prepared_statements_instances表有自己额外的聚合列:
      • COUNT_EXECUTE,SUM_TIMER_EXECUTE,MIN_TIMER_EXECUTE,AVG_TIMER_EXECUTE,MAX_TIMER_EXECUTE:执行prepare语句的摘要统计信息,详见3.5.4.prepared_statements_instances小节
  • 允许语句摘要表使用TRUNCATE TABLE语句。执行truncate语句时有如下行为:

    • 对于events_statements_summary_by_digest表,会被清空
    • 对于未按照帐户、主机、用户聚合的摘要表,truncate语句会将聚合列值重置为零,而不是删除行
    • 对于按照帐户、主机、用户聚合的摘要表,truncate语句会删除已开端连接的帐户,主机或用户对应的行,并将其他有连接的行的摘要列值重置为零(实测跟未按照帐号、主机、用户聚合的摘要表一样,只会被重置不会被删除)
  • 此外,按照帐户、主机、用户、线程聚合的每个语句事件摘要表或者events_statements_summary_global_by_event_name表,如果依赖的连接表(accounts、hosts、users表)执行truncate时,那么依赖这些连接表数据的摘要表会同时被隐式truncate

    • 如果statements_digest consumers启用,则在语句执行完成时,将会把语文本进行md5 hash计算之后 再发送到events_statements_summary_by_digest表中。聚合分组列基于该语句的DIGEST列值(md5 hash值)
      • 如果给定语句的统计信息行在events_statements_summary_by_digest表中已经存在,则将该语句的聚合信息进行更新,并更新LAST_SEEN列值为当前时间
      • 如果给定语句的统计信息行在events_statements_summary_by_digest表中没有已存在行,并且events_statements_summary_by_digest表空间限制未满的情况下,会在events_statements_summary_by_digest表中新插入一行聚合信息,FIRST_SEEN和LAST_SEEN列都使用当前时间
      • 如果给定语句的统计信息行在events_statements_summary_by_digest表中没有已存在行,且events_statements_summary_by_digest表空间限制已满的情况下,则该语句的统计信息将添加到DIGEST 列值为 NULL的特殊“catch-all”行,如果该特殊行不存在则新插入一行,FIRST_SEEN和LAST_SEEN列为当前时间。如果该特殊行已存在则更新该行的信息,LAST_SEEN为当前时间
    • 由于performance_schema表内存限制,所以维护了DIGEST = NULL的特殊行。 当events_statements_summary_by_digest表限制容量已满的情况下,且新的语句统计信息在需要插入到该表时又没有在该表中找到匹配的DIGEST列值时,就会把这些语句统计信息都聚合到 DIGEST = NULL的行中。此行可帮助您估算events_statements_summary_by_digest表的限制是否需要调整:
      • 如果DIGEST = NULL行的COUNT_STAR列值占据整个表中所有统计信息的COUNT_STAR列值的比例大于0%,则表示存在由于该表限制已满导致部分语句统计信息无法分类保存,如果你需要保存所有语句的统计信息,可以在server启动之前调整系统变量performance_schema_digests_size的值。默认大小为200
  • 存储程序监控行为:对于在setup_objects表中启用了instruments的存储程序类型,events_statements_summary_by_program将维护存储程序的统计信息,如下所示:

    • 当某给定对象在server中首次被使用时(即使用call语句调用了存储过程或自定义存储函数时),将在events_statements_summary_by_program表中添加一行统计信息
    • 当某给定对象被删除时,该对象在events_statements_summary_by_program表中的统计信息行将被删除
    • 当某给定对象被执行时,其对应的统计信息将记录在events_statements_summary_by_program表中并进行聚合
  • 表记录内容示例

# events_statements_summary_by_account_by_event_name表
admin@localhost : performance_schema 06:05:04> select * from events_statements_summary_by_account_by_event_name where COUNT_STAR!=0 limit 1\G;
*************************** 1. row ***************************
                      USER: admin
                      HOST: localhost
                EVENT_NAME: statement/sql/select
                COUNT_STAR: 30
            SUM_TIMER_WAIT: 78434665000
            MIN_TIMER_WAIT: 154197000
            AVG_TIMER_WAIT: 2614488000
            MAX_TIMER_WAIT: 18503824000
              SUM_LOCK_TIME: 4522000000
                SUM_ERRORS: 1
              SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 32696
          SUM_ROWS_EXAMINED: 49133
SUM_CREATED_TMP_DISK_TABLES: 0
    SUM_CREATED_TMP_TABLES: 1
      SUM_SELECT_FULL_JOIN: 0
SUM_SELECT_FULL_RANGE_JOIN: 0
          SUM_SELECT_RANGE: 0
    SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 29
      SUM_SORT_MERGE_PASSES: 0
            SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 29
    SUM_NO_GOOD_INDEX_USED: 0
1 row in set (0.01 sec)

# events_statements_summary_by_digest表
admin@localhost : performance_schema 06:05:44> select * from events_statements_summary_by_digest limit 1\G;
*************************** 1. row ***************************
                SCHEMA_NAME: NULL
                    DIGEST: 9ac13f5fadd2a6b86c089a0d1ac0bb79
                DIGEST_TEXT: SELECT `UNIX_TIMESTAMP` ( ) 
                COUNT_STAR: 1
            SUM_TIMER_WAIT: 95318418000
            MIN_TIMER_WAIT: 95318418000
            AVG_TIMER_WAIT: 95318418000
            MAX_TIMER_WAIT: 95318418000
              SUM_LOCK_TIME: 0
                SUM_ERRORS: 0
              SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 1
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
    SUM_CREATED_TMP_TABLES: 0
      SUM_SELECT_FULL_JOIN: 0
SUM_SELECT_FULL_RANGE_JOIN: 0
          SUM_SELECT_RANGE: 0
    SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
            SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
    SUM_NO_GOOD_INDEX_USED: 0
                FIRST_SEEN: 2017-08-28 08:58:21
                  LAST_SEEN: 2017-08-28 08:58:21
1 row in set (0.00 sec)

# events_statements_summary_by_host_by_event_name表
admin@localhost : performance_schema 06:06:10> select * from events_statements_summary_by_host_by_event_name where COUNT_STAR!=0 limit 1\G;
*************************** 1. row ***************************
                      HOST: localhost
                EVENT_NAME: statement/sql/select
                COUNT_STAR: 35
            SUM_TIMER_WAIT: 81829943000
            MIN_TIMER_WAIT: 154197000
            AVG_TIMER_WAIT: 2337998000
            MAX_TIMER_WAIT: 18503824000
              SUM_LOCK_TIME: 5413000000
                SUM_ERRORS: 1
              SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 32701
          SUM_ROWS_EXAMINED: 49518
SUM_CREATED_TMP_DISK_TABLES: 0
    SUM_CREATED_TMP_TABLES: 1
      SUM_SELECT_FULL_JOIN: 0
SUM_SELECT_FULL_RANGE_JOIN: 0
          SUM_SELECT_RANGE: 0
    SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 34
      SUM_SORT_MERGE_PASSES: 0
            SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 34
    SUM_NO_GOOD_INDEX_USED: 0
1 row in set (0.00 sec)

# events_statements_summary_by_program表
root@localhost : performance_schema 12:36:40> select * from events_statements_summary_by_program\G;
*************************** 1. row ***************************
                OBJECT_TYPE: PROCEDURE
              OBJECT_SCHEMA: sys
                OBJECT_NAME: ps_setup_enable_consumer
                COUNT_STAR: 1
            SUM_TIMER_WAIT: 307129000
            MIN_TIMER_WAIT: 0
            AVG_TIMER_WAIT: 307129000
            MAX_TIMER_WAIT: 307129000
          COUNT_STATEMENTS: 2
        SUM_STATEMENTS_WAIT: 259426000
        MIN_STATEMENTS_WAIT: 123380000
        AVG_STATEMENTS_WAIT: 129713000
        MAX_STATEMENTS_WAIT: 136046000
              SUM_LOCK_TIME: 25645000000
                SUM_ERRORS: 0
              SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 3
              SUM_ROWS_SENT: 1
          SUM_ROWS_EXAMINED: 15
SUM_CREATED_TMP_DISK_TABLES: 0
    SUM_CREATED_TMP_TABLES: 0
      SUM_SELECT_FULL_JOIN: 0
SUM_SELECT_FULL_RANGE_JOIN: 0
          SUM_SELECT_RANGE: 0
    SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
            SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
    SUM_NO_GOOD_INDEX_USED: 0
*************************** 2. row ***************************
                OBJECT_TYPE: FUNCTION
              OBJECT_SCHEMA: sys
                OBJECT_NAME: format_bytes
                COUNT_STAR: 1
            SUM_TIMER_WAIT: 4317940000
            MIN_TIMER_WAIT: 0
            AVG_TIMER_WAIT: 4317940000
            MAX_TIMER_WAIT: 4317940000
          COUNT_STATEMENTS: 7
        SUM_STATEMENTS_WAIT: 4294388000
        MIN_STATEMENTS_WAIT: 740000
        AVG_STATEMENTS_WAIT: 613484000
        MAX_STATEMENTS_WAIT: 4239343000
              SUM_LOCK_TIME: 0
                SUM_ERRORS: 0
              SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
    SUM_CREATED_TMP_TABLES: 0
      SUM_SELECT_FULL_JOIN: 0
SUM_SELECT_FULL_RANGE_JOIN: 0
          SUM_SELECT_RANGE: 0
    SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
            SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
    SUM_NO_GOOD_INDEX_USED: 0
2 rows in set (0.00 sec)

# events_statements_summary_by_thread_by_event_name表
admin@localhost : performance_schema 06:08:11> select * from events_statements_summary_by_thread_by_event_name where COUNT_STAR!=0 limit 1\G
*************************** 1. row ***************************
                  THREAD_ID: 46
                EVENT_NAME: statement/sql/select
                COUNT_STAR: 40
            SUM_TIMER_WAIT: 85224898000
            MIN_TIMER_WAIT: 154197000
            AVG_TIMER_WAIT: 2130622000
            MAX_TIMER_WAIT: 18503824000
              SUM_LOCK_TIME: 6277000000
                SUM_ERRORS: 1
              SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 32704
          SUM_ROWS_EXAMINED: 49901
SUM_CREATED_TMP_DISK_TABLES: 0
    SUM_CREATED_TMP_TABLES: 1
      SUM_SELECT_FULL_JOIN: 0
SUM_SELECT_FULL_RANGE_JOIN: 0
          SUM_SELECT_RANGE: 0
    SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 39
      SUM_SORT_MERGE_PASSES: 0
            SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 39
    SUM_NO_GOOD_INDEX_USED: 0
1 row in set (0.00 sec)

# events_statements_summary_by_user_by_event_name表
admin@localhost : performance_schema 06:08:22> select * from events_statements_summary_by_user_by_event_name where COUNT_STAR!=0 limit 1\G;
*************************** 1. row ***************************
                      USER: qfsys
                EVENT_NAME: statement/sql/select
                COUNT_STAR: 7
            SUM_TIMER_WAIT: 95979383000
            MIN_TIMER_WAIT: 46127000
            AVG_TIMER_WAIT: 13711340000
            MAX_TIMER_WAIT: 95318418000
              SUM_LOCK_TIME: 0
                SUM_ERRORS: 0
              SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 7
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
    SUM_CREATED_TMP_TABLES: 0
      SUM_SELECT_FULL_JOIN: 0
SUM_SELECT_FULL_RANGE_JOIN: 0
          SUM_SELECT_RANGE: 0
    SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
            SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
    SUM_NO_GOOD_INDEX_USED: 0
1 row in set (0.00 sec)

# events_statements_summary_global_by_event_name表
admin@localhost : performance_schema 06:08:58> select * from events_statements_summary_global_by_event_name limit 1\G;
*************************** 1. row ***************************
                EVENT_NAME: statement/sql/select
                COUNT_STAR: 1741
            SUM_TIMER_WAIT: 4741982300000
            MIN_TIMER_WAIT: 0
            AVG_TIMER_WAIT: 2723711000
            MAX_TIMER_WAIT: 103001449000
              SUM_LOCK_TIME: 753144000000
                SUM_ERRORS: 1
              SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 89481
          SUM_ROWS_EXAMINED: 113738
SUM_CREATED_TMP_DISK_TABLES: 0
    SUM_CREATED_TMP_TABLES: 1
      SUM_SELECT_FULL_JOIN: 0
SUM_SELECT_FULL_RANGE_JOIN: 0
          SUM_SELECT_RANGE: 0
    SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 84
      SUM_SORT_MERGE_PASSES: 0
            SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 83
    SUM_NO_GOOD_INDEX_USED: 0
1 row in set (0.00 sec)

上一篇: events_stages_summary_*表 | 下一篇: events_transactions_summary_*表