mysql sql语句digest收集与展示

article/2025/11/8 15:59:21

1.前言

mysql慢查询，已经有现成的成熟的方案收集展示了：pt-query-digest结合box公司的anemometer，没用过的移步：《mysql慢查询可视化》（本章内容需要提前了解anemometer）。
但DBA们一定还遇到过这样的问题：某个时间段CPU负载较高，但慢查却没有。这种情况一般是由高并发的但单个性能正常的SQL导致的，所以慢查没有，但总体负载会升高。
那怎么办呢？
选择1：配置events_statements_history_long，缺点明显：数据很容易被覆盖，查看不便。
选择2：之前本人参考oracle原生的一个功能，每10s自动抓一次活动会话，记录到表中，但有个明显的缺点：从库event_scheduler都是不开的，导致从库没法做。
针对该痛点，本人设计了一个定时收集events_statements_summary_by_digest，并通过amemometer展示的方案。

2.功能展示

选择时间段、host(沿用的慢查中的名称，理解为mysql实例)：

展示每个digest的总次数、最高执行频率（按分钟）、平均耗时（毫秒）

点击checksum，查看某个SQL执行频率的走势图，展示每分钟执行的次数

按天聚合：


如果想查看执行频率波动有异常，可以在having中添加条件：
最高频率是平均频率的3倍：max(ts_cnt) > (sum(ts_cnt)/count(*)) * 3

3.实现逻辑

3.1.数据来源

events_statements_summary_by_digest
默认performance_schema_digests_size=10000，SQL digest较多的库要调整到20000以上；
参数不能动态调整，没有停机时间的实例可以监控记录数，满了truncate即可。


3.2.表结构

global_query_review、global_query_review_history直接沿用慢查的表结构，字段不变;
db_instance实：例配置表，记录实例的信息，python自动定时扫描该表。

3.3.python程序

python程序由两个JOB构成：
job1:check_db_pool：定时扫描db_instance表，更新连接池
job2:handle_db_all: 定时处理所有实例的数据入库

job1
目标库连接池存放在字典：db_pool_dic
获取实例ip\端口等信息，包括ischanged(最近1分钟实例信息是否改变)：get_instance()
按顺序处理一下逻辑：

  • 在db_pool_dic中，但不在get_instance()中的，从db_pool_dic中删除
  • 检查db_pool_dic连接池的有效性(select 1)，无效则删除
  • 在get_instance()中，ischanged="changed"，且在db_pool_dic中的，从db_pool_dic中删除
  • 在get_instance()中，但不在db_pool_dic中，创建连接池，增加到db_pool_dic

job2
遍历get_instance(),以线程方式处理每个目标库：handle_db
handle_db主要任务是获取最近1分钟内每个digest执行次数的增量，入库
增量是通过连续2次获取的digest执行次数相减得到
按顺序执行以下过程：

  • 从redis中获取上次set的digest信息：df_full_last_bytes=rs.get(redis_key_name)
  • 查询digest中LAST_SEEN>now()-1mins的数据：df_1min
  • 查询digest全量信息：df_full
  • 如果df_full_last_bytes为空：return
  • df_full_last_bytes与df_1min关联，计算增量
  • redis set df_full_last_bytes,有效期90秒

补充2个SQL

df_full:
select concat('instance_name','-',digest,'-',ifnull(schema_name,'unknow')) checksum ,sum(count_star) count_star
from events_statements_summary_by_digest where digest is not NULL
group by checksumdf_1min:
select concat('instance_name ','-',digest,'-',ifnull(schema_name,'unknow')) checksum,ifnull(schema_name,'unknow') as db_max,
count_star,digest_text,round(avg_timer_wait/1000000000,1) query_time_avg
from events_statements_summary_by_digest
where LAST_SEEN > DATE_SUB(now(),INTERVAL 1 minute)
and digest is not NULL

4. 表结构

创建管理库：digest_stat

4.1. db_instance

instance_name：自定义的实例名，唯一约束；

update_time数据变更后自动更新，python程序根据该字段更新连接池配置信息；

status：目标库是否激活，启用。

