MySQL-performance_schema | HoleLin's Blog

参考文献

千金良方: MySQL性能优化金字塔法则

`performance_schema`

mysql> show tables ;
+------------------------------------------------------+
| Tables_in_performance_schema                         |
+------------------------------------------------------+
| accounts                                             |
| binary_log_transaction_compression_stats             |
| cond_instances                                       |
| data_lock_waits                                      |
| data_locks                                           |
| error_log                                            |
| events_errors_summary_by_account_by_error            |
| events_errors_summary_by_host_by_error               |
| events_errors_summary_by_thread_by_error             |
| events_errors_summary_by_user_by_error               |
| events_errors_summary_global_by_error                |
| events_stages_current                                |
| events_stages_history                                |
| events_stages_history_long                           |
| 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           |
| events_statements_current                            |
| events_statements_histogram_by_digest                |
| events_statements_histogram_global                   |
| events_statements_history                            |
| events_statements_history_long                       |
| 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       |
| 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     |
| events_waits_current                                 |
| events_waits_history                                 |
| events_waits_history_long                            |
| events_waits_summary_by_account_by_event_name        |
| events_waits_summary_by_host_by_event_name           |
| events_waits_summary_by_instance                     |
| events_waits_summary_by_thread_by_event_name         |
| events_waits_summary_by_user_by_event_name           |
| events_waits_summary_global_by_event_name            |
| file_instances                                       |
| file_summary_by_event_name                           |
| file_summary_by_instance                             |
| global_status                                        |
| global_variables                                     |
| host_cache                                           |
| hosts                                                |
| innodb_redo_log_files                                |
| keyring_component_status                             |
| keyring_keys                                         |
| log_status                                           |
| memory_summary_by_account_by_event_name              |
| memory_summary_by_host_by_event_name                 |
| memory_summary_by_thread_by_event_name               |
| memory_summary_by_user_by_event_name                 |
| memory_summary_global_by_event_name                  |
| metadata_locks                                       |
| mutex_instances                                      |
| objects_summary_global_by_type                       |
| performance_timers                                   |
| persisted_variables                                  |
| prepared_statements_instances                        |
| processlist                                          |
| replication_applier_configuration                    |
| replication_applier_filters                          |
| replication_applier_global_filters                   |
| replication_applier_status                           |
| replication_applier_status_by_coordinator            |
| replication_applier_status_by_worker                 |
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
| replication_connection_configuration                 |
| replication_connection_status                        |
| replication_group_member_stats                       |
| replication_group_members                            |
| rwlock_instances                                     |
| session_account_connect_attrs                        |
| session_connect_attrs                                |
| session_status                                       |
| session_variables                                    |
| setup_actors                                         |
| setup_consumers                                      |
| setup_instruments                                    |
| setup_objects                                        |
| setup_threads                                        |
| socket_instances                                     |
| socket_summary_by_event_name                         |
| socket_summary_by_instance                           |
| status_by_account                                    |
| status_by_host                                       |
| status_by_thread                                     |
| status_by_user                                       |
| table_handles                                        |
| table_io_waits_summary_by_index_usage                |
| table_io_waits_summary_by_table                      |
| table_lock_waits_summary_by_table                    |
| threads                                              |
| tls_channel_status                                   |
| user_defined_functions                               |
| user_variables_by_thread                             |
| users                                                |
| variables_by_thread                                  |
| variables_info                                       |
+------------------------------------------------------+
111 rows in set (0.00 sec)

mysql> show variables like 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

`performance_schema`中的表按照事件类型分类

语句事件记录表

记录语句事件信息的表

mysql> show tables like 'events_statement%';
+----------------------------------------------------+
| Tables_in_performance_schema (events_statement%)   |
+----------------------------------------------------+
| events_statements_current                          |
| events_statements_histogram_by_digest              |
| events_statements_histogram_global                 |
| events_statements_history                          |
| events_statements_history_long                     |
| 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     |
+----------------------------------------------------+
12 rows in set (0.01 sec)

