【第20天】SQL進階-查詢優化- performance_schema系列二：常用配置詳解（SQL 小虛竹)

回城傳送–》《32天SQL築基》

文章目錄

• 零、前言
• 一、練習題目
• 二、SQL思路

• SQL進階-查詢優化- performance_schema系列二：常用配置詳解

• 啟動時配置

• 啟動選項配置
• system variables

• 運作時設定

• performance_timers表
• setup_timers表
• setup_consumers表
• setup_instruments表
• setup_actors表
• setup_objects表
• threads表

• 三、總結
• 四、參考

零、前言

今天是學習 SQL 打卡的第 20 天，每天我會提供一篇文章供群成員閱讀（ 不需要訂閱付錢 ）。

希望大家先自己思考，如果實在沒有想法，再看下面的解題思路，自己再實作一遍。在小虛竹JAVA社群 中對應的 【打卡貼】打卡，今天的任務就算完成了，養成每天學習打卡的好習慣。

虛竹哥會組織大家一起學習同一篇文章，是以有什麼問題都可以在群裡問，群裡的小夥伴可以迅速地幫到你，一個人可以走得很快，一群人可以走得很遠，有一起學習交流的戰友，是多麼幸運的事情。

我的學習政策很簡單，題海政策+ 費曼學習法。如果能把這些題都認認真真自己實作一遍，那意味着 SQL 已經築基成功了。後面的進階學習，可以繼續跟着我，一起走向架構師之路。

今天的學習内容是：SQL進階-查詢優化- performance_schema系列二：常用配置詳解

本文内容有2萬多字，内容非常多，建議閱讀方式：記住标題和小子產品的介紹 ，内容詳解可幫助了解，看不懂也沒事，後續章節的實戰中也可慢慢體會。

本文适用于收藏，需要查閱指定配置時，可快速找到配置的詳解。

一、練習題目

題目連結	難度
-	-

二、SQL思路

SQL進階-查詢優化- performance_schema系列二：常用配置詳解

今天會講解performance_schema配置方式以及各個配置表的作用。

啟動時配置

performance_schema中的配置是儲存在記憶體中的，是易丢失的。也就是說儲存在performance_schema配置表(本章後續内容會講到)中的配置項在MySQL執行個體停止時會全部丢失。是以，如果想要把配置項持久化，就需要在MySQL的配置檔案中使用啟動選項來持久化配置項，讓MySQL每次重新開機都自動加載配置項，而不需要每次重新開機都再重新配置。

啟動選項配置

performance_schema有哪些啟動選項呢？我們可以通過如下指令行指令進行檢視:

mysqld --verbose --help |grep performance-schema |grep -v '\-\-' |sed '1d' |sed '/[0-9]\+/d'      

這些啟動項介紹：

• performance_schema_consumer_events_statements_current=TRUE；

是否在mysql server啟動時就開啟events_statements_current表的記錄功能(該表記錄目前的語句事件資訊)，啟動之後也可以在setup_consumers表中使用UPDATE語句進行動态更新setup_consumers配置表中的events_statements_current配置項，預設值為TRUE

• performance_schema_consumer_events_statements_history=TRUE;

與performance_schema_consumer_events_statements_current選項類似，但該選項是用于配置是否記錄語句事件短曆史資訊，預設為TRUE

• performance_schema_consumer_events_stages_history_lnotallow=FALSE

與performance_schema_consumer_events_statements_current選項類似，但該選項是用于配置是否記錄語句事件長曆史資訊，預設為FALSE

• 除了statement(語句)事件之外，還支援：wait(等待)事件、state(階段)事件、transaction(事務)事件，他們與statement事件一樣都有三個啟動項分别進行配置，但這些等待事件預設未啟用，如果需要在MySQL Server啟動時一同啟動，則通常需要寫進my.cnf配置檔案中
• performance_schema_consumer_global_instrumentatinotallow=TRUE

是否在MySQL Server啟動時就開啟全局表（如：mutex_instances、rwlock_instances、cond_instances、file_instances、users、hostsaccounts、socket_summary_by_event_name、file_summary_by_instance等大部分的全局對象計數統計和事件彙總統計資訊表 ）的記錄功能，啟動之後也可以在setup_consumers表中使用UPDATE語句進行動态更新全局配置項

預設值為TRUE

• performance_schema_consumer_statements_digest=TRUE

是否在MySQL Server啟動時就開啟events_statements_summary_by_digest 表的記錄功能，啟動之後也可以在setup_consumers表中使用UPDATE語句進行動态更新digest配置項

預設值為TRUE

• performance_schema_consumer_thread_instrumentatinotallow=TRUE

是否在MySQL Server啟動時就開啟

events_xxx_summary_by_yyy_by_event_name表的記錄功能，啟動之後也可以在setup_consumers表中使用UPDATE語句進行動态更新線程配置項

預設值為TRUE

• performance_schema_instrument[=name]

是否在MySQL Server啟動時就啟用某些采集器，由于instruments配置項多達數千個，是以該配置項支援key-value模式，還支援%号進行通配等，如下:

# [=name]可以指定為具體的Instruments名稱（但是這樣如果有多個需要指定的時候，就需要使用該選項多次），也可以使用通配符，可以指定instruments相同的字首+通配符，也可以使用%代表所有的instruments
## 指定開啟單個instruments
--performance-schema-instrument='instrument_name=value'
## 使用通配符指定開啟多個instruments
--performance-schema-instrument='wait/synch/cond/%=COUNTED'
## 開關所有的instruments
--performance-schema-instrument='%=ON'
--performance-schema-instrument='%=OFF'      

