初相識｜performance_schema全方位介紹

很久之前，當我還在嘗試着系統地學習performance_schema的時候，通過在網上各種搜尋資料進行學習，但很遺憾，學習的效果并不是很明顯，很多标稱類似 "深入淺出performance_schema" 的文章，基本上都是那種動不動就貼源碼的風格，然後深入了之後卻出不來了。對系統學習performance_schema的作用甚微。 

現在，很高興的告訴大家，我們基于 MySQL 官方文檔加上我們的驗證，整理了一份可以系統學習 performance_schema 的資料分享給大家，為了友善大家閱讀，我們整理為了一個系列，一共7篇文章。下面，請跟随我們一起開始performance_schema系統的學習之旅吧。

本文首先，大緻介紹了什麼是performance_schema？它能做什麼？

然後，簡單介紹了如何快速上手使用performance_schema的方法；

最後，簡單介紹了performance_schema中由哪些表組成，這些表大緻的作用是什麼。

PS：本系列文章所使用的資料庫版本為 MySQL 官方 5.7.17版本

1、什麼是performance_schema

MySQL的performance schema 用于監控MySQL server在一個較低級别的運作過程中的資源消耗、資源等待等情況，它具有以下特點：

提供了一種在資料庫運作時實時檢查server的内部執行情況的方法。performance_schema 資料庫中的表使用performance_schema存儲引擎。該資料庫主要關注資料庫運作過程中的性能相關的資料，與information_schema不同，information_schema主要關注server運作過程中的中繼資料資訊

performance_schema通過監視server的事件來實作監視server内部運作情況， “事件”就是server内部活動中所做的任何事情以及對應的時間消耗，利用這些資訊來判斷server中的相關資源消耗在了哪裡？一般來說，事件可以是函數調用、作業系統的等待、SQL語句執行的階段（如sql語句執行過程中的parsing 或 sorting階段）或者整個SQL語句與SQL語句集合。事件的采集可以友善的提供server中的相關存儲引擎對磁盤檔案、表I/O、表鎖等資源的同步調用資訊。

performance_schema中的事件與寫入二進制日志中的事件（描述資料修改的events）、事件計劃排程程式（這是一種存儲程式）的事件不同。performance_schema中的事件記錄的是server執行某些活動對某些資源的消耗、耗時、這些活動執行的次數等情況。

performance_schema中的事件隻記錄在本地server的performance_schema中，其下的這些表中資料發生變化時不會被寫入binlog中，也不會通過複制機制被複制到其他server中。

目前活躍事件、曆史事件和事件摘要相關的表中記錄的資訊。能提供某個事件的執行次數、使用時長。進而可用于分析某個特定線程、特定對象（如mutex或file）相關聯的活動。

PERFORMANCE_SCHEMA存儲引擎使用server源代碼中的“檢測點”來實作事件資料的收集。對于performance_schema實作機制本身的代碼沒有相關的單獨線程來檢測，這與其他功能（如複制或事件計劃程式）不同

收集的事件資料存儲在performance_schema資料庫的表中。這些表可以使用SELECT語句查詢，也可以使用SQL語句更新performance_schema資料庫中的表記錄（如動态修改performance_schema的setup_*開頭的幾個配置表，但要注意：配置表的更改會立即生效，這會影響資料收集）

performance_schema的表中的資料不會持久化存儲在磁盤中，而是儲存在記憶體中，一旦伺服器重新開機，這些資料會丢失（包括配置表在内的整個performance_schema下的所有資料）

MySQL支援的所有平台中事件監控功能都可用，但不同平台中用于統計事件時間開銷的計時器類型可能會有所差異。

performance_schema實作機制遵循以下設計目标：

啟用performance_schema不會導緻server的行為發生變化。例如，它不會改變線程排程機制，不會導緻查詢執行計劃（如EXPLAIN）發生變化

啟用performance_schema之後，server會持續不間斷地監測，開銷很小。不會導緻server不可用

在該實作機制中沒有增加新的關鍵字或語句，解析器不會變化

即使performance_schema的監測機制在内部對某事件執行監測失敗，也不會影響server正常運作

