MySQL 最朴素的监控方式!用完爱不释手

对于当前数据库的监控方式有很多,分为数据库自带、商用、开源三大类,每一种都有各自的特色;而对于 mysql 数据库由于其有很高的社区活跃度,监控方式更是多种多样,不管哪种监控方式最核心的就是监控数据,获取得到全面的监控数据后就是灵活的展示部分。

那我们今天就介绍一下完全采用 mysql 自有方式采集获取监控数据,在单体下达到最快速、方便、损耗最小。

本次文章完全使用 mysql 自带的 show 命令实现获取,从 connects、buffercache、lock、SQL、statement、Database throughputs、serverconfig 7 大方面全面获取监控数据。

连接数(Connects)最大使用连接数:

show status like `Max_used_connections`

当前打开的连接数:

show status like `Threads_connected`

缓存(bufferCache)未从缓冲池读取的次数:

show status like `Innodb_buffer_pool_reads`

从缓冲池读取的次数:

show status like `Innodb_buffer_pool_read_requests`

缓冲池的总页数:

show status like `Innodb_buffer_pool_pages_total`

缓冲池空闲的页数:

show status like `Innodb_buffer_pool_pages_free`

缓存命中率计算:

(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100%

缓存池使用率为:

((Innodb_buffer_pool_pages_total-Innodb_buffer_pool_pages_free)/Innodb_buffer_pool_pages_total)*100%

锁(lock)锁等待个数:

show status like `Innodb_row_lock_waits`

平均每次锁等待时间:

show status like `Innodb_row_lock_time_avg`

查看是否存在表锁:

show open TABLES where in_use>0;

有数据代表存在锁表,空为无表锁

备注:锁等待统计得数量为累加数据,每次获取得时候可以跟之前得数据进行相减,得到当前统计得数据

SQL查看 mysql 开关是否打开:

show variables like `slow_query_log`;

ON 为开启状态,如果为 OFF,set global slow_query_log=1 进行开启

查看 mysql 阈值:

show variables like `long_query_time`;

根据页面传递阈值参数,修改阈值 set global long_query_time=0.1

查看 mysql 慢 sql 目录:

show variables like `slow_query_log_file`;

格式化慢 sql 日志:

mysqldumpslow -s at -t 10 /export/data/mysql/log/slow.log

注:此语句通过 jdbc 执行不了,属于命令行执行。

意思为:显示出耗时最长的 10 个 SQL 语句执行信息,10 可以修改为 TOP 个数。显示的信息为:执行次数、平均执行时间、SQL 语句

备注:当 mysqldumpslow 命令执行失败时,将慢日志同步到本地进行格式化处理。

statementinsert 数量:show status like `Com_insert`

delete 数量:show status like `Com_delete`

update 数量:show status like `Com_update`

select 数量:show status like `Com_select`

吞吐(Database throughputs)发送吞吐量:show status like `Bytes_sent`

接收吞吐量:show status like `Bytes_received`

总吞吐量:Bytes_sent+Bytes_received

数据库参数(serverconfig)show variables

慢 SQL慢 SQL 指的是 MySQL 慢查询,具体指运行时间超过 long_query_time 值的 SQL。

我们常听 MySQL 中有二进制日志 binlog、中继日志 relaylog、重做回滚日志 redolog、undolog 等。针对慢查询,还有一种慢查询日志 slowlog,用来记录在 MySQL 中响应时间超过阀值的语句。

慢 SQL 对实际生产业务影响是致命的,所以测试人员在性能测试过程中,对数据库 SQL 语句执行情况实施监控,给开发提供准确的性能优化意见显得尤为重要。那怎么使用 Mysql 数据库提供的慢查询日志来监控 SQL 语句执行情况,找到消耗较高的 SQL 语句,以下详细说明一下慢查询日志的使用步骤:

确保打开慢 SQL 开关 slow_query_log

设置慢 SQL 域值 long_query_time这个 long_query_time 是用来定义慢于多少秒的才算 "慢查询",注意单位是秒,我通过执行 sql 指令 set long_query_time=1 来设置了 long_query_time 的值为 1, 也就是执行时间超过 1 秒的都算慢查询,如下:

查看慢 SQL 日志路径

通过慢 sql 分析工具 mysqldumpslow 格式化分析慢 SQL 日志

mysqldumpslow 慢查询分析工具,是 mysql 安装后自带的,可以通过./mysqldumpslow -help 查看使用参数说明

常见用法:

取出使用最多的 10 条慢查询

./mysqldumpslow -s c -t 10 /export/data/mysql/log/slow.log

取出查询时间最慢的 3 条慢查询

./mysqldumpslow -s t -t 3 /export/data/mysql/log/slow.log

注意:使用 mysqldumpslow 的分析结果不会显示具体完整的 sql 语句,只会显示 sql 的组成结构;

假如: SELECT FROM sms_send WHERE service_id=10 GROUP BY content LIMIT 0, 1000;

mysqldumpslow 命令执行后显示:

Count: 2 Time=1.5s (3s) Lock=0.00s (0s) Rows=1000.0 (2000), vgos_dba[vgos_dba]@[10.130.229.196]SELECT FROM sms_send WHERE service_id=N GROUP BY content LIMIT N, N

mysqldumpslow 的分析结果详解:

Count:#表示该类型的语句执行次数,上图中表示 select 语句执行了 2 次。

Time:#表示该类型的语句执行的平均时间(总计时间)

Lock:#锁时间 0s。

Rows:#单次返回的结果数是 1000 条记录,2 次总共返回 2000 条记录。

通过这个工具就可以查询出来哪些 sql 语句是慢 SQL,从而反馈研发进行优化,比如加索引,该应用的实现方式等。

常见慢 SQL 排查不使用子查询SELECT FROM t1 WHERE id (SELECT id FROM t2 WHERE name=`hechunyang`);

子查询在 MySQL5.5 版本里,内部执行计划器是这样执行的:先查外表再匹配内表,而不是先查内表 t2,当外表的数据很大时,查询速度会非常慢。

在 MariaDB10/MySQL5.6 版本里,采用 join 关联方式对其进行了优化,这条 SQL 会自动转换为 SELECT t1. FROM t1 JOIN t2 ON t1.id = t2.id;

但请注意的是:优化只针对 SELECT 有效,对 UPDATE/DELETE 子 查询无效, 生产环境尽量应避免使用子查询。

避免函数索引SELECT FROM t WHERE YEAR(d) >= 2016;

由于 MySQL 不像 Oracle 那样?持函数索引,即使 d 字段有索引,也会直接全表扫描。

应改为 > SELECT FROM t WHERE d >= `2016-01-01`;

用 IN 来替换 OR 低效查询慢 SELECT FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;

高效查询 > SELECT FROM t WHERE LOC_IN IN (10,20,30);

LIKE 双百分号无法使用到索引SELECT FROM t WHERE name LIKE `%de%`;

使用

SELECT FROM t WHERE name LIKE `de%`;

分组统计可以禁止排序SELECT goods_id,count() FROM t GROUP BY goods_id;

默认情况下,MySQL 对所有 GROUP BY col1,col2- 的字段进?排序。如果查询包括 GROUP BY,想要避免排序结果的消耗,则可以指定 ORDER BY NULL 禁止排序。

使用 SELECT goods_id,count () FROM t GROUP BY goods_id ORDER BY NULL;

禁止不必要的 ORDER BY 排序SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC;

使用 SELECT count (1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;

总结任何东西不应过重关注其外表,要注重内在的东西,往往绚丽的外表下会有对应的负担和损耗。

mysql 数据库的监控支持通过 SQL 方式从 performance_schema 库中访问对应的表数据,前提是初始化此库并开启监控数据写入。

对于监控而言,不在于手段的多样性,而需要明白监控的本质,以及需要的监控项内容,找到符合自身项目特色的监控方式。

在选择监控工具对 mysql 监控时,需要关注监控工具本身对于数据库服务器的消耗,不要影响到其自身的使用。

作者:安甲舒链接:my.oschina.net/u/4090830/blog/5564849

快速加K8s学习交流群,与大佬共卷!

「点点赞赏,手留余香」

赞赏

  • 旧佬冉谥厥扑06
  • sosoannieso
  • 2人赞过
2
0
0
评论 0 请文明上网,理性发言

相关文章

  • 一背景 在MySQL8.0之前,Server层和存储引擎(比如InnoDB)会各自保留一份元数据(schemaname,tabledefinition等),不仅在信息存储上有着重复冗余,而且可能存在两者之间存储的元数据不同步的现象。不同存储引擎之间(比如InnoDB和MyISAM)有着不同的元数据存储形式和位置(.F
    骏马_大大XT温v 8 0 0 条评论
  • 在说起MySQL,我想每一个开发者都不会陌生,作为一个超80%开发者都在使用的数据库,几乎所有一线大厂,都在用!01多数人只掌握了一些皮毛但是在我使用MySQL将近20年的过程中,我经常能看到很多程序员和我当年一样,有很多共性问题,比如: 我最开始工作面试的时候,天真的以为会写"增删改查"、做表关联就足够了,可悲催的是
    一号特工www 4 1 0 条评论
  • MySQL作为主流的数据库,是各大厂面试官百问不厌的知识点,但是需要了解到什么程度呢?仅仅停留在建库、创表、增删查改等基本操作的水平可不够。在面试后端开发的时候,一连几个问题,简直会被问到一脸懵。。 MySQL语句怎么优化? 分库,分表都适合哪些场景? 讲讲InnoDB如何使用B+树存储的? 一般来说,准备MySQL面
    qwv729 3 3 0 条评论
  • 作者|不剪发的Tony老师责编|欧阳姝黎 出品|CSDN博客 数据库函数是一种具有某种功能的模块,可以接收零个或多个输入值,并且返回一个输出值。MySQL为我们提供了许多用于处理和分析数据的系统函数,本文给大家介绍10个常用的字符串函数,以及相关的其他函数。 CONCAT() CONCAT(str1,str2,-))
    袭ZcPy 8 2 0 条评论
  • 首先问大家一个问题:MySQL当中的"My"是什么意思? 估计大部分人会说:My的意思不就是"我的"吗?MySQL,就是"我的"SQL。 这样想的话,就是大错特错了。 MySQL的发明者名叫Michael"Monty"Widenius,MySQL是以他女儿的名字"My"来命名的。对这位发明者来说,MySQL数据库就仿佛
    manhua猫咪 4 4 0 条评论
  • 应用系统分层架构,为了加速数据访问,会把最常访问的数据,放在缓存(cache)里,避免每次都去访问数据库。 操作系统,会有缓冲池(bufferpool)机制,避免每次访问磁盘,以加速数据的访问。 MySQL作为一个存储系统,同样具有缓冲池(bufferpool)机制,以避免每次查询数据都进行磁盘IO。 今天,和大家聊一
    mw1yf5 4 3 0 条评论
  • 来源:https://jeames.blog.csdn.net/?type=blog 前言MySQL在面试中经常被问到,本文总结了面试中的经典问题。 1.数据库三大范式是什么?第一范式:每个列都不可以再拆分。 第二范式:在第一范式的基础上,非主键列完全依赖于主键,而不能是依赖于主键的一部分。 第三范式:在第二范式的基础
    情商Uk13hf7 8 1 0 条评论
  • 来源|程序员大彬 作者|程序员大彬今天给大家分享MySQL常考的面试题,看看你们能答对多少 本期MySQL面试题的目录如下: 事务的四大特性?事务隔离级别有哪些?索引什么是索引?索引的优缺点?索引的作用?什么情况下需要建索引?什么情况下不建索引?索引的数据结构Hash索引和B+树索引的区别?为什么B+树比B树更适合实现
    勿子糖 6 8 0 条评论
  • 来源|捡田螺的小男孩作者|捡田螺的小男孩每一个好习惯都是一笔财富,本文分SQL后悔药、SQL性能优化、SQL规范优雅三个方向,分享写SQL的21个好习惯。 1.写完SQL先explain查看执行计划。【SQL性能优化】日常开发写SQL的时候,尽量养成这个好习惯呀:写完SQL后,用explain分析一下,尤其注意走不走索
    阿超的小红帽 6 3 0 条评论
  • 又到了一年的金三银四,估计不少人都在暗搓搓地想各种请假理由,准备出去面试。 就在前两天,我跟一个面试官聊,发现一个普遍想象,不少候选人,对数据库的认知,还处在比较基础的阶段,以为会写"增删改查"、做表关联就足够了,那些工作中经常出现的问题,却支支吾吾答不上来,比如: count(*)慢该如何解决? 对于千万级的大表,M
    小薇焚 4 0 0 条评论