Heim > Datenbank > MySQL-Tutorial > MySQL5.6PERFORMANCE_SCHEMA说明_MySQL

MySQL5.6PERFORMANCE_SCHEMA说明_MySQL

PHP中文网
Freigeben: 2016-05-27 13:45:09
Original
1381 Leute haben es durchsucht

背景:

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.
+--------------------+-------+
Nach dem Login kopieren

<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.
+----------------------------------------+
Nach dem Login kopieren

<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="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.
+------+------+------+
Nach dem Login kopieren

<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="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.
+--------------------------------+---------+
Nach dem Login kopieren

<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="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)
Nach dem Login kopieren

<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="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
Nach dem Login kopieren

<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="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
Nach dem Login kopieren

<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="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.
+---------------------------------+----------+
Nach dem Login kopieren

<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="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.
+-------------+--------------------+-------------+---------+-------+
Nach dem Login kopieren

<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="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.
+-----------+-------------+
Nach dem Login kopieren

<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="string"><code class="string"><code class="color1"><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"><strong>二:instance表</strong>

<code class="string"><code class="string"><code class="color1"><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">1,cond_instances:条件等待对象实例

<code class="string"><code class="string"><code class="color1"><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">表中记录了系统中使用的条件变量的对象,OBJECT_INSTANCE_BEGIN为对象的内存地址。

<code class="string"><code class="string"><code class="color1"><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">2,file_instances:文件实例

<code class="string"><code class="string"><code class="color1"><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">表中记录了系统中打开了文件的对象,包括ibdata文件,redo文件,binlog文件,用户的表文件等,open_count显示当前文件打开的数目,如果重来没有打开过,不会出现在表中。

view
 sourceprint?
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.
+---------------------------------+--------------------------------------+------------+
Nach dem Login kopieren

<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="string"><code class="string"><code class="color1"><code class="color1"><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">3,mutex_instances:互斥同步对象实例

<code class="string"><code class="string"><code class="color1"><code class="color1"><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">表中记录了系统中使用互斥量对象的所有记录,其中name为:wait/synch/mutex/*。LOCKED_BY_THREAD_ID显示哪个线程正持有mutex,若没有线程持有,则为NULL。

<code class="string"><code class="string"><code class="color1"><code class="color1"><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,rwlock_instances: 读写锁同步对象实例

<code class="string"><code class="string"><code class="color1"><code class="color1"><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">表中记录了系统中使用读写锁对象的所有记录,其中name为 wait/synch/rwlock/*。WRITE_LOCKED_BY_THREAD_ID为正在持有该对象的thread_id,若没有线程持有,则为NULL。READ_LOCKED_BY_COUNT为记录了同时有多少个读者持有读锁。(通过 events_waits_current 表可以知道,哪个线程在等待锁;通过rwlock_instances知道哪个线程持有锁。rwlock_instances的缺陷是,只能记录持有写锁的线程,对于读锁则无能为力)。

<code class="string"><code class="string"><code class="color1"><code class="color1"><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">5,socket_instances:活跃会话对象实例<br/>表中记录了thread_id,socket_id,ip和port,其它表可以通过thread_id与socket_instance进行关联,获取IP-PORT信息,能够与应用对接起来。<br/>event_name主要包含3类:<br/>

<code class="string"><code class="string"><code class="string"><code class="color1"><code class="color1"><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">wait/io/socket/sql/server_unix_socket,服务端unix监听socket<br/>wait/io/socket/sql/server_tcpip_socket,服务端tcp监听socket<br/>wait/io/socket/sql/client_connection,客户端socket<br/></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>
Nach dem Login kopieren

<code class="string"><code class="string"><code class="color1"><code class="color1"><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">三:Wait表

<code class="string"><code class="string"><code class="color1"><code class="color1"><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">1,events_waits_current:记录了当前线程等待的事件

<code class="string"><code class="string"><code class="color1"><code class="color1"><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">2,events_waits_history:记录了每个线程最近等待的10个事件

<code class="string"><code class="string"><code class="color1"><code class="color1"><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">3,events_waits_history_long:记录了最近所有线程产生的10000个事件

<code class="string"><code class="string"><code class="color1"><code class="color1"><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">表结构定义如下:

view
 sourceprint?
