MySQL performance schema詳解

MYSQL performance schema詳解

0匾二、performance_schema的介紹

? MySQL的performance schema 用于監(jiān)控MySQL server在一個(gè)較低級(jí)別的運(yùn)行過(guò)程中的資源消耗、資源等待等情況塔鳍。

? 特點(diǎn)如下:

? 1、提供了一種在數(shù)據(jù)庫(kù)運(yùn)行時(shí)實(shí)時(shí)檢查server的內(nèi)部執(zhí)行情況的方法笛质。performance_schema 數(shù)據(jù)庫(kù)中的表使用performance_schema存儲(chǔ)引擎炼邀。該數(shù)據(jù)庫(kù)主要關(guān)注數(shù)據(jù)庫(kù)運(yùn)行過(guò)程中的性能相關(guān)的數(shù)據(jù),與information_schema不同灯抛,information_schema主要關(guān)注server運(yùn)行過(guò)程中的元數(shù)據(jù)信息

? 2跃巡、performance_schema通過(guò)監(jiān)視server的事件來(lái)實(shí)現(xiàn)監(jiān)視server內(nèi)部運(yùn)行情況, “事件”就是server內(nèi)部活動(dòng)中所做的任何事情以及對(duì)應(yīng)的時(shí)間消耗牧愁,利用這些信息來(lái)判斷server中的相關(guān)資源消耗在了哪里素邪?一般來(lái)說(shuō),事件可以是函數(shù)調(diào)用猪半、操作系統(tǒng)的等待兔朦、SQL語(yǔ)句執(zhí)行的階段(如sql語(yǔ)句執(zhí)行過(guò)程中的parsing 或 sorting階段)或者整個(gè)SQL語(yǔ)句與SQL語(yǔ)句集合偷线。事件的采集可以方便的提供server中的相關(guān)存儲(chǔ)引擎對(duì)磁盤(pán)文件、表I/O沽甥、表鎖等資源的同步調(diào)用信息声邦。
? 3、performance_schema中的事件與寫(xiě)入二進(jìn)制日志中的事件(描述數(shù)據(jù)修改的events)摆舟、事件計(jì)劃調(diào)度程序(這是一種存儲(chǔ)程序)的事件不同亥曹。performance_schema中的事件記錄的是server執(zhí)行某些活動(dòng)對(duì)某些資源的消耗、耗時(shí)恨诱、這些活動(dòng)執(zhí)行的次數(shù)等情況媳瞪。
? 4、performance_schema中的事件只記錄在本地server的performance_schema中照宝,其下的這些表中數(shù)據(jù)發(fā)生變化時(shí)不會(huì)被寫(xiě)入binlog中蛇受,也不會(huì)通過(guò)復(fù)制機(jī)制被復(fù)制到其他server中。
? 5厕鹃、 當(dāng)前活躍事件兢仰、歷史事件和事件摘要相關(guān)的表中記錄的信息。能提供某個(gè)事件的執(zhí)行次數(shù)剂碴、使用時(shí)長(zhǎng)把将。進(jìn)而可用于分析某個(gè)特定線程、特定對(duì)象(如mutex或file)相關(guān)聯(lián)的活動(dòng)忆矛。
? 6秸弛、PERFORMANCE_SCHEMA存儲(chǔ)引擎使用server源代碼中的“檢測(cè)點(diǎn)”來(lái)實(shí)現(xiàn)事件數(shù)據(jù)的收集。對(duì)于performance_schema實(shí)現(xiàn)機(jī)制本身的代碼沒(méi)有相關(guān)的單獨(dú)線程來(lái)檢測(cè)洪碳,這與其他功能(如復(fù)制或事件計(jì)劃程序)不同
? 7递览、收集的事件數(shù)據(jù)存儲(chǔ)在performance_schema數(shù)據(jù)庫(kù)的表中。這些表可以使用SELECT語(yǔ)句查詢瞳腌,也可以使用SQL語(yǔ)句更新performance_schema數(shù)據(jù)庫(kù)中的表記錄(如動(dòng)態(tài)修改performance_schema的setup_*開(kāi)頭的幾個(gè)配置表绞铃,但要注意:配置表的更改會(huì)立即生效,這會(huì)影響數(shù)據(jù)收集)
? 8嫂侍、performance_schema的表中的數(shù)據(jù)不會(huì)持久化存儲(chǔ)在磁盤(pán)中儿捧,而是保存在內(nèi)存中,一旦服務(wù)器重啟挑宠,這些數(shù)據(jù)會(huì)丟失(包括配置表在內(nèi)的整個(gè)performance_schema下的所有數(shù)據(jù))
? 9菲盾、MySQL支持的所有平臺(tái)中事件監(jiān)控功能都可用,但不同平臺(tái)中用于統(tǒng)計(jì)事件時(shí)間開(kāi)銷的計(jì)時(shí)器類型可能會(huì)有所差異各淀。

