MySQL5.6 PERFORMANCE_SCHEMA 說明
https://blog.csdn.net/isoleo/article/details/51180593
背景：
5.5開始新增一個資料庫：PERFORMANCE_SCHEMA，主要用於收集資料庫伺服器效能引數。並且庫裡表的儲存引擎均為PERFORMANCE_SCHEMA，而使用者是不能建立儲存引擎為PERFORMANCE_SCHEMA的表。 5.5預設是關閉的，需要手動開啟，在配置檔案裡新增：
1.
[mysqld]
2.
performance_schema=ON
檢視是否開啟：
1.
mysql>show
variables like
'performance_schema'
;
2.
+--------------------+-------+
3.
|
Variable_name | Value |
4.
+--------------------+-------+
5.
|
performance_schema | <strong>ON</strong> |
6.
+--------------------+-------+
從MySQL5.6開始，預設開啟，本文就從MySQL5.6來說明，在資料庫使用當中PERFORMANCE_SCHEMA的一些比較常用的功能。具體的資訊可以檢視官方文件。
相關表資訊：
一：配置（setup）表：
01.
zjy
@performance_schema
10
:
16
:
56
>show
tables like
'%setup%'
;
02.
+----------------------------------------+
03.
|
Tables_in_performance_schema (%setup%) |
04.
+----------------------------------------+
05.
|
setup_actors |
06.
|
setup_consumers |
07.
|
setup_instruments |
08.
|
setup_objects |
09.
|
setup_timers |
10.
+----------------------------------------+
1，setup_actors ：配置使用者緯度的監控，預設監控所有使用者。
1.
zjy
@performance_schema
10
:
19
:
11
>select
* from setup_actors;
2.
+------+------+------+
3.
|
HOST | USER | ROLE |
4.
+------+------+------+
5.
|
% | % | % |
6.
+------+------+------+
2，setup_consumers ：配置events的消費者型別，即收集的events寫入到哪些統計表中。
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.
+--------------------------------+---------+
這裡需要說明的是需要檢視哪個就更新其ENABLED列為YES。如：
1.
zjy
@performance_schema
10
:
25
:
02
>update
setup_consumers set ENABLED=
'YES'
where
NAME in (
'events_stages_current'
,
'events_waits_current'
);
2.
Query
OK,
2
rows
affected (
0.00
sec)
更新完後立即生效，但是伺服器重啟之後又會變回預設值，要永久生效需要在配置檔案裡新增：
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
即在這些表的前面加上：performance_schema_consumer_xxx。表setup_consumers裡面的值有個層級關係：
1.
<strong>global_instrumentation</strong>
> <strong>thread_instrumentation</strong> =
<strong>statements_digest</strong> >
events_stages_<strong>current</strong> =
events_statements_current = events_waits_current >
events_stages_<strong>history</strong> =
events_statements_history = events_waits_history
> events_stages_<strong>history_long</strong> =
events_statements_history_long = events_waits_history_long
只有上一層次的為YES，才會繼續檢查該本層為YES or NO。global_instrumentation是最高階別consumer，如果它設定為NO，則所有的consumer都會忽略。其中history和history_long存的是current錶的歷史記錄條數，history表記錄了每個執行緒最近等待的10個事件，而history_long表則記錄了最近所有執行緒產生的10000個事件，這裡的10和10000都是可以配置的。這三個表表結構相同，history和history_long表資料都來源於current表。長度透過控制引數：
01.
zjy
@performance_schema
11
:
10
:
03
>show
variables like
'performance_schema%history%size'
;
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：
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.
+---------------------------------+----------+
idle表示socket空閒的時間，stage類表示語句的每個執行階段的統計，statement類統計語句維度的資訊，wait類統計各種等待事件，比如IO，mutux，spin_lock,condition等。
4，setup_objects ：配置監控物件，預設對mysql，performance_schema和information_schema中的表都不監控，而其它DB的所有表都監控。
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 | % | % | <strong>YES</strong> | <strong>YES</strong> |
09.
+-------------+--------------------+-------------+---------+-------+
5，setup_timers ：配置每種型別指令的統計時間單位。MICROSECOND表示統計單位是微妙，CYCLE表示統計單位是時鐘週期，時間度量與CPU的主頻有關，NANOSECOND表示統計單位是納秒。但無論採用哪種度量單位，最終統計表中統計的時間都會裝換到皮秒。（1秒＝1000000000000皮秒）
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.
+-----------+-------------+
二：instance表
1， cond_instances ：條件等待物件例項
表中記錄了系統中使用的條件變數的物件， OBJECT_INSTANCE_BEGIN 為物件的記憶體地址。
2， file_instances ：檔案例項
表中記錄了系統中開啟了檔案的物件，包括ibdata檔案，redo檔案，binlog檔案，使用者的表檔案等， open_count 顯示當前檔案開啟的數目，如果重來沒有開啟過，不會出現在表中。
01.
zjy
@performance_schema
11
:
20
:
04
>select
* from file_instances limit
2
,
5
;
02.
+---------------------------------+--------------------------------------+------------+
03.
|
FILE_NAME | EVENT_NAME | <strong>OPEN_COUNT</strong> |
04.
+---------------------------------+--------------------------------------+------------+
05.
|
/var/lib/mysql/mysql/plugin.frm | wait/io/file/sql/FRM |
|
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.
+---------------------------------+--------------------------------------+------------+
3，mutex_instances： 互斥同步物件例項
表中記錄了系統中使用互斥量物件的所有記錄，其中name為：wait/synch/mutex/*。 LOCKED_BY_THREAD_ID 顯示哪個執行緒正持有mutex，若沒有執行緒持有，則為NULL。
4， rwlock_instances： 讀寫鎖同步物件例項
表中記錄了系統中使用讀寫鎖物件的所有記錄，其中name為 wait/synch/rwlock/*。 WRITE_LOCKED_BY_THREAD_ID 為正在持有該物件的thread_id，若沒有執行緒持有，則為NULL。 READ_LOCKED_BY_COUNT 為記錄了同時有多少個讀者持有讀鎖。（透過 events_waits_current 表可以知道，哪個執行緒在等待鎖；透過rwlock_instances知道哪個執行緒持有鎖。rwlock_instances的缺陷是，只能記錄持有寫鎖的執行緒，對於讀鎖則無能為力）。
5，socket_instances：
活躍會話物件例項
表中記錄了thread_id,socket_id,ip和port，其它表可以透過thread_id與socket_instance進行關聯，獲取IP-PORT資訊，能夠與應用對接起來。
event_name主要包含3類：
wait/io/socket/sql/server_unix_socket，服務端unix監聽socket
wait/io/socket/sql/server_tcpip_socket，服務端tcp監聽socket
wait/io/socket/sql/client_connection，客戶端socket
三：Wait表
1， events_waits_current ：記錄了當前執行緒等待的事件
2， events_waits_history ：記錄了每個執行緒最近等待的10個事件
3， events_waits_history_long ：記錄了最近所有執行緒產生的10000個事件
表結構定義如下：
01.
CREATE
TABLE `events_waits_current` (
02.
`THREAD_ID`
bigint(
20
)
unsigned NOT NULL COMMENT
'執行緒ID'
,
03.
`EVENT_ID`
bigint(
20
)
unsigned NOT NULL COMMENT
'當前執行緒的事件ID，和THREAD_ID確定唯一'
,
04.
`END_EVENT_ID`
bigint(
20
)
unsigned DEFAULT NULL COMMENT
'當事件開始時，這一列被設定為NULL。當事件結束時，再更新為當前的事件ID'
,
05.
`EVENT_NAME`
varchar(
128
)
NOT NULL COMMENT
'事件名稱'
,
06.
`SOURCE`
varchar(
64
)
DEFAULT NULL COMMENT
'該事件產生時的原始碼檔案'
,
07.
`TIMER_START`
bigint(
20
)
unsigned DEFAULT NULL COMMENT
'事件開始時間（皮秒）'
,
08.
`TIMER_END`
bigint(
20
)
unsigned DEFAULT NULL COMMENT
'事件結束結束時間（皮秒）'
,
09.
`TIMER_WAIT`
bigint(
20
)
unsigned DEFAULT NULL COMMENT
'事件等待時間（皮秒）'
,
10.
`SPINS`
int
(
10
)
unsigned DEFAULT NULL COMMENT
''
,
11.
`OBJECT_SCHEMA`
varchar(
64
)
DEFAULT NULL COMMENT
'庫名'
,
12.
`OBJECT_NAME`
varchar(
512
)
DEFAULT NULL COMMENT
'檔名、表名、IP:SOCK值'
,
13.
`OBJECT_TYPE`
varchar(
64
)
DEFAULT NULL COMMENT
'FILE、TABLE、TEMPORARY
TABLE'
,
14.
`INDEX_NAME`
varchar(
64
)
DEFAULT NULL COMMENT
'索引名'
,
15.
`OBJECT_INSTANCE_BEGIN`
bigint(
20
)
unsigned NOT NULL COMMENT
'記憶體地址'
,
16.
`NESTING_EVENT_ID`
bigint(
20
)
unsigned DEFAULT NULL COMMENT
'該事件對應的父事件ID'
,
17.
`NESTING_EVENT_TYPE`
enum
(
'STATEMENT'
,
'STAGE'
,
'WAIT'
)
DEFAULT NULL COMMENT
'父事件型別(STATEMENT,
STAGE, WAIT)'
,
18.
`OPERATION`
varchar(
32
)
NOT NULL COMMENT
'操作型別（lock,
read, write）'
,
19.
`NUMBER_OF_BYTES`
bigint(
20
)
DEFAULT NULL COMMENT
''
,
20.
`FLAGS`
int
(
10
)
unsigned DEFAULT NULL COMMENT
'標記'
21.
)
ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
四：Stage 表
1，events_stages_current ：記錄了當前執行緒所處的執行階段
2，events_stages_history ：記錄了當前執行緒所處的執行階段10條歷史記錄
3，events_stages_history_long ：記錄了當前執行緒所處的執行階段10000條歷史記錄
表結構定義如下：
01.
CREATE
TABLE `events_stages_current` (
02.
`THREAD_ID`
bigint(
20
)
unsigned NOT NULL COMMENT
'執行緒ID'
,
03.
`EVENT_ID`
bigint(
20
)
unsigned NOT NULL COMMENT
'事件ID'
,
04.
`END_EVENT_ID`
bigint(
20
)
unsigned DEFAULT NULL COMMENT
'結束事件ID'
,
05.
`EVENT_NAME`
varchar(
128
)
NOT NULL COMMENT
'事件名稱'
,
06.
`SOURCE`
varchar(
64
)
DEFAULT NULL COMMENT
'原始碼位置'
,
07.
`TIMER_START`
bigint(
20
)
unsigned DEFAULT NULL COMMENT
'事件開始時間（皮秒）'
,
08.
`TIMER_END`
bigint(
20
)
unsigned DEFAULT NULL COMMENT
'事件結束結束時間（皮秒）'
,
09.
`TIMER_WAIT`
bigint(
20
)
unsigned DEFAULT NULL COMMENT
'事件等待時間（皮秒）'
,
10.
`NESTING_EVENT_ID`
bigint(
20
)
unsigned DEFAULT NULL COMMENT
'該事件對應的父事件ID'
,
11.
`NESTING_EVENT_TYPE`
enum
(
'STATEMENT'
,
'STAGE'
,
'WAIT'
)
DEFAULT NULL COMMENT
'父事件型別(STATEMENT,
STAGE, WAIT)'
12.
)
ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
五：Statement 表
1，events_statements_current ：透過 thread_id+event_id可以唯一確定一條記錄。Statments表只記錄最頂層的請求，SQL語句或是COMMAND，每條語句一行。event_name形式為statement/sql/*，或statement/com/*
2，events_statements_history
3，events_statements_history_long
表結構定義如下：
01.
CREATE
TABLE `events_statements_current` (
02.
`THREAD_ID`
bigint(
20
)
unsigned NOT NULL COMMENT
'執行緒ID'
,
03.
`EVENT_ID`
bigint(
20
)
unsigned NOT NULL COMMENT
'事件ID'
,
04.
`END_EVENT_ID`
bigint(
20
)
unsigned DEFAULT NULL COMMENT
'結束事件ID'
,
05.
`EVENT_NAME`
varchar(
128
)
NOT NULL COMMENT
'事件名稱'
,
06.
`SOURCE`
varchar(
64
)
DEFAULT NULL COMMENT
'原始碼位置'
,
07.
`TIMER_START`
bigint(
20
)
unsigned DEFAULT NULL COMMENT
'事件開始時間（皮秒）'
,
08.
`TIMER_END`
bigint(
20
)
unsigned DEFAULT NULL COMMENT
'事件結束結束時間（皮秒）'
,
09.
`TIMER_WAIT`
bigint(
20
)
unsigned DEFAULT NULL COMMENT
'事件等待時間（皮秒）'
,
10.
`LOCK_TIME`
bigint(
20
)
unsigned NOT NULL COMMENT
'鎖時間'
,
11.
`SQL_TEXT`
longtext COMMENT
'記錄SQL語句'
,
12.
`DIGEST`
varchar(
32
)
DEFAULT NULL COMMENT
'對SQL_TEXT做MD5產生的32位字串'
,
13.
`DIGEST_TEXT`
longtext COMMENT
'將語句中值部分用問號代替，用於SQL語句歸類'
,
14.
`CURRENT_SCHEMA`
varchar(
64
)
DEFAULT NULL COMMENT
'預設的資料庫名'
,
15.
`OBJECT_TYPE`
varchar(
64
)
DEFAULT NULL COMMENT
'保留欄位'
,
16.
`OBJECT_SCHEMA`
varchar(
64
)
DEFAULT NULL COMMENT
'保留欄位'
,
17.
`OBJECT_NAME`
varchar(
64
)
DEFAULT NULL COMMENT
'保留欄位'
,
18.
`OBJECT_INSTANCE_BEGIN`
bigint(
20
)
unsigned DEFAULT NULL COMMENT
'記憶體地址'
,
19.
`MYSQL_ERRNO`
int
(
11
)
DEFAULT NULL COMMENT
''
,
20.
`RETURNED_SQLSTATE`
varchar(
5
)
DEFAULT NULL COMMENT
''
,
21.
`MESSAGE_TEXT`
varchar(
128
)
DEFAULT NULL COMMENT
'資訊'
,
22.
`ERRORS`
bigint(
20
)
unsigned NOT NULL COMMENT
'錯誤數目'
,
23.
`WARNINGS`
bigint(
20
)
unsigned NOT NULL COMMENT
'警告數目'
,
24.
`ROWS_AFFECTED`
bigint(
20
)
unsigned NOT NULL COMMENT
'影響的數目'
,
25.
`ROWS_SENT`
bigint(
20
)
unsigned NOT NULL COMMENT
'返回的記錄數'
,
26.
`ROWS_EXAMINED`
bigint(
20
)
unsigned NOT NULL COMMENT
'讀取掃描的記錄數目'
,
27.
`CREATED_TMP_DISK_TABLES`
bigint(
20
)
unsigned NOT NULL COMMENT
'建立磁碟臨時表數目'
,
28.
`CREATED_TMP_TABLES`
bigint(
20
)
unsigned NOT NULL COMMENT
'建立臨時表數目'
,
29.
`SELECT_FULL_JOIN`
bigint(
20
)
unsigned NOT NULL COMMENT
'join時，第一個表為全表掃描的數目'
,
30.
`SELECT_FULL_RANGE_JOIN`
bigint(
20
)
unsigned NOT NULL COMMENT
'引用表採用range方式掃描的數目'
,
31.
`SELECT_RANGE`
bigint(
20
)
unsigned NOT NULL COMMENT
'join時，第一個表採用range方式掃描的數目'
,
32.
`SELECT_RANGE_CHECK`
bigint(
20
)
unsigned NOT NULL COMMENT
''
,
33.
`SELECT_SCAN`
bigint(
20
)
unsigned NOT NULL COMMENT
'join時，第一個表位全表掃描的數目'
,
34.
`SORT_MERGE_PASSES`
bigint(
20
)
unsigned NOT NULL COMMENT
''
,
35.
`SORT_RANGE`
bigint(
20
)
unsigned NOT NULL COMMENT
'範圍排序數目'
,
36.
`SORT_ROWS`
bigint(
20
)
unsigned NOT NULL COMMENT
'排序的記錄數目'
,
37.
`SORT_SCAN`
bigint(
20
)
unsigned NOT NULL COMMENT
'全表排序數目'
,
38.
`NO_INDEX_USED`
bigint(
20
)
unsigned NOT NULL COMMENT
'沒有使用索引數目'
,
39.
`NO_GOOD_INDEX_USED`
bigint(
20
)
unsigned NOT NULL COMMENT
''
,
40.
`NESTING_EVENT_ID`
bigint(
20
)
unsigned DEFAULT NULL COMMENT
'該事件對應的父事件ID'
,
41.
`NESTING_EVENT_TYPE`
enum
(
'STATEMENT'
,
'STAGE'
,
'WAIT'
)
DEFAULT NULL COMMENT
'父事件型別(STATEMENT,
STAGE, WAIT)'
42.
)
ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
六：Connection 表
1，users ：記錄使用者連線數資訊
2，hosts ：記錄了主機連線數資訊
3，accounts ：記錄了使用者主機連線數資訊
01.
zjy
@performance_schema
12
:
03
:
27
>select
* from users;
02.
+------------------+---------------------+-------------------+
03.
|
USER | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
04.
+------------------+---------------------+-------------------+
05.
|
debian-sys-maint |
|
36
|
06.
|
zjy |
1
|
22285
|
07.
|
dchat_php |
|
37864
|
08.
|
dxyslave |
2
|
9
|
09.
|
nagios |
|
10770
|
10.
|
dchat_data |
140
|
2233023
|
11.
|
NULL |
|
15866
|
12.
|
dchat_api |
160
|
2754212
|
13.
|
mha_data |
1
|
36
|
14.
|
backup |
|
15
|
15.
|
cacti |
|
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
|
|
698
|
27.
|
192.168
.
100.21
|
|
2
|
28.
|
192.168
.
100.220
|
150
|
2526136
|
29.
|
192.168
.
100.25
|
1
|
7
|
30.
|
NULL |
|
15867
|
31.
|
192.168
.
100.241
|
|
21558
|
32.
|
192.168
.
100.191
|
1
|
34
|
33.
|
localhost |
|
10807
|
34.
|
192.168
.
100.118
|
1
|
2
|
35.
|
192.168
.
100.251
|
|
4312
|
36.
|
192.168
.
100.23
|
1
|
31
|
37.
|
192.168
.
100.193
|
|
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
|
|
4313
|
46.
|
debian-sys-maint | localhost |
|
36
|
47.
|
backup |
192.168
.
100.193
|
|
15
|
48.
|
dchat_api |
192.168
.
100.220
|
80
|
1382585
|
49.
|
dchat_php |
192.168
.
100.220
|
|
20292
|
50.
|
zjy |
192.168
.
100.139
|
|
698
|
51.
|
zjy |
192.168
.
100.241
|
|
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
|
|
17572
|
59.
|
dchat_data |
192.168
.
100.220
|
70
|
1123306
|
60.
|
NULL | NULL |
|
15868
|
61.
|
mha_data |
192.168
.
100.21
|
|
2
|
62.
|
dchat_api |
192.168
.
100.218
|
80
|
1371918
|
63.
|
nagios | localhost |
|
10771
|
64.
+------------------+-----------------+---------------------+-------------------+
七：Summary 表： Summary表聚集了各個維度的統計資訊包括表維度，索引維度，會話維度，語句維度和鎖維度的統計資訊
1，events_waits_summary_global_by_event_name ：按等待事件型別聚合，每個事件一條記錄
1.
CREATE
TABLE `events_waits_summary_global_by_event_name` (
2.
`EVENT_NAME`
varchar(
128
)
NOT NULL COMMENT
'事件名稱'
,
3.
`COUNT_STAR`
bigint(
20
)
unsigned NOT NULL COMMENT
'事件計數'
,
4.
`SUM_TIMER_WAIT`
bigint(
20
)
unsigned NOT NULL COMMENT
'總的等待時間'
,
5.
`MIN_TIMER_WAIT`
bigint(
20
)
unsigned NOT NULL COMMENT
'最小等待時間'
,
6.
`AVG_TIMER_WAIT`
bigint(
20
)
unsigned NOT NULL COMMENT
'平均等待時間'
,
7.
`MAX_TIMER_WAIT`
bigint(
20
)
unsigned NOT NULL COMMENT
'最大等待時間'
8.
)
ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
2，events_waits_summary_by_instance
：按等待事件物件聚合，同一種等待事件，可能有多個例項，每個例項有不同的記憶體地址，因此
event_name+object_instance_begin唯一確定一條記錄。
01.
CREATE
TABLE `events_waits_summary_by_instance` (
02.
`EVENT_NAME`
varchar(
128
)
NOT NULL COMMENT
'事件名稱'
,
03.
`OBJECT_INSTANCE_BEGIN`
bigint(
20
)
unsigned NOT NULL COMMENT
'記憶體地址'
,
04.
`COUNT_STAR`
bigint(
20
)
unsigned NOT NULL COMMENT
'事件計數'
,
05.
`SUM_TIMER_WAIT`
bigint(
20
)
unsigned NOT NULL COMMENT
'總的等待時間'
,
06.
`MIN_TIMER_WAIT`
bigint(
20
)
unsigned NOT NULL COMMENT
'最小等待時間'
,
07.
`AVG_TIMER_WAIT`
bigint(
20
)
unsigned NOT NULL COMMENT
'平均等待時間'
,
08.
`MAX_TIMER_WAIT`
bigint(
20
)
unsigned NOT NULL COMMENT
'最大等待時間'
09.
)
ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
3，events_waits_summary_by_thread_by_event_name ：按每個執行緒和事件來統計，thread_id+event_name唯一確定一條記錄。
01.
CREATE
TABLE `events_waits_summary_by_thread_by_event_name` (
02.
`THREAD_ID`
bigint(
20
)
unsigned NOT NULL COMMENT
'執行緒ID'
,
03.
`EVENT_NAME`
varchar(
128
)
NOT NULL COMMENT
'事件名稱'
,
04.
`COUNT_STAR`
bigint(
20
)
unsigned NOT NULL COMMENT
'事件計數'
,
05.
`SUM_TIMER_WAIT`
bigint(
20
)
unsigned NOT NULL COMMENT
'總的等待時間'
,
06.
`MIN_TIMER_WAIT`
bigint(
20
)
unsigned NOT NULL COMMENT
'最小等待時間'
,
07.
`AVG_TIMER_WAIT`
bigint(
20
)
unsigned NOT NULL COMMENT
'平均等待時間'
,
08.
`MAX_TIMER_WAIT`
bigint(
20
)
unsigned NOT NULL COMMENT
'最大等待時間'
09.
)
ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
4，events_stages_summary_global_by_event_name ：按事件階段型別聚合，每個事件一條記錄，表結構同上。
5，events_stages_summary_by_thread_by_event_name ：按每個執行緒和事件來階段統計，表結構同上。
6，events_statements_summary_by_digest ：按照事件的語句進行聚合。
01.
CREATE
TABLE `events_statements_summary_by_digest` (
02.
`SCHEMA_NAME`
varchar(
64
)
DEFAULT NULL COMMENT
'庫名'
,
03.
`DIGEST`
varchar(
32
)
DEFAULT NULL COMMENT
'對SQL_TEXT做MD5產生的32位字串。如果為consumer表中沒有開啟statement_digest選項，則為NULL'
,
04.
`DIGEST_TEXT`
longtext COMMENT
'將語句中值部分用問號代替，用於SQL語句歸類。如果為consumer表中沒有開啟statement_digest選項，則為NULL。'
,
05.
`COUNT_STAR`
bigint(
20
)
unsigned NOT NULL COMMENT
'事件計數'
,
06.
`SUM_TIMER_WAIT`
bigint(
20
)
unsigned NOT NULL COMMENT
'總的等待時間'
,
07.
`MIN_TIMER_WAIT`
bigint(
20
)
unsigned NOT NULL COMMENT
'最小等待時間'
,
08.
`AVG_TIMER_WAIT`
bigint(
20
)
unsigned NOT NULL COMMENT
'平均等待時間'
,
09.
`MAX_TIMER_WAIT`
bigint(
20
)
unsigned NOT NULL COMMENT
'最大等待時間'
,
10.
`SUM_LOCK_TIME`
bigint(
20
)
unsigned NOT NULL COMMENT
'鎖時間總時長'
,
11.
`SUM_ERRORS`
bigint(
20
)
unsigned NOT NULL COMMENT
'錯誤數的總'
,
12.
`SUM_WARNINGS`
bigint(
20
)
unsigned NOT NULL COMMENT
'警告的總數'
,
13.
`SUM_ROWS_AFFECTED`
bigint(
20
)
unsigned NOT NULL COMMENT
'影響的總數目'
,
14.
`SUM_ROWS_SENT`
bigint(
20
)
unsigned NOT NULL COMMENT
'返回總數目'
,
15.
`SUM_ROWS_EXAMINED`
bigint(
20
)
unsigned NOT NULL COMMENT
'總的掃描的數目'
,
16.
`SUM_CREATED_TMP_DISK_TABLES`
bigint(
20
)
unsigned NOT NULL COMMENT
'建立磁碟臨時表的總數目'
,
17.
`SUM_CREATED_TMP_TABLES`
bigint(
20
)
unsigned NOT NULL COMMENT
'建立臨時表的總數目'
,
18.
`SUM_SELECT_FULL_JOIN`
bigint(
20
)
unsigned NOT NULL COMMENT
'第一個表全表掃描的總數目'
,
19.
`SUM_SELECT_FULL_RANGE_JOIN`
bigint(
20
)
unsigned NOT NULL COMMENT
'總的採用range方式掃描的數目'
,
20.
`SUM_SELECT_RANGE`
bigint(
20
)
unsigned NOT NULL COMMENT
'第一個表採用range方式掃描的總數目'
,
21.
`SUM_SELECT_RANGE_CHECK`
bigint(
20
)
unsigned NOT NULL COMMENT
''
,
22.
`SUM_SELECT_SCAN`
bigint(
20
)
unsigned NOT NULL COMMENT
'第一個表位全表掃描的總數目'
,
23.
`SUM_SORT_MERGE_PASSES`
bigint(
20
)
unsigned NOT NULL COMMENT
''
,
24.
`SUM_SORT_RANGE`
bigint(
20
)
unsigned NOT NULL COMMENT
'範圍排序總數'
,
25.
`SUM_SORT_ROWS`
bigint(
20
)
unsigned NOT NULL COMMENT
'排序的記錄總數目'
,
26.
`SUM_SORT_SCAN`
bigint(
20
)
unsigned NOT NULL COMMENT
'第一個表排序掃描總數目'
,
27.
`SUM_NO_INDEX_USED`
bigint(
20
)
unsigned NOT NULL COMMENT
'沒有使用索引總數'
,
28.
`SUM_NO_GOOD_INDEX_USED`
bigint(
20
)
unsigned NOT NULL COMMENT
''
,
29.
`FIRST_SEEN`
timestamp NOT NULL DEFAULT
'0000-00-00
00:00:00'
COMMENT
'第一次執行時間'
,
30.
`LAST_SEEN`
timestamp NOT NULL DEFAULT
'0000-00-00
00:00:00'
COMMENT
'最後一次執行時間'
31.
)
ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
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 normal
external lock則透過介面函式handler::external_lock呼叫儲存引擎層，OPERATION列的值為：read external、write external
14，Connection Summaries表 ：account、user、host
events_waits_summary_by_account_by_event_name
events_waits_summary_by_user_by_event_name
events_waits_summary_by_host_by_event_name
events_stages_summary_by_account_by_event_name
events_stages_summary_by_user_by_event_name
events_stages_summary_by_host_by_event_name
events_statements_summary_by_account_by_event_name
events_statements_summary_by_user_by_event_name
events_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
><strong>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.
</strong>***************************
1
.
row ***************************<strong>
03.
SCHEMA_NAME</strong>:
dchat
04.
<strong>DIGEST_TEXT</strong>:
SELECT ...
05.
<strong>COUNT_STAR</strong>:
1161210102
06.
SUM_ROWS_SENT:
1161207842
07.
SUM_ROWS_EXAMINED:
<strong>
08.
FIRST_SEEN</strong>:
2016
-
02
-
17
00
:
36
:
46
<strong>
09.
LAST_SEEN</strong>:
2016
-
03
-
07
11
:
36
:
29
各個欄位的註釋可以看上面的表結構說明：從2月17號到3月7號該SQL執行了1161210102次。
2，哪個SQL平均響應時間最多：
01.
zjy
@performance_schema
11
:
36
:
28
><strong>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.
</strong>***************************
1
.
row ***************************<strong>
03.
SCHEMA_NAME</strong>:
dchat
04.
<strong>DIGEST_TEXT</strong>:
SELECT ...
05.
COUNT_STAR:
1
<strong>
06.
AVG_TIMER_WAIT</strong>:
273238183964000
07.
SUM_ROWS_SENT:
50208
08.
SUM_ROWS_EXAMINED:
5565651
<strong>
09.
FIRST_SEEN</strong>:
2016
-
02
-
22
13
:
27
:
33
<strong>
10.
LAST_SEEN</strong>:
2016
-
02
-
22
13
:
27
:
33
各個欄位的註釋可以看上面的表結構說明：從2月17號到3月7號該SQL平均響應時間273238183964000皮秒（1000000000000皮秒=1秒）
3，哪個SQL掃描的行數最多：
SUM_ROWS_EXAMINED
4，哪個SQL使用的臨時表最多：
SUM_CREATED_TMP_DISK_TABLES、SUM_CREATED_TMP_TABLES
5，哪個SQL返回的結果集最多：
SUM_ROWS_SENT
6，哪個SQL排序數最多：
SUM_SORT_ROWS
透過上述指標我們可以間接獲得某類SQL的邏輯IO(SUM_ROWS_EXAMINED)，CPU消耗(SUM_SORT_ROWS)，網路頻寬(SUM_ROWS_SENT)的對比。
透過 file_summary_by_instance 表，可以獲得系統執行到現在，哪個檔案(表)物理IO最多，這可能意味著這個表經常需要訪問磁碟IO。
7，哪個表、檔案邏輯IO最多（熱資料）：
01.
zjy
@performance_schema
12
:
16
:
18
><strong>SELECT
FILE_NAME,EVENT_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ,COUNT_WRITE,SUM_NUMBER_OF_BYTES_WRITE
FROM file_summary_by_instance ORDER BY
SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE DESC LIMIT 2G
02.
</strong>***************************
1
.
row ***************************
03.
FILE_NAME:
/var/lib/mysql/<strong>ibdata1 #檔案</strong>
04.
EVENT_NAME:
wait/io/file/innodb/innodb_data_file
05.
COUNT_READ:
544
06.
SUM_NUMBER_OF_BYTES_READ:
10977280
07.
COUNT_WRITE:
3700729
08.
SUM_NUMBER_OF_BYTES_WRITE:
1433734217728
09.
***************************
2
.
row ***************************
10.
FILE_NAME:
/var/lib/mysql/dchat/<strong>fans.ibd #表</strong>
11.
EVENT_NAME:
wait/io/file/innodb/innodb_data_file
12.
COUNT_READ:
9370680
13.
SUM_NUMBER_OF_BYTES_READ:
153529188352
14.
COUNT_WRITE:
67576376
15.
SUM_NUMBER_OF_BYTES_WRITE:
1107815432192
8，哪個索引使用最多：
1.
zjy
@performance_schema
12
:
18
:
42
><strong>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 limit
1
;
2.
</strong>+-------------+------------+-------------+--------------+--------------+--------------+
3.
|
OBJECT_NAME | INDEX_NAME | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
4.
+-------------+------------+-------------+--------------+--------------+--------------+
5.
|
<strong>fans</strong> | <strong>PRIMARY</strong> |
29002695158
|
|
296373434
|
|
6.
+-------------+------------+-------------+--------------+--------------+--------------+
7.
1
row
in set (
0.29
sec)
透過 table_io_waits_summary_by_index_usage 表，可以獲得系統執行到現在，哪個表的具體哪個索引(包括主鍵索引，二級索引)使用最多。
9，哪個索引沒有使用過：
1.
zjy
@performance_schema
12
:
23
:
22
><strong>SELECT
OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME FROM
table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND
COUNT_STAR =
AND
OBJECT_SCHEMA <>
'mysql'
ORDER
BY OBJECT_SCHEMA,OBJECT_NAME;</strong>
10，哪個等待事件消耗的時間最多：
1.
zjy
@performance_schema
12
:
25
:
22
><strong>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 LIMIT
1
;</strong>
11，類似profiling功能：
分析具體某條SQL，該SQL在執行各個階段的時間消耗，透過events_statements_xxx表和events_stages_xxx表，就可以達到目的。兩個表透過event_id與nesting_event_id關聯，stages表的nesting_event_id為對應statements表的event_id；針對每個stage可能出現的鎖等待，一個stage會對應一個或多個wait，透過stage_xxx表的event_id欄位與waits_xxx表的nesting_event_id進行關聯。如：
001.
比如分析包含count(*)的某條SQL語句，具體如下：
002.
003.
SELECT
004.
EVENT_ID,
005.
sql_text
006.
FROM
events_statements_history
007.
WHERE
sql_text LIKE
'%count(*)%'
;
008.
+----------+--------------------------------------+
009.
|
EVENT_ID | sql_text |
010.
+----------+--------------------------------------+
011.
|
1690
|
select count(*) from chuck.test_slow |
012.
+----------+--------------------------------------+
013.
首先得到了語句的event_id為
1690
，透過查詢events_stages_xxx中nesting_event_id為
1690
的記錄，可以達到目的。
014.
015.
a.檢視每個階段的時間消耗：
016.
SELECT
017.
event_id,
018.
EVENT_NAME,
019.
SOURCE,
020.
TIMER_END
- TIMER_START
021.
FROM
events_stages_history_long
022.
WHERE
NESTING_EVENT_ID =
1690
;
023.
+----------+--------------------------------+----------------------+-----------------------+
024.
|
event_id | EVENT_NAME | SOURCE | TIMER_END-TIMER_START |
025.
+----------+--------------------------------+----------------------+-----------------------+
026.
|
1691
|
stage/sql/init | mysqld.cc:
990
|
316945000
|
027.
|
1693
|
stage/sql/checking permissions | sql_parse.cc:
5776
|
26774000
|
028.
|
1695
|
stage/sql/Opening tables | sql_base.cc:
4970
|
41436934000
|
029.
|
2638
|
stage/sql/init | sql_select.cc:
1050
|
85757000
|
030.
|
2639
|
stage/sql/System lock | lock.cc:
303
|
40017000
|
031.
|
2643
|
stage/sql/optimizing | sql_optimizer.cc:
138
|
38562000
|
032.
|
2644
|
stage/sql/statistics | sql_optimizer.cc:
362
|
52845000
|
033.
|
2645
|
stage/sql/preparing | sql_optimizer.cc:
485
|
53196000
|
034.
|
2646
|
stage/sql/executing | sql_executor.cc:
112
|
3153000
|
035.
|
2647
|
stage/sql/Sending data | sql_executor.cc:
192
|
7369072089000
|
036.
|
4304138
|
stage/sql/end | sql_select.cc:
1105
|
19920000
|
037.
|
4304139
|
stage/sql/query end | sql_parse.cc:
5463
|
44721000
|
038.
|
4304145
|
stage/sql/closing tables | sql_parse.cc:
5524
|
61723000
|
039.
|
4304152
|
stage/sql/freeing items | sql_parse.cc:
6838
|
455678000
|
040.
|
4304155
|
stage/sql/logging slow query | sql_parse.cc:
2258
|
83348000
|
041.
|
4304159
|
stage/sql/cleaning up | sql_parse.cc:
2163
|
4433000
|
042.
+----------+--------------------------------+----------------------+-----------------------+
043.
透過間接關聯，我們能分析得到SQL語句在每個階段的時間消耗，時間單位以皮秒錶示。這裡展示的結果很類似profiling功能，有了performance
schema，就不再需要profiling這個功能了。另外需要注意的是，由於預設情況下events_stages_history表中只為每個連線記錄了最近
10
條記錄，為了確保獲取所有記錄，需要訪問events_stages_history_long表
044.
045.
b.檢視某個階段的鎖等待情況
046.
針對每個stage可能出現的鎖等待，一個stage會對應一個或多個wait，events_waits_history_long這個表容易爆滿[預設閥值
10000
]。由於select
count(*)需要IO(邏輯IO或者物理IO)，所以在stage/sql/Sending data階段會有io等待的統計。透過stage_xxx表的event_id欄位與waits_xxx表的nesting_event_id進行關聯。
047.
SELECT
048.
event_id,
049.
event_name,
050.
source,
051.
timer_wait,
052.
object_name,
053.
index_name,
054.
operation,
055.
nesting_event_id
056.
FROM
events_waits_history_long
057.
WHERE
nesting_event_id =
2647
;
058.
+----------+---------------------------+-----------------+------------+-------------+------------+-----------+------------------+
059.
|
event_id | event_name | source | timer_wait | object_name | index_name | operation | nesting_event_id |
060.
+----------+---------------------------+-----------------+------------+-------------+------------+-----------+------------------+
061.
|
190607
|
wait/io/table/sql/handler | handler.cc:
2842
|
1845888
|
test_slow | idx_c1 | fetch |
2647
|
062.
|
190608
|
wait/io/table/sql/handler | handler.cc:
2842
|
1955328
|
test_slow | idx_c1 | fetch |
2647
|
063.
|
190609
|
wait/io/table/sql/handler | handler.cc:
2842
|
1929792
|
test_slow | idx_c1 | fetch |
2647
|
064.
|
190610
|
wait/io/table/sql/handler | handler.cc:
2842
|
1869600
|
test_slow | idx_c1 | fetch |
2647
|
065.
|
190611
|
wait/io/table/sql/handler | handler.cc:
2842
|
1922496
|
test_slow | idx_c1 | fetch |
2647
|
066.
+----------+---------------------------+-----------------+------------+-------------+------------+-----------+------------------+
067.
透過上面的實驗，我們知道了statement,stage,wait的三級結構，透過nesting_event_id進行關聯，它表示某個事件的父event_id。
068.
069.
(
2
).模擬innodb行鎖等待的例子
070.
會話A執行語句update
test_icp set y=y+
1
where
x=
1
(x為primary
key)，不commit；會話B執行同樣的語句update test_icp set y=y+
1
where
x=
1
，會話B堵塞，並最終報錯。透過連線連線查詢events_statements_history_long和events_stages_history_long，可以看到在updating階段花了大約60s的時間。這主要因為例項上的innodb_lock_wait_timeout設定為
60
，等待60s後超時報錯了。
071.
072.
SELECT
073.
statement.EVENT_ID,
074.
stages.event_id,
075.
statement.sql_text,
076.
stages.event_name,
077.
stages.timer_wait
078.
FROM
events_statements_history_long statement
079.
join
events_stages_history_long stages
080.
on
statement.event_id=stages.nesting_event_id
081.
WHERE
statement.sql_text =
'update
test_icp set y=y+1 where x=1'
;
082.
+----------+----------+-------------------------------------+--------------------------------+----------------+
083.
|
EVENT_ID | event_id | sql_text | event_name | timer_wait |
084.
+----------+----------+-------------------------------------+--------------------------------+----------------+
085.
|
5816
|
5817
|
update test_icp set y=y+
1
where
x=
1
|
stage/sql/init |
195543000
|
086.
|
5816
|
5819
|
update test_icp set y=y+
1
where
x=
1
|
stage/sql/checking permissions |
22730000
|
087.
|
5816
|
5821
|
update test_icp set y=y+
1
where
x=
1
|
stage/sql/Opening tables |
66079000
|
088.
|
5816
|
5827
|
update test_icp set y=y+
1
where
x=
1
|
stage/sql/init |
89116000
|
089.
|
5816
|
5828
|
update test_icp set y=y+
1
where
x=
1
|
stage/sql/System lock |
218744000
|
090.
|
5816
|
5832
|
update test_icp set y=y+
1
where
x=
1
|
stage/sql/updating |
6001362045000
|
091.
|
5816
|
5968
|
update test_icp set y=y+
1
where
x=
1
|
stage/sql/end |
10435000
|
092.
|
5816
|
5969
|
update test_icp set y=y+
1
where
x=
1
|
stage/sql/query end |
85979000
|
093.
|
5816
|
5983
|
update test_icp set y=y+
1
where
x=
1
|
stage/sql/closing tables |
56562000
|
094.
|
5816
|
5990
|
update test_icp set y=y+
1
where
x=
1
|
stage/sql/freeing items |
83563000
|
095.
|
5816
|
5992
|
update test_icp set y=y+
1
where
x=
1
|
stage/sql/cleaning up |
4589000
|
096.
+----------+----------+-------------------------------------+--------------------------------+----------------+
097.
檢視wait事件：
098.
SELECT
099.
event_id,
100.
event_name,
101.
source,
102.
timer_wait,
103.
object_name,
104.
index_name,
105.
operation,
106.
nesting_event_id
107.
FROM
events_waits_history_long
108.
WHERE
nesting_event_id =
5832
;
109.
***************************
1
.
row ***************************
110.
event_id:
5832
111.
event_name:
wait/io/table/sql/handler
112.
source:
handler.cc:
2782
113.
timer_wait:
6005946156624
114.
object_name:
test_icp
115.
index_name:
PRIMARY
116.
operation:
fetch
117.
從結果來看，waits表中記錄了一個fetch等待事件，但並沒有更細的innodb行鎖等待事件統計。
118.
119.
(
3
).模擬MDL鎖等待的例子
120.
會話A執行一個大查詢select
count(*) from test_slow，會話B執行表結構變更alter table test_slow modify c2 varchar(
152
);透過如下語句可以得到alter語句的執行過程，重點關注“stage/sql/Waiting
for
table
metadata lock”階段。
121.
122.
SELECT
123.
statement.EVENT_ID,
124.
stages.event_id,
125.
statement.sql_text,
126.
stages.event_name
as stage_name,
127.
stages.timer_wait
as stage_time
128.
FROM
events_statements_history_long statement
129.
left
join events_stages_history_long stages
130.
on
statement.event_id=stages.nesting_event_id
131.
WHERE
statement.sql_text =
'alter
table test_slow modify c2 varchar(152)'
;
132.
+-----------+-----------+----------------------------------------------+----------------------------------------------------+---------------+
133.
|
EVENT_ID | event_id | sql_text | stage_name | stage_time |
134.
+-----------+-----------+----------------------------------------------+----------------------------------------------------+---------------+
135.
|
326526744
|
326526745
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/init |
216662000
|
136.
|
326526744
|
326526747
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/checking permissions |
18183000
|
137.
|
326526744
|
326526748
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/checking permissions |
10294000
|
138.
|
326526744
|
326526750
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/init |
4783000
|
139.
|
326526744
|
326526751
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/Opening tables |
140172000
|
140.
|
326526744
|
326526760
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/setup |
157643000
|
141.
|
326526744
|
326526769
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/creating table |
8723217000
|
142.
|
326526744
|
326526803
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/After create |
257332000
|
143.
|
326526744
|
326526832
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/Waiting
for
table
metadata lock |
1000181831000
|
144.
|
326526744
|
326526835
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/After create |
33483000
|
145.
|
326526744
|
326526838
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/Waiting
for
table
metadata lock |
1000091810000
|
146.
|
326526744
|
326526841
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/After create |
17187000
|
147.
|
326526744
|
326526844
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/Waiting
for
table
metadata lock |
1000126464000
|
148.
|
326526744
|
326526847
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/After create |
27472000
|
149.
|
326526744
|
326526850
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/Waiting
for
table
metadata lock |
561996133000
|
150.
|
326526744
|
326526853
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/After create |
124876000
|
151.
|
326526744
|
326526877
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/System lock |
30659000
|
152.
|
326526744
|
326526881
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/preparing
for
alter
table |
40246000
|
153.
|
326526744
|
326526889
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/altering table |
36628000
|
154.
|
326526744
|
326528280
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/end |
43824000
|
155.
|
326526744
|
326528281
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/query end |
112557000
|
156.
|
326526744
|
326528299
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/closing tables |
27707000
|
157.
|
326526744
|
326528305
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/freeing items |
201614000
|
158.
|
326526744
|
326528308
|
alter table test_slow modify c2 varchar(
152
)
| stage/sql/cleaning up |
3584000
|
159.
+-----------+-----------+----------------------------------------------+----------------------------------------------------+---------------+
160.
從結果可以看到，出現了多次stage/sql/Waiting
for
table
metadata lock階段，並且間隔1s，說明每隔1s鍾會重試判斷。找一個該階段的event_id,透過nesting_event_id關聯，確定到底在等待哪個wait事件。
161.
SELECT
162.
event_id,
163.
event_name,
164.
source,
165.
timer_wait,
166.
object_name,
167.
index_name,
168.
operation,
169.
nesting_event_id
170.
FROM
events_waits_history_long
171.
WHERE
nesting_event_id =
326526850
;
172.
+-----------+---------------------------------------------------+------------------+--------------+-------------+------------+------------+------------------+
173.
|
event_id | event_name | source | timer_wait | object_name | index_name | operation | nesting_event_id |
174.
+-----------+---------------------------------------------------+------------------+--------------+-------------+------------+------------+------------------+
175.
|
326526851
|
wait/synch/cond/sql/MDL_context::COND_wait_status | mdl.cc:
1327
|
562417991328
|
NULL | NULL | timed_wait |
326526850
|
176.
|
326526852
|
wait/synch/mutex/mysys/my_thread_var::mutex | sql_class.h:
3481
|
733248
|
NULL | NULL | lock |
326526850
|
177.
+-----------+---------------------------------------------------+------------------+--------------+-------------+------------+------------+------------------+
178.
透過結果可以知道，產生阻塞的是條件變數MDL_context::COND_wait_status，並且顯示了程式碼的位置。
總結：
本文透過對Performance Schema資料庫的介紹，主要用於收集資料庫伺服器效能引數：①提供程式等待的詳細資訊，包括鎖、互斥變數、檔案資訊；②儲存歷史的事件彙總資訊，為提供MySQL伺服器效能做出詳細的判斷；③對於新增和刪除監控事件點都非常容易，並可以改變mysql伺服器的監控週期，例如（CYCLE、MICROSECOND）。透過該庫得到資料庫執行的統計資訊，更好分析定位問題和完善監控資訊。類似的監控還有：
1.
開啟標準的innodb監控：
2.
CREATE
TABLE innodb_monitor (a INT) ENGINE=INNODB;
3.
開啟innodb的鎖監控：
4.
CREATE
TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
5.
開啟innodb表空間監控：
6.
CREATE
TABLE innodb_tablespace_monitor (a INT) ENGINE=INNODB;
7.
開啟innodb表監控：
8.
CREATE
TABLE innodb_table_monitor (a INT) ENGINE=INNODB;
開啟MySQL performance_schema 後對效能的影響
原文地址 先佔坑，還需要進一步測試 接上篇 使用 sysbench 對資料庫進行壓測，觀察開啟performance_schema後，對資料庫效能的影響 壓測計劃 ...
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10973950/viewspace-2214513/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 說明
- 使用說明
- Hack 說明
- 專利說明書及其說明書附圖
- openssh版本更新與說明 openssl版本更新與說明
- SDWebImage中文說明Web
- git 操作說明Git
- objc物件說明OBJ物件
- MOBIM介面說明
- Oracle Latch 說明Oracle
- SpringBoot整合說明Spring Boot
- FreeSql 使用說明SQL
- postman 使用說明Postman
- QLExpress使用說明Express
- WebApiClientCore使用說明WebAPIclient
- ThinkSNS 更新說明
- Jupiter 使用說明
- certbot 使用說明
- cmake使用說明
- 轉換說明
- rust配置說明Rust
- flowable 更新說明
- ApplicationContextAware使用說明APPContext
- Ironic映象說明
- 說明符%d
- winscp操作說明，winscp操作說明的詳細解讀
- CentOS 7升級核心簡明說明CentOS
- oradebug的使用說明
- axios使用說明書iOS
- MobIM-API說明API
- wc 命令使用說明
- mysqldump引數說明MySql
- tesseract安裝說明
- TOP引數說明
- oracle orapwd使用說明Oracle
- Rest 風格說明REST
- java String類說明Java
- JPA EntityManager使用說明