MySQL預設資料庫之sys庫

內容參考連結如下：

https://dev.mysql.com/doc/refman/5.7/en/sys-schema-progress-reporting.html

https://dev.mysql.com/doc/refman/5.7/en/sys-schema-prerequisites.html

https://dev.mysql.com/doc/refman/5.7/en/sys-schema-usage.html

更多內容參考： http://blog.itpub.net/26736162/viewspace-2143988/

本系列基於MySQL 5.7.18 版本整理

1. sys系統庫使用基礎環境

在使用sys系統庫之前，你需要確保你的資料庫環境滿足如下條件：

1）sys系統庫支援MySQL 5.6或更高版本，5.5.x及其以下版本不支援；

2）因為sys系統庫提供了一些代替直接訪問performance_schema的檢視，所以必須啟用performance_schema(performance_schema系統引數設定為ON)之後sys系統庫的大部分功能才能正常使用；

3）要完全訪問sys系統庫，使用者必須具有以下許可權： 

* 對所有sys表和檢視具有SELECT許可權 
* 對所有sys儲存過程和函式具有EXECUTE許可權 
* 對sys_config表具有INSERT、UPDATE許可權 
* 對某些特定的sys系統庫儲存過程和函式需要額外許可權，如，ps_setup_save()儲存過程，需要臨時表相關的許可權

4）還有sys系統庫執行訪問的物件相關的許可權： 

* 任何被sys系統庫訪問的performance_schema表需要有SELECT許可權，如果要使用sys系統庫對performance_schema相關表執行更新，則需要performance_schema相關表的UPDATE許可權 
* INFORMATION_SCHEMA.INNODB_BUFFER_PAGE表的PROCESS

5）如果要充分使用sys系統庫的功能，則必須啟用某些performance_schema的instruments和consumers，如下： 

* 所有wait instruments 
* 所有stage instruments 
* 所有statement instruments 
* 對於所啟用的型別事件的instruments，還需要啟用對應型別的consumers(xxx_current和xxx_history_long)，要了解某儲存過程具體做了什麼事情可能透過show create procedure procedure_name;語句檢視

您可以使用 sys 系統庫本身來啟用所有需要的 instruments 和 consumers ：

* 啟用所有wait instruments：CALLsys.ps_setup_enable_instrument('wait');

* 啟用所有stage instruments：CALLsys.ps_setup_enable_instrument('stage');

* 啟用所有statement instruments：CALLsys.ps_setup_enable_instrument('statement');

* 啟用所有事件型別的current表：CALLsys.ps_setup_enable_consumer('current');

* 啟用所有事件型別的history_long表：CALLsys.ps_setup_enable_consumer('history_long');

* 注意：performance_schema的預設配置就可以滿足sys系統庫的大部分資料收集功能。啟用上述所提及的所有instruments和consumers會對效能產生一定影響，因此最好僅啟用所需的配置。如果你在啟用了一些預設配置之外的配置，則可以使用儲存過程：CALLsys.ps_setup_reset_to_default(TRUE); 來快速恢復到performance_schema的預設配置

PS：對於以上繁雜的許可權要求，通常建立一個具有管理員許可權的賬號即可，當然如果你有明確的需求，那另當別論，但sys系統庫通常都是提供給專業的DBA人員排查一些特定問題使用的，其下所涉及的各項查詢或多或少都會對效能有一定影響（主要體現在performance_schema功能實現的效能開銷），在不明需求的情況下，不建議開放這些功能來作為常規的監控手段使用。

2. sys系統庫初體驗

當你使用了use語句切換預設資料庫，那麼就可以直接使用sys系統庫下的檢視名稱進行查詢，就像查詢某個庫下的表一樣操作，如下：

# version檢視可以檢視sys 系統庫和mysql server的版本號
mysql> USE sys;
mysql> SELECT * FROM version;
+ ------------- + ----------------- +
| sys_version | mysql_version |
+ ------------- + ----------------- +
| 1.5.0 | 5.7.9-debug-log |
+ ------------- + ----------------- +