注意，這些啟動選項要生效的前提是，需要設定performance_schema=ON。另外，這些啟動選項雖然無法使用show variables語句檢視，但我們可以通過setup_instruments和setup_consumers表查詢這些選項指定的值。

system variables

與performance_schema相關的system variables可以使用如下語句檢視，這些variables用于限定consumers表的存儲限制，它們都是隻讀變量，需要在MySQL啟動之前就設定好這些變量的值。

show variables like '%performance_schema%';      

下面，我們将對這些system variables(以下稱為變量)中幾個需要關注的進行簡單解釋(其中大部分變量是-1值，代表會自動調整，無需太多關注，另外，大于-1值的變量在大多數時候也夠用，如果無特殊需求，不建議調整，調整這些參數會增加記憶體使用量):

• performance_schema=ON

• 控制performance_schema功能的開關，要使用MySQL的performance_schema，需要在mysqld啟動時啟用，以啟用事件收集功能
• 該參數在5.7.x之前支援performance_schema的版本中預設關閉，5.7.x版本開始預設開啟
• 注意：如果mysqld在初始化performance_schema時發現無法配置設定任何相關的内部緩沖區，則performance_schema将自動禁用，并将performance_schema設定為OFF

• performance_schema_digests_size=10000

• 控制events_statements_summary_by_digest表中的最大行數。如果産生的語句摘要資訊超過此最大值，便無法繼續存入該表，此時performance_schema會增加狀态變量

• performance_schema_events_statements_history_long_size=10000

• 控制events_statements_history_long表中的最大行數，該參數控制所有會話在events_statements_history_long表中能夠存放的總事件記錄數，超過這個限制之後，最早的記錄将被覆寫
• 全局變量，隻讀變量，整型值，5.6.3版本引入

• 5.6.x版本中，5.6.5及其之前的版本預設為10000，5.6.6及其之後的版本預設值為-1，通常情況下，自動計算的值都是10000
• 5.7.x版本中，預設值為-1，通常情況下，自動計算的值都是10000

• performance_schema_events_statements_history_size=10

• 控制events_statements_history表中單個線程（會話）的最大行數，該參數控制單個會話在events_statements_history表中能夠存放的事件記錄數，超過這個限制之後，單個會話最早的記錄将被覆寫
• 全局變量，隻讀變量，整型值，5.6.3版本引入

• 5.6.x版本中，5.6.5及其之前的版本預設為10，5.6.6及其之後的版本預設值為-1，通常情況下，自動計算的值都是10
• 5.7.x版本中，預設值為-1，通常情況下，自動計算的值都是10

• 除了statement(語句)事件之外，wait(等待)事件、state(階段)事件、transaction(事務)事件，他們與statement事件一樣都有三個參數分别進行存儲限制配置
• performance_schema_max_digest_length=1024

• 用于控制标準化形式的SQL語句文本在存入performance_schema時的限制長度，該變量與max_digest_length變量相關(max_digest_length變量含義請自行查閱相關資料)
• 全局變量，隻讀變量，預設值1024位元組，整型值，取值範圍0~1048576，5.6.26和5.7.8版本中引入

• performance_schema_max_sql_text_length=1024

• 控制存入events_statements_current，events_statements_history和events_statements_history_long語句事件表中的SQL_TEXT列的最大SQL長度位元組數。 超出系統變量performance_schema_max_sql_text_length的部分将被丢棄，不會記錄，一般情況下不需要調整該參數，除非被截斷的部分與其他SQL比起來有很大差異
• 全局變量，隻讀變量，整型值，預設值為1024位元組，取值範圍為0~1048576，5.7.6版本引入
• 降低系統變量performance_schema_max_sql_text_length值可以減少記憶體使用，但如果彙總的SQL中，被截斷部分有較大差異，會導緻沒有辦法再對這些有較大差異的SQL進行區分。 增加該系統變量值會增加記憶體使用，但對于彙總SQL來講可以更精準地區分不同的部分。

運作時設定

在MySQL啟動之後，我們就無法使用啟動選項來開關相應的consumers和instruments了，此時，我們如何根據自己的需求來靈活地開關performance_schema中的采集資訊呢？(例如：預設配置下很多配置項并未開啟，我們可能需要即時去修改配置，再如：高并發場景，大量的線程連接配接到MySQL，執行各種各樣的SQL時産生大量的事件資訊，而我們隻想看某一個會話産生的事件資訊時，也可能需要即時去修改配置)，我們可以通過修改performance_schema下的幾張配置表中的配置項實作

這些配置表中的配置項之間存在着關聯關系，按照配置影響的先後順序，可整理為如下圖(該表僅代表個人了解)：

performance_timers表

performance_timers表中記錄了server中有哪些可用的事件計時器（注意：該表中的配置項不支援增删改，是隻讀的。有哪些計時器就表示目前的版本支援哪些計時器），setup_timers配置表中的配置項引用此表中的計時器。

每個計時器的精度和數量相關的特征值會有所不同，可以通過如下查詢語句檢視performance_timers表中記錄的計時器和相關的特征資訊：

use performance_schema;

SELECT * FROM performance_timers;      

performance_timers表中的字段介紹：