1懒鉴、performance schema入門(mén)

? 在mysql的5.7版本中,性能模式是默認(rèn)開(kāi)啟的,如果想要顯式的關(guān)閉的話需要修改配置文件临谱,不能直接進(jìn)行修改璃俗,會(huì)報(bào)錯(cuò)Variable 'performance_schema' is a read only variable。

--查看performance_schema的屬性
mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

--在配置文件中修改performance_schema的屬性值悉默,on表示開(kāi)啟城豁,off表示關(guān)閉
[mysqld]
performance_schema=ON

--切換數(shù)據(jù)庫(kù)
use performance_schema;

--查看當(dāng)前數(shù)據(jù)庫(kù)下的所有表,會(huì)看到有很多表存儲(chǔ)著相關(guān)的信息
show tables;

--可以通過(guò)show create table tablename來(lái)查看創(chuàng)建表的時(shí)候的表結(jié)構(gòu)
mysql> show create table setup_consumers;
+-----------------+---------------------------------
| Table           | Create Table                    
+-----------------+---------------------------------
| setup_consumers | CREATE TABLE `setup_consumers` (
  `NAME` varchar(64) NOT NULL,                      
  `ENABLED` enum('YES','NO') NOT NULL               
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 |  
+-----------------+---------------------------------
1 row in set (0.00 sec)                             

? 想要搞明白后續(xù)的內(nèi)容,同學(xué)們需要理解兩個(gè)基本概念:

? instruments: 生產(chǎn)者抄课,用于采集mysql中各種各樣的操作產(chǎn)生的事件信息唱星,對(duì)應(yīng)配置表中的配置項(xiàng)我們可以稱為監(jiān)控采集配置項(xiàng)。

? consumers:消費(fèi)者跟磨,對(duì)應(yīng)的消費(fèi)者表用于存儲(chǔ)來(lái)自instruments采集的數(shù)據(jù)间聊,對(duì)應(yīng)配置表中的配置項(xiàng)我們可以稱為消費(fèi)存儲(chǔ)配置項(xiàng)。

2吱晒、performance_schema表的分類

? performance_schema庫(kù)下的表可以按照監(jiān)視不同的緯度就行分組。

--語(yǔ)句事件記錄表沦童,這些表記錄了語(yǔ)句事件信息仑濒,當(dāng)前語(yǔ)句事件表events_statements_current、歷史語(yǔ)句事件表events_statements_history和長(zhǎng)語(yǔ)句歷史事件表events_statements_history_long偷遗、以及聚合后的摘要表summary墩瞳,其中,summary表還可以根據(jù)帳號(hào)(account)氏豌,主機(jī)(host)喉酌,程序(program),線程(thread)泵喘,用戶(user)和全局(global)再進(jìn)行細(xì)分)
show tables like '%statement%';

--等待事件記錄表泪电,與語(yǔ)句事件類型的相關(guān)記錄表類似:
show tables like '%wait%';

--階段事件記錄表,記錄語(yǔ)句執(zhí)行的階段事件的表
show tables like '%stage%';

--事務(wù)事件記錄表纪铺,記錄事務(wù)相關(guān)的事件的表
show tables like '%transaction%';

--監(jiān)控文件系統(tǒng)層調(diào)用的表
show tables like '%file%';

--監(jiān)視內(nèi)存使用的表
show tables like '%memory%';

--動(dòng)態(tài)對(duì)performance_schema進(jìn)行配置的配置表
show tables like '%setup%';

3相速、performance_schema的簡(jiǎn)單配置與使用

? 數(shù)據(jù)庫(kù)剛剛初始化并啟動(dòng)時(shí),并非所有instruments(事件采集項(xiàng)鲜锚,在采集項(xiàng)的配置表中每一項(xiàng)都有一個(gè)開(kāi)關(guān)字段突诬,或?yàn)閅ES,或?yàn)镹O)和consumers(與采集項(xiàng)類似芜繁,也有一個(gè)對(duì)應(yīng)的事件類型保存表配置項(xiàng)旺隙,為YES就表示對(duì)應(yīng)的表保存性能數(shù)據(jù),為NO就表示對(duì)應(yīng)的表不保存性能數(shù)據(jù))都啟用了骏令,所以默認(rèn)不會(huì)收集所有的事件蔬捷,可能你需要檢測(cè)的事件并沒(méi)有打開(kāi),需要進(jìn)行設(shè)置榔袋,可以使用如下兩個(gè)語(yǔ)句打開(kāi)對(duì)應(yīng)的instruments和consumers(行計(jì)數(shù)可能會(huì)因MySQL版本而異)抠刺。

