知数堂@田帅萌出品， 例子、说明来自鄙人的屌丝机。
导读
MySQL5.7的新特性中，非常突出的特性之一就是sys库，不仅可以通过sys库完成MySQL信息的收集，还可以用来监控和排查问题。
但你知道吗，sys库其实还有很多种玩法，有的或许单看表名就知道了，但有一些，还是需要老司机带路哦~
一.用户、连接类
- 查看每个客户端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)
- 查看每个用户消耗资源情况
root@localhost [sys]>select * from user_summary\G
*************************** 1. row ***************************
user: repl
statements: 74
statement_latency: 7.33 w #等待时间
statement_avg_latency: 16.64 h #执行语句平均延迟时间
table_scans: 0
file_ios: 105123 #io时间总数
file_io_latency: 2.04 s #文件io延迟
current_connections: 1 #当前连接数
total_connections: 7 #总连接数
unique_hosts: 1
current_memory: 0 bytes
total_memory_allocated: 0 bytes
- 查看当前连接情况（有多少连接就应该有多少行）
[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)
- 查看当前正在执行的SQL和执行show full processlist的效果相当。
[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类
- 查看发生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 、内存
- 查看总共分配了多少内存
root@localhost [sys]>select * from memory_global_total\G
*************************** 1. row ***************************
total_allocated: 141.71 MiB
1 row in set (0.00 sec)
- 每个库（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)
- 统计innodb 引擎的 innodb缓存
下面的pages指在buffer pool中的page数量，pages_old指在LUR 列表中出于后37%位置的page，当出现buffer page不够用，那么就会征用这些page所占的空间。37%是默认位置，可以自定义
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)
- 统计每张表具体在InnoDB中具体的情况，比如占多少页
其实和第3例的pages的总数都是相等的，你可以借用sum（pages）运算验证一下哦！
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)
- 查询每个连接分配了多少内存(我这台没什么业务，所以有点不堪)，利用session表和memory_by_thread_by_current_bytes分配表进行关联查询。
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
四.字段、索引、锁
- 查看表自增字段最大值和当前值，有时候做数据增长的监控，可以作为参考，你使用时，不一定要加条件语句。
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索引使用情况统计
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中有哪些冗余索引和无用索引
库中展示没有冗余索引，所以没有数据，当有联合索引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 #正在等待的事务id
waiting_trx_started: 2017-09-10 16:36:39
waiting_trx_age: 00:00:12
waiting_trx_rows_locked: 1 #等待锁住的行数
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 #杀死产生阻塞的语句（query）
sql_kill_blocking_connection: KILL 4805 #杀死产生阻塞语句的会话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)
- 查看库级别的锁信息，这个需要先打开MDL锁的监控：
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中设置，不过需要重启给个链接
#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内部有多个线程在运行，线程类型及数量。
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)