01.
CREATE
 TABLE `events_waits_current` (
02.
`THREAD_ID`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;线程ID&#39;
,
03.
`EVENT_ID`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;当前线程的事件ID,和THREAD_ID确定唯一&#39;
,
04.
`END_EVENT_ID`
 bigint(
20
)
 unsigned DEFAULT NULL COMMENT 
&#39;当事件开始时,这一列被设置为NULL。当事件结束时,再更新为当前的事件ID&#39;
,
05.
`EVENT_NAME`
 varchar(
128
)
 NOT NULL COMMENT 
&#39;事件名称&#39;
,
06.
`SOURCE`
 varchar(
64
)
 DEFAULT NULL COMMENT 
&#39;该事件产生时的源码文件&#39;
,
07.
`TIMER_START`
 bigint(
20
)
 unsigned DEFAULT NULL COMMENT 
&#39;事件开始时间(皮秒)&#39;
,
08.
`TIMER_END`
 bigint(
20
)
 unsigned DEFAULT NULL COMMENT 
&#39;事件结束结束时间(皮秒)&#39;
,
09.
`TIMER_WAIT`
 bigint(
20
)
 unsigned DEFAULT NULL COMMENT 
&#39;事件等待时间(皮秒)&#39;
,
10.
`SPINS` 
int
(
10
)
 unsigned DEFAULT NULL COMMENT 
&#39;&#39;
,
11.
`OBJECT_SCHEMA`
 varchar(
64
)
 DEFAULT NULL COMMENT 
&#39;库名&#39;
,
12.
`OBJECT_NAME`
 varchar(
512
)
 DEFAULT NULL COMMENT 
&#39;文件名、表名、IP:SOCK值&#39;
,
13.
`OBJECT_TYPE`
 varchar(
64
)
 DEFAULT NULL COMMENT 
&#39;FILE、TABLE、TEMPORARY
 TABLE&#39;
,
14.
`INDEX_NAME`
 varchar(
64
)
 DEFAULT NULL COMMENT 
&#39;索引名&#39;
,
15.
`OBJECT_INSTANCE_BEGIN`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;内存地址&#39;
,
16.
`NESTING_EVENT_ID`
 bigint(
20
)
 unsigned DEFAULT NULL COMMENT 
&#39;该事件对应的父事件ID&#39;
,
17.
`NESTING_EVENT_TYPE` 
enum
(
&#39;STATEMENT&#39;
,
&#39;STAGE&#39;
,
&#39;WAIT&#39;
)
 DEFAULT NULL COMMENT 
&#39;父事件类型(STATEMENT,
 STAGE, WAIT)&#39;
,
18.
`OPERATION`
 varchar(
32
)
 NOT NULL COMMENT 
&#39;操作类型(lock,
 read, write)&#39;
,
19.
`NUMBER_OF_BYTES`
 bigint(
20
)
 DEFAULT NULL COMMENT 
&#39;&#39;
,
20.
`FLAGS` 
int
(
10
)
 unsigned DEFAULT NULL COMMENT 
&#39;标记&#39;
21.
)
 ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
Nach dem Login kopieren

<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="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="string"><code class="number"><code class="value"><code class="string"><code class="number"><code class="keyword"><code class="plain"><code class="value"><code class="plain"><code class="string"><code class="number"><code class="plain">四:Stage 表

<code class="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="string"><code class="number"><code class="value"><code class="string"><code class="number"><code class="keyword"><code class="plain"><code class="value"><code class="plain"><code class="string"><code class="number"><code class="plain">1,events_stages_current:记录了当前线程所处的执行阶段

<code class="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="string"><code class="number"><code class="value"><code class="string"><code class="number"><code class="keyword"><code class="plain"><code class="value"><code class="plain"><code class="string"><code class="number"><code class="plain">2,events_stages_history:记录了当前线程所处的执行阶段10条历史记录

<code class="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="string"><code class="number"><code class="value"><code class="string"><code class="number"><code class="keyword"><code class="plain"><code class="value"><code class="plain"><code class="string"><code class="number"><code class="plain">3,events_stages_history_long:记录了当前线程所处的执行阶段10000条历史记录

<code class="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="string"><code class="number"><code class="value"><code class="string"><code class="number"><code class="keyword"><code class="plain"><code class="value"><code class="plain"><code class="string"><code class="number"><code class="plain">表结构定义如下:

view
 sourceprint?
