MySQL資料庫 Performance-Schema(一) 配置表

performance-schema最早在MYSQL 5.5中出現,而現在5.6,5.7中performance-Schema又添加瞭更多的監控項,統計信息也更豐富,越來越有ORACLE-AWR統計信息的趕腳,真乃DBA童鞋進行性能診斷分析的福音。本文主要講Performance-Schema中的配置表,通過配置表能大概瞭解performance-schema的全貌,為後續使用和深入理解做準備。

 

配置表

 

Performance-Schema中主要有5個配置表,具體如下:

 

root@performance_schema 06:03:09>show tables like '%setup%';

+—————————————-+

| Tables_in_performance_schema (%setup%) |

+—————————————-+

| setup_actors |

| setup_consumers |

| setup_instruments |

| setup_objects |

| setup_timers |

+—————————————-+

 

1.setup_actors用於配置user維度的監控,默認情況下監控所有用戶線程。

root@performance_schema 05:47:27>select * from setup_actors;

+——+——+——+

| HOST | USER | ROLE |

+——+——+——+

| % | % | % |

+——+——+——+

 

2.setup_consumers表用於配置事件的消費者類型,即收集的事件最終會寫入到哪些統計表中。

root@performance_schema 05:48:16>select * from setup_consumers;

+——————————–+———+

| NAME | ENABLED |

+——————————–+———+

| events_stages_current | NO |

| events_stages_history | NO |

| events_stages_history_long | NO |

| events_statements_current | YES |

| events_statements_history | NO |

| events_statements_history_long | NO |

| events_waits_current | NO |

| events_waits_history | NO |

| events_waits_history_long | NO |

| global_instrumentation | YES |

| thread_instrumentation | YES |

| statements_digest | YES |

+——————————–+———+

可以看到有12個consumer,如果不想關註某些consumer,可以將ENABLED設置為NO,比如events_statements_history_long設置為NO,

則收集事件不會寫入到對應的表events_statements_history_long中。12個consumer不是平級的,存在多級層次關系。具體如下表:

global_instrumentation 

 |– thread_instrumentation

   |– events_waits_current

     |– events_waits_history

     |– events_waits_history_long

   |– events_stages_current

     |– events_stages_history

     |– events_stages_history_long

   |– events_statements_current

     |– events_statements_history

     |– events_statements_history_long

 |– statements_digest

 

多層次的consumer遵從一個基本原則,隻有上一層次的為YES,才會繼續檢查該本層為YES or NO。global_instrumentation是最高級別consumer,如果它設置為NO,則所有的consumer都會忽略。如果隻打開global_instrumentation,而關閉所有其它子consumer(設置為NO),則隻收集全局維度的統計信息,比如xxx_instance表,而不會收集用戶維度,語句維度的信息。第二層次的是thread_instrumentation,用戶線程維度的統計信息,比如xxx_by_thread表,另外一個是statements_digest,這個用於全局統計SQL-digest的信息。第三層次是語句維度,包括events_waits_current,events_stages_current和events_statements_current,分別用於統計wait,stages和statement信息,第四層次是歷史表信息,主要包括xxx_history和xxx_history_long。

 

3.setup_instruments表用於配置一條條具體的instrument,主要包含4大類:idle,stage/xxx,statement/xxx,wait/xxx.

root@performance_schema 06:25:50>select name,count(*) from setup_instruments group by LEFT(name,5);

+———————————+———-+

| name | count(*) |

+———————————+———-+

| idle | 1 |

| stage/sql/After create | 111 |

| statement/sql/select | 170 |

| wait/synch/mutex/sql/PAGE::lock | 296 |

+———————————+———-+

 

idle表示socket空閑的時間,stage類表示語句的每個執行階段的統計,statement類統計語句維度的信息,wait類統計各種等待事件,比如IO,mutux,spin_lock,condition等。從上表統計結果來看,可以基本看到每類的instrument數目,stage包含111個,statement包含170個,wait包含296個。

 

4.setup_objects表用於配置監控對象,默認情況下所有mysql,performance_schema和information_schema中的表都不監控。而其它DB的所有表都監控。

 

root@performance_schema 06:25:55>select * from setup_objects;

+————-+——————–+————-+———+——-+

| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | ENABLED | TIMED |

+————-+——————–+————-+———+——-+

| TABLE | mysql | % | NO | NO |

| TABLE | performance_schema | % | NO | NO |

| TABLE | information_schema | % | NO | NO |

| TABLE | % | % | YES | YES |

+————-+——————–+————-+———+——-+

 

5.setup_timers表用於配置每種類型指令的統計時間單位。MICROSECOND表示統計單位是微妙,CYCLE表示統計單位是時鐘周期,時間度量與CPU的主頻有關,NANOSECOND表示統計單位是納秒,關於每種類型的具體含義,可以參考performance_timer這個表。由於wait類包含的都是等待事件,單個SQL調用次數比較多,因此選擇代價最小的度量單位cycle。但無論采用哪種度量單位,最終統計表中統計的時間都會裝換到皮秒。

 

root@performance_schema 06:29:50>select * from setup_timers;

+———–+————-+

| NAME | TIMER_NAME |

+———–+————-+

| idle | MICROSECOND |

| wait | CYCLE |

| stage | NANOSECOND |

| statement | NANOSECOND |

+———–+————-+

 

配置方式

 

默認情況下,setup_instruments表隻打開瞭statement和wait/io部分的指令,setup_consumer表中很多consumer也沒有打開。為瞭打開需要的選項,可以通過update語句直接修改配置表,並且修改後可以立即生效,但這種方式必需得啟動伺服器後才可以修改,並且無法持久化,重啟後,又得重新設置一遍。從5.6.4開始提供瞭my.cnf的配置方式,格式如下:

 

1.設置采集的instrument

performance_schema_instrument='instrument_name=value'

(1)打開wait類型的指令

performance_schema_instrument='wait/%'

(2)打開所有指令

performance_schema_instrument='%=on'

 

2.設置consumer

performance_schema_consumer_xxx=value

(1)打開 events_waits_history consumer

 

performance_schema_consumer_events_waits_current=on

 

performance_schema_consumer_events_waits_history=on

 

這裡要註意consumer的層次關系, events_waits_history處於第4層,因此設置它時,要確保events_statements_current,thread_instrumentation和global_instrumentation的ENABLED狀態都為YES,才能生效。由於默認thread_instrumentation和global_instrumentation都是YES,因此隻需要顯示設置events_waits_current和events_waits_current即可。

 

3.設置統計表大小

所有的performance_schema表均采用PERFORMANCE_SCHEMA存儲引擎,表中的所有數據隻存在內存,表的大小在系統初始化時已經

固定好,因此占用的內存是一定的。可以通過配置來定制具體每個表的記錄數。

performance_schema_events_waits_history_size=20

performance_schema_events_waits_history_long_size=15000

You May Also Like