Home >Database >Mysql Tutorial >MySQL5.6PERFORMANCE_SCHEMA说明_MySQL

MySQL5.6PERFORMANCE_SCHEMA说明_MySQL

PHP中文网Original: 2016-05-27 13:45:091420browse

背景：

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

view sourceprint?1.<code class="plain">[mysqld]<code class="number">2.<code class="plain">performance_schema=ON

<code class="plain"><code class="number"><code class="plain">查看是否开启：

view
 sourceprint?
1.
mysql>show
 variables like 
&#39;performance_schema&#39;
;
2.
+--------------------+-------+
3.
|
 Variable_name      | Value |
4.
+--------------------+-------+
5.
|
 performance_schema | 
ON
    |
6.
+--------------------+-------+

<code class="plain"><code class="number"><code class="plain"><code class="number"><code class="plain"><code class="string"><code class="plain"><code class="number"><code class="plain"><code class="number"><code class="plain"><code class="number"><code class="plain"><code class="number"><code class="plain"><code class="number"><code class="plain">

<code class="number"><code class="plain"><code class="number"><code class="plain"><code class="number"><code class="plain">从MySQL5.6开始，默认打开，本文就从MySQL5.6来说明，在数据库使用当中PERFORMANCE_SCHEMA的一些比较常用的功能。具体的信息可以查看官方文档。

<code class="number"><code class="plain"><code class="number"><code class="plain"><code class="number"><code class="plain">相关表信息：

<code class="number"><code class="plain"><code class="number"><code class="plain"><code class="number"><code class="plain">一：配置（setup）表：

view
 sourceprint?
01.
zjy
@performance_schema 
10
:
16
:
56
>show
 tables like 
&#39;%setup%&#39;
;
02.
+----------------------------------------+
03.
|
 Tables_in_performance_schema (%setup%) |
04.
+----------------------------------------+
05.
|
 setup_actors                           |
06.
|
 setup_consumers                        |
07.
|
 setup_instruments                      |
08.
|
 setup_objects                          |
09.
|
 setup_timers                           |
10.
+----------------------------------------+

<code class="color1"><code class="value"><code class="value"><code class="value"><code class="string"><code class="number"><code class="plain"><code class="number"><code class="plain"><code class="number"><code class="plain">1，setup_actors：配置用户纬度的监控，默认监控所有用户。

view
 sourceprint?
1.
zjy
@performance_schema 
10
:
19
:
11
>select
 * from setup_actors;
2.
+------+------+------+
3.
|
 HOST | USER | ROLE |
4.
+------+------+------+
5.
|
 %    | %    | %    |
6.
+------+------+------+

<code class="color1"><code class="string"><code class="color1"><code class="value"><code class="value"><code class="value"><code class="number"><code class="plain"><code class="number"><code class="plain"><code class="number"><code class="plain">2，setup_consumers：配置events的消费者类型，即收集的events写入到哪些统计表中。

view
 sourceprint?
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.
+--------------------------------+---------+

<code class="color1"><code class="string"><code class="color1"><code class="value"><code class="value"><code class="value"><code class="number"><code class="plain"><code class="number"><code class="plain"><code class="number"><code class="plain">这里需要说明的是需要查看哪个就更新其ENABLED列为YES。如：

view
 sourceprint?
1.
zjy
@performance_schema 
10
:
25
:
02
>update
 setup_consumers set ENABLED=
&#39;YES&#39; 
where
 NAME in (
&#39;events_stages_current&#39;
,
&#39;events_waits_current&#39;
);
2.
Query
 OK, 
2 
rows
 affected (
0.00 
sec)

<code class="color1"><code class="number"><code class="number"><code class="color1"><code class="value"><code class="string"><code class="string"><code class="string"><code class="number"><code class="plain"><code class="value"><code class="plain"><code class="value"><code class="plain">更新完后立即生效，但是服务器重启之后又会变回默认值，要永久生效需要在配置文件里添加：

view
 sourceprint?
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

<code class="color1"><code class="color1"><code class="value"><code class="string"><code class="string"><code class="string"><code class="value"><code class="value"><code class="number"><code class="plain"><code class="number"><code class="plain"><code class="number"><code class="plain">即在这些表的前面加上：performance_schema_consumer_xxx。表setup_consumers里面的值有个层级关系：

view
 sourceprint?
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

<code class="color1"><code class="color1"><code class="value"><code class="string"><code class="string"><code class="string"><code class="value"><code class="value"><code class="number"><code class="plain"><code class="number"><code class="plain"><code class="number"><code class="plain">只有上一层次的为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
 sourceprint?
01.
zjy
@performance_schema 
11
:
10
:
03
>show
 variables like 
&#39;performance_schema%history%size&#39;
;
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
 sourceprint?
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.
+---------------------------------+----------+

<code class="string"><code class="string"><code class="color1"><code class="string"><code class="color1"><code class="value"><code class="number"><code class="value"><code class="number"><code class="plain"><code class="value"><code class="plain"><code class="number"><code class="plain">idle表示socket空闲的时间，stage类表示语句的每个执行阶段的统计，statement类统计语句维度的信息，wait类统计各种等待事件，比如IO，mutux，spin_lock,condition等。

<code class="string"><code class="string"><code class="color1"><code class="string"><code class="color1"><code class="value"><code class="number"><code class="value"><code class="number"><code class="plain"><code class="value"><code class="plain"><code class="number"><code class="plain">4，setup_objects：配置监控对象，默认对mysql，performance_schema和information_schema中的表都不监控，而其它DB的所有表都监控。

view
 sourceprint?
01.
zjy
@performance_schema 
11
:
00
:
18
>select
 * from setup_objects;
02.
+-------------+--------------------+-------------+---------+-------+
03.
|
 OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME | ENABLED | TIMED |
04.
+-------------+--------------------+-------------+---------+-------+
05.
|
 TABLE       | mysql              | %           | NO      | NO    |
06.
|
 TABLE       | performance_schema | %           | NO      | NO    |
07.
|
 TABLE       | information_schema | %           | NO      | NO    |
08.
|
 TABLE       | %                  | %           | 
YES
     | 
YES
   |
09.
+-------------+--------------------+-------------+---------+-------+

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

view
 sourceprint?
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.
+-----------+-------------+

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：MySql的隔离级别和锁的关系_MySQLNext article：Centos7mysql5.5启用innodb引擎_MySQL

See more

MySQL5.6PERFORMANCE_SCHEMA说明_MySQL

Related articles