学习使用MySQL 5.7的sys库
来源:互联网 发布:淘宝客服月度工作总结 编辑:程序博客网 时间:2024/05/01 00:58
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
字段名
意义
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里的百分比
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总共分配出去的内存(应该是server层)
Memory_by_thread_by_current_bytes
字段名
意义
Thread_id
内部线程ID可以和session中的thd_id关联
User
这个线程是哪个用户创建的
Current_count_used
当前使用的内存块还没有释放
Current_allocated
当前分配的内存大小(字节)而且没有被释放出来
Current_avg_alloc
平均分配的blocks
Current_max_alloc
当前线程分配的最多内存
Total_allocated
当前连总共分配的内存大小
Statement_analysis
字段名
意义
Query
归一化的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平均返回的行数
Tmp_tables
该SQL形成内存临时表的总次数
Tmp_disk_tables
该SQL形成文件临时表的总次数
Rows_sorted
该SQL总共排序的行数
Sort_merge_passes
用于排序中合并的总次数
Digest
该语句的hash值
First_screen
该SQL最早出现的时间
Last_screen
该SQL最近出现的时间
Processlist && session
字段名
意义
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
全表扫描的次数
…
Trx_state
当前事务的状态
Pid
对应到系统里的pid
Program_name
连接进来的标识名
Session和processlist视图基本一样,只是把后台线程过滤掉。
Innodb_buffer_stats_by_schema
字段名
意义
Object_schema
库名
Allocated
基于库分配的buffer pool大小
Data
基于schema实际缓存的数据大小
Pages
当前schema缓存的page数
Pages_hashed
Buffer pool中进行hash 索引的page
Pages_old
Buffer pool中的旧页,可能被置换出去
Rows_cached
Buffer pool中以行为单位的缓存
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;
- 学习使用MySQL 5.7的sys库
- 学习使用MySQL 5.7的sys库--补充
- MySQL 5.7中sys是一个MySQL自带的系统库
- python学习4:sys标准库模块使用
- MySQL 5.7 SYS系统SCHEMA
- MySQL- 5.7 sys schema笔记
- MySQL- 5.7 sys schema笔记
- MySQL- 5.7 sys schema笔记
- Python的sys.argv学习
- sys.dm_exec_cached_plans 视图的使用
- Python sys.path的使用
- mysql 5.7新数据库sys解析
- python学习笔记27(python中sys模块的使用)
- sys.sysobjects (Transact-SQL)的学习
- SQL SERVER 2005 SYS.SYSPROCESSES 的使用
- sys fs的原理和使用
- Flex中sys:DataGrid标签的使用
- 【oracle】使用sys进行的操作
- 一个经典例子让你彻彻底底理解java回调机制
- struts2 action中获取不到从jsp页面提交过来的bean
- Linux中的文件描述符与打开文件之间的关系
- [Docker]4.Docker 镜像使用
- 当REStful混合上传文件时,应该如何让jsp页面提交方法和后台方法统一
- 学习使用MySQL 5.7的sys库
- IOS TextField伴随键盘移动
- Openfoam学习记录(2017.06.18)(并行时的manual分区)
- React实现Angularjs ng-show、ng-if和ng-hide
- 微信小程序的上传
- IOS RunLoop 常驻线程的实现
- 考研之华南理工
- TableLayout的基本使用方式
- nginx+keepalived搭建高可用负载均衡(双主模式)