MySQL 之 Performance Schema（mysql安装及配置超详细教程）

MySQL 之 Performance Schema

1. 介绍

Performance Schema提供了在数据库运行时实时检查MySQL服务器的内部执行情况的方法，通过监视MySQL服务器的事件来实现监视内部运行情况。

首先需要了解以下两个概念：

1. Instruments：生产者，在MySQL代码中插入探测代码，用于采集MySQL中各种各样的操作产生的事件信息，对应配置表中的配置项我们可以称为监控采集配置项。例如：如果想收集关于元数据锁的适用情况，需要启用wait/lock/meta-data/sql/mdl。

在performance_schema中，setup_instruments表包含了所有支持的生产者的列表，名称由 / 分隔的部分组成，例如：

• statement/sql/select
• wait/synch/mutex/innodb/autoinc_mutex

命名规则：最左边的部分表示类型，其余部分从左到右依次表示从通用到特定的字系统。

以上两个示例解释如下：

• statement/sql/select：select是sql子系统的一部分，属于statement类型。
• wait/synch/mutex/innodb/autoinc_mutex：autoinc_mutex属于innodb，它是更通用的类mutex的一部分，而mutex又是更通用的类synch的一部分，属于wait类型。

注意：setup_instruments表中大部分名称都是自描述型的，可参考列DOCUMENTATION，其包含了更多的详细信息，但是很多Instruments的DOCUMENTATION列可能为空，则可依据名称或者源代码来理解。

1. Consumers：消费者，对应的消费者表用于存储来自Instruments采集的数据，对应配置表中的配置项我们可以称为消费存储配置项。

采集的数据结果存储在performance schema库的多个表中，基于用途，大致可分为以下几个类别：

*_current：当前服务器上进行中的事件，当前数据。

*_history：每个线程最近完成的10个事件，历史数据。

*_history_long：从全局来看，每个线程最近完成的10000个事件，历史数据。

*_summary：聚合后的摘要表，还可以根据帐号(account)，主机(host)，程序(program)，线程(thread)，用户(user)和全局(global)再进行细分。

注：*_history 与 *_history_long 表的大小可以配置。

注：performance schema将数据存储在使用performance_schema引擎的表中，该引擎将数据存储在内存中。

MySQL还包括一个和performance_schema配套使用的sys schema，sys schema全部基于performance_schema上的视图和存储组成。

1. 设置

2.1 性能模式设置

性能模式是默认开启的：

show VARIABLES like 'performance_schema'

如果想要显式关闭或者开启，需要修改配置文件。

[mysqld]

performance_schema=OFF/ON

2.2 instruments与consumers设置

performance schema的部分设置只能在服务器启动时更改：比如启用或禁用performance schema本身以及与内存适用和数据收集的限制相关的变量。instruments和consumers可以被动态启用或禁用。

2.2.1 instruments启用或禁用

可以通过setup_instruments表查看instruments的状态：

select * from setup_instruments where name = 'statement/sql/select' \G

ENABLED为YES，说明已经启用。

有如下三种方法可以启用或禁用：

方法一： update语句

Update setup_instruments

Set ENABLED = 'no'

Where name = 'statement/sql/select';

注：重启后失效。

方法二：sys 存储过程

Sys schema 提供了两个存储过程：

ps_setup_enable_instrument

ps_setup_disable_instrument
用于启用和禁用。

例如：

Call sys.ps_setup_enable_instrument( 'statement/sql/select');

Call sys.ps_setup_disable_instrument( 'statement/sql/select');

注：重启后失效。

方法三：启动选项

如重启后配置不失效，则需要使用
performance-schema-instrument配置参数进行配置，此参数支持
performance-schema-instrument=’instrument_name=value’这样的语法，还支持%号进行通配。其中，instrument_name为instrument的名字，value可以为：on（off）、true（false）或者1（0）。

例1：指定开启单个instruments

[mysqld]

performance-schema-instrument='statement/sql/select=off'

例2：使用通配符指定开启多个instruments

performance-schema-instrument= 'wait/synch/cond/%=1'