01.
CREATE
 TABLE `events_stages_current` (
02.
`THREAD_ID`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;线程ID&#39;
,
03.
`EVENT_ID`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;事件ID&#39;
,
04.
`END_EVENT_ID`
 bigint(
20
)
 unsigned DEFAULT NULL COMMENT 
&#39;结束事件ID&#39;
,
05.
`EVENT_NAME`
 varchar(
128
)
 NOT NULL COMMENT 
&#39;事件名称&#39;
,
06.
`SOURCE`
 varchar(
64
)
 DEFAULT NULL COMMENT 
&#39;源码位置&#39;
,
07.
`TIMER_START`
 bigint(
20
)
 unsigned DEFAULT NULL COMMENT 
&#39;事件开始时间(皮秒)&#39;
,
08.
`TIMER_END`
 bigint(
20
)
 unsigned DEFAULT NULL COMMENT 
&#39;事件结束结束时间(皮秒)&#39;
,
09.
`TIMER_WAIT`
 bigint(
20
)
 unsigned DEFAULT NULL COMMENT 
&#39;事件等待时间(皮秒)&#39;
,
10.
`NESTING_EVENT_ID`
 bigint(
20
)
 unsigned DEFAULT NULL COMMENT 
&#39;该事件对应的父事件ID&#39;
,
11.
`NESTING_EVENT_TYPE` 
enum
(
&#39;STATEMENT&#39;
,
&#39;STAGE&#39;
,
&#39;WAIT&#39;
)
 DEFAULT NULL COMMENT 
&#39;父事件类型(STATEMENT,
 STAGE, WAIT)&#39;
12.
)
 ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
Nach dem Login kopieren

<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="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="value"><code class="number"><code class="value"><code class="number"><code class="keyword"><code class="string"><code class="plain"><code class="string"><code class="plain"><code class="string"><code class="number"><code class="plain">五:Statement 表

<code class="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="value"><code class="number"><code class="value"><code class="number"><code class="keyword"><code class="string"><code class="plain"><code class="string"><code class="plain"><code class="string"><code class="number"><code class="plain">1,events_statements_current:通过 thread_id+event_id可以唯一确定一条记录。Statments表只记录最顶层的请求,SQL语句或是COMMAND,每条语句一行。event_name形式为statement/sql/*,或statement/com/*

<code class="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="value"><code class="number"><code class="value"><code class="number"><code class="keyword"><code class="string"><code class="plain"><code class="string"><code class="plain"><code class="string"><code class="number"><code class="plain">2,events_statements_history

<code class="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="value"><code class="number"><code class="value"><code class="number"><code class="keyword"><code class="string"><code class="plain"><code class="string"><code class="plain"><code class="string"><code class="number"><code class="plain">3,events_statements_history_long

<code class="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="value"><code class="number"><code class="value"><code class="number"><code class="keyword"><code class="string"><code class="plain"><code class="string"><code class="plain"><code class="string"><code class="number"><code class="plain">表结构定义如下:


view
 sourceprint?