--打開(kāi)等待事件的采集器配置項(xiàng)開(kāi)關(guān)塔淤,需要修改setup_instruments配置表中對(duì)應(yīng)的采集器配置項(xiàng)
UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES'where name like 'wait%';

--打開(kāi)等待事件的保存表配置開(kāi)關(guān),修改setup_consumers配置表中對(duì)應(yīng)的配置項(xiàng)
UPDATE setup_consumers SET ENABLED = 'YES'where name like '%wait%';

--當(dāng)配置完成之后可以查看當(dāng)前server正在做什么速妖,可以通過(guò)查詢events_waits_current表來(lái)得知高蜂,該表中每個(gè)線程只包含一行數(shù)據(jù),用于顯示每個(gè)線程的最新監(jiān)視事件
select * from events_waits_current\G
*************************** 1. row ***************************
            THREAD_ID: 11
             EVENT_ID: 570
         END_EVENT_ID: 570
           EVENT_NAME: wait/synch/mutex/innodb/buf_dblwr_mutex
               SOURCE: 
          TIMER_START: 4508505105239280
            TIMER_END: 4508505105270160
           TIMER_WAIT: 30880
                SPINS: NULL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: NULL
           INDEX_NAME: NULL
          OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 67918392
     NESTING_EVENT_ID: NULL
   NESTING_EVENT_TYPE: NULL
            OPERATION: lock
      NUMBER_OF_BYTES: NULL
                FLAGS: NULL
/*該信息表示線程id為11的線程正在等待buf_dblwr_mutex鎖罕容,等待事件為30880
屬性說(shuō)明:
    id:事件來(lái)自哪個(gè)線程备恤,事件編號(hào)是多少
    event_name:表示檢測(cè)到的具體的內(nèi)容
    source:表示這個(gè)檢測(cè)代碼在哪個(gè)源文件中以及行號(hào)
    timer_start:表示該事件的開(kāi)始時(shí)間
    timer_end:表示該事件的結(jié)束時(shí)間
    timer_wait:表示該事件總的花費(fèi)時(shí)間
注意:_current表中每個(gè)線程只保留一條記錄,一旦線程完成工作锦秒,該表中不會(huì)再記錄該線程的事件信息
*/

/*
_history表中記錄每個(gè)線程應(yīng)該執(zhí)行完成的事件信息露泊,但每個(gè)線程的事件信息只會(huì)記錄10條,再多就會(huì)被覆蓋旅择,*_history_long表中記錄所有線程的事件信息惭笑,但總記錄數(shù)量是10000,超過(guò)就會(huì)被覆蓋掉
*/
select thread_id,event_id,event_name,timer_wait from events_waits_history order by thread_id limit 21;

/*
summary表提供所有事件的匯總信息生真,該組中的表以不同的方式匯總事件數(shù)據(jù)(如:按用戶沉噩,按主機(jī),按線程等等)柱蟀。例如:要查看哪些instruments占用最多的時(shí)間川蒙,可以通過(guò)對(duì)events_waits_summary_global_by_event_name表的COUNT_STAR或SUM_TIMER_WAIT列進(jìn)行查詢(這兩列是對(duì)事件的記錄數(shù)執(zhí)行COUNT(*)、事件記錄的TIMER_WAIT列執(zhí)行SUM(TIMER_WAIT)統(tǒng)計(jì)而來(lái))
*/
SELECT EVENT_NAME,COUNT_STAR FROM events_waits_summary_global_by_event_name  ORDER BY COUNT_STAR DESC LIMIT 10;

/*
instance表記錄了哪些類型的對(duì)象會(huì)被檢測(cè)长已。這些對(duì)象在被server使用時(shí)畜眨,在該表中將會(huì)產(chǎn)生一條事件記錄,例如术瓮,file_instances表列出了文件I/O操作及其關(guān)聯(lián)文件名
*/
select * from file_instances limit 20; 

4康聂、常用配置項(xiàng)的參數(shù)說(shuō)明

1、啟動(dòng)選項(xiàng)

performance_schema_consumer_events_statements_current=TRUE
是否在mysql server啟動(dòng)時(shí)就開(kāi)啟events_statements_current表的記錄功能(該表記錄當(dāng)前的語(yǔ)句事件信息)胞四,啟動(dòng)之后也可以在setup_consumers表中使用UPDATE語(yǔ)句進(jìn)行動(dòng)態(tài)更新setup_consumers配置表中的events_statements_current配置項(xiàng)早抠,默認(rèn)值為T(mén)RUE

