PERFORMANCE_SCHEMA 详解

PERFORMANCE_SCHEMA 详解

原文地址

mysql 是否支持 performance_schema 引擎

mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES ;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ENGINE             | SUPPORT | COMMENT                                                        | TRANSACTIONS | XA   | SAVEPOINTS |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql服务器启用 performance_schema

my.cnf文件中
[mysqld]
performance_schema=ON
mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set (0.11 sec)

Performance Schema Instrument命名规则

最上层的instrument组件

  • idle

    idle instrument      组件
    
    idle event描述来自:socket_instances.STATE 列: Section 22.9.3.5, “The socket_instances Table”.
    
  • stage

    stage instrument     组件
    
    组成形式: stage/code_area/stage_name , code_area 一般是sql or myisam。
    stage name 一般来自: SHOW PROCESSLIST,如:Sorting result ,Sending data
    
  • statement

    Statement instrument 组件
    
    statement/abstract/* : 一般都是早期的stage,在抽象sql都还没来得及解析的时候。
    statement/com: SQL 命令操作 如:statement/com/Connect
    statement/sql: SQL语句操作 如:statement/sql/create_db
    
  • wait

    Wait Instrument      组件
    
    wait/io       : IO 等待事件
    wait/io/file  : 文件IO等待事件。等待文件操作完成的时间如:fwrite().但是物理IO有可能因为缓存的原因调用fwrite时不会写磁盘。
    wait/io/socket: socket相关的IO等待
    wait/io/table : 表相关的IO等待。一般对于记录rows来说有fetch,insert,update,delete四种操作。
                     不像其他等待事件,table I/O 还包含了其他的等待事件。                 比如:table io可能包含了文件IO和内存IO。因为读取table rows的时候,有可能会去从文件读取数据。
    
    * wait/lock:
        * wait/lock/table   : 表操作的锁等待事件
    * wait/synch:
        * wait/synch/cond   :condition就是线程与线程之间的信号。
        * wait/synch/mutex  : mutex主要用来锁住一块共享资源。
        * wait/synch/rwlock : 读写锁
    

收集所有的事件:

instrument 和 consumer 都会被enable , 所以一开始他们不会收集所有的事件。
为了让他们都enable 或者 enable event timing。
执行以下两条语句:
mysql> UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES';
Query OK, 338 rows affected (0.12 sec)
mysql> UPDATE setup_consumers SET ENABLED = 'YES';
Query OK, 8 rows affected (0.00 sec)
events_waits_history & events_waits_history_long 记录了每个thread最近10条和10000条event。

SELECT EVENT_ID, EVENT_NAME, TIMER_WAIT FROM events_waits_history WHERE THREAD_ID =21 ORDER BY EVENT_ID;
summary 表提供了整个时间段的一些统计信息。他们统计事件的处理方式和之前都不一样。如果想知道某个instrument 被执行的最频繁,或者发生的频率非常高,可以通过排序 events_waits_summary_global_by_event_name表,根据 COUNT_STAR 或者 SUM_TIMER_WAIT列。mysql> SELECT EVENT_NAME, COUNT_STAR FROM events_waits_summary_global_by_event_name ORDER BY COUNT_STAR DESC LIMIT 10;
+--------------------------------------+------------+
| EVENT_NAME                           | COUNT_STAR |
+--------------------------------------+------------+
| wait/io/file/sql/FRM                 |       1682 |
| wait/io/file/innodb/innodb_data_file |        596 |
| idle                                 |        204 |
| wait/io/file/sql/binlog              |        170 |
| wait/io/file/myisam/kfile            |        110 |
| wait/io/file/myisam/dfile            |         90 |
| wait/io/file/sql/binlog_index        |         47 |
| wait/io/file/innodb/innodb_log_file  |         18 |
| wait/io/file/sql/dbopt               |         14 |
| wait/io/file/sql/casetest            |         10 |
+--------------------------------------+------------+
10 rows in set (0.31 sec)
mysql> SELECT EVENT_NAME, SUM_TIMER_WAIT FROM events_waits_summary_global_by_event_name ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
+--------------------------------------+-------------------+
| EVENT_NAME                           | SUM_TIMER_WAIT    |
+--------------------------------------+-------------------+
| idle                                 | 20173041031000000 |
| wait/io/file/innodb/innodb_data_file |      666638377892 |
| wait/io/file/sql/dbopt               |      177140672226 |
| wait/io/file/sql/FRM                 |       91152018924 |
| wait/io/file/sql/binlog              |       43352476474 |
| wait/io/file/innodb/innodb_log_file  |       35962857780 |
| wait/io/file/sql/binlog_index        |       20679496964 |
| wait/io/file/myisam/kfile            |       16840088732 |
| wait/io/file/sql/ERRMSG              |        8064330196 |
| wait/io/file/myisam/dfile            |        4785274304 |
+--------------------------------------+-------------------+
10 rows in set (0.03 sec)
setup表用来配置和显示监控信息的。 例如:什么样的timer 被使用,请查询setup_timersmysql> SELECT * FROM setup_timers;
+-----------+-------------+
| NAME      | TIMER_NAME  |
+-----------+-------------+
| idle      | MICROSECOND |
| wait      | CYCLE       |
| stage     | NANOSECOND  |
| statement | NANOSECOND  |
+-----------+-------------+
4 rows in set (0.00 sec)
setup_instruments 列出了哪些event会被收集与监控:mysql> SELECT * FROM setup_instruments limit 10;
+-------------------------------------------------------+---------+-------+
| NAME                                                  | ENABLED | TIMED |
+-------------------------------------------------------+---------+-------+
| wait/synch/mutex/sql/PAGE::lock                       | YES     | YES   |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_sync           | YES     | YES   |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_active         | YES     | YES   |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_pool           | YES     | YES   |
| wait/synch/mutex/sql/LOCK_des_key_file                | YES     | YES   |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit       | YES     | YES   |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit_queue | YES     | YES   |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_done         | YES     | YES   |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_flush_queue  | YES     | YES   |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index        | YES     | YES   |
+-------------------------------------------------------+---------+-------+
10 rows in set (0.00 sec)
哪些event是不是instrument,可以给enabled设置yes or no

mysql>  UPDATE setup_instruments SET ENABLED = 'NO' WHERE NAME = 'wait/synch/mutex/sql/LOCK_mysql_create_db';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

消费者:接收EVENTS 的表

performance schema 使用收集的events 来更新performance_schema 数据库的那些表,这些表扮演着事件信息消费者的角色。setup_consumers 列出了可用的消费者 以及哪些是enabled.mysql>  SELECT * FROM setup_consumers;
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_stages_current          | YES     |
| events_stages_history          | YES     |
| events_stages_history_long     | YES     |
| events_statements_current      | YES     |
| events_statements_history      | YES     |
| events_statements_history_long | YES     |
| events_waits_current           | YES     |
| events_waits_history           | YES     |
| events_waits_history_long      | YES     |
| global_instrumentation         | YES     |
| thread_instrumentation         | YES     |
| statements_digest              | YES     |

MySQL在5.6版本中包含了一个强大的特性——performance-schema

合理的使用这个数据库中的表,能为我们解决一些瓶颈问题提供帮助,但是在我使用的5.6.21版本中,已经包含了52张表,后续还有可能会持续增加,所以搞清楚各个表之间的关系很重要。

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

1)setup_instruments [测量仪器]
使用performance-schema诊断问题的过程类似于医生给病人看病的过程。"setup_instruments"犹如医生能够使用的测量仪器(比如温度计,CT,X光等),在MySQL的官方文档中,使用“instrument”一词来表示,这应该也是这个表的名称的来源。这个表的每一行代表一个"instrument","instrument"在MySQL的源码中表示为一段为了测量相应指标的代码。这张表的三个字段分别为“name enabled timed”,enabled表示是否启用该"instrument", "timed"表示是否对其进行定量的耗时分析。

mysql> SELECT * FROM setup_instruments limit 10;
+-------------------------------------------------------+---------+-------+
| NAME                                                  | ENABLED | TIMED |
+-------------------------------------------------------+---------+-------+
| wait/synch/mutex/sql/PAGE::lock                       | YES     | YES   |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_sync           | YES     | YES   |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_active         | YES     | YES   |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_pool           | YES     | YES   |
| wait/synch/mutex/sql/LOCK_des_key_file                | YES     | YES   |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit       | YES     | YES   |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit_queue | YES     | YES   |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_done         | YES     | YES   |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_flush_queue  | YES     | YES   |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index        | YES     | YES   |
+-------------------------------------------------------+---------+-------+
10 rows in set (0.01 sec)

2)setup_objects
setup_objects类似某类病人(精神病,男科,妇科等),在MySQL中指table, 表示哪些表需要“instrument”,该表的默认值为:

mysql> SELECT * FROM setup_objects limit 10;                 
+-------------+--------------------+-------------+---------+-------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME | ENABLED | TIMED |
+-------------+--------------------+-------------+---------+-------+
| TABLE       | mysql              | %           | NO      | NO    |
| TABLE       | performance_schema | %           | NO      | NO    |
| TABLE       | information_schema | %           | NO      | NO    |
| TABLE       | %                  | %           | YES     | YES   |
+-------------+--------------------+-------------+---------+-------+
4 rows in set (0.00 sec)

表示出了系统表“mysql performance_schema information_schema”外,都需要对其进行“instrument”操作。

3)threads
"threads“类似于某个病人,在MySQL中的某个线程,这个表有一个非常重要的字段”INSTRUMENTED“,表示是否需要对该线程进行”instrument“,这个字段由”setup_actors“表中的内容决定。setup_actors的默认内容如下

mysql> SELECT THREAD_ID,NAME,TYPE,INSTRUMENTED,role FROM threads limit 10;
+-----------+---------------------------------+------------+--------------+------+
| THREAD_ID | NAME                            | TYPE       | INSTRUMENTED | role |
+-----------+---------------------------------+------------+--------------+------+
|         1 | thread/sql/main                 | BACKGROUND | YES          | NULL |
|         2 | thread/innodb/io_handler_thread | BACKGROUND | YES          | NULL |
|         3 | thread/innodb/io_handler_thread | BACKGROUND | YES          | NULL |
|         4 | thread/innodb/io_handler_thread | BACKGROUND | YES          | NULL |
|         5 | thread/innodb/io_handler_thread | BACKGROUND | YES          | NULL |
|         6 | thread/innodb/io_handler_thread | BACKGROUND | YES          | NULL |
|         7 | thread/innodb/io_handler_thread | BACKGROUND | YES          | NULL |
|         8 | thread/innodb/io_handler_thread | BACKGROUND | YES          | NULL |
|        11 | thread/innodb/io_handler_thread | BACKGROUND | YES          | NULL |
|         9 | thread/innodb/io_handler_thread | BACKGROUND | YES          | NULL |
+-----------+---------------------------------+------------+--------------+------+
10 rows in set (0.01 sec)

线程分前台线程和后台线程,后台线程指MySQL为了完成任务二自己开启的线程,这样的线程的setup_objects.INSTRUMENTED字段默认为true。

前台线程值用户操作而开启的线程。这时如果当前用户匹配setup_actors中的某个记录,那么setup_objects.INSTRUMENTED=true.
threads表的另一个功能是可以代替"show processlist"指令和"information_schema.processlist"表的功能,其不用获取同步的mutex对象,不会又那么重的额外消耗(overhead).

4)setup_consumers
setup_consumers表类似于各种诊断报告是否需要记录的配置,这个表的默认配置为:select * from setup_consumers;

mysql> select * from setup_consumers;
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_stages_current          | YES     |
| events_stages_history          | YES     |
| events_stages_history_long     | YES     |
| events_statements_current      | YES     |
| events_statements_history      | YES     |
| events_statements_history_long | YES     |
| events_waits_current           | YES     |
| events_waits_history           | YES     |
| events_waits_history_long      | YES     |
| global_instrumentation         | YES     |
| thread_instrumentation         | YES     |
| statements_digest              | YES     |
+--------------------------------+---------+
12 rows in set (0.00 sec)

5)setup_actors

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

6) setup_timers

mysql> select * from  setup_timers ;
+-----------+-------------+
| NAME      | TIMER_NAME  |
+-----------+-------------+
| idle      | MICROSECOND |
| wait      | CYCLE       |
| stage     | NANOSECOND  |
| statement | NANOSECOND  |
+-----------+-------------+
4 rows in set (0.00 sec)
这个表的各个记录还存在层级关系,只有当上级的配置启用时才会考虑下级的配置。
层级关系为:
global_instrumentation
|----thread_instrumentation
|         |----events_waits_current
|         |           |-events_waits_history
|         |           |-events_waits_history_long
|         |----events_stages_current
|         |           |-events_stages_history
|         |           |-events_stages_history_long
|         |----events_statements_current
|                     |-events_statements_history
|                     |-events_statements_history_long
|-----statements_digest

当global_instrumentation启用时,"thread_instrumentation"和"statements_digest"的配置才有可能生效。其他的配置类推。
只有当"setup_instruments","setup_objects",”setup_consumers“和"threads"都某一项测量指标都启用时才能收集到它的信息。

上一篇:Mac M1 安装 Composer


下一篇:vue3 script setup 定稿