01.
CREATE
 TABLE `events_statements_current` (
02.
`THREAD_ID`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;线程ID&#39;
,
03.
`EVENT_ID`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;事件ID&#39;
,
04.
`END_EVENT_ID`
 bigint(
20
)
 unsigned DEFAULT NULL COMMENT 
&#39;结束事件ID&#39;
,
05.
`EVENT_NAME`
 varchar(
128
)
 NOT NULL COMMENT 
&#39;事件名称&#39;
,
06.
`SOURCE`
 varchar(
64
)
 DEFAULT NULL COMMENT 
&#39;源码位置&#39;
,
07.
`TIMER_START`
 bigint(
20
)
 unsigned DEFAULT NULL COMMENT 
&#39;事件开始时间(皮秒)&#39;
,
08.
`TIMER_END`
 bigint(
20
)
 unsigned DEFAULT NULL COMMENT 
&#39;事件结束结束时间(皮秒)&#39;
,
09.
`TIMER_WAIT`
 bigint(
20
)
 unsigned DEFAULT NULL COMMENT 
&#39;事件等待时间(皮秒)&#39;
,
10.
`LOCK_TIME`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;锁时间&#39;
,
11.
`SQL_TEXT`
 longtext COMMENT 
&#39;记录SQL语句&#39;
,
12.
`DIGEST`
 varchar(
32
)
 DEFAULT NULL COMMENT 
&#39;对SQL_TEXT做MD5产生的32位字符串&#39;
,
13.
`DIGEST_TEXT`
 longtext COMMENT 
&#39;将语句中值部分用问号代替,用于SQL语句归类&#39;
,
14.
`CURRENT_SCHEMA`
 varchar(
64
)
 DEFAULT NULL COMMENT 
&#39;默认的数据库名&#39;
,
15.
`OBJECT_TYPE`
 varchar(
64
)
 DEFAULT NULL COMMENT 
&#39;保留字段&#39;
,
16.
`OBJECT_SCHEMA`
 varchar(
64
)
 DEFAULT NULL COMMENT 
&#39;保留字段&#39;
,
17.
`OBJECT_NAME`
 varchar(
64
)
 DEFAULT NULL COMMENT 
&#39;保留字段&#39;
,
18.
`OBJECT_INSTANCE_BEGIN`
 bigint(
20
)
 unsigned DEFAULT NULL COMMENT 
&#39;内存地址&#39;
,
19.
`MYSQL_ERRNO` 
int
(
11
)
 DEFAULT NULL COMMENT 
&#39;&#39;
,
20.
`RETURNED_SQLSTATE`
 varchar(
5
)
 DEFAULT NULL COMMENT 
&#39;&#39;
,
21.
`MESSAGE_TEXT`
 varchar(
128
)
 DEFAULT NULL COMMENT 
&#39;信息&#39;
,
22.
`ERRORS`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;错误数目&#39;
,
23.
`WARNINGS`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;警告数目&#39;
,
24.
`ROWS_AFFECTED`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;影响的数目&#39;
,
25.
`ROWS_SENT`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;返回的记录数&#39;
,
26.
`ROWS_EXAMINED`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;读取扫描的记录数目&#39;
,
27.
`CREATED_TMP_DISK_TABLES`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;创建磁盘临时表数目&#39;
,
28.
`CREATED_TMP_TABLES`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;创建临时表数目&#39;
,
29.
`SELECT_FULL_JOIN`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;join时,第一个表为全表扫描的数目&#39;
,
30.
`SELECT_FULL_RANGE_JOIN`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;引用表采用range方式扫描的数目&#39;
,
31.
`SELECT_RANGE`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;join时,第一个表采用range方式扫描的数目&#39;
,
32.
`SELECT_RANGE_CHECK`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;&#39;
,
33.
`SELECT_SCAN`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;join时,第一个表位全表扫描的数目&#39;
,
34.
`SORT_MERGE_PASSES`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;&#39;
,
35.
`SORT_RANGE`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;范围排序数目&#39;
,
36.
`SORT_ROWS`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;排序的记录数目&#39;
,
37.
`SORT_SCAN`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;全表排序数目&#39;
,
38.
`NO_INDEX_USED`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;没有使用索引数目&#39;
,
39.
`NO_GOOD_INDEX_USED`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;&#39;
,
40.
`NESTING_EVENT_ID`
 bigint(
20
)
 unsigned DEFAULT NULL COMMENT 
&#39;该事件对应的父事件ID&#39;
,
41.
`NESTING_EVENT_TYPE` 
enum
(
&#39;STATEMENT&#39;
,
&#39;STAGE&#39;
,
&#39;WAIT&#39;
)
 DEFAULT NULL COMMENT 
&#39;父事件类型(STATEMENT,
 STAGE, WAIT)&#39;
42.
)
 ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8view
 sourceprint?
Nach dem Login kopieren

<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="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="value"><code class="value"><code class="number"><code class="keyword"><code class="string"><code class="string"><code class="plain"><code class="string"><code class="number"><code class="plain"><code class="number">六:Connection 表

<code class="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="value"><code class="value"><code class="number"><code class="keyword"><code class="string"><code class="string"><code class="plain"><code class="string"><code class="number"><code class="plain"><code class="number">1,users:记录用户连接数信息

<code class="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="value"><code class="value"><code class="number"><code class="keyword"><code class="string"><code class="string"><code class="plain"><code class="string"><code class="number"><code class="plain"><code class="number">2,hosts:记录了主机连接数信息

<code class="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="value"><code class="value"><code class="number"><code class="keyword"><code class="string"><code class="string"><code class="plain"><code class="string"><code class="number"><code class="plain"><code class="number">3,accounts:记录了用户主机连接数信息

<code class="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="value"><code class="value"><code class="number"><code class="keyword"><code class="string"><code class="string"><code class="plain"><code class="string"><code class="number"><code class="plain"><code class="number"><img alt="加载中..." title="图片加载中..." src="http://img.bitscn.com/upimg/allimg/c160504/14623310K54E0-2C33.jpg"/><img alt="加载中..." title="图片加载中..." src="http://img.bitscn.com/upimg/allimg/c160504/14623310K54E0-2C33.jpg"/><code class="number">

01.
zjy
@performance_schema 
12
:
03
:
27
>select
 * from users;
02.
+------------------+---------------------+-------------------+
03.
|
 USER             | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
04.
+------------------+---------------------+-------------------+
05.
|
 debian-sys-maint |                   
0 
|                
36 
|
06.
|
 zjy              |                   
1 
|             
22285 
|
07.
|
 dchat_php        |                   