如果在開始收集事件資料時碰到有其他線程正在針對這些事件資訊進行查詢，那麼查詢會優先執行事件資料的收集，因為事件資料的收集是一個持續不斷的過程，而檢索(查詢)這些事件資料僅僅隻是在需要檢視的時候才進行檢索。也可能某些事件資料永遠都不會去檢索

需要很容易地添加新的instruments監測點

instruments(事件采集項)代碼版本化：如果instruments的代碼發生了變更，舊的instruments代碼還可以繼續工作。

注意：MySQL sys schema是一組對象（包括相關的視圖、存儲過程和函數），可以友善地通路performance_schema收集的資料。同時檢索的資料可讀性也更高(例如：performance_schema中的時間機關是皮秒，經過sys schema查詢時會轉換為可讀的us,ms,s,min,hour,day等機關)，sys schem在5.7.x版本預設安裝

2、performance_schema使用快速入門

現在，是否覺得上面的介紹内容太過枯燥呢？如果你這麼想，那就對了，我當初學習的時候也是這麼想的。但現在，對于什麼是performance_schema這個問題上，比起更早之前更清晰了呢？如果你還沒有打算要放棄閱讀本文的話，那麼，請跟随我們開始進入到"邊走邊唱"環節吧！

2.1檢查目前資料庫版本是否支援

performance_schema被視為存儲引擎。如果該引擎可用，則應該在INFORMATION_SCHEMA.ENGINES表或SHOW ENGINES語句的輸出中都可以看到它的SUPPORT值為YES，如下：

使用 INFORMATION_SCHEMA.ENGINES表來查詢你的資料庫執行個體是否支援INFORMATION_SCHEMA引擎

qogir_env@localhost : performance_schema 02:41:41> SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE ='PERFORMANCE_SCHEMA';

+--------------------+---------+--------------------+--------------+------+------------+

| ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

1 row in set (0.00 sec)

使用show指令來查詢你的資料庫執行個體是否支援INFORMATION_SCHEMA引擎

qogir_env@localhost : performance_schema 02:41:54> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| Engine | Support | Comment

| Transactions | XA | Savepoints |

......

| PERFORMANCE_SCHEMA | YES | Performance Schema

| NO | NO | NO |

9 rows in set (0.00 sec)

當我們看到PERFORMANCE_SCHEMA 對應的Support 字段輸出為YES時就表示我們目前的資料庫版本是支援performance_schema的。但知道我們的執行個體支援performance_schema引擎就可以使用了嗎？NO，很遺憾，performance_schema在5.6及其之前的版本中，預設沒有啟用，從5.7及其之後的版本才修改為預設啟用。現在，我們來看看如何設定performance_schema預設啟用吧！

2.2. 啟用performance_schema

從上文中我們已經知道，performance_schema在5.7.x及其以上版本中預設啟用（5.6.x及其以下版本預設關閉），如果要顯式啟用或關閉時，我們需要使用參數performance_schema=ON|OFF設定，并在my.cnf中進行配置：

[mysqld]

performance_schema = ON # 注意：該參數為隻讀參數，需要在執行個體啟動之前設定才生效

mysqld啟動之後，通過如下語句檢視performance_schema是否啟用生效（值為ON表示performance_schema已初始化成功且可以使用了。如果值為OFF表示在啟用performance_schema時發生某些錯誤。可以檢視錯誤日志進行排查）：

qogir_env@localhost : performance_schema 03:13:10> SHOW VARIABLES LIKE 'performance_schema';

+--------------------+-------+

| Variable_name | Value |

| performance_schema | ON |

現在，你可以在performance_schema下使用show tables語句或者通過查詢 INFORMATION_SCHEMA.TABLES表中performance_schema引擎相關的中繼資料來了解在performance_schema下存在着哪些表： 

通過從INFORMATION_SCHEMA.tables表查詢有哪些performance_schema引擎的表：

qogir_env@localhost : performance_schema 03:13:22> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES \

WHERE TABLE_SCHEMA ='performance_schema' and engine='performance_schema';

+------------------------------------------------------+

| TABLE_NAME |

| accounts |

