概述
MySQL5.7的新特性中,非常突出的特性之一就是sys庫,不僅可以通過sys庫完成MySQL資訊的收集,還可以用來監控和排查問題。下面介紹一些常用的用法。
一、使用者、連接配接類
1、檢視每個用戶端IP過來的連接配接消耗資源情況。
select * from sys.host_summary;
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5SYyATZ5YmN1AjZjFTZ1MzN3gjNkVDMiVGOiR2YmJWOi9CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
2、檢視每個使用者消耗資源情況
select * from sys.user_summary;
3、檢視目前連接配接情況(有多少連接配接就應該有多少行)
select host,current_connections,statements from sys.host_summary;
4、檢視目前正在執行的SQL
和執行show full processlist的結果差不多
select conn_id,pid,user,db,command,current_statement,last_statement,time,lock_latency from sys.session
二、SQL 和io類
1、檢視發生IO請求前5名的檔案。
select * from sys.io_global_by_file_by_bytes order by total limit 5;
三、buffer pool 、記憶體
1、檢視總共配置設定了多少記憶體
select * from sys.memory_global_total;select * from sys.memory_global_by_current_bytes;
2、每個庫(database)占用多少buffer pool
select * from sys.innodb_buffer_stats_by_schema order by allocated desc;
pages是指在buffer pool中的page數量;pages_old指在LUR 清單中處于後37%位置的page。
當出現buffer page不夠用時,就會征用這些page所占的空間。37%是預設位置,具體可以自定義。
3、統計每張表具體在InnoDB中具體的情況,比如占多少頁?
注意和前面的pages的總數都是相等的,也可以借用sum(pages)運算驗證一下。
select * from sys.innodb_buffer_stats_by_table;
4、查詢每個連接配接配置設定了多少記憶體
利用session表和memory_by_thread_by_current_bytes配置設定表進行關聯查詢。
SELECT b.USER, current_count_used, current_allocated, current_avg_alloc, current_max_alloc, total_allocated, current_statement FROM sys.memory_by_thread_by_current_bytes a, sys.SESSION b WHERE a.thread_id = b.thd_id;
四、字段、索引、鎖
1、檢視表自增字段最大值和目前值,有時候做資料增長的監控,可以作為參考。
select * from sys.schema_auto_increment_columns;
2、MySQL索引使用情況統計
select * from sys.schema_index_statistics order by rows_selected desc;
3、MySQL中有哪些備援索引和無用索引
若庫中展示沒有備援索引,則沒有資料;當有聯合索引idx_abc(a,b,c)和idx_a(a),那麼idx_a就算備援索引了。
select * from sys.schema_redundant_indexes;
4、檢視INNODB 鎖資訊
在未來的版本将被移除,可以采用其他方式
select * from sys.innodb_lock_waits
5、檢視庫級别的鎖資訊,這個需要先打開MDL鎖的監控:
--打開MDL鎖監控update performance_schema.setup_instruments set enabled='YES',TIMED='YES' where name='wait/lock/metadata/sql/mdl';select * from sys.schema_table_lock_waits;
五、線程類
1、MySQL内部有多個線程在運作,線程類型及數量
select user,count(*) from sys.`processlist` group by user;
六、主鍵自增
檢視MySQL自增id的使用情況
SELECT table_schema, table_name, ENGINE, Auto_increment FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ( "INFORMATION_SCHEMA