mysql监控分析（1）

mysql监控分析

一、体系结构（mysql 5.7）

1、client connectors不属于mysql server层的，理解为不同的客户端

可以用不同的语言进行交互
如果通过java运行数据库，需要添加依赖，maven的文件中添加依赖

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.47</version>
</dependency>

mysql
mysql-connector-java
5.1.47

2、结构分层：连接层、SQL层、存储引擎层、文件系统层

2.1 连接层：入口。功能：

应用程序通过这个入口来连接mysql,处理连接请求(功能检测通信协议是否ok,做一些线程相关处理，对用户密码做检验。
测通信协议检测：看客户端版本是否和sqlserver服务端是否相兼容
线程相关：一个sql连接请求是否分配线程
用户密码做检验,是不是授权连接等

2.2 SQL层

核心功能都在这一层实现：包含权限判断（检测用户是否有访问库、表、表里的数据的权限，有咩有对数据的操作的权限、另外对数据的缓存cache、对数据的解析parser、优化器timizer

2.2.1 Serveices & Utilities

系统管理工具、主要用来做数据备份及恢复、安全、数据集群、分区

2.2.2 SQL Interface

sql接口，用来接收用户的sql命令并把执行结果返回给用户

2.2.3 Parser

对接收的sql进行判断（判断语法是否正确，生成解析树）

2.2.4 Optimizer优化器

对sql进行优化（比如选择索引），生成一个最优的执行计划、通过执行引擎就可以调用存储引擎提供的接口，访问数据。

2.2.5 Caches

查询时，查询缓存里要查询的数据查询的数据直接从缓存里获取数据，不需要经过解析、优化、执行调存储引擎获取数据。
mysqi8.0去掉了查询缓存

2.3 存储引擎层

提供接口给服务层使用，负责数据的存储和提取。
常用的存储引擎是innodb,mysql5.5开始
存储引擎理解：关系型数据库是以表存储、可以理解为表的类型，存储操作表的类型。

2.4 文件系统层

将数据库的数据存储到操作系统的文件系统之上，完成和存储引擎层的交互

3、一个查询sql的执行顺序（执行过程）

首先mysql服务需要先启动起来，默认监听端口是3306

即sql的执行顺序生命周期
客户端进来后到连接器mysqlserver（管理认证等）—到sqlinterface（看sql的操作类型）—若是查询就先从查询缓存中查找（缓存命中：有查询的操作权限，就将数据返回用户）—若缓存没有命中进入到Parser解析器（会对语法进行检测）—Optimizer优化器（会对sql进行优化、怎么执行效率高、就会生成一个最优计划）----通过执行器调用存储引擎提供的接口–磁盘获取数据（把结果更新到缓存同时把数据返回给客户端）

4、监控数据来源

·

4.1 information_schema、performance_schema（库）

保存了所有的表、索引、配置参数、状态值（现在的使用量）
看配置：show global variables;
看状态：show global status;
两者结合用、现在的状态值是否达到最大的配置值，是的话调整配置。
若没有搭建监控工具，可以通过这两个命令关注的变量名所对应的配置值及对应的状态值（看状态值是否达到最大的配置值）

常用的：
show PROCSESSLIST;从information_schema中找的
可以看到各个连接、状态

5、监控数据库之前要做的配置

服务端监控分析（先要看哪个耗时多，若是数据库服务器耗时多，有没有慢sql之类的）
数据库耗时多，业务代码去连数据库做操作（不管是查询、修改）都是执行sql，耗时多，就是sql耗时多，就需要看哪些sql耗时多，缩小排查范围会开启慢查询，只需关注超过阈值的sql。

5.1 看默认是否开启慢sql（slow_query_log默认off）

SHOW VARIABLES LIKE '%QUERY%';

slow_query_log   默认off
long_query_time  默认10s（有点长，根据业务可以修改）

5.2 开启慢sql(两种方式）

6.2.1 临时开启

set global slow_query_log=on;

执行后有一个err（可以不处理）
也可以处理：
set  sql _mode='no_engine_subtitution,strict_trans_tables';

6.2.2 永久开启

改配置文件
[mysqld]
slow_query_log=on
注：需要要重启mysql服务
service mysqld restart

5.3 设置阈值

假设业务规定是100ms，就需要设置阈值

5.3.1 临时设置

set global long_query_time=0.1;

不需要重启mysql服务，需要重新连接

5.3.2 永久设置

[mysqld]
long_query_time=0.1
要重启mysql服务

5.4 设置日志文件路径

日志文件若要改到其他路径就需要改

5.4.1 临时设置

set global slow_query_log_file=‘/mysql/logs/slow.log’

5.4.2 永久设置

slow_query_log_file=/mysql/logs/slow.log
改完后配置需要确认配置是否更新

演示：
执行sql
select SLEEP(10);

5.5 统计慢sql个数

统计超过设置set global long_query_time=0.1;的这个阈值的sql个数

show global status like “%slow_queries%”;

5.6 查看慢sql日志

到设置的路径下查看慢sql日志是否获取到慢sql
每次执行场景时需要把慢sql日志清理干净

tail -f /mysql/logs/slow.log

查看慢日志确定慢sql这种方式比较繁琐
可以用工具mysqldumpslow
Linux服务器上
mysql的bin目录
mysqldumpslow --help

返回记录最多的10条
mysqldumpslow -s r -t 10 /mysql/logs/slow.log
访问次数最多的10条
mysqldumpslow -s c -t 10 /mysql/logs/slow.log
包含join的sql
mysqldumpslow -s t -t 10 -g 'join' /mysql/logs/slow.log

执行结果含义
sql出现次数
sql执行时间
锁时间
结果返回行数
扫码的数据行数
执行用户
执行的sql

通过这种方式就知道慢sql
然后就要深入去分析sql，通过explain看执行计划是怎么样的，是否走了索引，都用到了哪些索引，是否有用到临时表是否做了回表。

监控前配置做好后，就需要做整体监控

5.7 整体监控

通过时间分解发现是哪个服务耗时，就需要看整个服务的耗时情况
整体监控其实更多的是用可视化工具、动态更新数据

5.7.1 命令

5.7.1.1 连接

先看配置再看状态值，配置值和状态值作比较
配置
show variables like “%connec%”

1)max_connections,最大连接数

默认151
数据库服务对外提供连接数最大是151（所有应用）
jdbc是配的是应用的最大连接

修改：临时、永久
临时
set global max_connections=300;
永久
配置文件
max_connections=300

看状态（当前的连接）
show status like “%Threads_conn%”;（当前的连接、实际的连接）

show PROCESSLIST;(当前的明细，当前数据库的会话）
可以看到当前的sql、状态
第一例是id 可以kill id
第二列正在执行的用户
第三列是ip（客户端的IP）
第四列是当前线程使用的数据的库
第五列是执行的命令状态
第六列是状态的持续时间
第七列语句的执行状态（长时间处于发送状态）
info正在执行的sql

2)看锁

重点关注（lock wait看是什么在等待锁，看应用日志、看栈有等待的线程）
information_schema–INNODB_TRX
SELECT * from information_schema.INNODB_TRX;
还有就是更新数据时也会出现锁（多个请求更新数据、会对数据加锁，Innodb是行级锁，进一步参数化数据设置不合理，设置数量过小，很多的线程拿到的数据一样，若是更新操作就会有lock wait，就要具体分析）

压力端的数据量要设置合理：1.数据要足量，要设置多少业务（对数据更新的操作，假如用重复数据就会有锁的等待）2.数据的分布

5.7.1.2 缓存

尽可能把常用数据放到内存里，获取数据会更快。

5.7.1.2.1Querycache：sql层

若应用是查询操作多，需要将querycache开启
查询缓存会缓存sql的查询结果、如缓存命中，且有获取数据的查询，就直接返回数据给用户

若sql语句，某一个字段是大写，对数据库内存部来说是不一样，sql最好完全一样

不会做缓存的如实时计算（看当前计算的时间）、函数

配置
show variables like “%query%”; 需要先看query_cache_type是否先开启
若需要开启
临时：set query_cache_type=on;
配置文件中添加：query_cache_type = 1，重启mysql服务。
说明：这里单纯针对mysql而已，未考虑项目架构，比如缓存可以放redis里面

query_cache_size查询缓存的总内存，单位是字节，默认是1M，一般会调大一些，先做调整。
query_cache_limit超过该大小的不会被缓存
have_query_cache是否支持
query_cache_min_res_unit最小缓存大小，默认4KB

5.7.1.2.2缓存命中率

还要看一下缓存命中率（看一下状态值）
show status like “%Qcache%”;
Qcache_free_memory 还剩多少的内存
Qcache_hits 缓存命中次数
Qcache_inserts 未命中然后插入
Qcache_lowmem_prunes 命中率= Qcache_hits/(Qcache_hits + Qcache_inserts)

缓存内存不足被清除

Innodb_buffer_pool：存储引擎层（用来在内存中缓存表的数据、表的索引、目的是操作在内存中完成，避免磁盘io）
配置
show variables like “%innodb_buffer_pool%”;

innodb_buffer_pool_size
Innodb_buffer_pool的大小 单位是字节 默认是128M

状态
show status like ‘%Innodb_buffer_pool%’;

Innodb_buffer_pool_reads 从磁盘获取到数据的次数
Innodb_buffer_pool_read_requests 总共查询bp的次数
命中率=1-（reads/requests)
命中率低于90%或低于95%，就需要将buffer_pool调大

