1.基礎介紹
MySQL 5.7引入了sys schema，有了它，我們排查分析一些問題時將更得心應手。sys schema裡主要儲存的是檢視、儲存過程、函式等。
在mysql的5.7版本中，效能模式是預設開啟的，如果想要顯式的關閉的話需要修改配置檔案，不能直接進行修改。
檢視performance_schema的屬性
mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+
1 row in set (0.01 sec)
--在配置檔案中修改performance_schema的屬性值，on表示開啟，off表示關閉
[mysqld]
performance_schema=ON
（1）檢視：用於結果的彙總展示及配置持久化。
檢視sys schema檢視：
select table_name , table_type, engine from information_schema.tables where table_schema = 'sys' order by table_name;
+-----------------------------------------------+------------+--------+
| table_name | table_type | engine |
+-----------------------------------------------+------------+--------+
| host_summary | VIEW | NULL |
| host_summary_by_file_io | VIEW | NULL |
| host_summary_by_file_io_type | VIEW | NULL |
| host_summary_by_stages | VIEW | NULL |
對於每一個檢視，都有兩種形式：第一種是便於人類閱讀的形式，格式化了時間單位和位元組單位，另外一種以”x$”開頭的檢視名稱，是為了便於工具處理。如下所示：
mysql> select table_name from information_schema.tables where table_schema = 'sys' and table_name like '%memory%';
+-------------------------------------+
| table_name |
+-------------------------------------+
| memory_by_host_by_current_bytes |
| memory_by_thread_by_current_bytes |
| memory_by_user_by_current_bytes |
| memory_global_by_current_bytes |
| memory_global_total |
| x$memory_by_host_by_current_bytes |
| x$memory_by_thread_by_current_bytes |
| x$memory_by_user_by_current_bytes |
| x$memory_global_by_current_bytes |
| x$memory_global_total |
+-------------------------------------+
10 rows in set (0.00 sec)
（2）儲存過程：用於對Performance schema的控制及收集；
大部分儲存過程都是PS開頭的，PS是performance schema的簡寫，這些儲存過程都是用來控制performance schema統計的行為。
（3）函式：對於Performance schema的配置及資料格式化。
這些檢視的資訊來自哪裡呢？檢視中的資訊均來自performance schema和information schema中的統計資訊。
show function status where db = 'sys';
show PROCEDURE status where db='sys';
2、sys_config 表
CREATE TABLE `sys_config` (
`variable` varchar(128) NOT NULL comment '配置選項名稱',
`value` varchar(128) DEFAULT NULL comment '配置選項值',
`set_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '該行配置修改的時間',
`set_by` varchar(128) DEFAULT NULL comment '配置資訊修改者，如果從被安裝沒有修改過，那麼這個資料應該為NULL',
PRIMARY KEY (`variable`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
表中預設資料為
variable value set_time set_by
diagnostics.allow_i_s_tables OFF 2015-11-20 16:04:38 NULL
diagnostics.include_raw OFF 2015-11-20 16:04:38 NULL
statement_performance_analyzer.limit 100 2015-11-20 16:04:38 NULL
statement_performance_analyzer.view 2015-11-20 16:04:38 NULL
statement_truncate_len 64 2016-01-22 17:00:16 NULL
以上值的會話變數為@sys.+表中variable欄位，譬如：@sys.statement_truncate_len 可以set @sys.statement_truncate_len = 32 臨時改變值，在會話中會一直使用這個值，如果想要恢復使用表的預設值，只需要將這個會話值設定為null；set @sys.statement_truncate_len = null;
diagnostics.allow_i_s_tables
diagnostics.include_raw
這兩個值預設為OFF ，前者如果開啟表示允許diagnostics() 儲存過程執行掃描information_schema.tables 表，如果表很多，那麼可能會很耗效能，後者開啟將會從metrics檢視輸出未加工處理的資料 。
statement_performance_analyzer.limit 檢視在沒有加limit限制時，返回的最大行數
這個表非預設選項還有一個@sys.debug引數
可以手動加入
INSERT INTO sys_config (variable, value) VALUES('debug', 'ON');
UPDATE sys_config SET value = 'OFF' WHERE variable = 'debug';
SET @sys.debug = NULL;
sys schema介紹：
1. 主機相關：以host_summary開頭，主要彙總了IO延遲的資訊。
2. Innodb相關：以innodb開頭，彙總了innodb buffer資訊和事務等待innodb鎖的資訊。
3. I/o相關：以io開頭，彙總了等待I/O、I/O使用量情況。
4. 記憶體使用情況：以memory開頭，從主機、執行緒、事件等角度展示記憶體的使用情況
5. 連線與會話資訊：processlist和session相關檢視，總結了會話相關資訊。
6. 表相關：以schema_table開頭的檢視，展示了表的統計資訊。
7. 索引資訊：統計了索引的使用情況，包含冗餘索引和未使用的索引情況。
8. 語句相關：以statement開頭，包含執行全表掃描、使用臨時表、排序等的語句資訊。
9. 使用者相關：以user開頭的檢視，統計了使用者使用的檔案I/O、執行語句統計資訊。
10. 等待事件相關資訊：以wait開頭，展示等待事件的延遲情況。
3、基本檢視介紹
host_summary 總體檢視host維度
host_summary_by_file_io
host_summary_by_file_io_type
host_summary_by_stages
host_summary_by_statement_latency
host_summary_by_statement_type
欄位名 意義
host 從哪個客戶機上連過來。如果是NULL，表示內部的程序
statements 該客戶機共執行了多少語句
statement_latency 該客戶機發來等待語句執行的時間
statement_avg_latency 該客戶機等待語句執行的平均時間
table_scans 該客戶機發生全表掃描的次數
file_ios 該客戶機上IO事件請求的次數
file_io_latency 該客戶機請求等待IO的時間
current_connections 該客戶機當前的連線數
total_connections 該客戶機連線DB共有多少次
unique_user 該客戶機上有幾個不同使用者名稱的帳戶連線過來
current_memory 該客戶機上當前連線佔用的記憶體
total_memory_allocated 該客戶機上的請求總共使用的記憶體量（歷史累計值）
IO檢視：
io_by_thread_by_latency
io_global_by_file_by_bytes
io_global_by_file_by_latency
io_global_by_wait_by_bytes
io_global_by_wait_by_latency
latest_file_io
file 被操作的檔名
count_read 總共有多少次讀
total_read 總共讀了多少位元組
avg_read 平均每次讀的位元組數
count_write 總共有多少次寫
total_written 總共寫了多少位元組
avg_write 平均每次寫的位元組大小
total 讀和寫總共的IO大小
write_pct 寫請求佔總IO請求中的百分比（就是通常所說的讀寫比）
user_summary 總體檢視，user維度
user_summary_by_file_io
user_summary_by_file_io_type
user_summary_by_stages
user_summary_by_statement_latency
user_summary_by_statement_type
user 客戶端連線過來的使用者名稱，如果是NULL，表示內部程序
statements 該使用者執行了多少SQL
statement_latency 該使用者執行SQL的總延遲時間
statement_avg_latency 該使用者執行SQL的平均延遲時間
table_scans 該使用者執行SQL時發生全表掃描的次數
file_ios 該使用者發生的IO請求總量
file_io_latency 該使用者發生的IO請求總延遲時間
current_connections 該使用者當前的連線
total_connections 該使用者總的連線數
unique_hosts 該使用者從幾個不同客戶機連線過來
current_memory 該使用者當前佔用的記憶體
total_memory_allocated 該使用者總共申請到的記憶體（歷史累計值）
記憶體總體檢視
memory_by_host_by_current_bytes
memory_by_thread_by_current_bytes
memory_by_user_by_current_bytes
memory_global_by_current_bytes
memory_global_total
total_allocated Server 總共分配出去的記憶體大小
thread_id MySQL內部執行緒ID，可以和session檢視中的thd_id關聯
user 當前執行緒是哪個使用者建立
current_count_used 當前執行緒正在使用且未釋放的記憶體塊（記憶體塊不是固定大小的）
current_allocated 當前執行緒正在使用且未釋放的記憶體塊大小（位元組）
current_avg_alloc 當前執行緒每個記憶體塊平均分配記憶體大小（位元組）
current_max_alloc 當前執行緒單次曾經分配的最多記憶體大小（位元組）
total_allocated 當前執行緒總共分配的記憶體大小
statement_analysis 檢視
statements_with_errors_or_warnings
statements_with_full_table_scans
statements_with_runtimes_in_95th_percentile
statements_with_sorting
statements_with_temp_tables
query 格式化後的SQL（將SQL中的引數替換成？）
db 在哪個DB中執行，如果為NULL表示在任何DB
full_scan 是否使用了全表掃描
exec_count 該SQL被執行的總次數
err_count 發生錯誤的次數
warn_count 發生警告的次數
total_latency 總共發生延遲的時間
max_latency 最大延遲的時間
avg_latency 平均延遲的時間
lock_latency 因鎖等待佔用的總時間
rows_sent 執行該SQL返回的總行數
rows_sent_avg 執行該SQL平均返回的行數
rows_examined 執行該SQL掃描的總行數
rows_examined_avg 執行該SQL平均每次掃描的行數
tmp_tables 該SQL生成記憶體臨時表的總次數
tmp_disk_tables 該SQL生成磁碟檔案臨時表的總次數
rows_sorted 該SQL總共排序的行數
sort_merge_passes 用於排序中合併的總次數
digest 該語句的HASH值
first_seen 該SQL最早出現的時間
last_seen 該SQL最近出現的時間
processlist檢視
thd_id 內部執行緒ID
conn_id 連線的ID（對應show processlist中的"Id"列）
user 該執行緒建立的使用者名稱
db 連線的DB，如果是NULL表示後臺執行緒
command Client 發起命令的型別提示
state 命令的狀態
time 基於上面state停留的時間
current_statement 該執行緒執行的語句
statement_latency 語句執行總共佔用時間
progress 該語句執行完成的百分比
lock_latency 該語句用於鎖等待的時間
rows_examined 該語句掃描的行數
rows_send 該語句返回的行數
rows_affected 該語句影響到的行數(寫入語句)
tmp_tables 形成記憶體臨時表的次數
tmp_disk_tables 形成磁碟臨時表的次數
full_scan 全表描掃的次數
last_statement 上一條被執行的SQL
last_statement_latency 上一條被執行的SQL時延
current_memory 當前執行緒佔用的記憶體
last_wait 上一次等待事件
last_wait_latency 上一次等待時間時延
source 上一次等待事件對應的原始碼位置
trx_latency 事務時延
trx_state 當前事務狀態
pid 對應到系統裡的pid
program_name 連線進來的標識名
session檢視
session檢視和processlist檢視基本一樣，只是把後臺執行緒過濾掉。
innodb檢視
innodb_buffer_stats_by_schema
innodb_buffer_stats_by_table
innodb_lock_waits
object_schema schema名
allocated 該schema上分配的buffer pool大小（位元組）
data 該schema中快取的資料大小（位元組）
pages 該schema中快取的data page數
pages_hashed 該schema中分配了多少hashed page
pages_old 該schema中的old page數量
rows_cached 該schema中快取的row data數量
innodb_buffer_stats_by_table檢視
和innodb_buffer_stats_by_schema基本一致
wait等待事件
wait_classes_global_by_avg_latency
wait_classes_global_by_latency
waits_by_host_by_latency
waits_by_user_by_latency
waits_global_by_latency
events 等待事件名稱
total 次數
total_latency 總延遲
max_latency 最大延遲
min_latency 最小延遲
information_schema 庫中新增了三個關於鎖的表，亦即 innodb_trx、innodb_locks 和 innodb_lock_waits；
innodb_trx 表記錄當前執行的所有事務；
innodb_locks 表記錄當前出現的鎖；
innodb_lock_waits 表記錄鎖等待的對應關係；
SHOW ENGINE INNODB STATUS；
SHOW FULL PROCESSLIST；
啟用 InnoDB Lock Monitor；
3.舉例
（1）ps_truncate_all_tables 的作用就是truncate所有performance schema中summary相關的表。
（2）查詢：每個客戶端IP過來的連線消耗了多少資源：
mysql> select * from host_summary;
（3）檢視某個資料檔案上發生了多少IO請求：，讀多，還是寫的多
mysql> select * from io_global_by_file_by_bytes;
哪個表上的IO請求最多？
mysql> select * from io_global_by_file_by_bytes where file like '%ibd' order by total desc limit 10;
（4）使用者消耗的資源，檢視哪個使用者消耗了多少資源：
mysql> select * from user_summary;
（5）檢視總共分配了多少記憶體？
mysql> select * from memory_global_total;
（6）據庫連線來自哪裡，以及這些連線對資料庫的請求情況是怎樣的
檢視當前連線情況：
mysql> select host, current_connections, statements from host_summary;
（7）檢視當前正在執行的SQL：和執行show full processlist的效果相當，但更強大，更多資訊。
mysql> show processlist;
mysql> select conn_id,user,db,command,time,state,statement_latency,progress,lock_latency,rows_examined,rows_sent,tmp_tables,tmp_disk_tables,full_scan,current_memory from session;
（8）資料庫中哪些SQL被頻繁執行？查詢TOP 10最熱SQL：
mysql> select db,exec_count,query from statement_analysis order by exec_count desc limit 10;
增加了零時表，磁碟零時表，傳送行，平均延遲，排序，等等。
mysql> select db,exec_count,query,full_scan,avg_latency,max_latency,rows_sent_avg,tmp_tables,tmp_disk_tables,rows_sorted,sort_merge_passes from statement_analysis order by exec_count desc limit 10;
（9）哪個表被訪問的最多 ？
先訪問statement_analysis，根據熱門SQL排序找到相應的資料表。
（10）哪些語句延遲比較嚴重 ？
檢視 statement_analysis 中 avg_latency的最高的SQL：
mysql> select db,exec_count,query,full_scan,avg_latency,max_latency,rows_sent_avg,tmp_tables,tmp_disk_tables,rows_sorted,sort_merge_passes from statement_analysis order by avg_latency desc limit 10;
（11）哪些SQL語句使用了臨時表，又有哪些用到了磁碟臨時表 ？
檢視 statement_analysis 中哪個SQL的 tmp_tables 、tmp_disk_tables 值大於0即可：
mysql> select db, query,tmp_tables,tmp_disk_tables from statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc limit 20;
（12）哪個表佔用了最多的buffer pool ？
mysql> select * from innodb_buffer_stats_by_table order by allocated desc limit 10;
使用原始資料，然後自己來處理輸出：
mysql> select object_schema,object_name,(allocated/(1024*1024)) as allocated,(data/(1024*1024)) as data,pages,pages_hashed,pages_old,rows_cached from x$innodb_buffer_stats_by_table order by allocated desc limit 10;
以上allocated、data單位為M。
（13）每個庫（database）佔用多少buffer pool？
mysql> select * from innodb_buffer_stats_by_schema order by allocated desc limit 10;
（14）每個連線分配多少記憶體 ?
利用session表和memory_by_thread_by_current_bytes分配表進行關聯查詢：
mysql> select b.user, current_count_used, current_allocated, current_avg_alloc, current_max_alloc, total_allocated,current_statement from memory_by_thread_by_current_bytes a, session b where a.thread_id = b.thd_id;
（15）MySQL內部有多個執行緒在執行？MySQL內部的執行緒型別及數量：
mysql> select user, count(*) from processlist group by user;
（16）全表掃描的SQL語句
查詢語句，db，執行次數，總延遲，未使用索引百分比，平均行傳送，平均行掃描，首次執行時間，最後執行時間，digest。
取出執行次數最多，延遲最嚴重的top 20.
mysql> select query,db,exec_count,total_latency,no_index_used_pct,rows_sent_avg,rows_examined_avg,first_seen,last_seen,digest from statements_with_full_table_scans order by exec_count desc,total_latency desc limit 20;
（18）沒有正確關閉資料庫連線的使用者
SELECT ess.user, ess.host
, (a.total_connections - a.current_connections) - ess.count_star as not_closed
, ((a.total_connections - a.current_connections) - ess.count_star) * 100 /
(a.total_connections - a.current_connections) as pct_not_closed
FROM performance_schema.events_statements_summary_by_account_by_event_name ess
JOIN performance_schema.accounts a on (ess.user = a.user and ess.host = a.host)
WHERE ess.event_name = 'statement/com/quit'
AND (a.total_connections - a.current_connections) > ess.count_star ;
三、故障：
mysql> select * from host_summary;
1356 - View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
可能是許可權：
1.定義者：DEFINER=`mysql.sys`@`localhost`
2.使用的使用者dba，具有超級許可權。
檢查了host_summary本身就是invoke定義，那麼使用root也無法開啟。
--檢查該檢視本身。
mysql> show create view host_summary\G
或者：SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'sys' AND TABLE_NAME = 'host_summary';
直接執行其中的檢視：
select if(isnull(`performance_schema`.`accounts`.`HOST`),'background',。。。。。
[Err] 1305 - FUNCTION sys.format_time does not exist
原來是函式不存在。
方案1：
一種直接到base目錄下尋找，只修補需要的函式等。透過SQL執行。
cd /usr/local/mysql_3307/
[root@mysql mysql_3307]# cat share/mysql_sys_schema.sql | grep 'FUNCTION format_time'
DELIMITER $
CREATE DEFINER='mysql.sys'@'localhost' FUNCTION format_time ( picoseconds TEXT ) RETURNS TEXT CHARSET UTF8
COMMENT '\n Description\n \n Takes a raw picoseconds value, and converts it to a human readable form.\n \n Picoseconds are the precision that all latency values are printed in \n within Performance Schema, however are not user friendly when wanting\n to scan output from the command line.\n \n Parameters\n \n picoseconds (TEXT): \n The raw picoseconds value to convert.\n \n Returns\n \n TEXT\n \n Example\n \n mysql> select format_time(342342342342345);\n +------------------------------+\n | format_time(342342342342345) |\n +------------------------------+\n | 00:05:42 |\n +------------------------------+\n 1 row in set (0.00 sec)\n \n mysql> select format_time(342342342);\n +------------------------+\n | format_time(342342342) |\n +------------------------+\n | 342.34 us |\n +------------------------+\n 1 row in set (0.00 sec)\n \n mysql> select format_time(34234);\n +--------------------+\n | format_time(34234) |\n +--------------------+\n | 34.23 ns |\n +--------------------+\n 1 row in set (0.00 sec)\n '
SQL SECURITY INVOKER DETERMINISTIC NO SQL
BEGIN
IF picoseconds IS NULL THEN RETURN NULL; ELSEIF picoseconds >= 604800000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 604800000000000000, 2), ' w');
ELSEIF picoseconds >= 86400000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 86400000000000000, 2), ' d');
ELSEIF picoseconds >= 3600000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 3600000000000000, 2), ' h');
ELSEIF picoseconds >= 60000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 60000000000000, 2), ' m');
ELSEIF picoseconds >= 1000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 1000000000000, 2), ' s');
ELSEIF picoseconds >= 1000000000 THEN RETURN CONCAT(ROUND(picoseconds / 1000000000, 2), ' ms');
ELSEIF picoseconds >= 1000000 THEN RETURN CONCAT(ROUND(picoseconds / 1000000, 2), ' us');
ELSEIF picoseconds >= 1000 THEN RETURN CONCAT(ROUND(picoseconds / 1000, 2), ' ns');
ELSE RETURN CONCAT(picoseconds, ' ps');
END IF;
END $
DELIMITER ;
方案2：
直接整個sys修復。
cp /data/mysql_3307/share/mysql_sys_schema.sql /data/mysql_3307/share/mysql_sys_schema.sql.bak
echo '$$$' >> /data/mysql_3307/share/mysql_sys_schema.sql.bak
mysql -uroot -p -S /data/mysql_3307/mysql.sock --delimiter='$$$' </data/mysql_3307/share/mysql_sys_schema.sql.bak
參考連結：https://www.osso.nl/blog/mysql-sys-schema-mysqldump-failure/