MySQL5.6 PERFORMANCE_SCHEMA 说明

╰+哭是因爲堅強的太久メ 2022-07-30 13:50 157阅读 0赞

背景：

[MySQL][] 5.5开始新增一个数据库：PERFORMANCE\_SCHEMA，主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE\_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE\_SCHEMA的表。[MySQL][]5.5默认是关闭的，需要手动开启，在配置文件里添加：

[view source][] [print][] [?][Link 1]

`1.` `[mysqld]`

`2.` `performance_schema=ON`

查看是否开启：

[view source][] [print][] [?][Link 1]

`1.` `mysql>show variables like ``'performance_schema'``;`

`2.` `+--------------------+-------+`

`3.` `| Variable_name      | Value |`

`4.` `+--------------------+-------+`

`5.` `| performance_schema | ON    |`

`6.` `+--------------------+-------+`

从MySQL5.6开始，默认打开，本文就从MySQL5.6来说明，在数据库使用当中PERFORMANCE\_SCHEMA的一些比较常用的功能。具体的信息可以查看官方文档。

相关表信息：

一：配置（setup）表：

[view source][] [print][] [?][Link 1]

`01.` `zjy``@performance_schema` `10``:``16``:``56``>show tables like ``'%setup%'``;`

`02.` `+----------------------------------------+`

`03.` `| Tables_in_performance_schema (%setup%) |`

`04.` `+----------------------------------------+`

`05.` `| setup_actors                           |`

`06.` `| setup_consumers                        |`

`07.` `| setup_instruments                      |`

`08.` `| setup_objects                          |`

`09.` `| setup_timers                           |`

`10.` `+----------------------------------------+`

1，setup\_actors：配置用户纬度的监控，默认监控所有用户。

[view source][] [print][] [?][Link 1]

`1.` `zjy``@performance_schema` `10``:``19``:``11``>select * from setup_actors;`

`2.` `+------+------+------+`

`3.` `| HOST | USER | ROLE |`

`4.` `+------+------+------+`

`5.` `| %    | %    | %    |`

`6.` `+------+------+------+`

2，setup\_consumers：配置events的消费者类型，即收集的events写入到哪些统计表中。

[view source][] [print][] [?][Link 1]

`01.` `zjy@: performance_schema ``10``:``23``:``35``>select * from setup_consumers;`

`02.` `+--------------------------------+---------+`

`03.` `| NAME                           | ENABLED |`

`04.` `+--------------------------------+---------+`

`05.` `| events_stages_current          | NO      |`

`06.` `| events_stages_history          | NO      |`

`07.` `| events_stages_history_long     | NO      |`

`08.` `| events_statements_current      | YES     |`

`09.` `| events_statements_history      | NO      |`

`10.` `| events_statements_history_long | NO      |`

`11.` `| events_waits_current           | NO      |`

`12.` `| events_waits_history           | NO      |`

`13.` `| events_waits_history_long      | NO      |`

`14.` `| global_instrumentation         | YES     |`

`15.` `| thread_instrumentation         | YES     |`

`16.` `| statements_digest              | YES     |`

`17.` `+--------------------------------+---------+`

这里需要说明的是需要查看哪个就更新其ENABLED列为YES。如：

[view source][] [print][] [?][Link 1]

`1.` `zjy``@performance_schema` `10``:``25``:``02``>update setup_consumers set ENABLED=``'YES'` `where NAME in (``'events_stages_current'``,``'events_waits_current'``);`

`2.` `Query OK, ``2` `rows affected (``0.00` `sec)`

更新完后立即生效，但是服务器重启之后又会变回默认值，要永久生效需要在配置文件里添加：

[view source][] [print][] [?][Link 1]

`1.` `[mysqld]`

`2.` `#performance_schema`

`3.` `performance_schema_consumer_events_waits_current=on`

`4.` `performance_schema_consumer_events_stages_current=on`

`5.` `performance_schema_consumer_events_statements_current=on`

`6.` `performance_schema_consumer_events_waits_history=on`

`7.` `performance_schema_consumer_events_stages_history=on`

`8.` `performance_schema_consumer_events_statements_history=on`

即在这些表的前面加上：performance\_schema\_consumer\_xxx。表setup\_consumers里面的值有个层级关系：

[view source][] [print][] [?][Link 1]