二者区别
Qcache是sql层，查询缓存。自带的缓存，缓存的是sql语句和对应的结果集（缓存键值对)
若表里的数据发生变化，缓存会失效，下次查询就先去查querycache，没有就直接走解析、优化、执行，从磁盘获取数据把查询结果缓存到querycache，再把结果返回给客户端。
若是查询操作多，将这个querycache开启，提高执行效率。

innodb_buffer_pool 存储引擎层，缓存的是表里的数据。
表里的数据有很多，innodb_buffer_pool 应是设置的越大越好，这样就避免了磁盘io，从mem获取数据会更快。
常见设置为服务器mem的7-80%

命令结果不是很好。

整体监控一般是用可视化监控

5.7.2 可视化监控mysql_exportor+Prometheus+Grafana

1）搭建

https://www.cnblogs.com/uncleyong/p/13192456.html
第一步：需要安装mysql_exportor
第二步：在Prometheus的yum做配置
第三步：添加新的模板

2）监控指标说明

先检查mysql_exporter的端口9104

netstat -lntp | grep 9104

再检查prometheus服务的端口9090

netstat -lntp | grep 9090

再检查grafana服务的端口3000

netstat -lntp | grep 3000

mysql可视化监控页面

①每秒执行的语句数量

②Current QPS（每秒执行的语句数量）