• TIMER_NAME：表示可用計時器名稱，CYCLE是指基于CPU（處理器）周期計數器的定時器。在setup_timers表中可以使用performance_timers表中列值不為null的計時器（如果performance_timers表中有某字段值為NULL，則表示該定時器可能不支援目前server所在平台）
• TIMER_FREQUENCY：表示每秒鐘對應的計時器機關的數量（即，相對于每秒時間換算為對應的計時器機關之後的數值，例如：每秒=1000毫秒=1000000微秒=1000000000納秒）。對于CYCLE計時器的換算值，通常與CPU的頻率相關。對于performance_timers表中檢視到的CYCLE計時器的TIMER_FREQUENCY列值 ，是根據2.4GHz處理器的系統上獲得的預設值（在2.4GHz處理器的系統上，CYCLE可能接近2400000000）。NANOSECOND 、MICROSECOND 、MILLISECOND 計時器是基于固定的1秒換算而來。對于TICK計時器，TIMER_FREQUENCY列值可能會因平台而異（例如，某些平台使用100個tick/秒，某些平台使用1000個tick/秒）
• TIMER_RESOLUTION：計時器精度值，表示在每個計時器被調用時額外增加的值（即使用該計時器時，計時器被調用一次，需要額外增加的值）。如果計時器的分辨率為10，則其計時器的時間值在計時器每次被調用時，相當于TIMER_FREQUENCY值+10
• TIMER_OVERHEAD：表示在使用定時器擷取事件時開銷的最小周期值（performance_schema在初始化期間調用計時器20次，選擇一個最小值作為此字段值），每個事件的時間開銷值是計時器顯示值的兩倍，因為在事件的開始和結束時都調用計時器。注意：計時器代碼僅用于支援計時事件，對于非計時類事件（如調用次數的統計事件），這種計時器統計開銷方法不适用
• PS：對于performance_timers表，不允許使用TRUNCATE TABLE語句

setup_timers表

setup_timers表中記錄目前使用的事件計時器資訊（注意：該表不支援增加和删除記錄，隻支援修改和查詢）。

可以通過UPDATE語句來更改setup_timers.TIMER_NAME列值，以給不同的事件類别選擇不同的計時器，setup_timers.TIMER_NAME列有效值來自performance_timers.TIMER_NAME列值。

對setup_timers表的修改會立即影響監控。正在執行的事件可能會使用修改之前的計時器作為開始時間，但可能會使用修改之後的新的計時器作為結束時間，為了避免計時器更改後可能産生時間資訊收集到不可預測的結果，請在修改之後使用TRUNCATE TABLE語句來重置performance_schema中相關表中的統計資訊。

use performance_schema;
SELECT * FROM setup_timers;      

setup_timers表中的字段介紹：

• NAME：計時器類型，對應着某個事件類别(事件類别詳見 3.3.4 節)
• TIMER_NAME：計時器類型名稱。此列可以修改，有效值參見performance_timers.TIMER_NAME列值
• PS：對于setup_timers表，不允許使用TRUNCATE TABLE語句

注：mysql 8 删除了Performance Schema setup_timers表。

setup_consumers表

setup_consumers表列出了consumers可配置清單項(注意：該表不支援增加和删除記錄，隻支援修改和查詢)，如下：

use performance_schema;
SELECT * FROM setup_consumers;      

對setup_consumers表的修改會立即影響監控，setup_consumers字段含義如下：

• NAME：consumers配置名稱
• ENABLED：consumers是否啟用，有效值為YES或NO，此列可以使用UPDATE語句修改。如果需要禁用consumers就設定為NO，設定為NO時，server不會維護這些consumers表的内容新增和删除，且也會關閉consumers對應的instruments（如果沒有instruments發現采集資料沒有任何consumers消費的話）
• PS：對于setup_consumers表，不允許使用TRUNCATE TABLE語句

setup_instruments表

setup_instruments 表列出了instruments 清單配置項，即代表了哪些事件支援被收集：

use performance_schema;
SELECT * FROM setup_instruments;      

1238張表，非常齊全。

setup_instruments表字段詳解如下：

• NAME：instruments名稱，instruments名稱可能具有多個部分并形成層次結構(詳見下文)。當instruments被執行時，産生的事件名稱就取自instruments的名稱，事件沒有真正的名稱，直接使用instruments來作為事件的名稱，可以将instruments與産生的事件進行關聯
• ENABLED：instrumetns是否啟用，有效值為YES或NO，此列可以使用UPDATE語句修改。如果設定為NO，則這個instruments不會被執行，不會産生任何的事件資訊
• TIMED：instruments是否收集時間資訊，有效值為YES或NO，此列可以使用UPDATE語句修改，如果設定為NO，則這個instruments不會收集時間資訊
• 注意：記憶體操作沒有定時器資訊

對于記憶體instruments，setup_instruments中的TIMED列将被忽略（使用update語句對這些記憶體instruments設定timed列為YES時可以執行成功，但是你會發現執行update之後select這些instruments的timed列還是NO）

• setup_instruments表不允許使用TRUNCATE TABLE語句

官方文檔中沒有找到每一個instruments具體的說明文檔，官方文檔中列出如下幾個原因：

• instruments是服務端代碼，是以代碼可能經常變動
• instruments總數量有數百種，全部列出不現實
• instruments會因為你安裝的版本不同而有所不同，每一個版本所支援的instruments可以通過查詢setup_instruments表擷取

一些可能常用的場景相關的設定 :