也可以使用db_name.view_name、db_name.procedure_name、db_name.func_name等方式在不指定預設資料庫的情況下訪問sys 系統庫中的物件(這叫做名稱限定物件引用)，如下：

mysql> SELECT * FROM sys.version;
+ ------------- + ----------------- +
| sys_version | mysql_version |
+ ------------- + ----------------- +
| 1.5.0 | 5.7.9-debug-log |
+ ------------- + ----------------- +

PS：下文中的示例中，對於sys 系統庫的訪問都是假定指定了預設資料庫為sys 系統庫。

sys 系統庫下包含許多檢視，它們以各種方式對performance_schema表進行聚合計算展示。這些檢視中大部分都是成對出現，兩個檢視名稱相同，但有一個檢視是帶'x$'字元字首的，例如：host_summary_by_file_io和x$host_summary_by_file_io，代表按照主機進行彙總統計的檔案I/O效能資料，兩個檢視訪問資料來源是相同的，但是建立檢視的語句中，不帶x$的檢視是把相關數值資料經過單位換算再顯示的(顯示為毫秒、秒、分鐘、小時、天等)，帶x$字首的檢視顯示的是原始的資料(皮秒)，如下：

# x$host_summary_by_file_io檢視彙總資料，顯示未格式化的皮秒單位延遲時間，沒有x$字首字元的檢視輸出的資訊經過單位換算之後可讀性更高
mysql> SELECT * FROM host_summary_by_file_io;
+------------+-------+------------+
| host      | ios  | io_latency |
+------------+-------+------------+
| localhost  | 67570 | 5.38 s    |
| background |  3468 | 4.18 s    |
+------------+-------+------------+
# 對於帶x$的檢視顯示原始的皮秒單位數值，對於程式或工具獲取使用更易於資料處理
mysql> SELECT * FROM x$host_summary_by_file_io;
+------------+-------+---------------+
| host      | ios  | io_latency    |
+------------+-------+---------------+
| localhost  | 67574 | 5380678125144 |
| background |  3474 | 4758696829416 |
+------------+-------+---------------+

要檢視sys 系統庫物件定義語句，可以使用適當的SHOW語句或INFORMATION_SCHEMA庫查詢。例如，要檢視session檢視和format_bytes()函式的定義，可以使用如下語句：

mysql> SHOW CREATE VIEW session;
mysql> SHOW CREATE FUNCTION format_bytes;

然而，這些語句文字是經過格式化的，可讀性比較差。要檢視更易讀的格式物件定義語句，可以訪問sys 系統庫開發網站上的各個.sql檔案，或者使用mysqldump與mysqlpump工具匯出sys庫，預設情況下，mysqldump和mysqlpump都不會匯出sys 系統庫。要生成包含sys 系統庫的匯出檔案，可以使用如下命令顯式指定sys 系統庫（雖然可以匯出檢視定義，但是與原始的定義語句相比仍然缺失了相當一部分內容，只是可讀性比直接show create view要好一些）：

mysqldump --databases --routines sys> sys_dump.sql
mysqlpump sys> sys_dump.sql

如果要重新匯入sys 系統庫，可以使用如下命令：

mysql < sys_dump.sql

3. sys 系統庫的進度報告功能

從MySQL 5.7.9開始，sys 系統庫檢視提供檢視長時間執行的事務的進度報告，透過processlist和session以及x$字首的檢視進行檢視，其中processlist包含了後臺執行緒和前臺執行緒當前的事件資訊，session不包含後臺執行緒和command為Daemon的執行緒，如下：

processlist
session
x$processlist
x$session

session檢視是直接呼叫processlist檢視過濾了後臺執行緒和command為Daemon的執行緒（所以兩個檢視輸出結果的欄位相同），而processlist執行緒聯結查詢了threads、events_waits_current、events_stages_current、events_statements_current、events_transactions_current、sys.x$memory_by_thread_by_current_bytes、session_connect_attrs表，所以，需要開啟相應的instruments和consumers，否則誰沒開啟誰對應的資訊欄位列就為NULL，對於trx_state欄位為ACTIVE的執行緒，progress可以輸出百分比進度資訊(只有支援進度的事件才會被統計並列印進來)