③MySQL Connections（mysql连接数）

第二个是max use connections服务器启动以来同时使用的最大连接数

第三个是max connections mysql服务对外提供的最大连接数,默认是151（因为数据库服务不一定是一个应用在使用，数据库上可以建立多个数据库，不同应用建立不同数据库)

第一个是connections，表示的是mysql的连接数，这个连接要和项目中的jdbc连接最大连接（maxActive）相比，如果和jdbc最大连接相等同时小于数据库中的max connections，需要将jdbc中的连接数调大
springboot项目中的ymal文件配置
maxActive最大活跃连接

查看连接数

show variables like '%max_connections%';

修改连接数（在mysql中的配置文件my.cnf进行修改）

max_connections = 300

④Mysql Select Types（看是否有全表扫描）

⑤MySQL Slow Queries（查看慢查询使用情况）

查看这个指标，就需要先去配置慢sql是否开启
命令查看慢查询show variables like '%query%';

⑥MySQL Table Locks（表级锁使用情况）

Waited：表锁等待，值越小越好，如果此值较大，表示索引设计不佳或者有过多慢sql

⑦MySQL Network Traffic（mysql网络流量）

⑧Top Command Counters

⑨MySQL Query Cache Memory（查询缓存）

关于query_cache_size是否需要调大,也需要根据缓存命中率进行对比
数据库属于io密集型应用，尽可能将磁盘io转成内存io
如果应用查询操作多，可以将查询缓存开启。
查询缓存优点：可以完全缓存select查询结果，如果命中缓存，mysql就会立即返回结果，直接跳过解析优化执行操作

