知數(shù)堂@田帥萌出品， 例子屋彪、說明來自鄙人的屌絲機(jī)所宰。
導(dǎo)讀
MySQL5.7的新特性中，非常突出的特性之一就是sys庫畜挥，不僅可以通過sys庫完成MySQL信息的收集仔粥，還可以用來監(jiān)控和排查問題。
但你知道嗎蟹但，sys庫其實(shí)還有很多種玩法躯泰，有的或許單看表名就知道了，但有一些华糖，還是需要老司機(jī)帶路哦~
一.用戶麦向、連接類
- 查看每個(gè)客戶端IP過來的連接消耗資源情況。
root@localhost [sys]>select * from host_summary\G
*************************** 1. row ***************************
host: localhost
statements: 504
statement_latency: 350.92 ms
statement_avg_latency: 696.27 us
table_scans: 9
file_ios: 694
file_io_latency: 162.42 ms
current_connections: 1
total_connections: 2
unique_users: 1
current_memory: 0 bytes
total_memory_allocated: 0 bytes
1 row in set (0.01 sec)
- 查看每個(gè)用戶消耗資源情況
root@localhost [sys]>select * from user_summary\G
*************************** 1. row ***************************
user: repl
statements: 74
statement_latency: 7.33 w #等待時(shí)間
statement_avg_latency: 16.64 h #執(zhí)行語句平均延遲時(shí)間
table_scans: 0
file_ios: 105123 #io時(shí)間總數(shù)
file_io_latency: 2.04 s #文件io延遲
current_connections: 1 #當(dāng)前連接數(shù)
total_connections: 7 #總連接數(shù)
unique_hosts: 1
current_memory: 0 bytes
total_memory_allocated: 0 bytes
- 查看當(dāng)前連接情況（有多少連接就應(yīng)該有多少行）
[sys]>select host, current_connections, statements from host_summary\G
*************************** 1. row ***************************
host: 123.207.x.x
current_connections: 1
statements: 74
*************************** 2. row ***************************
host: 182.148.x.x
current_connections: 0
statements: 2416
*************************** 3. row ***************************
host: localhost
current_connections: 1
statements: 11716884
3 rows in set (0.03 sec)
- 查看當(dāng)前正在執(zhí)行的SQL和執(zhí)行show full processlist的效果相當(dāng)客叉。
[sys]>select conn_id, user, current_statement, last_statement from session\G
*************************** 1. row ***************************
conn_id: 3
user: root@localhost
current_statement: select conn_id, user, current_ ... t, last_statement from session
last_statement: NULL
1 row in set (0.04 sec)
二.SQL 和io類
- 查看發(fā)生IO請求前三名的文件诵竭。
[sys]>select * from io_global_by_file_by_bytes order by total desc limit 3\G
*************************** 1. row ***************************
file: @@datadir/mysql/help_topic.ibd
count_read: 61
total_read: 976.00 KiB
avg_read: 16.00 KiB
count_write: 0
total_written: 0 bytes
avg_write: 0 bytes
total: 976.00 KiB
write_pct: 0.00
*************************** 2. row ***************************
file: @@datadir/sakila/film_category.ibd
count_read: 6
total_read: 96.00 KiB
avg_read: 16.00 KiB
count_write: 0
total_written: 0 bytes
avg_write: 0 bytes
total: 96.00 KiB
write_pct: 0.00
三.buffer pool 、內(nèi)存
- 查看總共分配了多少內(nèi)存
root@localhost [sys]>select * from memory_global_total\G
*************************** 1. row ***************************
total_allocated: 141.71 MiB
1 row in set (0.00 sec)
- 每個(gè)庫（database）占用多少buffer pool兼搏。
select * from innodb_buffer_stats_by_schema order by allocated desc limit 2;
+---------------+-----------+-----------+-------+--------------+-----------+-------------+
| object_schema | allocated | data | pages | pages_hashed | pages_old | rows_cached |
+---------------+-----------+-----------+-------+--------------+-----------+-------------+
| InnoDB System | 7.77 MiB | 6.95 MiB | 497 | 0 | 15 | 6249 |
| exercise | 32.00 KiB | 605 bytes | 2 | 0 | 2 | 16 |
+---------------+-----------+-----------+-------+--------------+-----------+-------------+
2 rows in set (0.81 sec)
- 統(tǒng)計(jì)innodb 引擎的 innodb緩存
下面的pages指在buffer pool中的page數(shù)量卵慰，pages_old指在LUR 列表中出于后37%位置的page，當(dāng)出現(xiàn)buffer page不夠用佛呻，那么就會征用這些page所占的空間裳朋。37%是默認(rèn)位置，可以自定義
root@localhost [sys]>select * from innodb_buffer_stats_by_schema limit 2;
+---------------+------------+------------+-------+--------------+-----------+-------------+
| object_schema | allocated | data | pages | pages_hashed | pages_old | rows_cached |
+---------------+------------+------------+-------+--------------+-----------+-------------+
| sbtest | 308.12 MiB | 258.72 MiB | 19720 | 18070 | 7099 | 414149 |
| InnoDB System | 7.77 MiB | 6.96 MiB | 497 | 0 | 15 | 6256 |
+---------------+------------+------------+-------+--------------+-----------+-------------+
2 rows in set (0.49 sec)
- 統(tǒng)計(jì)每張表具體在InnoDB中具體的情況吓著，比如占多少頁
其實(shí)和第3例的pages的總數(shù)都是相等的鲤嫡，你可以借用sum（pages）運(yùn)算驗(yàn)證一下哦送挑！
root@localhost [sys]>select * from innodb_buffer_stats_by_table limit 2;
+---------------+-------------+-----------+-----------+-------+--------------+-----------+-------------+
| object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached |
+---------------+-------------+-----------+-----------+-------+--------------+-----------+-------------+
| sbtest | sbtest2 | 61.81 MiB | 51.82 MiB | 3956 | 3619 | 3003 | 249879 |
| sbtest | sbtest4 | 61.73 MiB | 51.77 MiB | 3951 | 3616 | 0 | 249932 |
+---------------+-------------+-----------+-----------+-------+--------------+-----------+-------------+
2 rows in set (1.19 sec)
- 查詢每個(gè)連接分配了多少內(nèi)存(我這臺沒什么業(yè)務(wù)，所以有點(diǎn)不堪)暖眼，利用session表和memory_by_thread_by_current_bytes分配表進(jìn)行關(guān)聯(lián)查詢惕耕。
root@localhost [sys]>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\G
*************************** 1. row ***************************
user: repl@123.207.x.x
current_count_used: 0
current_allocated: 0 bytes
current_avg_alloc: 0 bytes
current_max_alloc: 0 bytes
total_allocated: 0 bytes
current_statement: NULL
四.字段、索引罢荡、鎖
- 查看表自增字段最大值和當(dāng)前值赡突，有時(shí)候做數(shù)據(jù)增長的監(jiān)控，可以作為參考区赵，你使用時(shí)惭缰，不一定要加條件語句。
select * from schema_auto_increment_columns where table_name in ("payment","sbtest2","test1");
+--------------+------------+-------------+-----------+----------------------+-----------+-------------+------------+----------------+----------------------+
| table_schema | table_name | column_name | data_type | column_type | is_signed | is_unsigned | max_value | auto_increment | auto_increment_ratio |
+--------------+------------+-------------+-----------+----------------------+-----------+-------------+------------+----------------+----------------------+
| sakila | payment | payment_id | smallint | smallint(5) unsigned | 0 | 1 | 65535 | 16050 | 0.2449 |
| sbtest | sbtest2 | id | int | int(10) unsigned | 0 | 1 | 4294967295 | 999997 | 0.0002 |
| zf | test1 | id | int | int(11) | 1 | 0 | 2147483647 | 4 | 0.0000 |
+--------------+------------+-------------+-----------+----------------------+-----------+-------------+------------+----------------+----------------------+
3 rows in set (0.04 sec)
- MySQL索引使用情況統(tǒng)計(jì)
root@localhost [sys]>select * from schema_index_statistics limit 3;
+--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
| table_schema | table_name | index_name | rows_selected | select_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency |
+--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
| sbtest | sbtest4 | PRIMARY | 6170545 | 41.27 s | 0 | 0 ps | 26099 | 2.90 s | 12975 | 0 ps |
| sbtest | sbtest1 | PRIMARY | 6179516 | 41.52 s | 0 | 0 ps | 25999 | 2.99 s | 13045 | 0 ps |
| sbtest | sbtest3 | PRIMARY | 6122425 | 40.31 s | 0 | 0 ps | 26093 | 3.68 s | 12731 | 0 ps |
+--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
3 rows in set (0.00 sec)
- MySQL中有哪些冗余索引和無用索引
庫中展示沒有冗余索引笼才，所以沒有數(shù)據(jù)漱受，當(dāng)有聯(lián)合索引idx_abc(a,b,c)和idx_a(a)，那么就算idx_a就算冗余索引了骡送。
root@localhost [sys]>select * from schema_redundant_indexes;
Empty set (0.00 sec)
root@localhost [sys]>select * from schema_unused_indexes limit 3;
+---------------+-------------+---------------------+
| object_schema | object_name | index_name |
+---------------+-------------+---------------------+
| exercise | products | Manufacturer |
| sakila | actor | idx_actor_last_name |
| sakila | address | idx_fk_city_id |
+---------------+-------------+---------------------+
3 rows in set (0.01 sec)
- 查看INNODB 鎖信息
在未來的版本將被移除昂羡，可以采用其他方式
root@localhost [sys]>select * from innodb_lock_waits\G
*************************** 1. row ***************************
wait_started: 2017-09-10 16:36:39
wait_age: 00:00:12
wait_age_secs: 12
locked_table: `zf`.`test1`
locked_index: PRIMARY
locked_type: RECORD #鎖類型
waiting_trx_id: 3657662 #正在等待的事務(wù)id
waiting_trx_started: 2017-09-10 16:36:39
waiting_trx_age: 00:00:12
waiting_trx_rows_locked: 1 #等待鎖住的行數(shù)
waiting_trx_rows_modified: 0
waiting_pid: 4806 #等待鎖的id即第一章的4例中的conn_id,
waiting_query: update test1 set name='wwwwwww' where id=3 #等待的語句
waiting_lock_id: 3657662:124:3:4
waiting_lock_mode: X
blocking_trx_id: 3657661
blocking_pid: 4805 #持有鎖的conn_id
blocking_query: NULL
blocking_lock_id: 3657661:124:3:4
blocking_lock_mode: X #阻塞鎖模式
blocking_trx_started: 2017-09-10 16:36:03
blocking_trx_age: 00:00:48
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 1
sql_kill_blocking_query: KILL QUERY 4805 #殺死產(chǎn)生阻塞的語句（query）
sql_kill_blocking_connection: KILL 4805 #殺死產(chǎn)生阻塞語句的會話id（session）
1 row in set, 3 warnings (0.00 sec)
#咋們來看看警告什么的是什么
root@localhost [sys]>show warnings ;
+---------+------+-----------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------------------+
| Warning | 1681 | 'INFORMATION_SCHEMA.INNODB_LOCK_WAITS' is deprecated and will be removed in a future release. |
| Warning | 1681 | 'INFORMATION_SCHEMA.INNODB_LOCKS' is deprecated and will be removed in a future release. |
| Warning | 1681 | 'INFORMATION_SCHEMA.INNODB_LOCKS' is deprecated and will be removed in a future release. |
+---------+------+-----------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
- 查看庫級別的鎖信息，這個(gè)需要先打開MDL鎖的監(jiān)控：
root@localhost [sys]>use performance_schema;
root@localhost [sys]>UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
#也可以在my.cnf中設(shè)置摔踱，不過需要重啟給個(gè)鏈接
#https://dev.mysql.com/doc/refman/5.7/en/metadata-locks-table.html
root@localhost [sys]>select * from schema_table_lock_waits\G
*************************** 1. row ***************************
object_schema: zf
object_name: t_mobile
waiting_thread_id: 4873
waiting_pid: 4848
waiting_account: root@localhost
waiting_lock_type: EXCLUSIVE
waiting_lock_duration: TRANSACTION
waiting_query: alter table t_mobile add age1 int
waiting_query_secs: 16
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
blocking_thread_id: 4872
blocking_pid: 4847
blocking_account: root@localhost
blocking_lock_type: SHARED_WRITE
blocking_lock_duration: TRANSACTION
sql_kill_blocking_query: KILL QUERY 4847
sql_kill_blocking_connection: KILL 4847
*************************** 2. row ***************************
object_schema: zf
object_name: t_mobile
waiting_thread_id: 4873
waiting_pid: 4848
waiting_account: root@localhost
waiting_lock_type: EXCLUSIVE
waiting_lock_duration: TRANSACTION
waiting_query: alter table t_mobile add age1 int
waiting_query_secs: 16
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
blocking_thread_id: 4873
blocking_pid: 4848
blocking_account: root@localhost
blocking_lock_type: SHARED_UPGRADABLE
blocking_lock_duration: TRANSACTION
sql_kill_blocking_query: KILL QUERY 4848
sql_kill_blocking_connection: KILL 4848
2 rows in set (0.00 sec)
五.線程類
- MySQL內(nèi)部有多個(gè)線程在運(yùn)行虐先，線程類型及數(shù)量。
root@localhost [sys]>select user, count(*) from processlist group by user;
+---------------------------------+----------+
| user | count(*) |
+---------------------------------+----------+
| innodb/buf_dump_thread | 1 |
| innodb/dict_stats_thread | 1 |
| innodb/io_ibuf_thread | 1 |
| innodb/io_log_thread | 1 |
| innodb/io_read_thread | 4 |
| innodb/io_write_thread | 4 |
| innodb/page_cleaner_thread | 1 |
| innodb/srv_error_monitor_thread | 1 |
| innodb/srv_lock_timeout_thread | 1 |
| innodb/srv_master_thread | 1 |
| innodb/srv_monitor_thread | 1 |
| innodb/srv_purge_thread | 1 |
| innodb/srv_worker_thread | 3 |
| repl@123.207.36.131 | 1 |
| root@localhost | 1 |
| sql/compress_gtid_table | 1 |
| sql/main | 1 |
| sql/signal_handler | 1 |
| sql/thread_timer_notifier | 1 |
+---------------------------------+----------+
19 rows in set (0.06 sec)