performance_schema_consumer_events_statements_history=TRUE
與performance_schema_consumer_events_statements_current選項(xiàng)類似,但該選項(xiàng)是用于配置是否記錄語(yǔ)句事件短歷史信息撬讽,默認(rèn)為T(mén)RUE

performance_schema_consumer_events_stages_history_long=FALSE
與performance_schema_consumer_events_statements_current選項(xiàng)類似蕊连,但該選項(xiàng)是用于配置是否記錄語(yǔ)句事件長(zhǎng)歷史信息,默認(rèn)為FALSE

除了statement(語(yǔ)句)事件之外游昼,還支持:wait(等待)事件甘苍、state(階段)事件、transaction(事務(wù))事件烘豌,他們與statement事件一樣都有三個(gè)啟動(dòng)項(xiàng)分別進(jìn)行配置载庭,但這些等待事件默認(rèn)未啟用,如果需要在MySQL Server啟動(dòng)時(shí)一同啟動(dòng),則通常需要寫(xiě)進(jìn)my.cnf配置文件中
performance_schema_consumer_global_instrumentation=TRUE
是否在MySQL Server啟動(dòng)時(shí)就開(kāi)啟全局表(如:mutex_instances囚聚、rwlock_instances靖榕、cond_instances、file_instances顽铸、users茁计、hostsaccounts、socket_summary_by_event_name谓松、file_summary_by_instance等大部分的全局對(duì)象計(jì)數(shù)統(tǒng)計(jì)和事件匯總統(tǒng)計(jì)信息表 )的記錄功能星压,啟動(dòng)之后也可以在setup_consumers表中使用UPDATE語(yǔ)句進(jìn)行動(dòng)態(tài)更新全局配置項(xiàng)
默認(rèn)值為T(mén)RUE

performance_schema_consumer_statements_digest=TRUE
是否在MySQL Server啟動(dòng)時(shí)就開(kāi)啟events_statements_summary_by_digest 表的記錄功能,啟動(dòng)之后也可以在setup_consumers表中使用UPDATE語(yǔ)句進(jìn)行動(dòng)態(tài)更新digest配置項(xiàng)
默認(rèn)值為T(mén)RUE

performance_schema_consumer_thread_instrumentation=TRUE
是否在MySQL Server啟動(dòng)時(shí)就開(kāi)啟

events_xxx_summary_by_yyy_by_event_name表的記錄功能鬼譬,啟動(dòng)之后也可以在setup_consumers表中使用UPDATE語(yǔ)句進(jìn)行動(dòng)態(tài)更新線程配置項(xiàng)
默認(rèn)值為T(mén)RUE

performance_schema_instrument[=name]
是否在MySQL Server啟動(dòng)時(shí)就啟用某些采集器娜膘,由于instruments配置項(xiàng)多達(dá)數(shù)千個(gè),所以該配置項(xiàng)支持key-value模式优质,還支持%號(hào)進(jìn)行通配等竣贪,如下:

# [=name]可以指定為具體的Instruments名稱(但是這樣如果有多個(gè)需要指定的時(shí)候,就需要使用該選項(xiàng)多次)巩螃,也可以使用通配符演怎,可以指定instruments相同的前綴+通配符,也可以使用%代表所有的instruments

## 指定開(kāi)啟單個(gè)instruments

--performance-schema-instrument= 'instrument_name=value'

## 使用通配符指定開(kāi)啟多個(gè)instruments

--performance-schema-instrument= 'wait/synch/cond/%=COUNTED'

## 開(kāi)關(guān)所有的instruments

--performance-schema-instrument= '%=ON'

--performance-schema-instrument= '%=OFF'

注意牺六,這些啟動(dòng)選項(xiàng)要生效的前提是颤枪,需要設(shè)置performance_schema=ON汗捡。另外淑际,這些啟動(dòng)選項(xiàng)雖然無(wú)法使用show variables語(yǔ)句查看,但我們可以通過(guò)setup_instruments和setup_consumers表查詢這些選項(xiàng)指定的值扇住。

2春缕、系統(tǒng)變量