缺点：如果某个表中数据发生了变化，就会导致所有引用了该表的select语句在查询缓存中缓存的数据失效，若数据变化频繁，带来的开销也比较大

free memory：
query cache size：默认是1M,可以配置大一点
除了以上可视化监控看到的还可以通过命令看配置值
SHOW VARIABLES LIKE '%query_cache%';
query_cache_type 是on，表示开启查询缓存，默认OFF

开启：修改配置文件
query_cache_type = 1

query_cache_limit****缓存块的大小设置，超过该大小不会被缓存

query_cache_min_res_unit（默认4K）每个qcache最小的缓存空间大小

⑩MySQL Query Cache Activity（缓存命中率）

也可以通过命令方式查看缓存命中率

SHOW STATUS LIKE 'Qcache%';

重点关注Qcache_hits(缓存次数）、Qcache_insert（未命中插入）
缓存命中率=Qcache_hits(缓存次数）/Qcache_hits(缓存次数）+Qcache_insert（未命中插入）
缓存命中率越高越好

⑪ MySQL open Files（mysql进程打开的文件数，默认是65535）

如果这个open Files值和open_files_limit相等，就需要进行open_files_limit值调大
配置值查看

show variables like '%files%';

状态值查看

show status like '%files%';

⑫MySQL Open Tables（ 打开的表的数量，默认是2k）

如果这个值和已经配置的值相等，需要将配置值调大

看配置值

show variables like '%cache%';

⑬InnoDB Buffer Pool Size（innodb缓冲池）

从mysql5.5开始InnoDB就作为默认的存储引擎
buffer_poor是InnoDB的缓存池，在内存中缓存表数据和索引，减少磁盘io

配置值（默认128M）SHOW GLOBAL VARIABLES like "%innodb_buffer_pool_size%"；大小设置的越大越好，一般设置成物理服务器内存的70%左右
状态值show status like '%Innodb_buffer_pool%';
重点关注：innodb_buffer_pool_reads（从物理磁盘获取到的次数）和innodb_buffer_pool_read_requests(总共查询buffer_pool的次数）

*通过这两个参数可以得到
buffer_pool命中率=(1-innodb_buffer_pool_reads/innodb_buffer_pool_read_requests)100%
一般缓存命中率越大越好，若低于90%，就需要考虑加大缓冲池

⑭mysql uptime（运行时长）

mysql服务启动到现在的时长

重点
关注:
select type可以看到是否有全表扫描
slow queries可以看慢查询情况，越多，SQL耗时越多（前提是慢查询开启）

连接
mysql connections
最大连接是151
connnections，msql的连接数（当前的连接数）
max used connnections，msql的服务器从启动开始时到现在同时使用的最大连接数（服务器从启动-到当前的的connnection的最大值）
若connnections这个值与jdbc中配置的值相等，连接达到了最大，应该小于数据库对外提供的连接数），前提是不是有多个应用连接，专门供某一个应用使用）

mysql questions
服务器执行的语句，总共执行的语句数

thread cache
线程缓存
当客户端断开连接时，没有满，客户端的线程就会被放在这里，下一次在连就更快，从缓存获取

temporary Objects
临时对象

Aborted connections
终止连接

MySQL table locks重点关注（表级锁的一个使用情况）
锁相关
innodb有表级锁、行级锁

lock wait 表示表锁的等待，越小越好，越大（锁竞争越大）

Network Traffic
网络流量

Network Usage Hourly每小时统计网络流量

msql Internal Memory Overview 内存使用情况

top Command Counters命令统计
可以看到哪种操作类型多

MySQL query cache memory 查询缓存情况，默认是1M

MySQL open files 打开的文件数针对的是mysql进程，一般需要调大，设置65535

table Openings
MySQL open Cache Status 正在打开表的命中率

mysql open tables 打开表过多，也会有瓶颈，默认值2000,若看到快达到2000就需要调大。