MySQL 5.7系列之sys schema(2)

时间:2021-12-02 04:20:22

0、导读

MySQL 5.7引入了sys schema,有了它,我们排查分析一些问题时将更得心应手。sys schema里主要存储的是视图、存储过程、函数等。

视图:用于结果的汇总展示及配置持久化;

存储过程:用于对Performance schema的控制及收集;

函数:对于Performance schema的配置及数据格式化。

本文约2000字,阅读时间约10分钟。

1、本文目标

上篇我们只是先简单介绍了下sys schema可以做什么,以及几个基础概念,本文继续深入介绍sys schema,回答上篇我们留下的几个问题:

  • 哪个用户或者来自哪个IP的客户端使用了最多的资源;

  • 数据库连接来自哪里,以及这些连接对数据库的请求情况是怎样的;

  • 数据库中哪些SQL被频繁执行;

  • 哪个文件产生了最多的IO,读多,还是写的多;

  • 哪个表上的IO请求最多;

  • 哪个表被访问的最多;

  • 哪些语句延迟比较严重;

  • 哪些SQL语句使用了临时表,又有哪些用到了磁盘临时表;

  • 哪个表占用了最多的buffer pool;

  • 每个库(database)占用多少buffer pool;

  • 每个连接分配多少内存;

  • MySQL内部有多个线程在运行;

  • 其他...

2、基本视图介绍

  • host_summary视图

字段名

意义

host

从哪个客户机上连过来。如果是NULL,表示内部的进程

statements

该客户机共执行了多少语句

statement_latency

该客户机发来等待语句执行的时间

statement_avg_latency

该客户机等待语句执行的平均时间

table_scans

该客户机发生全表扫描的次数

file_ios

该客户机上IO事件请求的次数

file_io_latency

该客户机请求等待IO的时间

current_connections

该客户机当前的连接数

total_connections

该客户机连接DB共有多少次

unique_user

该客户机上有几个不同用户名的帐户连接过来

current_memory

该客户机上当前连接占用的内存

total_memory_allocated

该客户机上的请求总共使用的内存量(历史累计值)

  • io_global_by_file_by_bytes视图

file

被操作的文件名

count_read

总共有多少次读

total_read

总共读了多少字节

avg_read

平均每次读的字节数

count_write

总共有多少次写

total_written

总共写了多少字节

avg_write

平均每次写的字节大小

total

读和写总共的IO大小

write_pct

写请求占总IO请求中的百分比(就是通常所说的读写比)

  • user_summary视图

user

客户端连接过来的用户名,如果是NULL,表示内部进程

statements

该用户执行了多少SQL

statement_latency

该用户执行SQL的总延迟时间

statement_avg_latency

该用户执行SQL的平均延迟时间

table_scans

该用户执行SQL时发生全表扫描的次数

file_ios

该用户发生的IO请求总量

file_io_latency

该用户发生的IO请求总延迟时间

current_connections

该用户当前的连接

total_connections

该用户总的连接数

unique_hosts

该用户从几个不同客户机连接过来

current_memory

该用户当前占用的内存

total_memory_allocated

该用户总共申请到的内存(历史累计值)

  • memory_global_total视图

total_allocated

Server总共分配出去的内存大小

  • memory_by_thread_by_current_bytes视图

thread_id

MySQL内部线程ID,可以和session视图中的thd_id关联

user

当前线程是哪个用户创建

current_count_used

当前线程正在使用且未释放的内存块(内存块不是固定大小的)

current_allocated

当前线程正在使用且未释放的内存块大小(字节)

current_avg_alloc

当前线程每个内存块平均分配内存大小(字节)

current_max_alloc

当前线程单次曾经分配的最多内存大小(字节)

total_allocated

当前线程总共分配的内存大小

  • statement_analysis视图

query

格式化后的SQL(将SQL中的参数替换成?)

db

在哪个DB中执行,如果为NULL表示在任何DB

full_scan

是否使用了全表扫描

exec_count

该SQL被执行的总次数

err_count

发生错误的次数

warn_count

发生警告的次数

total_latency

总共发生延迟的时间

max_latency

最大延迟的时间

avg_latency

平均延迟的时间

lock_latency

因锁等待占用的总时间

rows_sent

执行该SQL返回的总行数

rows_sent_avg

执行该SQL平均返回的行数

rows_examined

执行该SQL扫描的总行数

rows_examined_avg

执行该SQL平均每次扫描的行数

tmp_tables