| cond_instances |

| users |

| variables_by_thread |

87 rows in set (0.00 sec)

直接在performance_schema庫下使用show tables語句來檢視有哪些performance_schema引擎表：

qogir_env@localhost : performance_schema 03:20:43> use performance_schema

Database changed

qogir_env@localhost : performance_schema 03:21:06> show tables from performance_schema;

| Tables_in_performance_schema |

現在，我們知道了在 MySQL 5.7.17 版本中，performance_schema 下一共有87張表，那麼，這87帳表都是存放什麼資料的呢？我們如何使用他們來查詢我們想要檢視的資料呢？先别着急，我們先來看看這些表是如何分類的。

2.3. performance_schema表的分類

performance_schema庫下的表可以按照監視不同的緯度進行了分組，例如：或按照不同資料庫對象進行分組，或按照不同的事件類型進行分組，或在按照事件類型分組之後，再進一步按照帳号、主機、程式、線程、使用者等，如下：

按照事件類型分組記錄性能事件資料的表 

語句事件記錄表，這些表記錄了語句事件資訊，目前語句事件表events_statements_current、曆史語句事件表events_statements_history和長語句曆史事件表events_statements_history_long、以及聚合後的摘要表summary，其中，summary表還可以根據帳号(account)，主機(host)，程式(program)，線程(thread)，使用者(user)和全局(global)再進行細分)

qogir_env@localhost : performance_schema 03:51:36> show tables like 'events_statement%';

+----------------------------------------------------+

| Tables_in_performance_schema (%statement%) |

| events_statements_current |

| events_statements_history |

| events_statements_history_long |

| events_statements_summary_by_account_by_event_name |

| events_statements_summary_by_digest |

| events_statements_summary_by_host_by_event_name |

| events_statements_summary_by_program |

| events_statements_summary_by_thread_by_event_name |

| events_statements_summary_by_user_by_event_name |

| events_statements_summary_global_by_event_name |

11 rows in set (0.00 sec)

等待事件記錄表，與語句事件類型的相關記錄表類似：

qogir_env@localhost : performance_schema 03:53:51> show tables like 'events_wait%';

+-----------------------------------------------+

| Tables_in_performance_schema (%wait%) |

| events_waits_current |

| events_waits_history |

| events_waits_history_long |

| events_waits_summary_by_account_by_event_name |

| events_waits_summary_by_host_by_event_name |

| events_waits_summary_by_instance |

| events_waits_summary_by_thread_by_event_name |

| events_waits_summary_by_user_by_event_name |

| events_waits_summary_global_by_event_name |

12 rows in set (0.01 sec)

階段事件記錄表，記錄語句執行的階段事件的表，與語句事件類型的相關記錄表類似：

qogir_env@localhost : performance_schema 03:55:07> show tables like 'events_stage%';

+------------------------------------------------+

| Tables_in_performance_schema (%stage%) |

| events_stages_current |

| events_stages_history |

| events_stages_history_long |

| events_stages_summary_by_account_by_event_name |

| events_stages_summary_by_host_by_event_name |

| events_stages_summary_by_thread_by_event_name |

| events_stages_summary_by_user_by_event_name |

| events_stages_summary_global_by_event_name |

8 rows in set (0.00 sec)

事務事件記錄表，記錄事務相關的事件的表，與語句事件類型的相關記錄表類似：

qogir_env@localhost : performance_schema 03:55:30> show tables like 'events_transaction%';

| Tables_in_performance_schema (%transaction%) |

| events_transactions_current |

| events_transactions_history |

| events_transactions_history_long |

| events_transactions_summary_by_account_by_event_name |

| events_transactions_summary_by_host_by_event_name |

| events_transactions_summary_by_thread_by_event_name |

| events_transactions_summary_by_user_by_event_name |

| events_transactions_summary_global_by_event_name |

監視檔案系統層調用的表：

qogir_env@localhost : performance_schema 03:58:27> show tables like '%file%';

+---------------------------------------+

| Tables_in_performance_schema (%file%) |

| file_instances |

| file_summary_by_event_name |

| file_summary_by_instance |