0 
|             
37864 
|
08.
|
 dxyslave         |                   
2 
|                 
9 
|
09.
|
 nagios           |                   
0 
|             
10770 
|
10.
|
 dchat_data       |                 
140 
|           
2233023 
|
11.
|
 NULL             |                   
0 
|             
15866 
|
12.
|
 dchat_api        |                 
160 
|           
2754212 
|
13.
|
 mha_data         |                   
1 
|                
36 
|
14.
|
 backup           |                   
0 
|                
15 
|
15.
|
 cacti            |                   
0 
|              
4312 
|
16.
|
 kol              |                  
10 
|            
172414 
|
17.
+------------------+---------------------+-------------------+
18.
12 
rows
 in set (
0.00 
sec)
19. 
20.
zjy
@performance_schema 
12
:
03
:
34
>select
 * from hosts;
21.
+-----------------+---------------------+-------------------+
22.
|
 HOST            | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
23.
+-----------------+---------------------+-------------------+
24.
| 
192.168
.
100.218 
|                 
150 
|           
2499422 
|
25.
| 
192.168
.
100.240 
|                  
10 
|            
172429 
|
26.
| 
192.168
.
100.139 
|                   
0 
|               
698 
|
27.
| 
192.168
.
100.21  
|                   
0 
|                 
2 
|
28.
| 
192.168
.
100.220 
|                 
150 
|           
2526136 
|
29.
| 
192.168
.
100.25  
|                   
1 
|                 
7 
|
30.
|
 NULL            |                   
0 
|             
15867 
|
31.
| 
192.168
.
100.241 
|                   
0 
|             
21558 
|
32.
| 
192.168
.
100.191 
|                   
1 
|                
34 
|
33.
|
 localhost       |                   
0 
|             
10807 
|
34.
| 
192.168
.
100.118 
|                   
1 
|                 
2 
|
35.
| 
192.168
.
100.251 
|                   
0 
|              
4312 
|
36.
| 
192.168
.
100.23  
|                   
1 
|                
31 
|
37.
| 
192.168
.
100.193 
|                   
0 
|                
15 
|
38.
+-----------------+---------------------+-------------------+
39.
14 
rows
 in set (
0.01 
sec)
40. 
41.
zjy
@performance_schema 
12
:
05
:
21
>select
 * from accounts;
42.
+------------------+-----------------+---------------------+-------------------+
43.
|
 USER             | HOST            | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
44.
+------------------+-----------------+---------------------+-------------------+
45.
|
 cacti            | 
192.168
.
100.251 
|                   
0 
|              
4313 
|
46.
|
 debian-sys-maint | localhost       |                   
0 
|                
36 
|
47.
|
 backup           | 
192.168
.
100.193 
|                   
0 
|                
15 
|
48.
|
 dchat_api        | 
192.168
.
100.220 
|                  
80 
|           
1382585 
|
49.
|
 dchat_php        | 
192.168
.
100.220 
|                   
0 
|             
20292 
|
50.
|
 zjy              | 
192.168
.
100.139 
|                   
0 
|               
698 
|
51.
|
 zjy              | 
192.168
.
100.241 
|                   
0 
|             
21558 
|
52.
|
 mha_data         | 
192.168
.
100.191 
|                   
1 
|                
34 
|
53.
|
 dxyslave         | 
192.168
.
100.118 
|                   
1 
|                 
2 
|
54.
|
 kol              | 
192.168
.
100.240 
|                  
10 
|            
172431 
|
55.
|
 dxyslave         | 
192.168
.
100.25  
|                   
1 
|                 
7 
|
56.
|
 dchat_data       | 
192.168
.
100.218 
|                  
70 
|           
1109974 
|
57.
|
 zjy              | 
192.168
.
100.23  
|                   
1 
|                
31 
|
58.
|
 dchat_php        | 
192.168
.
100.218 
|                   
0 
|             
17572 
|
59.
|
 dchat_data       | 
192.168
.
100.220 
|                  
70 
|           
1123306 
|
60.
|
 NULL             | NULL            |                   
0 
|             
15868 
|
61.
|
 mha_data         | 
192.168
.
100.21  
|                   
0 
|                 
2 
|
62.
|
 dchat_api        | 
192.168
.
100.218 
|                  
80 
|           
1371918 
|
63.
|
 nagios           | localhost       |                   
0 
|             
10771 
|
64.
+------------------+-----------------+---------------------+-------------------+view
 sourceprint?
Nach dem Login kopieren