show variables like '%performance_schema%';
--重要的屬性解釋
performance_schema=ON
/*
控制performance_schema功能的開(kāi)關(guān),要使用MySQL的performance_schema艘蹋,需要在mysqld啟動(dòng)時(shí)啟用锄贼,以啟用事件收集功能
該參數(shù)在5.7.x之前支持performance_schema的版本中默認(rèn)關(guān)閉,5.7.x版本開(kāi)始默認(rèn)開(kāi)啟
注意:如果mysqld在初始化performance_schema時(shí)發(fā)現(xiàn)無(wú)法分配任何相關(guān)的內(nèi)部緩沖區(qū)女阀,則performance_schema將自動(dòng)禁用宅荤,并將performance_schema設(shè)置為OFF
*/

performance_schema_digests_size=10000
/*
控制events_statements_summary_by_digest表中的最大行數(shù)。如果產(chǎn)生的語(yǔ)句摘要信息超過(guò)此最大值浸策,便無(wú)法繼續(xù)存入該表冯键,此時(shí)performance_schema會(huì)增加狀態(tài)變量
*/
performance_schema_events_statements_history_long_size=10000
/*
控制events_statements_history_long表中的最大行數(shù),該參數(shù)控制所有會(huì)話在events_statements_history_long表中能夠存放的總事件記錄數(shù)庸汗,超過(guò)這個(gè)限制之后惫确,最早的記錄將被覆蓋
全局變量,只讀變量,整型值改化,5.6.3版本引入 * 5.6.x版本中掩蛤,5.6.5及其之前的版本默認(rèn)為10000,5.6.6及其之后的版本默認(rèn)值為-1陈肛,通常情況下揍鸟,自動(dòng)計(jì)算的值都是10000 * 5.7.x版本中,默認(rèn)值為-1燥爷,通常情況下蜈亩,自動(dòng)計(jì)算的值都是10000
*/
performance_schema_events_statements_history_size=10
/*
控制events_statements_history表中單個(gè)線程(會(huì)話)的最大行數(shù),該參數(shù)控制單個(gè)會(huì)話在events_statements_history表中能夠存放的事件記錄數(shù)前翎,超過(guò)這個(gè)限制之后稚配,單個(gè)會(huì)話最早的記錄將被覆蓋
全局變量,只讀變量港华,整型值道川,5.6.3版本引入 * 5.6.x版本中,5.6.5及其之前的版本默認(rèn)為10立宜,5.6.6及其之后的版本默認(rèn)值為-1冒萄,通常情況下,自動(dòng)計(jì)算的值都是10 * 5.7.x版本中橙数,默認(rèn)值為-1尊流,通常情況下,自動(dòng)計(jì)算的值都是10
除了statement(語(yǔ)句)事件之外灯帮,wait(等待)事件崖技、state(階段)事件、transaction(事務(wù))事件钟哥,他們與statement事件一樣都有三個(gè)參數(shù)分別進(jìn)行存儲(chǔ)限制配置迎献,有興趣的同學(xué)自行研究,這里不再贅述
*/
performance_schema_max_digest_length=1024
/*
用于控制標(biāo)準(zhǔn)化形式的SQL語(yǔ)句文本在存入performance_schema時(shí)的限制長(zhǎng)度腻贰,該變量與max_digest_length變量相關(guān)(max_digest_length變量含義請(qǐng)自行查閱相關(guān)資料)
全局變量吁恍,只讀變量,默認(rèn)值1024字節(jié)播演,整型值冀瓦,取值范圍0~1048576
*/
performance_schema_max_sql_text_length=1024
/*
控制存入events_statements_current,events_statements_history和events_statements_history_long語(yǔ)句事件表中的SQL_TEXT列的最大SQL長(zhǎng)度字節(jié)數(shù)写烤。 超出系統(tǒng)變量performance_schema_max_sql_text_length的部分將被丟棄翼闽,不會(huì)記錄,一般情況下不需要調(diào)整該參數(shù)顶霞,除非被截?cái)嗟牟糠峙c其他SQL比起來(lái)有很大差異
全局變量肄程,只讀變量锣吼,整型值,默認(rèn)值為1024字節(jié)蓝厌,取值范圍為0~1048576玄叠,5.7.6版本引入
降低系統(tǒng)變量performance_schema_max_sql_text_length值可以減少內(nèi)存使用,但如果匯總的SQL中拓提,被截?cái)嗖糠钟休^大差異读恃,會(huì)導(dǎo)致沒(méi)有辦法再對(duì)這些有較大差異的SQL進(jìn)行區(qū)分。 增加該系統(tǒng)變量值會(huì)增加內(nèi)存使用代态,但對(duì)于匯總SQL來(lái)講可以更精準(zhǔn)地區(qū)分不同的部分寺惫。
*/

5、重要配置表的相關(guān)說(shuō)明

? 配置表之間存在相互關(guān)聯(lián)關(guān)系蹦疑,按照配置影響的先后順序西雀,可添加為