3 rows in set (0.01 sec)

監視記憶體使用的表：

qogir_env@localhost : performance_schema 03:58:38> show tables like '%memory%';

+-----------------------------------------+

| Tables_in_performance_schema (%memory%) |

| memory_summary_by_account_by_event_name |

| memory_summary_by_host_by_event_name |

| memory_summary_by_thread_by_event_name |

| memory_summary_by_user_by_event_name |

| memory_summary_global_by_event_name |

5 rows in set (0.01 sec)

動态對performance_schema進行配置的配置表：

root@localhost : performance_schema 12:18:46> show tables like '%setup%';

+----------------------------------------+

| Tables_in_performance_schema (%setup%) |

| setup_actors |

| setup_consumers |

| setup_instruments |

| setup_objects |

| setup_timers |

5 rows in set (0.00 sec)

現在，我們已經大概知道了performance_schema中的主要表的分類，但，如何使用他們來為我們提供需要的性能事件資料呢？下面，我們介紹如何通過performance_schema下的配置表來配置與使用performance_schema。

2.4. performance_schema簡單配置與使用

資料庫剛剛初始化并啟動時，并非所有instruments(事件采集項，在采集項的配置表中每一項都有一個開關字段，或為YES，或為NO)和consumers(與采集項類似，也有一個對應的事件類型儲存表配置項，為YES就表示對應的表儲存性能資料，為NO就表示對應的表不儲存性能資料)都啟用了，是以預設不會收集所有的事件，可能你需要檢測的事件并沒有打開，需要進行設定，可以使用如下兩個語句打開對應的instruments和consumers（行計數可能會因MySQL版本而異），例如，我們以配置監測等待事件資料為例進行說明： 

打開等待事件的采集器配置項開關，需要修改setup_instruments 配置表中對應的采集器配置項

qogir_env@localhost : performance_schema 03:34:40> UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES' where name like 'wait%';;

Query OK, 0 rows affected (0.00 sec)

Rows matched: 323 Changed: 0 Warnings: 0

打開等待事件的儲存表配置開關，修改修改setup_consumers 配置表中對應的配置i向

qogir_env@localhost : performance_schema 04:23:40> UPDATE setup_consumers SET ENABLED = 'YES' where name like '%wait%';

Query OK, 3 rows affected (0.04 sec)

Rows matched: 3 Changed: 3 Warnings: 0

配置好之後，我們就可以檢視server目前正在做什麼，可以通過查詢events_waits_current表來得知，該表中每個線程隻包含一行資料，用于顯示每個線程的最新監視事件（正在做的事情）：

qogir_env@localhost : performance_schema 04:23:52> SELECT * FROM events_waits_current limit 1\G

*************************** 1. row ***************************

THREAD_ID: 4

EVENT_ID: 60

END_EVENT_ID: 60

EVENT_NAME: wait/synch/mutex/innodb/log_sys_mutex

SOURCE: log0log.cc:1572

TIMER_START: 1582395491787124480

TIMER_END: 1582395491787190144

TIMER_WAIT: 65664

SPINS: NULL

OBJECT_SCHEMA: NULL

OBJECT_NAME: NULL

INDEX_NAME: NULL

OBJECT_TYPE: NULL

OBJECT_INSTANCE_BEGIN: 955681576

NESTING_EVENT_ID: NULL

NESTING_EVENT_TYPE: NULL

OPERATION: lock

NUMBER_OF_BYTES: NULL

FLAGS: NULL

1 row in set (0.02 sec)

# 該事件資訊表示線程ID為4的線程正在等待innodb存儲引擎的log_sys_mutex鎖，這是innodb存儲引擎的一個互斥鎖，

等待時間為65664皮秒（*_ID清單示事件來自哪個線程、事件編号是多少；EVENT_NAME表示檢測到的具體的内容；SOURCE表示這個檢測代碼在哪個源檔案中以及行号；

計時器字段TIMER_START、TIMER_END、TIMER_WAIT分别表示該事件的開始時間、結束時間、以及總的花費時間，如果該事件正在運作而沒有結束，

