**Mysql5.7新特性之----- 浅谈Sys库**

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL 5.7开始引入了一个新的sys schema,sys是一个MySQL自带的系统库,在安装MySQL 5.7以后的版本,使用mysqld进行初始化时,会自动创建sys库

**Mysql5.7新特性之-----

    浅谈Sys库**

随着mysql5.7的逐渐升温,人们对其也越来越感兴趣,我最近又重新学习了一下5.7版本的 SYS库。

**SYS库介绍
什么是sys库?**
MySQL 5.7开始引入了一个新的sys schema,sys是一个MySQL自带的系统库,在安装MySQL 5.7以后的版本,使用mysqld进行初始化时,会自动创建sys库,
sys库里面的表、视图、函数、存储过程可以使我们更方便、快捷的了解到MySQL的一些信息,比如哪些语句使用了临时表、哪个SQL没有使用索引、哪个schema中有冗余索引、查找使用全表扫描的SQL、查找用户占用的IO等.
Sys库的数据来源:
sys库里这些视图中的数据,都是从information_schema里面获得的,目标是把performance_schema的把复杂度降低,让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况。
Sys库下有两种表
字母开头: 适合人阅读,显示是格式化的数
x$开头 : 适合工具采集数据,原始类数据
这些信息都可以通过show tables;查看或者在information_schema中查看:
select table_name,table_type,engine from
information_schema.tables where
table_schema='sys' order by table_name;
13
14

每类表大概介绍
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或是用户?
    对于该问题可以从host, user, io三个方面去了解,大概谁的请求最多。对于使用资源问题可以直接从下面四个视图里有一个大概的了解。

Select*from host_summary limit 1G
Select*from io_global_by_file_by_bytes limit 1G
Select*from user_summary limit 1G
Select*from memory_global_total;

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

  1. 大部分连接来自哪里及发送的SQL情况
    查看当前连接情况:

select host, current_connections,statements from host_summary;
查看当前正在执行的SQL:
select conn_id, user, current_statement, last_statement from session;

  1. 机器执行最多的SQL语句是什么样?
    例如查一下系统里执行最多的TOP 10 SQL。

SQL如下:
select * from statement_analysis order byexec_count desc limit 10G;

  1. 哪张表的IO最多?哪张表访问次数最多
    • from io_global_by_file_by_byteslimit 10;(参见上面表格说明)

哪张表访问次数最多,可以参考上面先查询执行最多的语句,然后查找对应的表。
SQL如下:
select * from statement_analysis order byexec_count desc limit 10G;

  1. 哪些语句延迟比较严重
    statement_analysis中avg_latency的最高的。(参考上面写法)

SQL语句:
select * from statement_analysis order byavg_latency desc limit 10;

  1. 哪些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;

  1. 哪张表占用了最多的buffer pool
    例如查询在buffer pool中占用前10的表。

SQL如下:
select * from innodb_buffer_stats_by_tableorder by pages desc limit 10;

  1. 每个库占用多少buffer pool
    SQL如下:

select * frominnodb_buffer_stats_by_schema;

  1. 每个连接分配多少内存
    利用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;

  1. MySQL内部现在有多个线程在运行
    MySQL内部的线程类型及数量:

select user, count(*) from processlistgroup by user;

当然要理解上面的问题与方法还需要对一些视图的字段理解其中的意思
例如:

host_summary
字段名 意义
host 从哪个服务器上连过来。如果是NULL,表示内部的进程
Statements 这台服务器共执行了多少语句(从启动开始统计?)
Statement_latency 这台服务器发来等待语句执行的时间
Statement_avg_latency 该服务器等待语句执行的平均时间
Table_scans 该服务器扫描表的次数(非全表)
File_io 该服务器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 写占total里的百分比

当然,要全面理解SYS库的所有视图的含义 并不是一个简短的工程,我提供给大家的只是一个理解的思路,与我个人的一些见解,如果有错的地方,希望大家可以指出,互相学习一下。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
7天前
|
关系型数据库 MySQL 数据库
docker MySQL删除数据库时的错误(errno: 39)
docker MySQL删除数据库时的错误(errno: 39)
60 0
|
7天前
|
存储 JSON 关系型数据库
《MySQL 简易速速上手小册》第9章:高级 MySQL 特性和技巧(2024 最新版)
《MySQL 简易速速上手小册》第9章:高级 MySQL 特性和技巧(2024 最新版)
39 1
|
7天前
|
关系型数据库 MySQL API
Flink CDC产品常见问题之mysql整库同步到starrock时任务挂掉如何解决
Flink CDC(Change Data Capture)是一个基于Apache Flink的实时数据变更捕获库,用于实现数据库的实时同步和变更流的处理;在本汇总中,我们组织了关于Flink CDC产品在实践中用户经常提出的问题及其解答,目的是辅助用户更好地理解和应用这一技术,优化实时数据处理流程。
|
7天前
|
SQL 关系型数据库 MySQL
MySQL8.0索引新特性
MySQL8.0索引新特性
17 0
|
7天前
|
SQL 关系型数据库 MySQL
SQL Error (2013): Lost connection to MySQL server at 'waiting for initial communication packet', sys...
SQL Error (2013): Lost connection to MySQL server at 'waiting for initial communication packet', sys...
|
7天前
|
Java 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口(下)
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口
44 0
|
7天前
|
Java 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口(上)
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口
47 0
|
7天前
|
关系型数据库 MySQL 分布式数据库
PolarDB MySQL版集群版本支持库表恢复功能的版本要求是什么?
【5月更文挑战第13天】PolarDB MySQL版集群版本支持库表恢复功能的版本要求是什么?
10 0
|
7天前
|
存储 关系型数据库 MySQL
学习MySQL(5.7)第二战:四大引擎、账号管理以及建库(干货满满)
学习MySQL(5.7)第二战:四大引擎、账号管理以及建库(干货满满)
|
7天前
|
SQL 安全 关系型数据库
【Mysql-12】一文解读【事务】-【基本操作/四大特性/并发事务问题/事务隔离级别】
【Mysql-12】一文解读【事务】-【基本操作/四大特性/并发事务问题/事务隔离级别】