<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="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="value"><code class="value"><code class="number"><code class="keyword"><code class="string"><code class="string"><code class="plain"><code class="string"><code class="number"><code class="plain"><code class="number">七:Summary 表: Summary表聚集了各个维度的统计信息包括表维度,索引维度,会话维度,语句维度和锁维度的统计信息

<code class="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="value"><code class="value"><code class="number"><code class="keyword"><code class="string"><code class="string"><code class="plain"><code class="string"><code class="number"><code class="plain"><code class="number">1,events_waits_summary_global_by_event_name:按等待事件类型聚合,每个事件一条记录

<code class="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="value"><code class="value"><code class="number"><code class="keyword"><code class="string"><code class="string"><code class="plain"><code class="string"><code class="number"><code class="plain"><code class="number"><code class="number">

1.
CREATE
 TABLE `events_waits_summary_global_by_event_name` (
2.
`EVENT_NAME`
 varchar(
128
)
 NOT NULL COMMENT 
&#39;事件名称&#39;
,
3.
`COUNT_STAR`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;事件计数&#39;
,
4.
`SUM_TIMER_WAIT`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;总的等待时间&#39;
,
5.
`MIN_TIMER_WAIT`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;最小等待时间&#39;
,
6.
`AVG_TIMER_WAIT`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;平均等待时间&#39;
,
7.
`MAX_TIMER_WAIT`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;最大等待时间&#39;
8.
)
 ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8view
 sourceprint?
Nach dem Login kopieren

<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="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="value"><code class="value"><code class="number"><code class="keyword"><code class="string"><code class="string"><code class="plain"><code class="string"><code class="number"><code class="plain"><code class="number">2,events_waits_summary_by_instance:按等待事件对象聚合,同一种等待事件,可能有多个实例,每个实例有不同的内存地址,因此event_name+object_instance_begin唯一确定一条记录。

<code class="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="value"><code class="value"><code class="number"><code class="keyword"><code class="string"><code class="string"><code class="plain"><code class="string"><code class="number"><code class="plain"><code class="number"><br/><code class="number">

01.
CREATE
 TABLE `events_waits_summary_by_instance` (
02.
`EVENT_NAME`
 varchar(
128
)
 NOT NULL COMMENT 
&#39;事件名称&#39;
,
03.
`OBJECT_INSTANCE_BEGIN`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;内存地址&#39;
,
04.
`COUNT_STAR`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;事件计数&#39;
,
05.
`SUM_TIMER_WAIT`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;总的等待时间&#39;
,
06.
`MIN_TIMER_WAIT`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;最小等待时间&#39;
,
07.
`AVG_TIMER_WAIT`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;平均等待时间&#39;
,
08.
`MAX_TIMER_WAIT`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;最大等待时间&#39;
09.
)
 ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8view
 sourceprint?
Nach dem Login kopieren

<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="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="value"><code class="value"><code class="number"><code class="keyword"><code class="string"><code class="string"><code class="plain"><code class="string"><code class="number"><code class="plain"><code class="number">3,events_waits_summary_by_thread_by_event_name:按每个线程和事件来统计,thread_id+event_name唯一确定一条记录。

<code class="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="value"><code class="value"><code class="number"><code class="keyword"><code class="string"><code class="string"><code class="plain"><code class="string"><code class="number"><code class="plain"><code class="number"><code class="number">

01.
CREATE
 TABLE `events_waits_summary_by_thread_by_event_name` (
02.
`THREAD_ID`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;线程ID&#39;
,
03.
`EVENT_NAME`
 varchar(
128
)
 NOT NULL COMMENT 
&#39;事件名称&#39;
,
04.
`COUNT_STAR`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;事件计数&#39;
,
05.
`SUM_TIMER_WAIT`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;总的等待时间&#39;
,
06.
`MIN_TIMER_WAIT`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;最小等待时间&#39;
,
07.
`AVG_TIMER_WAIT`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;平均等待时间&#39;
,
08.
`MAX_TIMER_WAIT`
 bigint(
20
)
 unsigned NOT NULL COMMENT 
&#39;最大等待时间&#39;
09.
)
 ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8view
 sourceprint?
Nach dem Login kopieren

<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="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="value"><code class="value"><code class="number"><code class="keyword"><code class="string"><code class="string"><code class="plain"><code class="string"><code class="number"><code class="plain"><code class="number">4,events_stages_summary_global_by_event_name:按事件阶段类型聚合,每个事件一条记录,表结构同上。

<code class="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="value"><code class="value"><code class="number"><code class="keyword"><code class="string"><code class="string"><code class="plain"><code class="string"><code class="number"><code class="plain"><code class="number">5,events_stages_summary_by_thread_by_event_name:按每个线程和事件来阶段统计,表结构同上。