• metadata locks監控需要打開’wait/lock/metadata/sql/mdl’ instruments才能監控

開啟這個instruments之後在表performance_schema.metadata_locks表中可以查詢到MDL鎖資訊

• 表鎖監控需要打開’wait/io/table/sql/handler’ instruments

開啟這個instruments之後在表 performance_schema.table_handles中會記錄了目前打開了哪些表(執行flush tables強制關閉打開的表時，該表中的資訊會被清空)，哪些表已經被加了表鎖（某會話持有表鎖時，相關記錄行中的OWNER_THREAD_ID和OWNER_EVENT_ID列值會記錄相關的thread id和event id），表鎖被哪個會話持有（釋放表鎖時，相關記錄行中的OWNER_THREAD_ID和OWNER_EVENT_ID列值會被清零）

• 查詢語句top number監控

需要打開’statement/sql/select’ instruments，然後打開events_statements_xxx表，通過查詢performance_schema.events_statements_xxx表的SQL_TEXT字段可以看到原始的SQL語句，查詢TIMER_WAIT字段可以知道總的響應時間，LOCK_TIME字段可以知道加鎖時間（注意時間機關是皮秒，需要除以1000000000000才是機關秒）

setup_actors表

setup_actors用于配置是否為新的前台server線程（與用戶端連接配接相關聯的線程）啟用監視和曆史事件日志記錄。預設情況下，此表的最大行數為100。可以使用系統變量performance_schema_setup_actors_size在server啟動之前更改此表的最大配置行數:

• 對于每個新的前台server線程，perfromance_schema會比對該表中的User,Host列進行比對，如果比對到某個配置行，則繼續比對該行的ENABLED和HISTORY列值，ENABLED和HISTORY列值也會用于生成threads配置表中的行INSTRUMENTED和HISTORY列。如果使用者線程在建立時在該表中沒有比對到User,Host列，則該線程的INSTRUMENTED和HISTORY列将設定為NO，表示不對這個線程進行監控，不記錄該線程的曆史事件資訊。
• 對于背景線程（如IO線程，日志線程，主線程，purged線程等），沒有關聯的使用者， INSTRUMENTED和HISTORY列值預設為YES，并且背景線程在建立時，不會檢視setup_actors表的配置，因為該表隻能控制前台線程，背景線程也不具備使用者、主機屬性

setup_actors表的初始内容是比對任何使用者和主機，是以對于所有前台線程，預設情況下啟用監視和曆史事件收集功能，如下：

use performance_schema;
SELECT * FROM setup_actors;      

setup_actors表字段含義如下：

• HOST：與grant語句類似的主機名，一個具體的字元串名字，或使用“％”表示“任何主機”
• USER：一個具體的字元串名稱，或使用“％”表示“任何使用者”
• ROLE：MySQL 8.0中才啟用角色功能
• ENABLED：是否啟用與HOST，USER，ROLE比對的前台線程的監控功能，有效值為：YES或NO
• HISTORY：是否啟用與HOST， USER，ROLE比對的前台線程的曆史事件記錄功能，有效值為：YES或NO

PS：setup_actors表允許使用TRUNCATE TABLE語句清空表，或者DELETE語句删除指定行

對setup_actors表的修改僅影響修改之後新建立的前台線程，對于修改之前已經建立的前台線程沒有影響，如果要修改已經建立的前台線程的監控和曆史事件記錄功能，可以修改threads表行的INSTRUMENTED和HISTORY列值：

當一個前台線程初始化連接配接mysql server時，performance_schema會對表setup_actors執行查詢，在表中查找每個配置行，首先嘗試使用USER和HOST列（ROLE未使用）依次找出比對的配置行，然後再找出最佳比對行并讀取比對行的ENABLED和HISTORY列值，用于填充threads表中的ENABLED和HISTORY列值。

• 示例，假如setup_actors表中有如下HOST和USER值：

• USER =‘literal’ and HOST =‘literal’
• USER =‘literal’ and HOST =‘％’
• USER =‘％’ and HOST =‘literal’
• USER =‘％’ and HOST =‘％’

• 比對順序很重要，因為不同的比對行可能具有不同的USER和HOST值（mysql中對于使用者帳号是使用user@host進行區分的），根據比對行的ENABLED和HISTORY列值來決定對每個HOST，USER或ACCOUNT（USER和HOST組合，如：user@host）對應的線程在threads表中生成對應的比對行的ENABLED和HISTORY列值 ，以便決定是否啟用相應的instruments和曆史事件記錄，類似如下：

• 當在setup_actors表中的最佳比對行的ENABLED = YES時，threads表中對應線程的配置行中INSTRUMENTED列值将變為YES，HISTORY 列同理
• 當在setup_actors表中的最佳比對行的ENABLED = NO時，threads表中對應線程的配置行中INSTRUMENTED列值将變為NO，HISTORY 列同理
• 當在setup_actors表中找不到比對時，threads表中對應線程的配置行中INSTRUMENTED和HISTORY值值将變為NO
• setup_actors表配置行中的ENABLED和HISTORY列值可以互相獨立設定為YES或NO，互不影響，一個是是否啟用線程對應的instruments，一個是是否啟用線程相關的曆史事件記錄的consumers

• 預設情況下，所有新的前台線程啟用instruments和曆史事件收集，因為setup_actors表中的預設值是host=‘%’，user=‘%’,ENABLED=‘YES’,HISTORY='YES’的。如果要執行更精細的比對(例如僅對某些前台線程進行監視），那就必須要對該表中的預設值進行修改，如下：