`1.` `global_instrumentation > thread_instrumentation = statements_digest > events_stages_current = events_statements_current = events_waits_current > events_stages_history = events_statements_history = events_waits_history > events_stages_history_long = events_statements_history_long = events_waits_history_long`

只有上一层次的为YES，才会继续检查该本层为YES or NO。global\_instrumentation是最高级别consumer，如果它设置为NO，则所有的consumer都会忽略。其中history和history\_long存的是current表的历史记录条数，history表记录了每个线程最近等待的10个事件，而history\_long表则记录了最近所有线程产生的10000个事件，这里的10和10000都是可以配置的。这三个表表结构相同，history和history\_long表数据都来源于current表。长度通过控制参数：

[view source][] [print][] [?][Link 1]

`01.` `zjy``@performance_schema` `11``:``10``:``03``>show variables like ``'performance_schema%history%size'``;`

`02.` `+--------------------------------------------------------+-------+`

`03.` `| Variable_name                                          | Value |`

`04.` `+--------------------------------------------------------+-------+`

`05.` `| performance_schema_events_stages_history_long_size     | ``10000` `|`

`06.` `| performance_schema_events_stages_history_size          | ``10`    `|`

`07.` `| performance_schema_events_statements_history_long_size | ``10000` `|`

`08.` `| performance_schema_events_statements_history_size      | ``10`    `|`

`09.` `| performance_schema_events_waits_history_long_size      | ``10000` `|`

`10.` `| performance_schema_events_waits_history_size           | ``10`    `|`

`11.` `+--------------------------------------------------------+-------+`

3，setup\_instruments：配置具体的instrument，主要包含4大类：idle、stage/xxx、statement/xxx、wait/xxx：

[view source][] [print][] [?][Link 1]

`01.` `zjy``@performance_schema` `10``:``56``:``35``>select name,count(*) from setup_instruments group by LEFT(name,``5``);`

`02.` `+---------------------------------+----------+`

`03.` `| name                            | count(*) |`

`04.` `+---------------------------------+----------+`

`05.` `| idle                            |        ``1` `|`

`06.` `| stage/sql/After create          |      ``111` `|`

`07.` `| statement/sql/select            |      ``179` `|`

`08.` `| wait/synch/mutex/sql/PAGE::lock |      ``296` `|`

`09.` `+---------------------------------+----------+`

idle表示socket空闲的时间，stage类表示语句的每个执行阶段的统计，statement类统计语句维度的信息，wait类统计各种等待事件，比如IO，mutux，spin\_lock,condition等。

4，setup\_objects：配置监控对象，默认对mysql，performance\_schema和information\_schema中的表都不监控，而其它DB的所有表都监控。

[view source][] [print][] [?][Link 1]

`01.` `zjy``@performance_schema` `11``:``00``:``18``>select * from setup_objects;`

`02.` `+-------------+--------------------+-------------+---------+-------+`

`04.` `+-------------+--------------------+-------------+---------+-------+`

`05.` `| TABLE       | mysql              | %           | NO      | NO    |`

`06.` `| TABLE       | performance_schema | %           | NO      | NO    |`

`07.` `| TABLE       | information_schema | %           | NO      | NO    |`

`08.` `| TABLE       | %                  | %           | YES     | YES   |`

`09.` `+-------------+--------------------+-------------+---------+-------+`

5，setup\_timers：配置每种类型指令的统计时间单位。MICROSECOND表示统计单位是微妙，CYCLE表示统计单位是时钟周期，时间度量与CPU的主频有关，NANOSECOND表示统计单位是纳秒。但无论采用哪种度量单位，最终统计表中统计的时间都会装换到皮秒。（1秒＝1000000000000皮秒）

[view source][] [print][] [?][Link 1]

`01.` `zjy``@performance_schema` `11``:``05``:``12``>select * from setup_timers;`

`02.` `+-----------+-------------+`

`03.` `| NAME      | TIMER_NAME  |`

`04.` `+-----------+-------------+`

`05.` `| idle      | MICROSECOND |`

`06.` `| wait      | CYCLE       |`

`07.` `| stage     | NANOSECOND  |`

`08.` `| statement | NANOSECOND  |`

`09.` `+-----------+-------------+`

二：instance表

1，cond\_instances：条件等待对象实例