<code class="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="value"><code class="value"><code class="number"><code class="keyword"><code class="string"><code class="string"><code class="plain"><code class="string"><code class="number"><code class="plain"><code class="number">6,events_statements_summary_by_digest:按照事件的语句进行聚合。

<code class="color1"><code class="color1"><code class="keyword"><code class="keyword"><code class="value"><code class="value"><code class="value"><code class="number"><code class="keyword"><code class="string"><code class="string"><code class="plain"><code class="string"><code class="number"><code class="plain"><code class="number"><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">01. CREATE TABLE `events_statements_summary_by_digest` ( 02. `SCHEMA_NAME` varchar( 64 ) DEFAULT NULL COMMENT &amp;#39;库名&amp;#39; , 03. `DIGEST` varchar( 32 ) DEFAULT NULL COMMENT &amp;#39;对SQL_TEXT做MD5产生的32位字符串。如果为consumer表中没有打开statement_digest选项,则为NULL&amp;#39; , 04. `DIGEST_TEXT` longtext COMMENT &amp;#39;将语句中值部分用问号代替,用于SQL语句归类。如果为consumer表中没有打开statement_digest选项,则为NULL。&amp;#39; , 05. `COUNT_STAR` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;事件计数&amp;#39; , 06. `SUM_TIMER_WAIT` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;总的等待时间&amp;#39; , 07. `MIN_TIMER_WAIT` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;最小等待时间&amp;#39; , 08. `AVG_TIMER_WAIT` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;平均等待时间&amp;#39; , 09. `MAX_TIMER_WAIT` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;最大等待时间&amp;#39; , 10. `SUM_LOCK_TIME` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;锁时间总时长&amp;#39; , 11. `SUM_ERRORS` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;错误数的总&amp;#39; , 12. `SUM_WARNINGS` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;警告的总数&amp;#39; , 13. `SUM_ROWS_AFFECTED` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;影响的总数目&amp;#39; , 14. `SUM_ROWS_SENT` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;返回总数目&amp;#39; , 15. `SUM_ROWS_EXAMINED` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;总的扫描的数目&amp;#39; , 16. `SUM_CREATED_TMP_DISK_TABLES` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;创建磁盘临时表的总数目&amp;#39; , 17. `SUM_CREATED_TMP_TABLES` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;创建临时表的总数目&amp;#39; , 18. `SUM_SELECT_FULL_JOIN` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;第一个表全表扫描的总数目&amp;#39; , 19. `SUM_SELECT_FULL_RANGE_JOIN` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;总的采用range方式扫描的数目&amp;#39; , 20. `SUM_SELECT_RANGE` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;第一个表采用range方式扫描的总数目&amp;#39; , 21. `SUM_SELECT_RANGE_CHECK` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;&amp;#39; , 22. `SUM_SELECT_SCAN` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;第一个表位全表扫描的总数目&amp;#39; , 23. `SUM_SORT_MERGE_PASSES` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;&amp;#39; , 24. `SUM_SORT_RANGE` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;范围排序总数&amp;#39; , 25. `SUM_SORT_ROWS` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;排序的记录总数目&amp;#39; , 26. `SUM_SORT_SCAN` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;第一个表排序扫描总数目&amp;#39; , 27. `SUM_NO_INDEX_USED` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;没有使用索引总数&amp;#39; , 28. `SUM_NO_GOOD_INDEX_USED` bigint( 20 ) unsigned NOT NULL COMMENT &amp;#39;&amp;#39; , 29. `FIRST_SEEN` timestamp NOT NULL DEFAULT &amp;#39;0000-00-00 00:00:00&amp;#39; COMMENT &amp;#39;第一次执行时间&amp;#39; , 30. `LAST_SEEN` timestamp NOT NULL DEFAULT &amp;#39;0000-00-00 00:00:00&amp;#39; COMMENT &amp;#39;最后一次执行时间&amp;#39; 31. ) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8view sourceprint?</pre><div class="contentsignin">Nach dem Login kopieren</div></div><code class="plain">

7,events_statements_summary_global_by_event_name:按照事件的语句进行聚合。表结构同上。

8,events_statements_summary_by_thread_by_event_name:按照线程和事件的语句进行聚合,表结构同上。

9,file_summary_by_instance:按事件类型统计(物理IO维度)

10,file_summary_by_event_name:具体文件统计(物理IO维度)

9和10一起说明:

