PERFORMANCE_SCHEMA 详解

本文主要是介绍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"都某一项测量指标都启用时才能收集到它的信息。

这篇关于PERFORMANCE_SCHEMA 详解的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

C/C++教程