表中记录了系统中使用的条件变量的对象，OBJECT\_INSTANCE\_BEGIN为对象的内存地址。

2，file\_instances：文件实例

表中记录了系统中打开了文件的对象，包括ibdata文件，redo文件，binlog文件，用户的表文件等，open\_count显示当前文件打开的数目，如果重来没有打开过，不会出现在表中。

[view source][] [print][] [?][Link 1]

`01.` `zjy``@performance_schema` `11``:``20``:``04``>select * from file_instances limit ``2``,``5``;`

`02.` `+---------------------------------+--------------------------------------+------------+`

`03.` `| FILE_NAME                       | EVENT_NAME                           | OPEN_COUNT |`

`04.` `+---------------------------------+--------------------------------------+------------+`

`05.` `| /var/lib/mysql/mysql/plugin.frm | wait/io/file/sql/FRM                 |          ``0` `|`

`06.` `| /var/lib/mysql/mysql/plugin.MYI | wait/io/file/myisam/kfile            |          ``1` `|`

`07.` `| /var/lib/mysql/mysql/plugin.MYD | wait/io/file/myisam/dfile            |          ``1` `|`

`08.` `| /var/lib/mysql/ibdata1          | wait/io/file/innodb/innodb_data_file |          ``2` `|`

`09.` `| /var/lib/mysql/ib_logfile0      | wait/io/file/innodb/innodb_log_file  |          ``2` `|`

`10.` `+---------------------------------+--------------------------------------+------------+`

3，mutex\_instances：互斥同步对象实例

表中记录了系统中使用互斥量对象的所有记录，其中name为：wait/synch/mutex/\*。LOCKED\_BY\_THREAD\_ID显示哪个线程正持有mutex，若没有线程持有，则为NULL。

4，rwlock\_instances： 读写锁同步对象实例

表中记录了系统中使用读写锁对象的所有记录，其中name为 wait/synch/rwlock/\*。WRITE\_LOCKED\_BY\_THREAD\_ID为正在持有该对象的thread\_id，若没有线程持有，则为NULL。READ\_LOCKED\_BY\_COUNT为记录了同时有多少个读者持有读锁。（通过 events\_waits\_current 表可以知道，哪个线程在等待锁；通过rwlock\_instances知道哪个线程持有锁。rwlock\_instances的缺陷是，只能记录持有写锁的线程，对于读锁则无能为力）。

5，socket\_instances：活跃会话对象实例  
表中记录了thread\_id,socket\_id,ip和port，其它表可以通过thread\_id与socket\_instance进行关联，获取IP-PORT信息，能够与应用对接起来。  
event\_name主要包含3类：  
wait/io/socket/sql/server\_unix\_socket，服务端unix监听socket  
wait/io/socket/sql/server\_tcpip\_socket，服务端tcp监听socket  
wait/io/socket/sql/client\_connection，客户端socket

三：Wait表

1，events\_waits\_current：记录了当前线程等待的事件

2，events\_waits\_history：记录了每个线程最近等待的10个事件

3，events\_waits\_history\_long：记录了最近所有线程产生的10000个事件

表结构定义如下：

[view source][] [print][] [?][Link 1]