/*
performance_timers表中記錄了server中有哪些可用的事件計(jì)時(shí)器
字段解釋:
    timer_name:表示可用計(jì)時(shí)器名稱,CYCLE是基于CPU周期計(jì)數(shù)器的定時(shí)器
    timer_frequency:表示每秒鐘對(duì)應(yīng)的計(jì)時(shí)器單位的數(shù)量,CYCLE計(jì)時(shí)器的換算值與CPU的頻率相關(guān)歉摧、
    timer_resolution:計(jì)時(shí)器精度值艇肴,表示在每個(gè)計(jì)時(shí)器被調(diào)用時(shí)額外增加的值
    timer_overhead:表示在使用定時(shí)器獲取事件時(shí)開(kāi)銷的最小周期值
*/
select * from performance_timers;

/*
setup_timers表中記錄當(dāng)前使用的事件計(jì)時(shí)器信息
字段解釋:
    name:計(jì)時(shí)器類型,對(duì)應(yīng)某個(gè)事件類別
    timer_name:計(jì)時(shí)器類型名稱
*/
select * from setup_timers;

/*
setup_consumers表中列出了consumers可配置列表項(xiàng)
字段解釋:
    NAME:consumers配置名稱
    ENABLED:consumers是否啟用叁温,有效值為YES或NO再悼,此列可以使用UPDATE語(yǔ)句修改。
*/
select * from setup_consumers;

/*
setup_instruments 表列出了instruments 列表配置項(xiàng)膝但,即代表了哪些事件支持被收集:
字段解釋:
    NAME:instruments名稱冲九,instruments名稱可能具有多個(gè)部分并形成層次結(jié)構(gòu)
    ENABLED:instrumetns是否啟用,有效值為YES或NO跟束,此列可以使用UPDATE語(yǔ)句修改莺奸。如果設(shè)置為NO,則這個(gè)instruments不會(huì)被執(zhí)行泳炉,不會(huì)產(chǎn)生任何的事件信息
    TIMED:instruments是否收集時(shí)間信息憾筏,有效值為YES或NO嚎杨,此列可以使用UPDATE語(yǔ)句修改花鹅,如果設(shè)置為NO,則這個(gè)instruments不會(huì)收集時(shí)間信息
*/
SELECT * FROM setup_instruments;

/*
setup_actors表的初始內(nèi)容是匹配任何用戶和主機(jī)枫浙,因此對(duì)于所有前臺(tái)線程刨肃,默認(rèn)情況下啟用監(jiān)視和歷史事件收集功能
字段解釋:
    HOST:與grant語(yǔ)句類似的主機(jī)名,一個(gè)具體的字符串名字箩帚,或使用“%”表示“任何主機(jī)”
    USER:一個(gè)具體的字符串名稱真友,或使用“%”表示“任何用戶”
    ROLE:當(dāng)前未使用,MySQL 8.0中才啟用角色功能
    ENABLED:是否啟用與HOST紧帕,USER盔然,ROLE匹配的前臺(tái)線程的監(jiān)控功能桅打,有效值為:YES或NO
    HISTORY:是否啟用與HOST, USER愈案,ROLE匹配的前臺(tái)線程的歷史事件記錄功能挺尾,有效值為:YES或NO
*/
SELECT * FROM setup_actors;

/*
setup_objects表控制performance_schema是否監(jiān)視特定對(duì)象。默認(rèn)情況下站绪,此表的最大行數(shù)為100行遭铺。
字段解釋:
    OBJECT_TYPE:instruments類型,有效值為:“EVENT”(事件調(diào)度器事件)恢准、“FUNCTION”(存儲(chǔ)函數(shù))魂挂、“PROCEDURE”(存儲(chǔ)過(guò)程)、“TABLE”(基表)馁筐、“TRIGGER”(觸發(fā)器)涂召,TABLE對(duì)象類型的配置會(huì)影響表I/O事件(wait/io/table/sql/handler instrument)和表鎖事件(wait/lock/table/sql/handler instrument)的收集
    OBJECT_SCHEMA:某個(gè)監(jiān)視類型對(duì)象涵蓋的數(shù)據(jù)庫(kù)名稱,一個(gè)字符串名稱敏沉,或“%”(表示“任何數(shù)據(jù)庫(kù)”)
    OBJECT_NAME:某個(gè)監(jiān)視類型對(duì)象涵蓋的表名芹扭,一個(gè)字符串名稱,或“%”(表示“任何數(shù)據(jù)庫(kù)內(nèi)的對(duì)象”)
    ENABLED:是否開(kāi)啟對(duì)某個(gè)類型對(duì)象的監(jiān)視功能赦抖,有效值為:YES或NO舱卡。此列可以修改
    TIMED:是否開(kāi)啟對(duì)某個(gè)類型對(duì)象的時(shí)間收集功能,有效值為:YES或NO队萤,此列可以修改
*/
SELECT * FROM setup_objects;