# 首先使用UPDATE語句把預設配置行禁用
UPDATE setup_actors SET ENABLED = 'NO', HISTORY = 'NO' WHERE HOST = '%' AND USER = '%';
# 插入使用者joe@'localhost'對應ENABLED和HISTORY都為YES的配置行
INSERT INTO setup_actors (HOST,USER,ROLE,ENABLED,HISTORY) VALUES('localhost','joe','%','YES','YES');
# 插入使用者joe@'hosta.example.com'對應ENABLED=YES、HISTORY=NO的配置行
INSERT INTO setup_actors (HOST,USER,ROLE,ENABLED,HISTORY) VALUES('hosta.example.com','joe','%','YES','NO');
# 插入使用者sam@'%'對應ENABLED=NO、HISTORY=YES的配置行
INSERT INTO setup_actors (HOST,USER,ROLE,ENABLED,HISTORY) VALUES('%','sam','%','NO','YES');
# 此時，threads表中對應使用者的前台線程配置行中INSTRUMENTED和HISTORY列生效值如下
## 當joe從localhost連接配接到mysql server時，則連接配接符合第一個INSERT語句插入的配置行，threads表中對應配置行的INSTRUMENTED和HISTORY列值變為YES
## 當joe從hosta.example.com連接配接到mysql server時，則連接配接符合第二個INSERT語句插入的配置行，threads表中對應配置行的INSTRUMENTED列值為YES，HISTORY列值為NO
## 當joe從其他任意主機(%比對除了localhost和hosta.example.com之外的主機)連接配接到mysql server時，則連接配接符合第三個INSERT語句插入的配置行，threads表中對應配置行的INSTRUMENTED和HISTORY列值變為NO
## 當sam從任意主機(%比對)連接配接到mysql server時，則連接配接符合第三個INSERT語句插入的配置行，threads表中對應配置行的INSTRUMENTED列值變為NO，HISTORY列值為YES
## 除了joe和sam使用者之外，其他任何使用者從任意主機連接配接到mysql server時，比對到第一個UPDATE語句更新之後的預設配置行，threads表中對應配置行的INSTRUMENTED和HISTORY列值變為NO
## 如果把UPDATE語句改成DELETE，讓未明确指定的使用者在setup_actors表中找不到任何比對行，則threads表中對應配置行的INSTRUMENTED和HISTORY列值變為NO      

注意：對于背景線程，對setup_actors表的修改不生效，如果要幹預背景線程預設的設定，需要查詢threads表找到相應的線程，然後使用UPDATE語句直接修改threads表中的INSTRUMENTED和HISTORY列值。

setup_objects表

setup_objects表控制performance_schema是否監視特定對象。預設情況下，此表的最大行數為100行。要更改表行數大小，可以在server啟動之前修改系統變量performance_schema_setup_objects_size的值。

setup_objects表初始内容如下所示：

use performance_schema;
SELECT * FROM setup_objects;      

對setup_objects表的修改會立即影響對象監控

在setup_objects中列出的監控對象類型，在進行比對時，performance_schema基于OBJECT_SCHEMA和OBJECT_NAME列依次往後比對，如果沒有比對的對象則不會被監視

預設配置中開啟監視的對象不包含mysql，INFORMATION_SCHEMA和performance_schema資料庫中的所有表（從上面的資訊中可以看到這幾個庫的enabled和timed字段都為NO，注意：對于INFORMATION_SCHEMA資料庫，雖然該表中有一行配置，但是無論該表中如何設定，都不會監控該庫，在setup_objects表中information_schema.%的配置行僅作為一個預設值）

當performance_schema在setup_objects表中進行比對檢測時，會嘗試首先找到最具體（最精确）的比對項。例如，在比對db1.t1表時，它會從setup_objects表中先查找“db1”和“t1”的比對項，然後再查找“db1”和“％”，然後再查找“％”和“％”。比對的順序很重要，因為不同的比對行可能具有不同的ENABLED和TIMED列值

如果使用者對該表具有INSERT和DELETE權限，則可以對該表中的配置行進行删除和插入新的配置行。對于已經存在的配置行，如果使用者對該表具有UPDATE權限，則可以修改ENABLED和TIMED列，有效值為：YES和NO

setup_objects表列含義如下：

• OBJECT_TYPE：instruments類型，有效值為：“EVENT”（事件排程器事件）、“FUNCTION”（存儲函數）、“PROCEDURE”（存儲過程）、“TABLE”（基表）、“TRIGGER”（觸發器），TABLE對象類型的配置會影響表I/O事件（wait/io/table/sql/handler instrument）和表鎖事件（wait/lock/table/sql/handler instrument）的收集
• OBJECT_SCHEMA：某個監視類型對象涵蓋的資料庫名稱，一個字元串名稱，或“％”(表示“任何資料庫”)
• OBJECT_NAME：某個監視類型對象涵蓋的表名，一個字元串名稱，或“％”(表示“任何資料庫内的對象”)
• ENABLED：是否開啟對某個類型對象的監視功能，有效值為：YES或NO。此列可以修改
• TIMED：是否開啟對某個類型對象的時間收集功能，有效值為：YES或NO，此列可以修改
• PS：對于setup_objects表，允許使用TRUNCATE TABLE語句

