学习使用MySQL 5.7的sys库

发布时间:2017-6-29 10:27:28编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"学习使用MySQL 5.7的sys库",主要涉及到学习使用MySQL 5.7的sys库方面的内容,对于学习使用MySQL 5.7的sys库感兴趣的同学可以参考一下。

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;


上一篇:AnnotationConfigApplicationContext的register方法
下一篇:根据GUID找到位于那张表,那一列

相关文章

相关评论

本站评论功能暂时取消,后续此功能例行通知。

一、不得利用本站危害国家安全、泄露国家秘密,不得侵犯国家社会集体的和公民的合法权益,不得利用本站制作、复制和传播不法有害信息!

二、互相尊重,对自己的言论和行为负责。

好贷网好贷款