/*
threads表對(duì)于每個(gè)server線程生成一行包含線程相關(guān)的信息轮锥,
字段解釋:
    THREAD_ID:線程的唯一標(biāo)識(shí)符(ID)
    NAME:與server中的線程檢測(cè)代碼相關(guān)聯(lián)的名稱(注意,這里不是instruments名稱)
    TYPE:線程類型要尔,有效值為:FOREGROUND舍杜、BACKGROUND。分別表示前臺(tái)線程和后臺(tái)線程
    PROCESSLIST_ID:對(duì)應(yīng)INFORMATION_SCHEMA.PROCESSLIST表中的ID列赵辕。
    PROCESSLIST_USER:與前臺(tái)線程相關(guān)聯(lián)的用戶名既绩,對(duì)于后臺(tái)線程為NULL。
    PROCESSLIST_HOST:與前臺(tái)線程關(guān)聯(lián)的客戶端的主機(jī)名还惠,對(duì)于后臺(tái)線程為NULL饲握。
    PROCESSLIST_DB:線程的默認(rèn)數(shù)據(jù)庫(kù),如果沒(méi)有蚕键,則為NULL救欧。
    PROCESSLIST_COMMAND:對(duì)于前臺(tái)線程,該值代表著當(dāng)前客戶端正在執(zhí)行的command類型锣光,如果是sleep則表示當(dāng)前會(huì)話處于空閑狀態(tài)
    PROCESSLIST_TIME:當(dāng)前線程已處于當(dāng)前線程狀態(tài)的持續(xù)時(shí)間(秒)
    PROCESSLIST_STATE:表示線程正在做什么事情笆怠。
    PROCESSLIST_INFO:線程正在執(zhí)行的語(yǔ)句,如果沒(méi)有執(zhí)行任何語(yǔ)句誊爹,則為NULL蹬刷。
    PARENT_THREAD_ID:如果這個(gè)線程是一個(gè)子線程(由另一個(gè)線程生成)瓢捉,那么該字段顯示其父線程ID
    ROLE:暫未使用
    INSTRUMENTED:線程執(zhí)行的事件是否被檢測(cè)。有效值:YES办成、NO 
    HISTORY:是否記錄線程的歷史事件泊柬。有效值:YES、NO * 
    THREAD_OS_ID:由操作系統(tǒng)層定義的線程或任務(wù)標(biāo)識(shí)符(ID):
*/
select * from threads

注意:在performance_schema庫(kù)中還包含了很多其他的庫(kù)和表诈火,能對(duì)數(shù)據(jù)庫(kù)的性能做完整的監(jiān)控兽赁,大家需要參考官網(wǎng)詳細(xì)了解。

6冷守、performance_schema實(shí)踐操作

? 基本了解了表的相關(guān)信息之后刀崖,可以通過(guò)這些表進(jìn)行實(shí)際的查詢操作來(lái)進(jìn)行實(shí)際的分析。