setup_objects配置表中預設的配置規則是不啟用對mysql、INFORMATION_SCHEMA、performance_schema資料庫下的對象進行監視的（ENABLED和TIMED列值全都為NO）

performance_schema在setup_objects表中進行查詢比對時，如果發現某個OBJECT_TYPE列值有多行，則會嘗試着比對更多的配置行，如下（performance_schema按照如下順序進行檢查）：

• OBJECT_SCHEMA =‘literal’ and OBJECT_NAME =‘literal’
• OBJECT_SCHEMA =‘literal’ and OBJECT_NAME =‘％’
• OBJECT_SCHEMA =‘％’ and OBJECT_NAME =‘％’
• 例如，要比對表對象db1.t1，performance_schema在setup_objects表中先查找“OBJECT_SCHEMA = db1”和“OBJECT_NAME = t1”的比對項，然後查找“OBJECT_SCHEMA = db1”和“OBJECT_NAME =％”，然後查找“OBJECT_SCHEMA = ％”和“OBJECT_NAME = ％”。比對順序很重要，因為不同的比對行中的ENABLED和TIMED列可以有不同的值，最終會選擇一個最精确的比對項

對于表對象相關事件，instruments是否生效需要看setup_objects與setup_instruments兩個表中的配置内容相結合，以确定是否啟用instruments以及計時器功能（例如前面說的I/O事件：wait/io/table/sql/handler instrument和表鎖事件：wait/lock/table/sql/handler instrument，在setup_instruments配置表中也有明确的配置選項）：

• 隻有在Setup_instruments和setup_objects中的ENABLED列都為YES時，表的instruments才會生成事件資訊
• 隻有在Setup_instruments和setup_objects中的TIMED列都為YES時，表的instruments才會啟用計時器功能（收集時間資訊）
• 例如：要監視db1.t1、db1.t2、db2.%、db3.%這些表，setup_instruments和setup_objects兩個表中有如下配置項

# setup_instruments表
select * from setup_instruments where name like '%/table/%';      

# setup_objects表
+-------------+---------------+-------------+---------+-------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | ENABLED | TIMED |
+-------------+---------------+-------------+---------+-------+
| TABLE       | db1           | t1          | YES     | YES   |
| TABLE       | db1           | t2          | NO      | NO    |
| TABLE       | db2           | %           | YES     | YES   |
| TABLE       | db3           | %           | NO      | NO    |
| TABLE       | %             | %           | YES     | YES   |
+-------------+---------------+-------------+---------+-------+
# 以上兩個表中的配置項綜合之後，隻有db1.t1、db2.%、%.%的表對象的instruments會被啟用，db1.t2和db3.%不會啟用，因為這兩個對象在setup_objects配置表中ENABLED和TIMED字段值為NO      

對于存儲程式對象相關的事件，performance_schema隻需要從setup_objects表中讀取配置項的ENABLED和TIMED列值。因為存儲程式對象在setup_instruments表中沒有對應的配置項

如果持久性表和臨時表名稱相同，則在setup_objects表中進行比對時，針對這兩種類型的表的比對規則都同時生效（不會發生一個表啟用監控，另外一個表不啟用）

threads表

threads表對于每個server線程生成一行包含線程相關的資訊，例如：顯示是否啟用監視，是否啟用曆史事件記錄功能，如下:

select * from threads where TYPE='FOREGROUND' limit 2;      

當performance_schema初始化時，它根據當時存在的線程每個線程生成一行資訊記錄在threads表中。此後，每建立一個線程在該表中就會新增一行對應線程的記錄

新線程資訊的INSTRUMENTED和HISTORY列值由setup_actors表中的配置決定。

當某個線程結束時，會從threads表中删除對應行。對于與用戶端會話關聯的線程，當會話結束時會删除threads表中與用戶端會話關聯的線程配置資訊行。如果用戶端自動重新連接配接，則也相當于斷開一次（會删除斷開連接配接的配置行）再重新建立新的連接配接，兩次連接配接建立的PROCESSLIST_ID值不同。新線程初始INSTRUMENTED和HISTORY值可能與斷開之前的線程初始INSTRUMENTED和HISTORY值不同：setup_actors表在此期間可能已更改，并且如果一個線程在建立之後，後續再修改了setup_actors表中的INSTRUMENTED或HISTORY列值，那麼後續修改的值不會影響到threads表中已經建立好的線程的INSTRUMENTED或HISTORY列值

• PROCESSLIST_*開頭的列提供與INFORMATION_SCHEMA.PROCESSLIST表或SHOW PROCESSLIST語句類似的資訊。但threads表中與其他兩個資訊來源有所不同：
• 對threads表的通路不需要互斥體，對server性能影響最小。 而使用INFORMATION_SCHEMA.PROCESSLIST和SHOW PROCESSLIST查詢線程資訊的方式會損耗一定性能，因為他們需要互斥體
• threads表為每個線程提供附加資訊，例如：它是前台還是背景線程，以及與線程相關聯的server内部資訊
• threads表提供有關背景線程的資訊，而INFORMATION_SCHEMA.PROCESSLIST和SHOW PROCESSLIST不能提供
• 可以通過threads表中的INSTRUMENTED字段靈活地動态開關某個線程的監視功能、HISTORY字段靈活地動态開關某個線程的曆史事件日志記錄功能。要控制新的前台線程的初始INSTRUMENTED和HISTORY列值，通過setup_actors表的HOST、 USER對某個主機、使用者進行配置。要控制已建立線程的采集和曆史事件記錄功能，通過threads表的INSTRUMENTED和HISTORY列進行設定
• 對于INFORMATION_SCHEMA.PROCESSLIST和SHOW PROCESSLIST，需要有PROCESS權限，對于threads表隻要有SELECT權限就可以檢視所有使用者的線程資訊