等待事件记录表

mysql> show tables like 'events_wait%';
+-----------------------------------------------+
| Tables_in_performance_schema (events_wait%)   |
+-----------------------------------------------+
| events_waits_current                          |
| events_waits_history                          |
| events_waits_history_long                     |
| events_waits_summary_by_account_by_event_name |
| events_waits_summary_by_host_by_event_name    |
| events_waits_summary_by_instance              |
| events_waits_summary_by_thread_by_event_name  |
| events_waits_summary_by_user_by_event_name    |
| events_waits_summary_global_by_event_name     |
+-----------------------------------------------+
9 rows in set (0.01 sec)

阶段事件记录表

mysql> show tables like 'events_stage%';
+------------------------------------------------+
| Tables_in_performance_schema (events_stage%)   |
+------------------------------------------------+
| events_stages_current                          |
| events_stages_history                          |
| events_stages_history_long                     |
| 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     |
+------------------------------------------------+
8 rows in set (0.01 sec)

事务事件记录表

mysql> show tables like 'events_transaction%';
+------------------------------------------------------+
| Tables_in_performance_schema (events_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     |
+------------------------------------------------------+
8 rows in set (0.00 sec)

监视文件系统层调用的表

mysql> show tables like '%file%';
+---------------------------------------+
| Tables_in_performance_schema (%file%) |
+---------------------------------------+
| file_instances                        |
| file_summary_by_event_name            |
| file_summary_by_instance              |
| innodb_redo_log_files                 |
+---------------------------------------+
4 rows in set (0.01 sec)

监视内存使用的表

mysql> show tables like '%memory%';
+-----------------------------------------+
| Tables_in_performance_schema (%memory%) |
+-----------------------------------------+
| memory_summary_by_account_by_event_name |
| memory_summary_by_host_by_event_name    |
| memory_summary_by_thread_by_event_name  |
| memory_summary_by_user_by_event_name    |
| memory_summary_global_by_event_name     |
+-----------------------------------------+
5 rows in set (0.01 sec)

动态对`performance_schema`进行配置的配置表

mysql> show tables like '%setup%';
+----------------------------------------+
| Tables_in_performance_schema (%setup%) |
+----------------------------------------+
| setup_actors                           |
| setup_consumers                        |
| setup_instruments                      |
| setup_objects                          |
| setup_threads                          |
+----------------------------------------+
5 rows in set (0.00 sec)

`performance_schema`配置

instruments：生产者,用于采集MySQL中各种操作产生的事件信息,对应配置表中的配置项,可
以称之为事件采集配置项。以下提及的生产者均统称为instruments。
consumers：消费者,对应的消费者表用于存储来自instruments采集的数据,对应配置表中的配置项,
可以称之为消费存储配置项。以下提及的消费者均统称为consumers。

`performance_timers`表

performance_timers表中记录了Server中有哪些可用的事件计时器(注意：该表中的配置项不支持增、删、改,是只读的。记录了哪些计时器就表示当前的版本支持这些计时器)

mysql> select * from performance_timers;
+-------------+-----------------+------------------+----------------+
| TIMER_NAME  | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |
+-------------+-----------------+------------------+----------------+
| CYCLE       |      2391621890 |                1 |             44 |
| NANOSECOND  |      1000000000 |                1 |             76 |
| MICROSECOND |         1000000 |                1 |             76 |
| MILLISECOND |            1036 |                1 |             78 |
| THREAD_CPU  |       218245585 |                1 |           1340 |
+-------------+-----------------+------------------+----------------+
5 rows in set (0.02 sec)

`setup_consumers`表

setup_consumers表中列出了consumers可配置列表项(注意：该表不支持增加和删除记录,只支持修改和查询记录)。

mysql> select * from setup_consumers;
+----------------------------------+---------+
| NAME                             | ENABLED |
+----------------------------------+---------+
| events_stages_current            | NO      |
| events_stages_history            | NO      |
| events_stages_history_long       | NO      |
| events_statements_cpu            | NO      |
| events_statements_current        | YES     |
| events_statements_history        | YES     |
| events_statements_history_long   | NO      |
| events_transactions_current      | YES     |
| events_transactions_history      | YES     |
| events_transactions_history_long | NO      |
| events_waits_current             | NO      |
| events_waits_history             | NO      |
| events_waits_history_long        | NO      |
| global_instrumentation           | YES     |
| thread_instrumentation           | YES     |
| statements_digest                | YES     |
+----------------------------------+---------+
16 rows in set (0.01 sec)

ENABLED：是否启用consumers,有效值为YES或NO,此字段可以使用UPDATE语句修改。如果需要禁用consumers,就将ENABLED字段设置为NO,当设置为NO时,Server不会对consumers表的内容新增和删除进行维护,并且也会关闭consumers对应的instruments(如果instruments发现采集数据没有任何consumers消费的话)

`setup_instruments`表

setup_instruments表中列出了instruments可配置列表项,即代表哪些事件支持被收集。

常用的监控场景

监控metadata locks(MDL元数据锁)需要打'wait/lock/metadata/sql/mdl' instruments,在
performance_schema.metadata_locks表中可以查询到MDL锁信息

mysql> select * from setup_instruments where name like 'wait/lock/metadata/sql/mdl%';
+----------------------------+---------+-------+------------+-------+------------+---------------+
| NAME                       | ENABLED | TIMED | PROPERTIES | FLAGS | VOLATILITY | DOCUMENTATION |
+----------------------------+---------+-------+------------+-------+------------+---------------+
| wait/lock/metadata/sql/mdl | YES     | YES   |            | NULL  |          0 | NULL          |
+----------------------------+---------+-------+------------+-------+------------+---------------+
1 row in set (0.00 sec)

监控表锁需要打开'wait/io/table/sql/handler'instruments,在performance_schema.table_handles表中会记录当前打开了哪些表

mysql> select * from setup_instruments where name like 'wait/io/table/sql/handler%';
+---------------------------+---------+-------+------------+-------+------------+---------------+
| NAME                      | ENABLED | TIMED | PROPERTIES | FLAGS | VOLATILITY | DOCUMENTATION |
+---------------------------+---------+-------+------------+-------+------------+---------------+
| wait/io/table/sql/handler | YES     | YES   |            | NULL  |          0 | NULL          |
+---------------------------+---------+-------+------------+-------+------------+---------------+
1 row in set (0.00 sec)

监控查询语句top number,需要打开'statement/sql/select' instruments然后打开
events_statements_xxx表,通过查询performance_schema.events_statements_xxx表的SQL_TEXT字段可以看到原始的SQL语句,查询TIMER_WAIT字段可以知道总的响应时间,查询LOCK_TIME字段可以知道加锁时间(注意时间单位是皮秒,需要除以1 000 000 000 000单位才是秒

mysql> select * from setup_instruments where name like 'statement/sql/select';
+----------------------+---------+-------+------------+-------+------------+---------------+
| NAME                 | ENABLED | TIMED | PROPERTIES | FLAGS | VOLATILITY | DOCUMENTATION |
+----------------------+---------+-------+------------+-------+------------+---------------+
| statement/sql/select | YES     | YES   |            | NULL  |          0 | NULL          |
+----------------------+---------+-------+------------+-------+------------+---------------+
1 row in set (0.00 sec)

mysql> select * from events_statements_current\G
*************************** 1. row ***************************
              THREAD_ID: 61
               EVENT_ID: 10
           END_EVENT_ID: NULL
             EVENT_NAME: statement/com/Binlog Dump
                 SOURCE: init_net_server_extension.cc:103
            TIMER_START: 2873004447444000
              TIMER_END: 79520861038420000
             TIMER_WAIT: 76647856590976000
              LOCK_TIME: 0
               SQL_TEXT: NULL
                 DIGEST: NULL
            DIGEST_TEXT: NULL
         CURRENT_SCHEMA: NULL
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: NULL
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 0
          ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
     CREATED_TMP_TABLES: 0
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 0
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 0
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL
     NESTING_EVENT_TYPE: NULL
    NESTING_EVENT_LEVEL: 0
           STATEMENT_ID: 161
               CPU_TIME: 0
  MAX_CONTROLLED_MEMORY: 0
       MAX_TOTAL_MEMORY: 0
       EXECUTION_ENGINE: PRIMARY
*************************** 2. row ***************************
              THREAD_ID: 65
               EVENT_ID: 168
           END_EVENT_ID: NULL
             EVENT_NAME: statement/sql/select
                 SOURCE: init_net_server_extension.cc:103
            TIMER_START: 79520860683668000
              TIMER_END: 79520861072545000
             TIMER_WAIT: 388877000
              LOCK_TIME: 3000000
               SQL_TEXT: select * from events_statements_current
                 DIGEST: d2cb1ecf648cf0ec173e79a74aa085d330c1cabcabbbd5785623ab5f69b0a811
            DIGEST_TEXT: SELECT * FROM `events_statements_current`
         CURRENT_SCHEMA: performance_schema
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: NULL
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 1
          ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
     CREATED_TMP_TABLES: 0
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 1
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 1
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL
     NESTING_EVENT_TYPE: NULL
    NESTING_EVENT_LEVEL: 0
           STATEMENT_ID: 348
               CPU_TIME: 0
  MAX_CONTROLLED_MEMORY: 0
       MAX_TOTAL_MEMORY: 0
       EXECUTION_ENGINE: PRIMARY
2 rows in set (0.00 sec)

`setup_actors`表

setup_actors表用于配置是否为新的前台Server线程(与客户端连接相关联的线程)启用监视和历史事件日志记录。在默认情况下,此表的最大行数为100。可以使用系统变performance_schema_setup_actors_size,在Server启动之前更改此表的最大配置行数

mysql> select * from setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| %    | %    | %    | YES     | YES     |
+------+------+------+---------+---------+
1 row in set (0.00 sec)

HOST：一个具体的字符串名称(是能够解析为IP地址的主机名或DNS域名),或者使用“%”表示任意主机。
USER：一个具体的字符串名称,或者使用“%”表示任意用户。
ROLE：当前未使用,在MySQL 8.0中才启用角色功能。
ENABLED：是否启用与HOST、USER、ROLE匹配的前台线程的监控功能,其有效值为YES或NO。
HISTORY：是否启用与HOST、USER、ROLE匹配的前台线程的历史事件记录功能,其有效值为YES或NO。

`setup_objects`表

setup_objects表控制performance_schema是否监控特定对象。在默认情况下,此表的最大行数为100行。若要更改表行数,则可以在Server启动之前修改系统变量performance_schema_setup_objects_size的值。

mysql> select * from setup_objects;
+-------------+--------------------+-------------+---------+-------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME | ENABLED | TIMED |
+-------------+--------------------+-------------+---------+-------+
| EVENT       | mysql              | %           | NO      | NO    |
| EVENT       | performance_schema | %           | NO      | NO    |
| EVENT       | information_schema | %           | NO      | NO    |
| EVENT       | %                  | %           | YES     | YES   |
| FUNCTION    | mysql              | %           | NO      | NO    |
| FUNCTION    | performance_schema | %           | NO      | NO    |
| FUNCTION    | information_schema | %           | NO      | NO    |
| FUNCTION    | %                  | %           | YES     | YES   |
| PROCEDURE   | mysql              | %           | NO      | NO    |
| PROCEDURE   | performance_schema | %           | NO      | NO    |
| PROCEDURE   | information_schema | %           | NO      | NO    |
| PROCEDURE   | %                  | %           | YES     | YES   |
| TABLE       | mysql              | %           | NO      | NO    |
| TABLE       | performance_schema | %           | NO      | NO    |
| TABLE       | information_schema | %           | NO      | NO    |
| TABLE       | %                  | %           | YES     | YES   |
| TRIGGER     | mysql              | %           | NO      | NO    |
| TRIGGER     | performance_schema | %           | NO      | NO    |
| TRIGGER     | information_schema | %           | NO      | NO    |
| TRIGGER     | %                  | %           | YES     | YES   |
+-------------+--------------------+-------------+---------+-------+
20 rows in set (0.00 sec)

`threads`表

threads表对于每个Server线程都会生成一行线程相关信息,例如：显示是否启用监控和历史事件记录功能

示例

找出谁持有全局读锁

mysql> select connection_id();
mysql> show processlist;
mysql> select * from performance_schema.data_locks;
mysql> select * from performance_schema.data_lock_waits;
mysql> select * from information_schema.innodb_trx;
mysql> show engine innodb status;
mysql> select * from performance_schema.metadata_locks where owner_thread_id!=sys.ps_thread_id(connection_id())\G
mysql> sys.ps_thread_id({process id});

找出谁持有`MDL`锁

MDL锁记录对应的instruments为wait/lock/metadata/sql/mdl,默认未启用；对应的consumers为
performance_schema.metadata_locks,在setup_consumers中只受全局配置项global_instrumentation控制,默认已启用

mysql> show processlist;
mysql> select * from performance_schema.metadata_locks where owner_thread_id!=sys.ps_thread_id(connection_id())\G
mysql> select * from information_schema.innodb_trx;
mysql> select * from performance_schema.events_statements_current where thread_id={具体的线程ID}\G

找出谁持有表级锁

表级锁对应的instruments(wait/lock/table/sql/handler)默认已启用,对应的consumers
为performance_schema.table_handles,在setup_consumers中只受全局配置项global_ instrumentation控制,默认已启用。所以,在默认情况下,只需要设置系统配置参数performance_schema=ON即可

mysql> show processlist;
mysql> select * from performance_schema.metadata_locks where owner_thread_id!=sys.ps_thread_id(connection_id())\G
mysql> select * from performance_schema.data_locks;
mysql> select * from performance_schema.data_lock_waits;
mysql> select * from information_schema.innodb_trx;
mysql> select  * from performance_schema.table_handles where owner_thread_id!=0;
mysql> select * from performance_schema.events_statements_current where thread_id={具体的线程ID}\G
mysql> select processlist_id from performance_schema.threads where thread_id={具体的线程ID};

找出谁持有行级锁

1
2
3

mysql> show processlist;
mysql> select * from performance_schema.data_locks;
mysql> select * from sys.innodb_lock_waits;

查看最近的`TOP SQL`语句

mysql> select thread_id,event_name,sys.format_time(timer_wait),sys.format_time(lock_time),sql_text,current_schema,message_text,rows_affected,rows_sent,rows_examined from events_statements_history where current_schema!='performance_schema' order by timer_wait desc limit 10\G

mysql> select SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, sys.format_time(SUM_TIMER_WAIT) as sum_time, sys.format_time(MIN_TIMER_WAIT) as min_time, sys.format_time(AVG_TIMER_WAIT) as avg_time, sys.format_time(MAX_TIMER_WAIT) as max_time, sys.format_time(SUM_LOCK_TIME) as sum_lock_time, SUM_ROWS_AFFECTED, SUM_ROWS_SENT, SUM_ROWS_EXAMINED from events_statements_summary_by_digest where SCHEMA_NAME is not null order by COUNT_STAR desc limit 10\G

查看最近执行失败的SQL语句

mysql> select thread_id,event_name,source,sys.format_time(timer_wait) as exec_time,sys.format_time(lock_time) as lock_time,sql_text,current_schema,message_text,rows_affected,rows_sent,rows_examined,mysql_errno from events_statements_history limit 10\G

mysql> select schema_name,digest_text,count_star,sys.format_time(avg_timer_wait) as avg_time,sys.format_time(max_timer_wait) as max_time,sys.format_time(sum_lock_time) as sum_lock,sum_errors,first_seen,last_seen from events_statements_summary_by_digest  where sum_errors!=0\G

mysql> select thread_id,event_name,source,sys.format_time(timer_wait) as exec_time,sys.format_time(lock_time) as lock_time,sql_text,current_schema,message_text,rows_affected,rows_sent,rows_examined,mysql_errno from events_statements_history_long limit 10\G

查看`SQL`语句执行阶段和进度信息

首先需要进行配置启用,阶段事件默认并未启用

mysql> use performance_schema;
Database changed

mysql> update setup_instruments set enabled='yes',timed='yes' where name like 'stage/%';
Query OK, 116 rows affected (0.01 sec)
Rows matched: 132  Changed: 116  Warnings: 0

mysql> update setup_consumers set enabled='yes' where name like '%stage%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

开启一个会话,查看线程信息

mysql> select sys.ps_thread_id(connection_id());
+-----------------------------------+
| sys.ps_thread_id(connection_id()) |
+-----------------------------------+
|                                47 |
+-----------------------------------+
1 row in set (0.00 sec)

先对之前旧的信息进行清理,避免干扰

# 先关闭其他线程的事件记录功能,使用前面的步骤查询到thread_id
mysql> update performance_schema.threads set instrumented='NO' where thread_id!=47;
Query OK, 47 rows affected (0.00 sec)
Rows matched: 47  Changed: 47  Warnings: 0

# 清空阶段事件的三个表
mysql> truncate performance_schema.events_stages_current; truncate performance_schema.events_stages_history; truncate performance_schema.events_stages_history_long;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

查看events_stages_history_long表

1	mysql> select thread_id,event_name,source,sys.format_time(timer_wait) as exec_time,work_completed,work_estimated from performance_schema.events_stages_history_long;

查看`SQL`语句执行进度信息

mysql> select * from sys.session where conn_id!=connection_id()\G
*************************** 1. row ***************************
                thd_id: 533
               conn_id: 494
                  user: root@192.168.64.3
                    db: athena
               command: Sleep
                 state: NULL
                  time: 4696
     current_statement: NULL
      execution_engine: PRIMARY
     statement_latency: NULL
              progress: NULL # 进度百分比
          lock_latency:   0 ps
           cpu_latency:   0 ps
         rows_examined: 0
             rows_sent: 0
         rows_affected: 0
            tmp_tables: 0
       tmp_disk_tables: 0
             full_scan: NO
        last_statement: NULL
last_statement_latency: 18.82 us
        current_memory: 1.38 MiB
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
           trx_latency: 15.64 ms
             trx_state: COMMITTED
        trx_autocommit: NO
                   pid: NULL
          program_name: NULL

查看最近的事务执行信息

首先需要进行配置启用,事务事件默认并未启用

mysql> update setup_instruments set enabled='yes',timed='yes' where name like 'transaction';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> update setup_consumers set enabled='yes' where name like '%transaction%';
Query OK, 1 row affected (0.00 sec)
Rows matched: 3  Changed: 1  Warnings: 0

执行清理,避免其他事务干扰

mysql> truncate performance_schema.events_transactions_current; truncate performance_schema.events_transactions_history; truncate performance_schema.events_transactions_history_long;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

查看活跃的事务

mysql> select thread_id,event_name,state,trx_id,gtid,source,timer_wait,access_mode,isolation_level,autocommit,nesting_event_id,nesting_event_type from events_transactions_current\G
*************************** 1. row ***************************
         thread_id: 534
        event_name: transaction
             state: COMMITTED
            trx_id: 422087403820624
              gtid: AUTOMATIC
            source: handler.cc:1346
        timer_wait: 219049000
       access_mode: READ WRITE
   isolation_level: REPEATABLE READ
        autocommit: YES
  nesting_event_id: 1681
nesting_event_type: STATEMENT

查看历史的事务

mysql> select thread_id,event_name,state,trx_id,gtid,source,timer_wait,access_mode,isolation_level,autocommit,nesting_event_id,nesting_event_type from events_transactions_history_long limit 1\G
*************************** 1. row ***************************
         thread_id: 537
        event_name: transaction
             state: COMMITTED
            trx_id: 422087403821432
              gtid: AUTOMATIC
            source: handler.cc:1346
        timer_wait: 33152279000
       access_mode: READ WRITE
   isolation_level: REPEATABLE READ
        autocommit: YES
  nesting_event_id: 2237
nesting_event_type: STATEMENT
1 row in set (0.00 sec)

如果一个事务长时间未提交(长时间处于ACTIVE状态),对于这种情况,虽然从events_transactions_current表中可以查询到未提交的事务事件信息,但是并不能很直观地看到事务是什么时间点开始的,可以借助information_schema.innodb_trx表来进行辅助判断.
1
mysql> select * from information_schema.innodb_trx;

查看多线程复制报错详情

在启用了并行复制之后，一旦发生复制报错，通常通过show slave status语句无法查看到具体的报错详情（通过show slave status语句只能查看到SQL线程的报错信息
1
mysql> show slave status\G
根据报错提示查看performance_schema.replication_applier_status_by_worker表，在该表中详细记录了每一个Worker线程的详细信息
1
2
mysql> select * from performance_schema.replication_applier_status_by_worker where last_error_message!=''\G

其他收集

-- 1、哪类的SQL执行最多？
SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest WHERE SCHEMA_NAME NOT IN ('mysql','performance_schema') ORDER BY COUNT_STAR DESC;
-- 2、哪类SQL的平均响应时间最多？
SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest WHERE SCHEMA_NAME NOT IN ('mysql','performance_schema') ORDER BY COUNT_STAR DESC
-- 3、哪类SQL排序记录数最多？
SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest WHERE SCHEMA_NAME NOT IN ('mysql','performance_schema') ORDER BY COUNT_STAR DESC;
-- 4、哪类SQL扫描记录数最多？
SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest
WHERE SCHEMA_NAME NOT IN ('mysql','performance_schema') ORDER BY COUNT_STAR DESC;
-- 5、哪类SQL使用临时表最多？
SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest WHERE SCHEMA_NAME NOT IN ('mysql','performance_schema') ORDER BY COUNT_STAR DESC;
-- 6、哪类SQL返回结果集最多？
SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC;
-- 7、哪个表物理IO最多？
SELECT file_name,event_name,SUM_NUMBER_OF_BYTES_READ,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC;
-- 8、哪个表逻辑IO最多？
SELECT object_name,COUNT_READ,COUNT_WRITE,COUNT_FETCH,SUM_TIMER_WAIT FROM table_io_waits_summary_by_table WHERE OBJECT_SCHEMA NOT IN ('mysql','performance_schema') ORDER BY sum_timer_wait DESC;
-- 9、哪个索引访问最多？
SELECT OBJECT_NAME,INDEX_NAME,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE FROM table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA NOT IN ('mysql','performance_schema') ORDER BY SUM_TIMER_WAIT DESC;
-- 10、哪个索引从来没有用过？
SELECT OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA not in ('mysql','performance_schema') ORDER BY OBJECT_SCHEMA,OBJECT_NAME;
-- 11、哪个等待事件消耗时间最多？
SELECT EVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT,AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC
-- 12-1、剖析某条SQL的执行情况,包括statement信息,stege信息,wait信息
SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';
-- 12-2、查看每个阶段的时间消耗
SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = {EVENT_ID};
-- 12-3、查看每个阶段的锁等待情况
SELECT event_id,event_name,source,timer_wait,object_name,index_name,operation,nesting_event_id FROM events_waits_history_long WHERE nesting_event_id = {EVENT_ID};

参考文献

performance_schema

performance_schema中的表按照事件类型分类