--1拍摇、哪類的SQL執(zhí)行最多亮钦?
SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--2、哪類SQL的平均響應(yīng)時(shí)間最多充活?
SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--3蜂莉、哪類SQL排序記錄數(shù)最多?
SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--4混卵、哪類SQL掃描記錄數(shù)最多映穗?
SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--5、哪類SQL使用臨時(shí)表最多幕随?
SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--6蚁滋、哪類SQL返回結(jié)果集最多?
SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--7赘淮、哪個(gè)表物理IO最多辕录?
SELECT file_name,event_name,SUM_NUMBER_OF_BYTES_READ,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC
--8、哪個(gè)表邏輯IO最多梢卸?
SELECT object_name,COUNT_READ,COUNT_WRITE,COUNT_FETCH,SUM_TIMER_WAIT FROM table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC
--9走诞、哪個(gè)索引訪問(wèn)最多?
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
--10蛤高、哪個(gè)索引從來(lái)沒(méi)有用過(guò)蚣旱?
SELECT OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;
--11、哪個(gè)等待事件消耗時(shí)間最多襟齿?
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
--12-1姻锁、剖析某條SQL的執(zhí)行情況枕赵,包括statement信息猜欺,stege信息,wait信息
SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';
--12-2拷窜、查看每個(gè)階段的時(shí)間消耗
SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553;
--12-3开皿、查看每個(gè)階段的鎖等待情況
SELECT event_id,event_name,source,timer_wait,object_name,index_name,operation,nesting_event_id FROM events_waits_history_longWHERE nesting_event_id = 1553;
最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末涧黄,一起剝皮案震驚了整個(gè)濱河市,隨后出現(xiàn)的幾起案子赋荆,更是在濱河造成了極大的恐慌笋妥,老刑警劉巖,帶你破解...
    沈念sama閱讀 211,639評(píng)論 6 492
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件窄潭,死亡現(xiàn)場(chǎng)離奇詭異春宣,居然都是意外死亡,警方通過(guò)查閱死者的電腦和手機(jī)嫉你,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,277評(píng)論 3 385
  • 文/潘曉璐 我一進(jìn)店門(mén)月帝,熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái),“玉大人幽污,你說(shuō)我怎么就攤上這事嚷辅。” “怎么了距误?”我有些...
    開(kāi)封第一講書(shū)人閱讀 157,221評(píng)論 0 348
  • 文/不壞的土叔 我叫張陵簸搞,是天一觀的道長(zhǎng)。 經(jīng)常有香客問(wèn)我准潭,道長(zhǎng)趁俊,這世上最難降的妖魔是什么? 我笑而不...
    開(kāi)封第一講書(shū)人閱讀 56,474評(píng)論 1 283
  • 正文 為了忘掉前任刑然,我火速辦了婚禮则酝,結(jié)果婚禮上涉枫,老公的妹妹穿的比我還像新娘峦朗。我一直安慰自己鹰溜,他們只是感情好盒使,可當(dāng)我...
    茶點(diǎn)故事閱讀 65,570評(píng)論 6 386
  • 文/花漫 我一把揭開(kāi)白布擒贸。 她就那樣靜靜地躺著故源,像睡著了一般魏烫。 火紅的嫁衣襯著肌膚如雪怖亭。 梳的紋絲不亂的頭發(fā)上沐鼠,一...
    開(kāi)封第一講書(shū)人閱讀 49,816評(píng)論 1 290
  • 那天挚瘟,我揣著相機(jī)與錄音,去河邊找鬼饲梭。 笑死乘盖,一個(gè)胖子當(dāng)著我的面吹牛,可吹牛的內(nèi)容都是我干的憔涉。 我是一名探鬼主播订框,決...
    沈念sama閱讀 38,957評(píng)論 3 408
  • 文/蒼蘭香墨 我猛地睜開(kāi)眼,長(zhǎng)吁一口氣:“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼兜叨!你這毒婦竟也來(lái)了穿扳?” 一聲冷哼從身側(cè)響起衩侥,我...
    開(kāi)封第一講書(shū)人閱讀 37,718評(píng)論 0 266
  • 序言:老撾萬(wàn)榮一對(duì)情侶失蹤,失蹤者是張志新(化名)和其女友劉穎矛物,沒(méi)想到半個(gè)月后茫死,有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 44,176評(píng)論 1 303
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡履羞,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,511評(píng)論 2 327
  • 正文 我和宋清朗相戀三年峦萎,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片忆首。...
    茶點(diǎn)故事閱讀 38,646評(píng)論 1 340
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡骨杂,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出雄卷,到底是詐尸還是另有隱情搓蚪,我是刑警寧澤,帶...
    沈念sama閱讀 34,322評(píng)論 4 330
  • 正文 年R本政府宣布丁鹉,位于F島的核電站妒潭,受9級(jí)特大地震影響,放射性物質(zhì)發(fā)生泄漏揣钦。R本人自食惡果不足惜雳灾,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,934評(píng)論 3 313
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望冯凹。 院中可真熱鬧谎亩,春花似錦、人聲如沸宇姚。這莊子的主人今日做“春日...
    開(kāi)封第一講書(shū)人閱讀 30,755評(píng)論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)浑劳。三九已至阱持,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間魔熏,已是汗流浹背衷咽。 一陣腳步聲響...
    開(kāi)封第一講書(shū)人閱讀 31,987評(píng)論 1 266
  • 我被黑心中介騙來(lái)泰國(guó)打工, 沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留蒜绽,地道東北人镶骗。 一個(gè)月前我還...
    沈念sama閱讀 46,358評(píng)論 2 360
  • 正文 我出身青樓,卻偏偏與公主長(zhǎng)得像躲雅,于是被迫代替她去往敵國(guó)和親鼎姊。 傳聞我的和親對(duì)象是個(gè)殘疾皇子,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 43,514評(píng)論 2 348

推薦閱讀更多精彩內(nèi)容