那麼TIMER_END和TIMER_WAIT的值顯示為NULL。注：計時器統計的值是近似值，并不是完全精确）

_current表中每個線程隻保留一條記錄，且一旦線程完成工作，該表中不會再記錄該線程的事件資訊，_history表中記錄每個線程已經執行完成的事件資訊，但每個線程的隻事件資訊隻記錄10條，再多就會被覆寫掉，*_history_long表中記錄所有線程的事件資訊，但總記錄數量是10000行，超過會被覆寫掉，現在咱們檢視一下曆史表events_waits_history 中記錄了什麼：

qogir_env@localhost : performance_schema 06:14:08>

SELECT THREAD_ID,EVENT_ID,EVENT_NAME,TIMER_WAIT FROM events_waits_history ORDER BY THREAD_ID limit 21;

+-----------+----------+------------------------------------------+------------+

| THREAD_ID | EVENT_ID | EVENT_NAME | TIMER_WAIT |

| 4 | 341 | wait/synch/mutex/innodb/fil_system_mutex | 84816 |

| 4 | 342 | wait/synch/mutex/innodb/fil_system_mutex | 32832 |

| 4 | 343 | wait/io/file/innodb/innodb_log_file | 544126864 |

| 4 | 348 | wait/io/file/innodb/innodb_log_file | 693076224 |

| 4 | 349 | wait/synch/mutex/innodb/fil_system_mutex | 65664 |

| 4 | 350 | wait/synch/mutex/innodb/log_sys_mutex | 25536 |

| 13 | 2260 | wait/synch/mutex/innodb/buf_pool_mutex | 111264 |

| 13 | 2259 | wait/synch/mutex/innodb/fil_system_mutex | 8708688 |

| 13 | 2261 | wait/synch/mutex/innodb/flush_list_mutex | 122208 |

| 15 | 291 | wait/synch/mutex/innodb/buf_dblwr_mutex | 37392 |

21 rows in set (0.00 sec)

summary表提供所有事件的彙總資訊。該組中的表以不同的方式彙總事件資料（如：按使用者，按主機，按線程等等）。例如：要檢視哪些instruments占用最多的時間，可以通過對events_waits_summary_global_by_event_name表的COUNT_STAR或SUM_TIMER_WAIT列進行查詢（這兩列是對事件的記錄數執行COUNT（*）、事件記錄的TIMER_WAIT列執行SUM（TIMER_WAIT）統計而來），如下：

qogir_env@localhost : performance_schema 06:17:23> SELECT EVENT_NAME,COUNT_STAR FROM events_waits_summary_global_by_event_name \

ORDER BY COUNT_STAR DESC LIMIT 10;

| EVENT_NAME | COUNT_STAR |

+---------------------------------------------------+------------+

| wait/synch/mutex/mysys/THR_LOCK_malloc | 6419 |

| wait/io/file/sql/FRM | 452 |

| wait/synch/mutex/sql/LOCK_plugin | 337 |

| wait/synch/mutex/mysys/THR_LOCK_open | 187 |

| wait/synch/mutex/mysys/LOCK_alarm | 147 |

| wait/synch/mutex/sql/THD::LOCK_thd_data | 115 |

| wait/io/file/myisam/kfile | 102 |

| wait/synch/mutex/sql/LOCK_global_system_variables | 89 |

| wait/synch/mutex/mysys/THR_LOCK::mutex | 89 |

| wait/synch/mutex/sql/LOCK_open | 88 |

qogir_env@localhost : performance_schema 06:19:20> SELECT EVENT_NAME,SUM_TIMER_WAIT FROM events_waits_summary_global_by_event_name\

ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

+----------------------------------------+----------------+

| EVENT_NAME | SUM_TIMER_WAIT |

| wait/io/file/sql/MYSQL_LOG | 1599816582 |

| wait/synch/mutex/mysys/THR_LOCK_malloc | 1530083250 |

| wait/io/file/sql/binlog_index | 1385291934 |

| wait/io/file/sql/FRM | 1292823243 |

| wait/io/file/myisam/kfile | 411193611 |

| wait/io/file/myisam/dfile | 322401645 |