查詢示例

# 檢視當前正在執行的語句進度資訊
admin@localhost : sys 06:57:21> select * from session where conn_id!=connection_id() and trx_state='ACTIVE'\G;
*************************** 1. row ***************************
            thd_id: 47
          conn_id: 5
              user: admin@localhost
                db: sbtest
          command: Query
            state: alter table (merge sort)
              time: 29
current_statement: alter table sbtest1 add index i_c(c)
statement_latency: 29.34 s
          progress: 49.70
      lock_latency: 4.34 ms
    rows_examined: 0
        rows_sent: 0
    rows_affected: 0
        tmp_tables: 0
  tmp_disk_tables: 0
        full_scan: NO
    last_statement: NULL
last_statement_latency: NULL
    current_memory: 4.52 KiB
        last_wait: wait/io/file/innodb/innodb_temp_file
last_wait_latency: 369.52 us
            source: os0file.ic:470
      trx_latency: 29.45 s
        trx_state: ACTIVE
    trx_autocommit: YES
              pid: 4667
      program_name: mysql
1 row in set (0.12 sec)
# 檢視已經執行完的語句相關統計資訊
admin@localhost : sys 07:02:21> select * from session where conn_id!=connection_id() and trx_state='COMMITTED'\G;
*************************** 1. row ***************************
            thd_id: 47
          conn_id: 5
              user: admin@localhost
                db: sbtest
          command: Sleep
            state: NULL
              time: 372
current_statement: NULL
statement_latency: NULL
          progress: NULL
      lock_latency: 4.34 ms
    rows_examined: 0
        rows_sent: 0
    rows_affected: 0
        tmp_tables: 0
  tmp_disk_tables: 0
        full_scan: NO
    last_statement: alter table sbtest1 add index i_c(c)
last_statement_latency: 1.61 m
    current_memory: 4.52 KiB
        last_wait: idle
last_wait_latency: Still Waiting
            source: socket_connection.cc:69
      trx_latency: 1.61 m
        trx_state: COMMITTED
    trx_autocommit: YES
              pid: 4667
      program_name: mysql
1 row in set (0.12 sec)

對於stage事件進度報告要求必須啟用events_stages_current consumers，啟用需要檢視進度相關的instruments。例如：

stage/sql/Copying to tmp table
stage/innodb/alter table (end)
stage/innodb/alter table (flush)
stage/innodb/alter table (insert)
stage/innodb/alter table (log apply index)
stage/innodb/alter table (log apply table)
stage/innodb/alter table (merge sort)
stage/innodb/alter table (read PK and internal sort)
stage/innodb/buffer pool load

對於不支援進度的stage 事件，或者未啟用所需的instruments或consumers的stage事件，則對應的進度資訊列顯示為NULL。

---

---

About Me

........................................................................................................................ ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub、部落格園、CSDN和個人微 信公眾號（ xiaomaimiaolhr ）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文部落格園地址： http://www.cnblogs.com/lhrbest ● 本文CSDN地址： https://blog.csdn.net/lihuarongaini ● 本文pdf版、個人簡介及小麥苗雲盤地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址： ........................................................................................................................ ● QQ群號： 230161599 （滿） 、618766405 ● 微 信群：可加我微 信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友 （ 646634621 ） ，註明新增緣由 ● 於 2019-07-01 06:00 ~ 2019-07-31 24:00 在西安完成 ● 最新修改時間：2019-07-01 06:00 ~ 2019-07-31 24:00 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店 ： ● 小麥苗出版的資料庫類叢書 ： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用網路班 ： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麥苗騰訊課堂主頁 ： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 微 信客戶端 掃描下面的二維碼來關注小麥苗的微 信公眾號（ xiaomaimiaolhr ）及QQ群（DBA寶典）、新增小麥苗微 信， 學習最實用的資料庫技術。 ........................................................................................................................