背景
performance_schema最早在MYSQL 5.5中出现，而现在5.6，5.7中performance_schema又添加了更多的监控项，统计信息也更丰富，真乃DBA童鞋进行性能诊断分析的福音。
检查当前数据库版本是否支持
performance_schema被视为存储引擎。 如果该引擎可用，则应该在INFORMATION_SCHEMA.ENGINES表或SHOW ENGINES语句的输出中都可以看到它的SUPPORT值为YES
但知道我们的实例支持performance_schema引擎就可以使用了吗？NO，很遗憾，performance_schema在5.6及其之前的版本中，默认没有启用，从5.7及其之后的版本才修改为默认启用。现在，我们来看看如何设置performance_schema默认启用吧！ 我们需要使用参数performance_schema=ON|OFF设置，并在my.cnf中进行配置：
[mysqld] performance_schema = ON # 注意：该参数为只读参数，需要在实例启动之前设置才生效
查看是否生效
SHOW VARIABLES LIKE 'performance_schema';
概念
instruments ：生产者，用于采集MySQL 中各种各样的操作产生的事件信息，对应配置表中的配置项我们可以称为监控采集配置项，以下提及生产者均统称为instruments
consumers ：消费者，对应的消费者表用于存储来自instruments采集的数据，对应配置表中的配置项我们可以称为消费存储配置项，以下提及消费者均统称为consumers
配置表
Performance-Schema中主要有5个配置表，具体如下：
show tables like '%setup%'; +----------------------------------------+ | Tables_in_performance_schema (%setup%) | +----------------------------------------+ | setup_actors | | setup_consumers | | setup_instruments | | setup_objects | | setup_timers | +----------------------------------------+
1.setup_actors用于配置user维度的监控，默认情况下监控所有用户线程。
select * from setup_actors; +------+------+------+ | HOST | USER | ROLE | +------+------+------+ | % | % | % | +------+------+------+
2.setup_consumers表用于配置事件的消费者类型，即收集的事件最终会写入到哪些统计表中。
select * from setup_consumers; +--------------------------------+---------+ | NAME | ENABLED | +--------------------------------+---------+ | events_stages_current | NO | | events_stages_history | NO | | events_stages_history_long | NO | | events_statements_current | YES | | events_statements_history | NO | | events_statements_history_long | NO | | events_waits_current | NO | | events_waits_history | NO | | events_waits_history_long | NO | | global_instrumentation | YES | | thread_instrumentation | YES | | statements_digest | YES | +--------------------------------+---------+
可以看到有12个consumer，如果不想关注某些consumer，可以将ENABLED设置为NO，比如events_statements_history_long设置为NO，
则收集事件不会写入到对应的表events_statements_history_long中。12个consumer不是平级的，存在多级层次关系。具体如下表：
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
多层次的consumer遵从一个基本原则，只有上一层次的为YES，才会继续检查该本层为YES or NO。global_instrumentation是最高级别consumer，如果它设置为NO，则所有的consumer都会忽略。如果只打开global_instrumentation，而关闭所有其它子consumer(设置为NO)，则只收集全局维度的统计信息，比如xxx_instance表，而不会收集用户维度，语句维度的信息。第二层次的是thread_instrumentation，用户线程维度的统计信息，比如xxx_by_thread表，另外一个是statements_digest，这个用于全局统计SQL-digest的信息。第三层次是语句维度，包括events_waits_current，events_stages_current和events_statements_current，分别用于统计wait，stages和statement信息，第四层次是历史表信息，主要包括xxx_history和xxx_history_long。
3.setup_instruments表用于配置一条条具体的instrument，主要包含4大类：idle,stage/xxx,statement/xxx,wait/xxx.
select name,count(*) from setup_instruments group by LEFT(name,5); +---------------------------------+----------+ | name | count(*) | +---------------------------------+----------+ | idle | 1 | | stage/sql/After create | 111 | | statement/sql/select | 170 | | wait/synch/mutex/sql/PAGE::lock | 296 | +---------------------------------+----------+
idle表示socket空闲的时间，stage类表示语句的每个执行阶段的统计，statement类统计语句维度的信息，wait类统计各种等待事件，比如IO，mutux，spin_lock,condition等。从上表统计结果来看，可以基本看到每类的instrument数目，stage包含111个，statement包含170个，wait包含296个。
4.setup_objects表用于配置监控对象，默认情况下所有mysql，performance_schema和information_schema中的表都不监控。而其它DB的所有表都监控。
select * from setup_objects; +-------------+--------------------+-------------+---------+-------+ | 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 | +-------------+--------------------+-------------+---------+-------+
5.setup_timers表用于配置每种类型指令的统计时间单位。MICROSECOND表示统计单位是微妙，CYCLE表示统计单位是时钟周期，时间度量与CPU的主频有关，NANOSECOND表示统计单位是纳秒，关于每种类型的具体含义，可以参考performance_timer这个表。由于wait类包含的都是等待事件，单个SQL调用次数比较多，因此选择代价最小的度量单位cycle。但无论采用哪种度量单位，最终统计表中统计的时间都会转换到皮秒。
select * from setup_timers； +-----------+-------------+ | NAME | TIMER_NAME | +-----------+-------------+ | idle | MICROSECOND | | wait | CYCLE | | stage | NANOSECOND | | statement | NANOSECOND | +-----------+-------------+
配置方式
默认情况下，setup_instruments表只打开了statement和wait/io部分的指令，setup_consumer表中很多consumer也没有打开。为了打开需要的选项，可以通过update语句直接修改配置表，并且修改后可以立即生效，但这种方式必须得启动服务器后才可以修改，并且无法持久化，重启后，又得重新设置一遍。从5.6.4开始提供了my.cnf的配置方式，格式如下：
1.设置采集的instrument
performance_schema_instrument='instrument_name=value'
(1)打开wait类型的指令
performance_schema_instrument='wait/%'
(2)打开所有指令
performance_schema_instrument='%=on'
2.设置consumer
performance_schema_consumer_xxx=value
(1)打开 events_waits_history consumer
performance_schema_consumer_events_waits_current=on performance_schema_consumer_events_waits_history=on
这里要注意consumer的层次关系， events_waits_history处于第4层，因此设置它时，要确保events_statements_current，thread_instrumentation和global_instrumentation的ENABLED状态都为YES，才能生效。由于默认thread_instrumentation和global_instrumentation都是YES，因此只需要显示设置events_waits_current和events_waits_current即可。
3.设置统计表大小
所有的performance_schema表均采用PERFORMANCE_SCHEMA存储引擎，表中的所有数据只存在内存，表的大小在系统初始化时已经
固定好，因此占用的内存是一定的。可以通过配置来定制具体每个表的记录数。
performance_schema_events_waits_history_size=20 performance_schema_events_waits_history_long_size=15000
扫一扫
3237
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
