学习使用MySQL 5.7的sys库（下）

本文主要解答如下问题

1. 谁使用了最多的资源？ 基于 IP 或是用户？
2. 大部分连接来自哪里及发送的 SQL 情况？
3. 机器执行最多的 SQL 语句是什么样？
4. 哪张表的 IO 最多？哪张表访问次数最多？
5. 哪些语句延迟比较严重？
6. 哪些 SQL 语句使用了磁盘临时表？
7. 哪张表占用了最多的 buffer pool？
8. 每个库占用多少 buffer pool？
9. 每个连接分配多少内存？
10. MySQL 内部现在有多个线程在运行？

基础知识

下面看一些本次解答问题涉及到的重要视图。

1. host_summary

2. io_global_by_file_by_bytes

3.user_summary

4.memory_global_total

5.memory_by_thread_by_current_bytes

6. statement_analysis

7. processlist

session 视图和 processlist 视图基本一样，只是把后台线程过滤掉。

8. innodb_buffer_stats_by_schema

innodb_buffer_stats_by_table 和 innodb_buffer_stats_by_schema 基本一致，只是比上面那个视图多了一个ojbect_name 用于指定表名。

问题 1. 谁使用了最多的资源？ 基于IP或是用户？

对于该问题可以从 host, user, io 三个方面去了解，大概谁的请求最多。对于使用资源问题可以直接从下面四个视图里有一个大概的了解。

问题2 . 大部分连接来自哪里及发送的SQL情况？

查看当前连接情况：

select host, current_connections, statements from host_summary;

查看当前正在执行的 SQL：

select  conn_id, user, current_statement, last_statement from session;

问题3. 机器执行最多的 SQL 语句是什么样?

例如查一下系统里执行最多的 TOP 10 SQL。

SQL 如下：

select * from statement_analysis order by exec_count desc limit 10\G;

问题4. 哪张表的 IO 最多？哪张表访问次数最多?

直接访问 io_global_by_file_by_bytes 这个视图

select * from io_global_by_file_by_bytes limit 10;

哪张表访问次数最多，可以参考上面先查询执行最多的语句，然后查找对应的表。

SQL如下：

select * from statement_analysis order by exec_count desc limit 10\G;

问题5. 哪些语句延迟比较严重?

statement_analysis 中 avg_latency 的最高的。(参考上面写法)

SQL 语句：

select * from statement_analysis order by avg_latency desc limit 10;

问题6 . 哪些 SQL 语句使用了磁盘临时表?

利用 statement_analysis 中 tmp_tables ，tmp_disk_tables 进行计算。(参考上面写法)

参考SQL：

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；

问题7. 哪张表占用了最多的 buffer pool?

例如查询在 buffer pool 中占用前10的表。

SQL 如下：

select * from innodb_buffer_stats_by_table order by pages desc limit 10;

问题8 .每个库占用多少 buffer pool?

SQL 如下：

select * from innodb_buffer_stats_by_schema;

问题9. 每个连接分配多少内存?

利用 session 表和 memory_by_thread_by_current_bytes 分配表进行关联查询。

SQL 如下：

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;

问题10. MySQL 内部现在有多个线程在运行?

MySQL 内部的线程类型及数量：

select user, count(*) from processlist group by user;