| wait/synch/mutex/mysys/LOCK_alarm | 145126935 |

| wait/io/file/sql/casetest | 104324715 |

| wait/synch/mutex/sql/LOCK_plugin | 86027823 |

| wait/io/file/sql/pid | 72591750 |

# 這些結果表明，THR_LOCK_malloc互斥事件是最熱的。注：THR_LOCK_malloc互斥事件僅在DEBUG版本中存在,GA版本不存在

instance表記錄了哪些類型的對象會被檢測。這些對象在被server使用時，在該表中将會産生一條事件記錄，例如，file_instances表列出了檔案I/O操作及其關聯檔案名：

qogir_env@localhost : performance_schema 06:27:26> SELECT * FROM file_instances limit 20;

+------------------------------------------------------+--------------------------------------+------------+

| FILE_NAME | EVENT_NAME | OPEN_COUNT |

| /home/mysql/program/share/english/errmsg.sys | wait/io/file/sql/ERRMSG

| 0 |

| /home/mysql/program/share/charsets/Index.xml | wait/io/file/mysys/charset

| /data/mysqldata1/innodb_ts/ibdata1 | wait/io/file/innodb/innodb_data_file | 3 |

| /data/mysqldata1/innodb_log/ib_logfile0 | wait/io/file/innodb/innodb_log_file | 2 |

| /data/mysqldata1/innodb_log/ib_logfile1 | wait/io/file/innodb/innodb_log_file | 2 |

| /data/mysqldata1/undo/undo001 | wait/io/file/innodb/innodb_data_file | 3 |

| /data/mysqldata1/undo/undo002 | wait/io/file/innodb/innodb_data_file | 3 |

| /data/mysqldata1/undo/undo003 | wait/io/file/innodb/innodb_data_file | 3 |

| /data/mysqldata1/undo/undo004 | wait/io/file/innodb/innodb_data_file | 3 |

| /data/mysqldata1/mydata/multi_master/test.ibd | wait/io/file/innodb/innodb_data_file | 1 |

| /data/mysqldata1/mydata/mysql/engine_cost.ibd | wait/io/file/innodb/innodb_data_file | 3 |

| /data/mysqldata1/mydata/mysql/gtid_executed.ibd | wait/io/file/innodb/innodb_data_file | 3 |

| /data/mysqldata1/mydata/mysql/help_category.ibd | wait/io/file/innodb/innodb_data_file | 3 |

| /data/mysqldata1/mydata/mysql/help_keyword.ibd | wait/io/file/innodb/innodb_data_file | 3 |

| /data/mysqldata1/mydata/mysql/help_relation.ibd | wait/io/file/innodb/innodb_data_file | 3 |

| /data/mysqldata1/mydata/mysql/help_topic.ibd | wait/io/file/innodb/innodb_data_file | 3 |

| /data/mysqldata1/mydata/mysql/innodb_index_stats.ibd | wait/io/file/innodb/innodb_data_file | 3 |

| /data/mysqldata1/mydata/mysql/innodb_table_stats.ibd | wait/io/file/innodb/innodb_data_file | 3 |

| /data/mysqldata1/mydata/mysql/plugin.ibd | wait/io/file/innodb/innodb_data_file | 3 |

| /data/mysqldata1/mydata/mysql/server_cost.ibd | wait/io/file/innodb/innodb_data_file | 3 |

20 rows in set (0.00 sec)

本文小結

本篇内容到這裡就接近尾聲了，相信很多人都認為，我們大多數時候并不會直接使用performance_schema來查詢性能資料，而是使用sys schema下的視圖代替，為什麼不直接學習sys schema呢？那你知道sys schema中的資料是從哪裡吐出來的嗎？performance_schema 中的資料實際上主要是從performance_schema、information_schema中擷取，是以要想玩轉sys schema，全面了解performance_schema必不可少。

另外，對于sys schema、informatiion_schema甚至是MySQL

SCHEMA，我們後續也會推出不同的系列文章分享給大家。

原文釋出時間為：2018-05-16

本文作者：羅小波

本文來自雲栖社群合作夥伴“

老葉茶館

”，了解相關資訊可以關注“

”。