MySQL Performance-Schema(一) 配置篇
為了系統(tǒng)的整理MYSQL 性能優(yōu)化方面的知識(shí)，這里收集部分好文章，方便自己學(xué)習(xí)。
performance-schema
performance-schema最早在MYSQL 5.5中出現(xiàn)，而現(xiàn)在5.6，5.7中performance-Schema又添加了更多的監(jiān)控項(xiàng)，統(tǒng)計(jì)信息也更豐富，越來越有ORACLE-AWR統(tǒng)計(jì)信息的趕腳，真乃DBA童鞋進(jìn)行性能診斷分析的福音。本文主要講Performance-Schema中的配置表，通過配置表能大概了解performance-schema的全貌，為后續(xù)使用和深入理解做準(zhǔn)備。
配置表
Performance-Schema中主要有5個(gè)配置表，具體如下：
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維度的監(jiān)控，默認(rèn)情況下監(jiān)控所有用戶線程。
root@performance_schema 05:47:27>select * from setup_actors;
+------+------+------+
| HOST | USER | ROLE |
+------+------+------+
| % | % | % |
+------+------+------+
2.setup_consumers表用于配置事件的消費(fèi)者類型，即收集的事件最終會(huì)寫入到哪些統(tǒng)計(jì)表中。
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個(gè)consumer，如果不想關(guān)注某些consumer，可以將ENABLED設(shè)置為NO，比如events_statements_history_long設(shè)置為NO，
則收集事件不會(huì)寫入到對(duì)應(yīng)的表events_statements_history_long中。12個(gè)consumer不是平級(jí)的，存在多級(jí)層次關(guān)系。具體如下表：
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遵從一個(gè)基本原則，只有上一層次的為YES，才會(huì)繼續(xù)檢查該本層為YES or NO。global_instrumentation是最高級(jí)別consumer，如果它設(shè)置為NO，則所有的consumer都會(huì)忽略。如果只打開global_instrumentation，而關(guān)閉所有其它子consumer(設(shè)置為NO)，則只收集全局維度的統(tǒng)計(jì)信息，比如xxx_instance表，而不會(huì)收集用戶維度，語句維度的信息。第二層次的是thread_instrumentation，用戶線程維度的統(tǒng)計(jì)信息，比如xxx_by_thread表，另外一個(gè)是statements_digest，這個(gè)用于全局統(tǒng)計(jì)SQL-digest的信息。第三層次是語句維度，包括events_waits_current，events_stages_current和events_statements_current，分別用于統(tǒng)計(jì)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空閑的時(shí)間，stage類表示語句的每個(gè)執(zhí)行階段的統(tǒng)計(jì)，statement類統(tǒng)計(jì)語句維度的信息，wait類統(tǒng)計(jì)各種等待事件，比如IO，mutux，spin_lock,condition等。從上表統(tǒng)計(jì)結(jié)果來看，可以基本看到每類的instrument數(shù)目，stage包含111個(gè)，statement包含170個(gè)，wait包含296個(gè)。
4.setup_objects表用于配置監(jiān)控對(duì)象，默認(rèn)情況下所有mysql，performance_schema和information_schema中的表都不監(jiān)控。而其它DB的所有表都監(jiān)控。
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表用于配置每種類型指令的統(tǒng)計(jì)時(shí)間單位。MICROSECOND表示統(tǒng)計(jì)單位是微妙，CYCLE表示統(tǒng)計(jì)單位是時(shí)鐘周期，時(shí)間度量與CPU的主頻有關(guān)，NANOSECOND表示統(tǒng)計(jì)單位是納秒，關(guān)于每種類型的具體含義，可以參考performance_timer這個(gè)表。由于wait類包含的都是等待事件，單個(gè)SQL調(diào)用次數(shù)比較多，因此選擇代價(jià)最小的度量單位cycle。但無論采用哪種度量單位，最終統(tǒng)計(jì)表中統(tǒng)計(jì)的時(shí)間都會(huì)裝換到皮秒。
root@performance_schema 06:29:50>select * from setup_timers；
+-----------+-------------+
| NAME | TIMER_NAME |
+-----------+-------------+
| idle | MICROSECOND |
| wait | CYCLE |
| stage | NANOSECOND |
| statement | NANOSECOND |
+-----------+-------------+
配置方式
? ? ? 默認(rèn)情況下，setup_instruments表只打開了statement和wait/io部分的指令，setup_consumer表中很多consumer也沒有打開。為了打開需要的選項(xiàng)，可以通過update語句直接修改配置表，并且修改后可以立即生效，但這種方式必需得啟動(dòng)服務(wù)器后才可以修改，并且無法持久化，重啟后，又得重新設(shè)置一遍。從5.6.4開始提供了my.cnf的配置方式，格式如下：
1.設(shè)置采集的instrument
performance_schema_instrument='instrument_name=value'
(1)打開wait類型的指令
performance_schema_instrument='wait/%'
(2)打開所有指令
performance_schema_instrument='%=on'
2.設(shè)置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的層次關(guān)系，?events_waits_history處于第4層，因此設(shè)置它時(shí)，要確保events_statements_current，thread_instrumentation和global_instrumentation的ENABLED狀態(tài)都為YES，才能生效。由于默認(rèn)thread_instrumentation和global_instrumentation都是YES，因此只需要顯示設(shè)置events_waits_current和events_waits_current即可。
3.設(shè)置統(tǒng)計(jì)表大小
所有的performance_schema表均采用PERFORMANCE_SCHEMA存儲(chǔ)引擎，表中的所有數(shù)據(jù)只存在內(nèi)存，表的大小在系統(tǒng)初始化時(shí)已經(jīng)
固定好，因此占用的內(nèi)存是一定的。可以通過配置來定制具體每個(gè)表的記錄數(shù)。
performance_schema_events_waits_history_size=20
performance_schema_events_waits_history_long_size=15000
轉(zhuǎn)載自： http://www.cnblogs.com/cchust/p/5022148.html
總結(jié)
以上是生活随笔為你收集整理的MySQL Performance-Schema(一) 配置篇的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： KUDU--秒级查询的数据仓库
- 下一篇： MySQL Performance-Sc