CREATE TABLE `db_instance` (`id` int(11) NOT NULL AUTO_INCREMENT,`instance_name` varchar(50) COLLATE utf8mb4_bin NOT NULL,`ip_addr` varchar(15) COLLATE utf8mb4_bin NOT NULL,`port` int(11) NOT NULL,`user_name` varchar(50) COLLATE utf8mb4_bin NOT NULL,`password` varchar(50) COLLATE utf8mb4_bin NOT NULL,`status` int(1) NOT NULL DEFAULT '0' COMMENT '0:active, 1:inactive',`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`id`),UNIQUE KEY `idxu_instancename` (`instance_name`),UNIQUE KEY `idx_ip_port` (`ip_addr`,`port`)
) ENGINE=InnoDB

4.2. global_query_review

CREATE TABLE `global_query_review` (`checksum` varchar(200) NOT NULL,`fingerprint` text NOT NULL,`sample` longtext,`first_seen` datetime DEFAULT NULL,`last_seen` datetime DEFAULT NULL,`reviewed_by` varchar(20) DEFAULT NULL,`reviewed_on` datetime DEFAULT NULL,`comments` text,`reviewed_status` varchar(24) DEFAULT NULL,PRIMARY KEY (`checksum`)
) ENGINE=InnoDB

4.3.global_query_review_history

 

CREATE TABLE `global_query_review_history` (`hostname_max` varchar(64) NOT NULL,`db_max` varchar(64) DEFAULT NULL,`checksum` varchar(200) NOT NULL,`sample` longtext,`ts_min` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',`ts_max` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',`ts_cnt` float DEFAULT NULL,`query_time_avg` float DEFAULT NULL,UNIQUE KEY `hostname_max` (`hostname_max`,`checksum`,`ts_min`,`ts_max`),KEY `ts_min` (`ts_min`),KEY `checksum` (`checksum`)
) ENGINE=InnoDB

该表记录数会很多，我司的3个库，保留了2天数据，记录数分别为41w,84w,163w

因此：
1，一定要对该表自动清理，一般不要超过7天；
2、可以调整python数据抽取策略，如每分钟超过30次的才收集，平均耗时大于1毫秒的才收集，等等

5. Anemometer程序调整

在慢查调整过的基础上，再做以下调整：

1. conf/datasource_slowlog.inc.php

修改数据库信息

2. conf/config.inc.php 

$conf['history_defaults'] = array(
'table_fields' => array('date', 'cnt','max_freq','first_seen','last_seen','query_time_avg')$conf['report_defaults'] = array('fact-order'    => 'cnt DESC','table_fields' => array('checksum','hostname','db','sql_short','cnt','max_freq','first_seen','last_seen','query_time_avg'),'dimension-hostname_max' => '一个默认的实例名称'   ##  指定实例默认值，否则默认查所有数据，响应慢'custom_fields' => array('checksum' => 'checksum','hostname' => 'hostname_max','db' => 'db_max','sql_short' => 'LEFT(fact.sample,30)','cnt' => 'sum(ts_cnt)','max_freq' => 'max(ts_cnt)','query_time_avg' => 'ROUND(avg(query_time_avg),1)','first_seen'  => 'substring(min(ts_min),1,16)','last_seen'  => 'substring(max(ts_max),1,16)','date' => 'substring(ts_min,1,10)','minute_ts'     => 'round(unix_timestamp(substring(ts_min,1,16)))'),

    3. lib/Anemometer.php 

    private function translate_checksum($checksum){{//throw new Exception("Invalid query checksum");return $checksum;}}

最后，具体python程序见：https://github.com/meishd/mysql_allsql_digest/

 


http://chatgpt.dhexx.cn/article/jcNdjkE6.shtml

相关文章

HTTP Digest authentication

HTTP Digest authentication

什么是摘要认证 摘要认证（ Digest authentication）是一个简单的认证机制，最初是为HTTP协议开发的，因而也常叫做HTTP摘要，在RFC2617中描述。其身份验证机制很简单，它采用杂凑式（hash&#xff09…
阅读更多...
配置Apache Digest认证

配置Apache Digest认证

Apache常见的用户认证可以分为下面三种： 基于IP，子网的访问控制(ACL)基本用户验证(Basic Authentication)消息摘要式身份验证(Digest Authentication) 消息摘要式身份验证(Digest Authentication) Digest Authentication在基本身份验证上面扩展了安全…
阅读更多...
http Digest认证计算方法整理

http Digest认证计算方法整理

摘要认证及实现HTTP digest authentication - 简书 HTTP Basic和Digest认证介绍与计算 - 诸子流 - 博客园 不要不知道上面说的URI是什么意思啊 图解HTTP 第 8 章 确认访问用户身份的认证 - 简书8.1 何为认证 为了弄清究竟是谁在访问服务器，就得让对方的客户端自报家…
阅读更多...
业务维度digest日志的记录与监控方案

业务维度digest日志的记录与监控方案

需求 ​   为了满足从业务整体的维度 实现监控和链路复原，我们希望对于一个业务接口，记录一行请求日志，并通过某个 Unique Id（如UserId、OrderId）将多行日志关联起来，最终产出一批和业务强相关的数据&am…
阅读更多...
java发起Digest Auth请求

java发起Digest Auth请求

常规认证方式 上代码&#xff1a; 需要的Maven <dependency><groupId>org.apache.httpcomponents</groupId><artifactId>httpclient</artifactId><version>4.5</version></dependency><dependency><groupId>org.…
阅读更多...
认证学习3 - Digest摘要认证讲解、代码实现、演示

认证学习3 - Digest摘要认证讲解、代码实现、演示

文章目录 Digest摘要认证 - 密文讲解&#xff08;Digest摘要认证&#xff09;实现&#xff08;Digest认证&#xff09;代码&#xff08;Digest认证&#xff09;代码&#xff08;Digest认证-客户端&#xff09;演示&#xff08;Digest认证-postman&#xff09; 认证大全&#xf…
阅读更多...
HTTP的认证方式之DIGEST 认证（摘要认证）

HTTP的认证方式之DIGEST 认证（摘要认证）

核心步骤&#xff1a; 步骤 1&#xff1a; 请求需认证的资源时&#xff0c;服务器会随着状态码 401Authorization Required&#xff0c;返回带WWW-Authenticate 首部字段的响应。该字段内包含质问响应方式认证所需的临时质询码&#xff08;随机数&#xff0c;nonce&#xff09;…
阅读更多...
Digest Auth 摘要认证

Digest Auth 摘要认证

Digest Auth 摘要认证 1.非常规方式 转载&#xff1a;https://blog.csdn.net/qq_25391785/article/details/86595529 public static void postMethod(String url, String query) {try {CredentialsProvider credsProvider new BasicCredentialsProvider();credsProvider.setC…
阅读更多...
digest鉴权

digest鉴权

“摘要”式认证&#xff08; Digest authentication&#xff09;是一个简单的认证机制&#xff0c;最初是为HTTP协议开发的&#xff0c;因而也常叫做HTTP摘要&#xff0c;在RFC2671中描述。其身份验证机制很简单&#xff0c;它采用杂凑式&#xff08;hash&#xff09;加密方法&…
阅读更多...
消息摘要（Digest），数字签名（Signature），数字证书（Certificate）是什么？

消息摘要（Digest），数字签名（Signature），数字证书（Certificate）是什么？

1. 消息摘要&#xff08;Digest&#xff09; 1. 什么是消息摘要&#xff1f; 对一份数据&#xff0c;进行一个单向的 Hash 函数&#xff0c;生成一个固定长度的 Hash 值&#xff0c;这个值就是这份数据的摘要&#xff0c;也称为指纹。 2. 摘要算法 常见的摘要算法有 MD5、SHA…
阅读更多...
HTTP通讯安全中的Digest摘要认证释义与实现

HTTP通讯安全中的Digest摘要认证释义与实现

摘要 出于安全考虑&#xff0c;HTTP规范定义了几种认证方式以对访问者身份进行鉴权&#xff0c;最常见的认证方式之一是Digest认证 Digest认证简介 HTTP通讯采用人类可阅读的文本格式进行数据通讯&#xff0c;其内容非常容易被解读。出于安全考虑&#xff0c;HTTP规范定义了几…
阅读更多...
http协议之digest(摘要)认证，详细讲解并附Java SpringBoot源码

http协议之digest(摘要)认证，详细讲解并附Java SpringBoot源码

目录 1.digest认证是什么&#xff1f; 2.digest认证过程 3.digest认证参数详解 4.基于SpringBoot实现digest认证 5.digest认证演示 6.digest认证完整项目 7.参考博客 1.digest认证是什么&#xff1f; HTTP通讯采用人类可阅读的文本格式进行数据通讯&#xff0c;其内容非…
阅读更多...
【WinRAR】WinRAR 6.01 官方最新简体中文版

【WinRAR】WinRAR 6.01 官方最新简体中文版

WinRAR 6.01 官方简体中文商业版下载地址&#xff08;需要注册&#xff09;&#xff1a; 64位&#xff1a; https://www.win-rar.com/fileadmin/winrar-versions/sc/sc20210414/wrr/winrar-x64-601sc.exe https://www.win-rar.com/fileadmin/winrar-versions/sc/sc20210414/…
阅读更多...
WinRAR命令行

WinRAR命令行

基本使用 实践 将文件夹压缩到zip包 输入&#xff1a;文件夹如下&#xff0c;文件夹为class。 输出&#xff1a;classes.zip 指令如下&#xff1a; rar a classes.zip .\classes或者 WinRAR a classes.zip .\classes结果如下&#xff1a; PS C:\Users\liyd\Desktop\kuai…
阅读更多...
WinRAR安装教程

WinRAR安装教程

文章目录 WinRAR安装教程无广告1. 下载2. 安装3. 注册4. 去广告 WinRAR安装教程无广告 1. 下载 国内官网&#xff1a;https://www.winrar.com.cn/ 2. 安装 双击&#xff0c;使用默认路径&#xff1a; 点击“安装”。 点击“确定”。 点击“完成”。 3. 注册 链接&#x…
阅读更多...
WinRAR注册+去广告教程

WinRAR注册+去广告教程

1、注册 在WinRAR安装目录创建rarreg.key文件&#xff0c; 拷贝如下内容并保存&#xff1a; RAR registration data Federal Agency for Education 1000000 PC usage license UIDb621cca9a84bc5deffbf 6412612250ffbf533df6db2dfe8ccc3aae5362c06d54762105357d 5e3b1489e751c…
阅读更多...
WinRAR4.20注册文件rarreg.key

WinRAR4.20注册文件rarreg.key

2019独角兽企业重金招聘Python工程师标准>>> 在WinRAR的安装目录下&#xff0c;新建rarreg.key文件&#xff08;注意不要创建成rarreg.key.txt文件了^_^&#xff09;&#xff0c;内容为如下&#xff1a; RAR registration data Team EAT Single PC usage license UI…
阅读更多...
Android按钮样式

Android按钮样式

//创建一个新的XML文件&#xff0c;可命名为styles<style name"button1"><item name"android:layout_height">wrap_content</item><item name"android:textColor">#FFFFFF</item><item name"android:text…
阅读更多...
漂亮的Button按钮样式

漂亮的Button按钮样式

开发中各种样式的Button，其实这些样式所有的View都可以共用的，可能对于你改变的只有颜色 所有的都是用代码实现 边框样式，给你的View加上边框 <Buttonandroid:layout_width="0dip"android:layout_height="match_parent"android:layout_margin=&q…
阅读更多...
「HTML+CSS」--自定义按钮样式【001】

「HTML+CSS」--自定义按钮样式【001】

前言 Hello&#xff01;小伙伴&#xff01; 首先非常感谢您阅读海轰的文章&#xff0c;倘若文中有错误的地方&#xff0c;欢迎您指出&#xff5e; 哈哈 自我介绍一下 昵称&#xff1a;海轰 标签&#xff1a;程序猿一只&#xff5c;C选手&#xff5c;学生 简介&#xff1a;因C语…
阅读更多...
推荐文章

Copyright @ 2022~2023