学习使用MySQL 5.7.27的sys库

冷不防 2022-05-24 04:56 71阅读 0赞

### Sys库里的数据来源 ###

Sys库所有的数据源来自：performance\_schema。目标是把performance\_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况。

### Sys库下有两种表 ###

字母开头： 适合人阅读，显示是格式化的数

x$开头 ： 适合工具采集数据，原始类数据

### 每类表大概介绍 ###

sys\_开头是库里的配置表：

sys\_config用于sys schema库的配置

视图：

host : 以IP分组相关的统计信息

innodb : innodb buffer 相关信息

io : 数据内不同维度展的IO相关的信息

memory : 以IP，连接，用户，分配的类型分组及总的占用显示内存的使用

metrics : DB的内部的统计值

processlist : 线程相关的信息(包含内部线程及用户连接）

ps\_ : 没有工具统计的一些变量（没看出来存在的价值）

schema : 表结构相关的信息，例如： 自增，索引， 表里的每个字段类型，等待的锁等等

session : 用户连接相关的信息

statement : 基于语句的统计信息（重店）

statements\_ : 出错的语句，进行全表扫描， 运行时间超长，排序相等（重点）

user\_ : 和host\_开头的相似，只是以用户分组统计

wait :  等待事件，比较专业，难看懂。

waits : 以IP，用户分组统计出来的一些延迟事件，有一定的参考价值。

### Sys库能做什么，那么我们先来看看以下的问题，对于数据库，你有没有以下的疑问？ ###

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

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

3. 机器执行多的SQL语句是什么样？

4. 哪个文件产生了最多的IO，它的IO模式是怎么样的？

5. 那个表的IO最多？

6. 哪张表被访问过最多？

7. 哪些语句延迟比较严重？

8. 哪些SQL语句使用了磁盘临时表

9. 哪张表占用了最多的buffer pool

10. 每个库占用多少Buffer pool

11. 每个连接分配多少内存？

12. MySQL内部现在有多个线程在运行？

要解答上面的这些问题，我们首先需要学习一点基础知识，现在一块看下一些重要的视图结构：

host\_summary

<table style="border-spacing:0px;width:711.111px;color:rgb(51,51,51);font-size:14px;background-color:rgb(255,255,255);"> 
 <tbody> 
 <tr> 
 <td style="margin:0px;">字段名</td> 
 <td style="margin:0px;">意义</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">host</td> 
 <td style="margin:0px;">从哪个服务器上连过来。如果是NULL，表示内部的进程</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Statements</td> 
 <td style="margin:0px;">这台服务器共执行了多少语句（从启动开始统计？）</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Statement_latency</td> 
 <td style="margin:0px;">这台服务器发来等待语句执行的时间</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Statement_avg_latency</td> 
 <td style="margin:0px;">该服务器等待语句执行的平均时间</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Table_scans</td> 
 <td style="margin:0px;">该服务器扫描表的次数（非全表）</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">File_io</td> 
 <td style="margin:0px;">该服务器IO事件请求的次数</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">File_io_latency</td> 
 <td style="margin:0px;">该服务器请求等待IO的时间</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Current_connections</td> 
 <td style="margin:0px;">该服务器当前的连接数</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Total_connections</td> 
 <td style="margin:0px;">该服务器总连接DB共连接多少次</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Unique_user</td> 
 <td style="margin:0px;">该服务器上有几个不同用户名的账户连接过来</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Current_memory</td> 
 <td style="margin:0px;">该服务器上当前连接等占用的内存</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Total_memory_allocated</td> 
 <td style="margin:0px;">该服务器上的请求总共使用的内存</td> 
 </tr> 
 </tbody> 
</table>

Io\_global\_by\_file\_by\_bytes

User\_summary

Memory\_global\_total

Total\_allocated        server总共分配出去的内存（应该是server层）

Memory\_by\_thread\_by\_current\_bytes

Statement\_analysis

<table style="border-spacing:0px;width:711.111px;color:rgb(51,51,51);font-size:14px;background-color:rgb(255,255,255);"> 
 <tbody> 
 <tr> 
 <td style="margin:0px;">字段名</td> 
 <td style="margin:0px;">意义</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Query</td> 
 <td style="margin:0px;">归一化的SQL样子</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Db</td> 
 <td style="margin:0px;">在哪个DB中执行。NULL表示在任何DB</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Full_scan</td> 
 <td style="margin:0px;">全表扫描的次数</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Exec_count</td> 
 <td style="margin:0px;">该SQL执行的总次数</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Err_count</td> 
 <td style="margin:0px;">发生错误的次数</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Warn_count</td> 
 <td style="margin:0px;">发生警告的次数</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Total_latency</td> 
 <td style="margin:0px;">总共发生延迟的实际</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Max_latency</td> 
 <td style="margin:0px;">最大延迟时间</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Avg_latency</td> 
 <td style="margin:0px;">平均延迟时间</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Lock_latency</td> 
 <td style="margin:0px;">因锁等待占用的总时间</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Rows_sent</td> 
 <td style="margin:0px;">执行该SQL返回的总行数</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Rows_sent_avg</td> 
 <td style="margin:0px;">执行该SQL平均返回的行数</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Tmp_tables</td> 
 <td style="margin:0px;">该SQL形成内存临时表的总次数</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Tmp_disk_tables</td> 
 <td style="margin:0px;">该SQL形成文件临时表的总次数</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Rows_sorted</td> 
 <td style="margin:0px;">该SQL总共排序的行数</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Sort_merge_passes</td> 
 <td style="margin:0px;">用于排序中合并的总次数</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Digest</td> 
 <td style="margin:0px;">该语句的hash值</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">First_screen</td> 
 <td style="margin:0px;">该SQL最早出现的时间</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Last_screen</td> 
 <td style="margin:0px;">该SQL最近出现的时间</td> 
 </tr> 
 </tbody> 
</table>

Processlist && session

<table style="border-spacing:0px;width:711.111px;color:rgb(51,51,51);font-size:14px;background-color:rgb(255,255,255);"> 
 <tbody> 
 <tr> 
 <td style="margin:0px;">字段名</td> 
 <td style="margin:0px;">意义</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Thd_id</td> 
 <td style="margin:0px;">内部线程ID</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Conn_id</td> 
 <td style="margin:0px;">连接的ID，对应show processlist中的ID列</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">User</td> 
 <td style="margin:0px;">该线程创建的用户名</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Db</td> 
 <td style="margin:0px;">连接的DB，如果NULL表示后台线程</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Command</td> 
 <td style="margin:0px;">Client发起命令的类型提示</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">State</td> 
 <td style="margin:0px;">命令的状态</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Time</td> 
 <td style="margin:0px;">基于上面的state停留的实际</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Current_statement</td> 
 <td style="margin:0px;">该线程执行的语句</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Statement_latency</td> 
 <td style="margin:0px;">语句运行总共占用时间</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Progress</td> 
 <td style="margin:0px;">该语句运行完成的百分比</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Lock_latency</td> 
 <td style="margin:0px;">该语句用于锁等待的时间</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Rows_examined</td> 
 <td style="margin:0px;">该语句扫描的次数</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Rows_send</td> 
 <td style="margin:0px;">该语句返回的行数</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Rows_affected</td> 
 <td style="margin:0px;">该语句影响到的行数（写入语句）</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Tmp_tables</td> 
 <td style="margin:0px;">形成内存临时表的次数</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Tmp_disk_tables</td> 
 <td style="margin:0px;">形成磁盘临时表的次数</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Full_scan</td> 
 <td style="margin:0px;">全表扫描的次数</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">…</td> 
 <td style="margin:0px;">&nbsp;</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Trx_state</td> 
 <td style="margin:0px;">当前事务的状态</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Pid</td> 
 <td style="margin:0px;">对应到系统里的pid</td> 
 </tr> 
 <tr> 
 <td style="margin:0px;">Program_name</td> 
 <td style="margin:0px;">连接进来的标识名</td> 
 </tr> 
 </tbody> 
</table>

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

Innodb\_buffer\_stats\_by\_schema

Innodb\_buffer\_stats\_by\_table

和innodb\_buffer\_stats\_by\_schema基本一致。只是比上面多了个object\_name指定表名。

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

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

Select\*from host\_summary limit 1\\G

Select\*from io\_global\_by\_file\_by\_bytes limit 1\\G

Select\*from user\_summary limit 1\\G

Select\*from memory\_global\_total;

注意内存部分，不包括innodbbuffer pool。只是server 层申请的内存

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 byexec\_count desc limit 10\\G;

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

select \* from io\_global\_by\_file\_by\_byteslimit 10;(参见上面表格说明)

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

SQL如下：

select \* from statement\_analysis order byexec\_count desc limit 10\\G;

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

statement\_analysis中avg\_latency的最高的。(参考上面写法)

SQL语句：

select \* from statement\_analysis order byavg\_latency desc limit 10;

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

利用statement\_analysis 中tmp\_tables ，tmp\_disk\_tables 进行计算。(参考上面写法)

参考SQL：

select db, query, tmp\_tables,tmp\_disk\_tables  from statement\_analysiswhere 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\_tableorder by pages desc limit 10;

8. 每个库占用多少buffer pool

SQL如下：

select \* frominnodb\_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 processlistgroup by user;