统计IO操作:COUNT_STAR,SUM_TIMER_WAIT,MIN_TIMER_WAIT,AVG_TIMER_WAIT,MAX_TIMER_WAIT

统计读 :COUNT_READ,SUM_TIMER_READ,MIN_TIMER_READ,AVG_TIMER_READ,MAX_TIMER_READ, SUM_NUMBER_OF_BYTES_READ

统计写 :COUNT_WRITE,SUM_TIMER_WRITE,MIN_TIMER_WRITE,AVG_TIMER_WRITE,MAX_TIMER_WRITE, SUM_NUMBER_OF_BYTES_WRITE

统计其他IO事件,比如create,delete,open,close等:COUNT_MISC,SUM_TIMER_MISC,MIN_TIMER_MISC,AVG_TIMER_MISC,MAX_TIMER_MISC

11,table_io_waits_summary_by_table:根据wait/io/table/sql/handler,聚合每个表的I/O操作(逻辑IO纬度)

统计IO操作:COUNT_STAR,SUM_TIMER_WAIT,MIN_TIMER_WAIT,AVG_TIMER_WAIT,MAX_TIMER_WAIT

统计读 :COUNT_READ,SUM_TIMER_READ,MIN_TIMER_READ,AVG_TIMER_READ,MAX_TIMER_READ

:COUNT_FETCH,SUM_TIMER_FETCH,MIN_TIMER_FETCH,AVG_TIMER_FETCH, MAX_TIMER_FETCH

统计写 :COUNT_WRITE,SUM_TIMER_WRITE,MIN_TIMER_WRITE,AVG_TIMER_WRITE,MAX_TIMER_WRITE

INSERT统计,相应的还有DELETE和UPDATE统计:COUNT_INSERT,SUM_TIMER_INSERT,MIN_TIMER_INSERT,AVG_TIMER_INSERT,MAX_TIMER_INSERT

12,table_io_waits_summary_by_index_usage:与table_io_waits_summary_by_table类似,按索引维度统计

13,table_lock_waits_summary_by_table:聚合了表锁等待事件,包括internal lock 和 external lock

internal lock通过SQL层函数thr_lock调用,OPERATION值为:read normal、read with shared locks、read high priority、read no insert、write allow write、write concurrent insert、write delayed、write low priority、write normalexternal lock则通过接口函数handler::external_lock调用存储引擎层,OPERATION列的值为:read external、write external



14,Connection Summaries表:account、user、host

events_waits_summary_by_account_by_event_nameevents_waits_summary_by_user_by_event_nameevents_waits_summary_by_host_by_event_name events_stages_summary_by_account_by_event_nameevents_stages_summary_by_user_by_event_nameevents_stages_summary_by_host_by_event_name events_statements_summary_by_account_by_event_nameevents_statements_summary_by_user_by_event_nameevents_statements_summary_by_host_by_event_name









15,socket_summary_by_instance、socket_summary_by_event_name:socket聚合统计表。

八:其他相关表

1,performance_timers:系统支持的统计时间单位

2,threads:监视服务端的当前运行的线程

统计应用:

关于SQL维度的统计信息主要集中在events_statements_summary_by_digest表中,通过将SQL语句抽象出digest,可以统计某类SQL语句在各个维度的统计信息

1,哪个SQL执行最多:

01.
zjy
@performance_schema 
11
:
36
:
22
>*************************** 
SELECT
 SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM 
events_statements_summary_by_digest ORDER BY COUNT_STAR desc LIMIT 1G
02.
1
.
 row ***************************:
 dchat
03.
SCHEMA_NAME
04.
:
 SELECT ...
DIGEST_TEXT
05.
: 
COUNT_STAR
1161210102
06.
SUM_ROWS_SENT: 
1161207842
07.
SUM_ROWS_EXAMINED: 
0
: 
08.
FIRST_SEEN
2016
-
02
-
17 
00
:
36
:
46
: 
09.
LAST_SEEN
2016
-
03
-
07 
11
:
36
:
29view
 sourceprint?
Nach dem Login kopieren

各个字段的注释可以看上面的表结构说明:从2月17号到3月7号该SQL执行了1161210102次。

2,哪个SQL平均响应时间最多:

01.
zjy
@performance_schema 
11
:
36
:
28
>*************************** 
SELECT
 SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,
FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT desc LIMIT 1G
02.
1
.
 row ***************************
<co
Nach dem Login kopieren

以上就是MySQL5.6PERFORMANCE_SCHEMA说明_MySQL的内容,更多相关内容请关注PHP中文网(www.php.cn)!

Verwandte Etiketten:
Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage