Performance Schema使用简介(一)

向右看齐 2022-06-13 02:13 325阅读 0赞

作者  董红禹

沃趣科技MySQL高级技术专家

Performance Schema简介

Oracle DBA都应该知道 Oracle中提供了大量的视图供DBA们排查问题使用，并且有等待事件帮助大家快速定位问题属于哪一类。MySQL 中也有Performance Schema帮助大家去分析排查问题，并且在5.7中增加了Sys Schema，将Performance Schema和information\_schema的信息格式化后，供大家更方便的分析问题。

这里先介绍先Performance Schema的使用方式，便于后面大家更好的去使用Sys Schema。

Performance Schema开启方式

*  开启Performance Schema库

> 在配置文件中添加performance\_schema=on

*  设置instruments及consumers开启

开启performance\_schema=on之后我们还需要进行设置instruments及consumers

instruments通俗讲就是监控项可以通过setup\_instruments表设置需要开启对哪些项监控进行统计

consumers就是控制是否将监控到的结果进行记录

> UPDATE setup\_instruments SET ENABLED = 'YES', TIMED = 'YES';
> 
> UPDATE setup\_consumers SET ENABLED = 'YES';

如果不开启instruments和consumers则相应的事件检测则不能使用，也就不能收集相应的等待事件和性能的统计。

当然我们也可以单独设置某一个instruments的开启和关闭

> UPDATE setup\_instruments SET ENABLED = 'NO'
> 
> WHERE NAME = 'wait/io/file/sql/binlog';

为了防止重启后恢复默认配置可以添加到配置文件中设置

> performance-schema-instrument='instrument\_name=value'
> 
> performance-schema-consumer-consumer\_name=value   
> 
> 
> instrument\_name和consumer\_name

> 可以通过查询setup\_instruments和setup\_consumers;查看

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)

setup\_consumers中的事件表有层级之分 ，我们可以通过上面看到有 global、thread、current、history、history\_long，他们之间的层级如下:

> 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的才会去检查thread的有没有被设置，如果thread的设置了才会去检查current的有没有被设置，如果global的没有被设置则thread和current都不去检查，相应的信息也不会记录到对应的表中。

如果global\_instrumentation没有被设置则对应的表中都不会记录任何信息。

这里的history、history\_long可以通过参数设置大小,因为是内存表，如果太大会占用很多内存。

setup\_instruments中的类型

> mysql> select name,count(\*) from setup\_instruments group by left(name,5);
> 
> \+---------------------------------+----------+  
> | name                            | count(\*) |  
> \+---------------------------------+----------+  
> | idle                            |        1 |  
> | stage/sql/After create          |      108 |  
> | statement/sql/select            |      168 |  
> | wait/synch/mutex/sql/PAGE::lock |      279 |  
> \+---------------------------------+----------+  
> 
> 
> 4 rows in set (0.00 sec)

我们可以看到setup\_instruments中的类型有四种(5.6版本)。

具体的解释大家可以在官网中查看:

https://dev.mysql.com/doc/refman/5.6/en/performance-schema-instrument-naming.html

简单使用介绍

简单介绍几个语句用于排查SQL语句的效率

*  查询没有使用到索引或者索引效率低下的语句:

> SELECT OBJECT\_SCHEMA,  THREAD\_ID TID, SUBSTR(SQL\_TEXT, 1, 50) SQL\_TEXT, ROWS\_SENT RS, ROWS\_EXAMINED RE,CREATED\_TMP\_TABLES,NO\_INDEX\_USED,NO\_GOOD\_INDEX\_USED FROM performance\_schema.events\_statements\_history WHERE (NO\_INDEX\_USED=1 OR NO\_GOOD\_INDEX\_USED=1) and sql\_text  NOT LIKE '%performance\_schema%'\\G

*  查看哪些索引没有被使用过

> 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 <> 'mysql'  
> 
> 
> ORDER BY OBJECT\_SCHEMA,OBJECT\_NAME;

*  查看SQL语句在哪个阶段消耗最大

> SELECT eshl.event\_name, sql\_text, eshl.timer\_wait/1000000000000 w\_s
> 
> FROM performance\_schema.events\_stages\_history\_long eshl  
> JOIN performance\_schema.events\_statements\_history\_long esthl  
> ON (eshl.nesting\_event\_id = esthl.event\_id)  
> 
> 
> WHERE eshl.timer\_wait > 1\*10000000000\\G

总  结

此次简单介绍了Performance Schema的使用，后面会逐步介绍使用Performance Schema的一些实战功能。