mysql 5.7中嶄新的 mysql sys schema

czxin788發表於2018-01-11
MySql
mysql 5.7中嶄新的 mysql sys schema
宣告：本文內容來自於《MySQL運維內參》，筆者讀後整理的筆記，僅供學習使用。

以下內容都是MySQL 5.7的新功能
一、 Performance Schema的改進
1、後設資料庫鎖
select * from performance_schema.metadata_locks;
透過該表可以看到：
a)、哪些會話擁有後設資料鎖；
b）、哪些會話正在等待後設資料鎖；
c）、哪些請求由於死鎖被殺掉，或者鎖等待超時而被丟棄。

2、程式跟蹤
select * from performance_schema.events_stages_current;
透過該表可以跟蹤長時間操作的進度（比如ALTER TABLE）:
備註：stages是階段的意思。


3、檢視未提交事務
從mysql 5.7開始，新增events_transactions_current表，透過該表檢視當前線上的事務的狀態，如果線上資料庫遇到undo log大量增長，資料庫效能急劇下降，可以透過該表檢視當前是否存在處於未提交狀態的事務。如果發現的確有大量的事務的state處於active狀態，這時可以確定資料庫的事務未提交。


二、SYS庫的介紹

performation schema使用起來不方便，mysql 5.7用sys庫來解決這個問題。
mysql sys庫本身不採集和儲存什麼資訊，而是將performance schema和infomation schema的資料以更加容易理解的方式總結歸檔出的檢視。
在sys庫中，沒有x$字首的檢視提供了更加友好且易讀的資料；x$字首的檢視提供了原始資料，需要加工才好看。

1）、主機相關資訊：以host_summary開頭的檢視，從主機、檔案時間型別、語句型別角度展示檔案IO延遲的資訊；
2）、innodb buffer pool和鎖的相關資訊：以innodb開頭的檢視，彙總了innodb buffer page資訊和事務等待鎖的資訊；
3）、io使用情況：以io開頭的檢視，總結了io使用者的資訊，包括等待io的情況、io使用量情況；
4)、 記憶體使用情況：以memory開頭的檢視，從主機、執行緒、使用者、事件的角度展示了記憶體使用情況；
5）、連線與會話資訊：processlist和session總結了連線與會話資訊；
6）、表相關資訊：以schema_table開頭的檢視，從全表掃描、innodb緩衝池等方面展示了表統計資訊；
7）、索引資訊：其中包含index的檢視，統計了索引使用情況，以及重複索引和未使用索引情況；
8）、語句相關資訊：以statement開頭的檢視，統計的規範化後的語句使用情況，包括錯誤數、警告數、執行全表掃描的、使用臨時表、執行排序等資訊；
9）、使用者的相關資訊：以user開頭的檢視，統計了使用者使用檔案IO、執行的語句統計資訊等；
10）、等待事件相關資訊：以wait開頭的檢視，從主機和事件角度展示等待事件的延遲情況；
11）、鎖資訊：innodb_lock_waits和schema_table_lock_waits展示了鎖資訊


三、重點檢視與應用場景
1、檢視錶訪問量
場景：檢視每張表的讀寫次數



2、冗餘索引和未使用的索引

透過sys庫中的schema_index_statistics  和schema_redundant_indexes兩個檢視，可以看到哪些索引沒有被使用過或者使用率低。

3、表自增id監控
場景：知道哪張表有自增主鍵，並且監控自增主鍵是否快要超過閾值



4、檢視例項消耗的磁碟IO
DBA可以透過該查詢大致的瞭解磁碟IO消耗在哪裡，哪些檔案消耗的最多。DBA可以根據這些資訊，針對某表，某庫進行最佳化。

5、監控全表掃描的sql語句
透過sys.statements_with_full_table_scans來看全表掃描的sql語句：
透過上面可以看到，該sql語句總共執行了5次，有5次都沒有使用索引，總共消耗了997.89 us。

6、操作風險
sys庫的檢視來源於performace_schema和information_schema，我們知道performace_schema開啟後，資料庫會有10%效能下降：

所以，我們查詢sys或者performance_schema時，要謹慎操作。

7參考
參考《mysql運維內參》一書

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28916011/viewspace-2150040/，如需轉載，請註明出處，否則將追究法律責任。

相關文章