例3：开启或禁用所有的instruments

performance-schema-instrument= '%=ON'

performance-schema-instrument= '%=OFF'

2.2.2 consumers启用或禁用

与instrument启用或禁用类似，也可以通过三种方法启用或禁用consumers：

方法一： update语句

Update setup_consumers

Set ENABLED = 'no'

Where name = 'events_statements_current';

方法二：sys 存储过程

Sys schema 提供了两个存储过程：

ps_setup_enable_consumer

ps_setup_disable_consumer
用于启用和禁用。

Call sys.ps_setup_enable_consumer( 'events_statements_current');

Call sys.ps_setup_disable_consumer( 'events_statements_current');

方法三：启动选项

使用
performance-schema-consumer配置参数进行配置。

2.3 特定对象的监控设置

Performance Schema 可以针对特定对象启用或禁用监控，其在setup_objects表中进行配置。

对象类型（OBJECT_TYPE）可以为EVENT，FUNCTION，PROCEDURE，TABLE，TRIGGER之一。另外，还可以指定OBJECT_SCHEMA，OBJECT_NAME，并且支持通配符。

例：要关闭test数据库中触发器的performance_schema采集，可以使用如下：

Insert into performance_schema.setup_objects(OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,ENABLED)

Values ('TRIGGER','test','%','no');

例：如果要保留名为utr_update_trigger的触发器的信息采集，可以使用如下：

Insert into performance_schema.setup_objects(OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,ENABLED)

Values ('TRIGGER','test','utr_update_trigger','yes');

当performance_schema决定是否需要监控特定对象时候，它会首先搜索更具体的规则，然后再退回到一般的规则。

则针对前面的示例，当用户触发了数据库test下的表的触发器utr_update_trigger，则会监控该触发器所触发的语句，但其它的触发器则不会监控。

注：这些对象无配置文件的选项，如果需要持久化，避免重启丢失，则需要将这些Insert语句写入SQL文件中，并在启动时候使用init_file选项加载该SQL文件。

2.4 线程的监控设置

setup_threads表包含了可以监控的线程列表。ENABLED列指定是否启用了特定线程的监控，HISTORY列指定特定线程的监控事件是否也应存储在_history和_history_long表中。

例如：禁用事件调度程序的历史日志纪录，执行：

UPDATE performance_schema.setup_threads

SET HISTORY='NO'

WHERE NAME='thread/sql/event_scheduler';

注：用户线程的设置不在setup_threads表中，而是在setup_actors表中。如果想要修改用户线程，则需要使用setup_actors表进行设置：

Insert into performance_schema.setup_actors(`HOST`,`USER`,ENABLED,HISTORY)

VALUES ('localhost','paul','YES','NO');

以上示例表明启用了paul@localhost的监控，但禁用了历史记录。

注：线程和actor都无配置文件的选项，如果需要持久化，避免重启丢失，则需要将这些Insert语句写入SQL文件中，并在启动时候使用init_file选项加载该SQL文件。

1. 使用performance schema

下面通过一些示例来演示如何使用performance schema来解决常见的故障。

3.1 检查SQL语句

检查SQL语句，需要启用statement类型的instruments。

3.1.1 常规SQL语句

performance schema将语句存储在events_statement_current（_history，_history_long）表中。

例如：

执行如下SQL：

select *

from person p1 join ( select name from person where name like '%dad%' and birthday <'2015-5-1' limit 10 ) as lim

on p1.name=lim.name

之后，进行如下查看：

select * from events_statements_history

where CURRENT_SCHEMA = 'test'

可以看到如下信息：

通常情况下，events_statements_history表中可以作为优化指标的列为：

要找出那些语句需要优化，则可以选择上述列中的任意一列，并与0进行比较。

例如，查找没有使用合适索引的查询：

select SQL_TEXT

, NO_INDEX_USED

, NO_GOOD_INDEX_USED

from events_statements_history

where NO_INDEX_USED > 0 or NO_GOOD_INDEX_USED > 0

同时，sys schema提供了需要优化的语句的视图，如下：

3.1.2 预处理语句

prepared_statements_instances包含服务器中存在的所有预处理语句，和events_statement_current表有相同的统计数据，此外还有关于预处理语句所属的线程以及该语句被执行了多少次的信息，不同events_statement_current表的是，统计数据是累加的。

检查预处理语句，需要启用的instruments为：

例如，执行如下：

PREPARE stmt from ' select count(1) from person where birthday < ?';

set @bd='2015-5-1';

EXECUTE stmt using @bd;

set @bd='2016-5-1';

EXECUTE stmt using @bd;

set @bd='2017-5-1';

EXECUTE stmt using @bd;

注：上述使用了不同的值，执行了3次。

查看监控结果：

SELECT STATEMENT_NAME

, SQL_TEXT

, COUNT_REPREPARE

, COUNT_EXECUTE

, SUM_TIMER_EXECUTE

from prepared_statements_instances

注意，当删除预处理语句后，将无法再访问相关的统计信息。

例如，执行：

DROP PREPARE stmt;

查看监控结果：

SELECT STATEMENT_NAME

, SQL_TEXT

, COUNT_REPREPARE

, COUNT_EXECUTE

, SUM_TIMER_EXECUTE

from prepared_statements_instances

3.1.3 存储过程

使用performance_schema可以监控存储过程如何执行的信息：例如，If... else流程控制语句的那个分支被执行了，或者是否调用了错误处理程序等。

要启用存储过程监控，需要启用匹配 ’statement/sp/%’的instruments。 statement/sp/stmt负责过程内部调用的语句，而其它负责跟踪事件，例如进入或离开过程，循环或者其它控制指令。

例如：

CREATE TABLE `t_uid` (

`id` int NOT NULL

) ;

CREATE PROCEDURE `usp_i_tuid`(val int)

BEGIN

DECLARE CONTINUE HANDLER FOR 1364,1048

BEGIN

INSERT IGNORE INTO t_uid VALUES('string');

get stacked diagnostics CONDITION 1 @stacked_state = returned_sqlstate;

get stacked diagnostics CONDITION 1 @stacked_msg = message_text;

END;

INSERT INTO t_uid VALUES(val);

END;

接下来，调用存储过程：

call usp_i_tuid(1);

查看监控信息：

SELECT THREAD_ID

, EVENT_NAME

, SQL_TEXT

from events_statements_history

where EVENT_NAME like 'statement/sp%'

再次调用存储过程：

call usp_i_tuid(null);

查看监控信息：

SELECT THREAD_ID

, EVENT_NAME

, SQL_TEXT

from events_statements_history

where EVENT_NAME like 'statement/sp%'

3.1.4 语句剖析

events_stages_[current|history|history_long]表包含语句剖析信息。例如MySQL在创建临时表，更新或等待锁时花费了多少时间。需要启用’stage/%’模式的instruments以及启用’events_stages%’模式的setup_consumers，启用后可以找到类似“查询执行的哪个阶段花费了非常长的时间”等问题。

例如：查找搜索耗时超过1秒的阶段：

select eshl.EVENT_NAME

,eshl.TIMER_WAIT

,eshl.TIMER_WAIT/10000000000 as ws

from events_stages_history_long eshl join events_statements_history_long esthl

on eshl.NESTING_EVENT_ID = esthl.EVENT_ID

where eshl.TIMER_WAIT> 1 * 10000000000;

代表不同性能问题的阶段：

3.2 检查读写性能

statement类型的instruments对于工作负载是读是写很有用，可以从统计各类型语句的执行量入手：

select EVENT_NAME

,count(EVENT_NAME)

from events_statements_history_long

GROUP BY EVENT_NAME

假如statement/sql/select类型的数量大，则表明读操作多。

如果需要获取读和写的字节数，可以使用全局状态变量Handler_：

with cte_read as (

select sum(VARIABLE_VALUE) as rows_read

from global_status

where VARIABLE_NAME in ('Handler_read_first','Handler_read_key','Handler_read_last','Handler_read_next'

,'Handler_read_prev','Handler_read_rnd','Handler_read_rnd_next')

),

cte_write as (

select sum(VARIABLE_VALUE) as rows_write

from global_status

where VARIABLE_NAME in ('Handler_write')

)

select * from cte_read,cte_write

3.3 检查元数据锁

元数据锁用于保护数据库对象定义不被修改。Metadata_locks表包含关于当前由不同线程设置的锁的信息，以及处于等待状态的锁请求信息。

启用元数据锁监控，需要启用
wait/lock/metadata/sql/mdl

使用如下语句获取监控信息：

select processlist_id,object_type,lock_type,lock_status,source

from metadata_locks join threads on owner_thread_id = thread_id

where object_schema='employees' and object_name='titles'

3.4 检查内存使用

启用内存监控，需要启用Memory类的instruments。

内存使用统计信息存储在以memory_summary_前缀开头的系列表中。

例如，查找占用大部分内存的innodb结构：

SELECT EVENT_NAME

, CURRENT_NUMBER_OF_BYTES_USED/1024/1024 as current_mb

, HIGH_NUMBER_OF_BYTES_USED/1024/1024 as high_mb

from memory_summary_global_by_event_name

where EVENT_NAME like 'memory/innodb/%'

order by CURRENT_NUMBER_OF_BYTES_USED DESC

limit 10;

也可以使用sys schema的memory_前缀开头的系列视图获取内存统计信息。

1. 常用的示例

4.1 哪类的SQL执行最多

SELECT DIGEST_TEXT

,COUNT_STAR

,FIRST_SEEN

,LAST_SEEN

FROM events_statements_summary_by_digest

ORDER BY COUNT_STAR DESC

4.2 哪类SQL的平均响应时间最多

SELECT DIGEST_TEXT

,AVG_TIMER_WAIT

FROM events_statements_summary_by_digest

ORDER BY COUNT_STAR DESC

4.3 哪类SQL排序记录数最多

SELECT DIGEST_TEXT

,SUM_SORT_ROWS

FROM events_statements_summary_by_digest

ORDER BY COUNT_STAR DESC

4.4 哪类SQL扫描记录数最多

SELECT DIGEST_TEXT

,SUM_ROWS_EXAMINED

FROM events_statements_summary_by_digest

ORDER BY COUNT_STAR DESC

4.5 哪类SQL使用临时表最多

SELECT DIGEST_TEXT

,SUM_CREATED_TMP_TABLES

,SUM_CREATED_TMP_DISK_TABLES

FROM events_statements_summary_by_digest

ORDER BY COUNT_STAR DESC

4.6 哪类SQL返回结果集最多

SELECT DIGEST_TEXT

,SUM_ROWS_SENT

FROM events_statements_summary_by_digest

ORDER BY COUNT_STAR DESC

4.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

4.8 哪个表逻辑IO最多

SELECT object_name

,COUNT_READ

,COUNT_WRITE

,COUNT_FETCH

,SUM_TIMER_WAIT

FROM table_io_waits_summary_by_table

ORDER BY sum_timer_wait DESC

4.9 哪个索引访问最多

SELECT OBJECT_NAME

,INDEX_NAME

,COUNT_FETCH

,COUNT_INSERT

,COUNT_UPDATE

,COUNT_DELETE

FROM table_io_waits_summary_by_index_usage

ORDER BY SUM_TIMER_WAIT DESC

4.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 <> 'mysql'

ORDER BY OBJECT_SCHEMA,OBJECT_NAME;

4.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

4.12剖析某条SQL的执行情况，包括statement信息，stege信息，wait信息

SELECT EVENT_ID

,sql_text

FROM events_statements_history

WHERE sql_text LIKE '%count(*)%'; -- 假设返回 EVENT_ID 为1523

查看每个阶段的时间消耗

SELECT event_id

,EVENT_NAME

,SOURCE

,TIMER_END - TIMER_START

FROM events_stages_history_long

WHERE NESTING_EVENT_ID = 1523;

查看每个阶段的锁等待情况

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 = 1523;