threads表字段含義如下：

• THREAD_ID：線程的唯一辨別符（ID）
• NAME：與server中的線程檢測代碼相關聯的名稱(注意，這裡不是instruments名稱)。例如，thread/sql/one_connection對應于負責處理使用者連接配接的代碼中的線程函數名，thread/sql/main表示server的main（）函數名稱
• TYPE：線程類型，有效值為：FOREGROUND、BACKGROUND。分别表示前台線程和背景線程，如果是使用者建立的連接配接或者是複制線程建立的連接配接，則标記為前台線程（如：複制IO和SQL線程，worker線程，dump線程等），如果是server内部建立的線程（不能使用者幹預的線程），則标記為背景線程，如：innodb的背景IO線程等
• PROCESSLIST_ID：對應INFORMATION_SCHEMA.PROCESSLIST表中的ID列。該列值與show processlist語句、INFORMATION_SCHEMA.PROCESSLIST表、connection_id()函數傳回的線程ID值相等。另外，threads表中記錄了内部線程，而processlist表中沒有記錄内部線程，是以，對于内部線程，在threads表中的該字段顯示為NULL，是以在threads表中NULL值不唯一（可能有多個背景線程）
• PROCESSLIST_USER：與前台線程相關聯的使用者名，對于背景線程為NULL。
• PROCESSLIST_HOST：與前台線程關聯的用戶端的主機名，對于背景線程為NULL。與INFORMATION_SCHEMA PROCESSLIST表的HOST列或SHOW PROCESSLIST輸出的主機列不同，PROCESSLIST_HOST列不包括TCP/IP連接配接的端口号。要從performance_schema中擷取端口資訊，需要查詢socket_instances表（關于socket的instruments wait/io/socket/sql/*預設關閉）：
• PROCESSLIST_DB：線程的預設資料庫，如果沒有，則為NULL。

• PROCESSLIST_COMMAND：對于前台線程，該值代表着目前用戶端正在執行的command類型，如果是sleep則表示目前會話處于空閑狀态。有關線程command的詳細說明，參見連結：

  ​ https://dev.mysql.com/doc/refman/5.7/en/thread-information.html。對于背景線程不會執行這些command，是以此列值可能為NULL​​

• PROCESSLIST_TIME：目前線程已處于目前線程狀态的持續時間（秒）

• PROCESSLIST_STATE：表示線程正在做什麼事情。有關PROCESSLIST_STATE值的說明，詳見連結：

  ​ https://dev.mysql.com/doc/refman/5.7/en/thread-information.html。如果列值為NULL，則該線程可能處于空閑狀态或者是一個背景線程。大多數狀态值停留的時間非常短暫。如果一個線程在某個狀态下停留了非常長的時間，則表示可能有性能問題需要排查​​

• PROCESSLIST_INFO：線程正在執行的語句，如果沒有執行任何語句，則為NULL。該語句可能是發送到server的語句，也可能是某個其他語句執行時内部調用的語句。例如：如果CALL語句執行存儲程式，則在存儲程式中正在執行SELECT語句，那麼PROCESSLIST_INFO值将顯示SELECT語句
• PARENT_THREAD_ID：如果這個線程是一個子線程（由另一個線程生成），那麼該字段顯示其父線程ID
• ROLE：暫未使用
• INSTRUMENTED：

• 線程執行的事件是否被檢測。有效值：YES、NO
• 1)、對于前台線程，初始INSTRUMENTED值還需要看控制前台線程的setup_actors表中的INSTRUMENTED字段值。如果在setup_actors表中找到了對應的使用者名和主機行，則會用該表中的INSTRUMENTED字段生成theads表中的INSTRUMENTED字段值，setup_actors表中的USER和HOST字段值也會一并寫入到threads表的PROCESSLIST_USER和PROCESSLIST_HOST列。如果某個線程産生一個子線程，則子線程會再次與setup_actors表進行比對
• 2)、對于背景線程，INSTRUMENTED預設為YES。 初始值無需檢視setup_actors表，因為該表不控制背景線程，因為背景線程沒有關聯的使用者
• 3)、對于任何線程，其INSTRUMENTED值可以線上程的生命周期内更改
• 要監視線程産生的事件，如下條件需滿足：
• 1)、setup_consumers表中的thread_instrumentation consumers必須為YES
• 2)、threads.INSTRUMENTED列必須為YES
• 3)、setup_instruments表中線程相關的instruments配置行的ENABLED列必須為YES
• 4)、如果是前台線程，那麼setup_actors中對應主機和使用者的配置行中的INSTRUMENTED列必須為YES

• HISTORY：

• 是否記錄線程的曆史事件。有效值：YES、NO
• 1)、對于前台線程，初始HISTORY值還需要看控制前台線程的setup_actors表中的HISTORY字段值。如果在setup_actors表中找到了對應的使用者名和主機行，則會用該表中的HISTORY字段生成theads表中的HISTORY字段值，setup_actors表中的USER和HOST字段值也會一并寫入到threads表的PROCESSLIST_USER和PROCESSLIST_HOST列。如果某個線程産生一個子線程，則子線程會再次與setup_actors表進行比對
• 2)、對于背景線程，HISTORY預設為YES。初始值無需檢視setup_actors表，因為該表不控制背景線程，因為背景線程沒有關聯的使用者
• 3)、對于任何線程，其HISTORY值可以線上程的生命周期内更改
• 要記錄線程産生的曆史事件，如下條件需滿足：
• 1)、setup_consumers表中相關聯的consumers配置必須啟用，如：要記錄線程的等待事件曆史記錄，需要啟用events_waits_history和events_waits_history_long consumers
• 2)、threads.HISTORY列必須為YES
• 3)、setup_instruments表中相關聯的instruments配置必須啟用
• 4)、如果是前台線程，那麼setup_actors中對應主機和使用者的配置行中的HISTORY列必須為YES

• CONNECTION_TYPE：用于建立連接配接的協定，如果是背景線程則為NULL。有效值為：TCP/IP（不使用SSL建立的TCP/IP連接配接）、SSL/TLS（與SSL建立的TCP/IP連接配接）、Socket（Unix套接字檔案連接配接）、Named Pipe（Windows命名管道連接配接）、Shared Memory(Windows共享記憶體連接配接）
• THREAD_OS_ID：

• 由作業系統層定義的線程或任務辨別符（ID）：
• 1)、當一個MySQL線程與作業系統中與某個線程關聯時，那麼THREAD_OS_ID字段可以檢視到與這個mysql線程相關聯的作業系統線程ID
• 2)、當一個MySQL線程與作業系統線程不關聯時，THREAD_OS_ID列值為NULL。例如：使用者使用線程池插件時
• 對于Windows，THREAD_OS_ID對應于Process Explorer中可見的線程ID
• 對于Linux，THREAD_OS_ID對應于gettid（）函數擷取的值。例如：使用perf或ps -L指令或proc檔案系統（/proc/[pid]/task/[tid]）可以檢視此值。

• PS：threads表不允許使用TRUNCATE TABLE語句

關于線程類對象，前台線程與背景線程還有少許差别：

• 對于前台線程（由用戶端連接配接産生的連接配接，可以是使用者發起的連接配接，也可以是不同server之間發起的連接配接），當使用者或者其他server與某個server建立了一個連接配接之後（連接配接方式可能是socket或者TCP/IP），在threads表中就會記錄一條這個線程的配置資訊行，此時，threads表中該線程的配置行中的INSTRUMENTED和HISTORY列值的預設值是YES還是NO，還需要看與線程相關聯的使用者帳戶是否比對setup_actors表中的配置行(檢視某使用者在setup_actors表中配置行的ENABLED和HISTORY列配置為YES還是NO，threads表中與setup_actors表關聯使用者帳号的線程配置行中的ENABLED和HISTORY列值以setup_actors表中的值為準)
• 對于背景線程，不可能存在關聯的使用者，是以threads表中的 INSTRUMENTED和HISTORY線上程建立時的初始配置列值預設值為YES，不需要檢視setup_actors表

關閉與開啟所有背景線程的監控采集功能：

# 關閉所有背景線程的事件采集
update threads set INSTRUMENTED='NO' where TYPE='BACKGROUND';
# 開啟所有背景線程的事件采集
update threads set INSTRUMENTED='YES' where TYPE='BACKGROUND';      

關閉與開啟除了目前連接配接之外的所有線程的事件采集（不包括背景線程）

# 關閉除了目前連接配接之外的所有前台線程的事件采集
root@localhost : performance_schema 05:47:44> update threads set INSTRUMENTED='NO' where PROCESSLIST_ID!=connection_id();
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0
# 開啟除了目前連接配接之外的所有前台線程的事件采集
root@localhost : performance_schema 05:48:32> update threads set INSTRUMENTED='YES' where PROCESSLIST_ID!=connection_id();
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0
# 當然，如果要連背景線程一起操作，請帶上條件PROCESSLIST_ID is NULL
update ... where PROCESSLIST_ID!=connection_id() or PROCESSLIST_ID is NULL;      

三、總結

本文介紹了performance_schema的常用配置詳解，主要有兩個子產品：啟動時配置和運作時設定。

啟動選項配置可以通過如下指令行指令進行檢視:

mysqld --verbose --help |grep performance-schema |grep -v '\-\-' |sed '1d' |sed '/[0-9]\+/d'      

登入到資料庫中使用 show variables like ‘%performance_schema%’;語句檢視完整的system variables清單；

運作時設定主要介紹了七張表：

• performance_timers：表中記錄了server中有哪些可用的事件計時器；
• setup_timers：表中記錄目前使用的事件計時器資訊；
• setup_consumers：表列出了consumers可配置清單項；
• setup_instruments：表列出了instruments 清單配置項，即代表了哪些事件支援被收集；
• setup_actors：表用于配置是否為新的前台server線程（與用戶端連接配接相關聯的線程）啟用監視和曆史事件日志記錄；
• setup_objects：表控制performance_schema是否監視特定對象；
• threads：表對于每個server線程生成一行包含線程相關的資訊；

本文内容有2萬多字，内容非常多，建議閱讀方式：記住标題和小子產品的介紹 ，内容詳解可幫助了解，看不懂也沒事，後續章節的實戰中也可慢慢體會。

本文适用于收藏，需要查閱指定配置時，可快速找到配置的詳解。

四、參考