该SQL生成内存临时表的总次数

tmp_disk_tables

该SQL生成磁盘文件临时表的总次数

rows_sorted

该SQL总共排序的行数

sort_merge_passes

用于排序中合并的总次数

digest

该语句的HASH值

first_seen

该SQL最早出现的时间

last_seen

该SQL最近出现的时间

  • processlist视图

thd_id

内部线程ID

conn_id

连接的ID(对应show processlist中的"Id"列)

user

该线程创建的用户名

db

连接的DB,如果是NULL表示后台线程

command

Client发起命令的类型提示

state

命令的状态

time

基于上面state停留的时间

current_statement

该线程执行的语句

statement_latency

语句运行总共占用时间

progress

该语句运行完成的百分比

lock_latency

该语句用于锁等待的时间

rows_examined

该语句扫描的行数

rows_send

该语句返回的行数

rows_affected

该语句影响到的行数(写入语句)

tmp_tables

形成内存临时表的次数

tmp_disk_tables

形成磁盘临时表的次数

full_scan

全表描扫的次数

last_statement

上一条被执行的SQL

last_statement_latency

上一条被执行的SQL时延

current_memory

当前线程占用的内存

last_wait

上一次等待事件

last_wait_latency

上一次等待时间时延

source

上一次等待事件对应的源码位置

trx_latency

事务时延

trx_state

当前事务状态

pid

对应到系统里的pid

program_name

连接进来的标识名

  • session视图

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

  • innodb_buffer_stats_by_schema视图

object_schema

schema名

allocated

该schema上分配的buffer pool大小(字节)

data

该schema中缓存的数据大小(字节)

pages

该schema中缓存的data page数

pages_hashed

该schema中分配了多少hashed page

pages_old

该schema中的old page数量

rows_cached

该schema中缓存的row data数量

  • innodb_buffer_stats_by_table视图

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

3、sys schema运用

上面把几个基础的视图都介绍完了,接下来就可以回答上篇提出的那些问题了。

  • 哪个用户或者来自哪个IP的客户端使用了最多的资源

查看每个客户端IP过来的连接消耗了多少资源:MySQL 5.7系列之sys schema(2)

查看某个数据文件上发生了多少IO请求:MySQL 5.7系列之sys schema(2)

查看哪个用户消耗了多少资源:MySQL 5.7系列之sys schema(2)

查看总共分配了多少内存:MySQL 5.7系列之sys schema(2)

  • 数据库连接来自哪里,以及这些连接对数据库的请求情况是怎样的

查看当前连接情况:

select host, current_connections, statements from host_summary;

MySQL 5.7系列之sys schema(2)

查看当前正在执行的SQL:

select  conn_id, user, current_statement, last_statement from session;

和执行show full processlist的效果相当。

  • 数据库中哪些SQL被频繁执行

执行下面命令查询TOP 10最热SQL:

select db,exec_count,query from statement_analysis order by exec_count desc limit 10;

MySQL 5.7系列之sys schema(2)

  • 哪个文件产生了最多的IO,读多,还是写的多

select * from io_global_by_file_by_bytes limit 10;

  • 哪个表上的IO请求最多

select * from io_global_by_file_by_bytes where file like '%ibd' order by total desc limit 10;

  • 哪个表被访问的最多

先访问statement_analysis,根据热门SQL排序找到相应的数据表。

  • 哪些语句延迟比较严重

查看 statement_analysis 中 avg_latency的最高的SQL:

select * from statement_analysis order by avg_latency desc limit 10;

  • 哪些SQL语句使用了临时表,又有哪些用到了磁盘临时表

查看 statement_analysis 中哪个SQL的 tmp_tables 、tmp_disk_tables  值大于0即可:

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;

  • 哪个表占用了最多的buffer pool

select * from innodb_buffer_stats_by_table order by allocated desc limit 10;

  • 每个库(database)占用多少buffer pool

select * from innodb_buffer_stats_by_schema order by allocated desc limit 10;

  • 每个连接分配多少内存

利用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 memory_by_thread_by_current_bytes a, session b where a.thread_id = b.thd_id;

MySQL 5.7系列之sys schema(2)

  • MySQL内部有多个线程在运行

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

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

写到这里,其实我们只涉及到 sys schema 很少的一部分功能。估计MySQL的官方AWR也要快出现了,大家也可以期待一下(MySQL 8.0这块又进一步规范了一下)

MySQL 5.7系列之sys schema(1)

阅读原文