`01.` ``CREATE TABLE `events_waits_current` (``

`02.` `` `THREAD_ID` bigint(```20``) unsigned NOT NULL COMMENT ``'线程ID'``,`

`03.` `` `EVENT_ID` bigint(```20``) unsigned NOT NULL COMMENT ``'当前线程的事件ID，和THREAD_ID确定唯一'``,`

`04.` `` `END_EVENT_ID` bigint(```20``) unsigned DEFAULT NULL COMMENT ``'当事件开始时，这一列被设置为NULL。当事件结束时，再更新为当前的事件ID'``,`

`05.` `` `EVENT_NAME` varchar(```128``) NOT NULL COMMENT ``'事件名称'``,`

`06.` `` `SOURCE` varchar(```64``) DEFAULT NULL COMMENT ``'该事件产生时的源码文件'``,`

`07.` `` `TIMER_START` bigint(```20``) unsigned DEFAULT NULL COMMENT ``'事件开始时间（皮秒）'``,`

`08.` `` `TIMER_END` bigint(```20``) unsigned DEFAULT NULL COMMENT ``'事件结束结束时间（皮秒）'``,`

`09.` `` `TIMER_WAIT` bigint(```20``) unsigned DEFAULT NULL COMMENT ``'事件等待时间（皮秒）'``,`

`10.` `` `SPINS` ```int``(``10``) unsigned DEFAULT NULL COMMENT ``''``,`

`11.` `` `OBJECT_SCHEMA` varchar(```64``) DEFAULT NULL COMMENT ``'库名'``,`

`12.` `` `OBJECT_NAME` varchar(```512``) DEFAULT NULL COMMENT ``'文件名、表名、IP:SOCK值'``,`

`13.` `` `OBJECT_TYPE` varchar(```64``) DEFAULT NULL COMMENT ``'FILE、TABLE、TEMPORARY TABLE'``,`

`14.` `` `INDEX_NAME` varchar(```64``) DEFAULT NULL COMMENT ``'索引名'``,`

`15.` `` `OBJECT_INSTANCE_BEGIN` bigint(```20``) unsigned NOT NULL COMMENT ``'内存地址'``,`

`16.` `` `NESTING_EVENT_ID` bigint(```20``) unsigned DEFAULT NULL COMMENT ``'该事件对应的父事件ID'``,`

`17.` `` `NESTING_EVENT_TYPE` ```enum``(``'STATEMENT'``,``'STAGE'``,``'WAIT'``) DEFAULT NULL COMMENT ``'父事件类型(STATEMENT, STAGE, WAIT)'``,`

`18.` `` `OPERATION` varchar(```32``) NOT NULL COMMENT ``'操作类型（lock, read, write）'``,`

`19.` `` `NUMBER_OF_BYTES` bigint(```20``) DEFAULT NULL COMMENT ``''``,`

`20.` `` `FLAGS` ```int``(``10``) unsigned DEFAULT NULL COMMENT ``'标记'`

`21.` `) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8`

四：Stage 表

1，events\_stages\_current：记录了当前线程所处的执行阶段

2，events\_stages\_history：记录了当前线程所处的执行阶段10条历史记录

3，events\_stages\_history\_long：记录了当前线程所处的执行阶段10000条历史记录

表结构定义如下：

[view source][] [print][] [?][Link 1]

`01.` ``CREATE TABLE `events_stages_current` (``

`02.` `` `THREAD_ID` bigint(```20``) unsigned NOT NULL COMMENT ``'线程ID'``,`

`03.` `` `EVENT_ID` bigint(```20``) unsigned NOT NULL COMMENT ``'事件ID'``,`

`04.` `` `END_EVENT_ID` bigint(```20``) unsigned DEFAULT NULL COMMENT ``'结束事件ID'``,`

`05.` `` `EVENT_NAME` varchar(```128``) NOT NULL COMMENT ``'事件名称'``,`

`06.` `` `SOURCE` varchar(```64``) DEFAULT NULL COMMENT ``'源码位置'``,`

`07.` `` `TIMER_START` bigint(```20``) unsigned DEFAULT NULL COMMENT ``'事件开始时间（皮秒）'``,`

`08.` `` `TIMER_END` bigint(```20``) unsigned DEFAULT NULL COMMENT ``'事件结束结束时间（皮秒）'``,`

`09.` `` `TIMER_WAIT` bigint(```20``) unsigned DEFAULT NULL COMMENT ``'事件等待时间（皮秒）'``,`

`10.` `` `NESTING_EVENT_ID` bigint(```20``) unsigned DEFAULT NULL COMMENT ``'该事件对应的父事件ID'``,`

`11.` `` `NESTING_EVENT_TYPE` ```enum``(``'STATEMENT'``,``'STAGE'``,``'WAIT'``) DEFAULT NULL COMMENT ``'父事件类型(STATEMENT, STAGE, WAIT)'`

`12.` `) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8`

五：Statement 表

1，events\_statements\_current：通过 thread\_id+event\_id可以唯一确定一条记录。Statments表只记录最顶层的请求，SQL语句或是COMMAND，每条语句一行。event\_name形式为statement/sql/\*，或statement/com/\*

2，events\_statements\_history

3，events\_statements\_history\_long

表结构定义如下：

[view source][] [print][] [?][Link 1]

`01.` ``CREATE TABLE `events_statements_current` (``