走进SQL审计视图——《OceanBase诊断系列》之二

1. 前言


在SQL性能诊断上,OceanBase有一个非常实用的功能 —— SQL审计视图(gv$sql_audit)。在OceanBase 4.0.0及更高版本中,该功能是 gv$ob_sql_audit。它可以使开发和运维人员更方便地排查在OceanBase上运行过的任意一条SQL,无论这些SQL是成功与否,都有详细的运行信息记录。这些信息包括客户端和服务端的IP端口、SQL语句、执行时间、执行节点、执行计划ID、会话ID、执行时间、等待时间、总时间、排队时间、以及相关的块读取信息和执行报错信息等。

查询方式说明
(g)v$ob_sql_auditOceanBase 4.0.0.0 及以上版本,gv$xx查询该租户所有机器v$xxx查询该租户本机器(不保证路由准确)
(g)v$sql_auditOceanBase 4.0.0.0 以下版本gv$xx查询该租户所有机器v$xxx查询该租户本机器(不保证路由准确)

sql_audit是基于虚拟表__all_virtual_sql_audit的视图, 该虚拟表对应的数据存放在一个可配置的内存空间中,能够记录并显示每一次SQL请求的来源、执行状态及统计信息,由于存放这些记录的内存是有限的,因此到达一定内存使用量,会触发淘汰。

  • sql_audit 每隔 1s 会检测后台任务并根据以下标准决定是否淘汰:
    • sql_audit 内存最大可使用上限为 avail_mem_limit = min (OBServer 可使用内存 *10%,sql_audit_memory_limit)。
    • 当 avail_mem_limit 在 [64M, 100M] 范围内时, 内存使用达到 avail_mem_limit-20M 时触发淘汰。
    • 当 avail_mem_limit 在 [100M, 5G] 范围内时, 内存使用达到 availmem_limit*0.8 时触发淘汰。
    • 当 avail_mem_limit 在 [5G, +∞)范围内时, 内存使用达到 availmem_limit-1G 时触发淘汰。
    • 当 sql_audidt 记录数超过 900 万条时,触发淘汰。
  • sql_audit 根据以下标准决定是否停止淘汰:
    • 如果是达到内存上限触发淘汰则:
    • 当 avail_mem_limit 在 [64M, 100M] 时, 内存使用淘汰到 avail_mem_limit-40M 时停止淘汰。
    • 当 avail_mem_limit 在 [100M, 5G] 时, 内存使用淘汰到 availmem_limit*0.6 时停止淘汰。
    • 当 avail_mem_limit 在 [5G, +∞] 时, 内存使用淘汰到 availmem_limit-2G 时停止淘汰。
    • 如果是达到记录数上限触发的淘汰则淘汰到 800 万行记录时停止淘汰。

2. sql_audit视图字段介绍

字段名称类型描述
SVR_IPvarchar(32)ip地址
SVR_PORTbigint(20)端口号
REQUEST_IDbigint(20)请求的id号
TRACE_IDvarchar(128)这条语句的trace_id
CLIENT_IPvarchar(32)发送请求的client ip
CLIENT_PORTbigint(20)发送请求的client port
TENANT_IDbigint(20)发送请求的租户id
TENANT_NAMEvarchar(64)发送请求的租户 名称
USER_IDbigint(20)发送请求的用户id
USER_NAMEvarchar(64)发送请求的用户名称
SQL_IDvarchar(32)这条SQL的id
QUERY_SQLvarchar(32768)实际的SQL语句
PLAN_IDbigint(20)执行计划id
AFFECTED_ROWSbigint(20)影响行数
RETURN_ROWSbigint(20)返回行数
PARTITION_CNTbigint(20)该请求涉及的分区数
RET_CODEbigint(20)执行结果返回码
EVENTvarchar(64)最长等待事件名称
P1TEXTvarchar(64)等待事件参数1
P1bigint(20) unsigned等待事件参数1的值
P2TEXTvarchar(64)等待事件参数2
P2bigint(20) unsigned等待事件参数2的值
P3TEXTvarchar(64)等待事件参数3
P3bigint(20) unsigned等待事件参数3的值
LEVELbigint(20)等待事件的level级别
WAIT_CLASS_IDbigint(20)等待事件所属的class id
WAIT_CLASS#bigint(20)等待事件所属的class 的下标
WAIT_CLASSvarchar(64)等待事件所属的class 名称
STATEvarchar(19)等待事件的状态
WAIT_TIME_MICRObigint(20)该等待事件所等待的时间
TOTAL_WAIT_TIME_MICRObigint(20)执行过程所有等待的总时间
TOTAL_WAITSbigint(20)执行过程总等待的次数
RPC_COUNTbigint(20)发送rpc个数
PLAN_TYPEbigint(20)执行计划类型
IS_INNER_SQLtinyint(4)是否内部sql请求
IS_EXECUTOR_RPCtinyint(4)当前请求是否rpc请求
IS_HIT_PLANtinyint(4)是否命中plan_cache
REQUEST_TIMEbigint(20)开始执行时间点
ELAPSED_TIMEbigint(20)接收到请求到执行结束消耗 总时间
NET_TIMEbigint(20)发送rpc到接收到请求时间
NET_WAIT_TIMEbigint(20)接收到请求到进入队列时间
QUEUE_TIMEbigint(20)请求在队列等待事件
DECODE_TIMEbigint(20)出队列后decode时间
GET_PLAN_TIMEbigint(20)开始process到获得plan时间
EXECUTE_TIMEbigint(20)plan执行消耗时间
APPLICATION_WAIT_TIMEbigint(20) unsigned所有application类事件的总时间
CONCURRENCY_WAIT_TIMEbigint(20) unsigned所有concurrency类事件的总时间
USER_IO_WAIT_TIMEbigint(20) unsigned所有user_io类事件的总时间
SCHEDULE_TIMEbigint(20) unsigned所有schedule类事件的时间
ROW_CACHE_HITbigint(20)行缓存命中次数
BLOOM_FILTER_CACHE_HITbigint(20)bloom filter缓存命中次数
BLOCK_CACHE_HITbigint(20)块缓存命中次数
BLOCK_INDEX_CACHE_HITbigint(20)块索引缓存命中次数
DISK_READSbigint(20)物理读次数
EXECUTION_IDbigint(20)执行ID
SESSION_IDbigint(20)session id
RETRY_CNTbigint(20)重试次数
TABLE_SCANtinyint(4)判断该请求是否含全表扫描
CONSISTENCY_LEVELbigint(20)一致性级别
MEMSTORE_READ_ROW_COUNTbigint(20)MEMSTORE中的读行数
SSSTORE_READ_ROW_COUNTbigint(20)SSSTORE中读的行数
REQUEST_MEMORY_USEDbigint(20)该请求消耗的内存
  • 一些重要的事件间隔

3. 基于sql_audit的诊断case

3.1. 最近100s某个租户的TOP SQL耗时监控

  • 检查语句:
-- OceanBase 4.0.0.0及以上版本,请替换tenant_name的值为实际的租户名 
select /*+read_consistency(weak),query_timeout(100000000)*/ SQL_ID,count(1),avg(ELAPSED_TIME),avg(EXECUTE_TIME),avg(QUEUE_TIME),avg(AFFECTED_ROWS),avg(GET_PLAN_TIME) 
from gv$ob_sql_audit  
where time_to_usec(now(6))-request_time <1000000000 
and tenant_name='test_tenant' 
group by SQL_ID order by avg(ELAPSED_TIME)*count(1) desc limit 20;

-- OceanBase 4.0.0.0以下版本,请替换tenant_name的值为实际的租户名 
select /*+read_consistency(weak),query_timeout(100000000)*/ SQL_ID,count(1),avg(ELAPSED_TIME),avg(EXECUTE_TIME),avg(QUEUE_TIME),avg(AFFECTED_ROWS),avg(GET_PLAN_TIME) 
from gv$sql_audit  
where time_to_usec(now(6))-request_time <1000000000 
and tenant_name='test_tenant'  
group by SQL_ID order by avg(ELAPSED_TIME)*count(1) desc limit 20 ;
  • 期望值: 观察SQL 整体耗时,cpu_time, 物理读及逻辑消耗是否合理,一般单行insert 和 主键查询 500us以内
  • 对应建议:通过SQL语义与表结构比对,确认执行计划是否合理,耗时是否正常

3.2. 查看集群中 SQL 请求流量是否均匀

  • 思路:我们首先可以查出某个时间段内数据库中所有 SQL 并按照 server 级别进行聚合,再统计该时间段内每台机器上的 QPS。
  • 语句:
-- OceanBase 4.0.0.0及以上版本,请替换t1.tenant_id的值为实际租户的值
select t2.zone, t1.svr_ip,  count(*) as QPS
from oceanbase.gv$ob_sql_audit t1, oceanbase.__all_server t2
where t1.svr_ip = t2.svr_ip and t1.tenant_id = 1001
and IS_EXECUTOR_RPC = 0 and request_time > (time_to_usec(now()) - 1000000)
and request_time < time_to_usec(now())
group by t1.svr_ip   order by QPS;


-- OceanBase 4.0.0.0以下版本,请替换t1.tenant_id的值为实际租户的值
select t2.zone, t1.svr_ip,  count(*) as QPS
from oceanbase.gv$ob_sql_audit t1, oceanbase.__all_server t2
where t1.svr_ip = t2.svr_ip and t1.tenant_id = 1001
and IS_EXECUTOR_RPC = 0 and request_time > (time_to_usec(now()) - 1000000)
and request_time < time_to_usec(now())
group by t1.svr_ip   order by QPS;

3.3. 某个时间段请求次数排在 TOP-N 的 SQL

  • 思路:我们首先可以查出某个时间段内数据库中所有 SQL 并按照 sql_id 级别进行聚合,再统计该时间段内每个SQL_ID的 QPS,取出top值。
  • 语句:
-- OceanBase 4.0.0.0及以上版本,请替换tenant_id的值为实际租户的值
select SQL_ID, count(*) as QPS, avg(t1.elapsed_time) RT
from oceanbase.gv$ob_sql_audit t1
where   tenant_id = 1001       and IS_EXECUTOR_RPC = 0
and request_time > (time_to_usec(now()) - 10000000)
and request_time < time_to_usec(now())
group by t1.sql_id order by QPS desc limit 10;



-- OceanBase 4.0.0.0以下版本,请替换tenant_id的值为实际租户的值
select SQL_ID, count(*) as QPS, avg(t1.elapsed_time) RT
from oceanbase.gv$sql_audit t1
where   tenant_id = 1001       and IS_EXECUTOR_RPC = 0
and request_time > (time_to_usec(now()) - 10000000)
and request_time < time_to_usec(now())
group by t1.sql_id order by QPS desc limit 10;

3.4. 定位所有SQL中消耗CPU最多的sql

思路:消耗CPU的时间是elapsed_time - queue_time,因为queue_time的过程中是在排队,并不消耗cpu. 排查消耗CPU最多的sql在cpu飙高的场景非常有用

语句:

-- OceanBase 4.0.0.0及以上版本,请替换tenant_id的值为实际租户的值
select sql_id, substr(query_sql, 1, 20) as query_sql, 
      sum(elapsed_time - queue_time) sum_t, count(*) cnt, 
      avg(get_plan_time), avg(execute_time)    
from oceanbase.gv$ob_sql_audit     
where   tenant_id = 1001      
       and request_time > (time_to_usec(now()) - 10000000)     
       and request_time < time_to_usec(now())
group by sql_id order by sum_t desc   limit 10;



-- OceanBase 4.0.0.0以下版本,请替换tenant_id的值为实际租户的值
select sql_id, substr(query_sql, 1, 20) as query_sql, 
      sum(elapsed_time - queue_time) sum_t, count(*) cnt, 
      avg(get_plan_time), avg(execute_time)    
from oceanbase.gv$sql_audit     
where   tenant_id = 1001      
       and request_time > (time_to_usec(now()) - 10000000)     
       and request_time < time_to_usec(now())
group by sql_id order by sum_t desc   limit 10;

3.5. 查看SQL的执行是否出现大量请求不合理的使用了远程执行

思路:sql_audit的PLAN_TYPE字段可以看到该SQL的执行计划类型,

  • plan_type=1 :本地执行计划。性能最好。
  • plan_type=2 : 远程执行计划。
  • plan_type=3 : 分布式执行计划。包含本地执行计划和远程执行计划。

一般情况下,如果出现远程执行比较多时可能时出现切主或proxy客户端路由不准的情况。

语句:

-- OceanBase 4.0.0.0及以上版本,请替换tenant_id的值为实际租户的值
select count(*), plan_type    
from oceanbase.gv$ob_sql_audit     
where tenant_id = 1001          
      and IS_EXECUTOR_RPC = 0          
      and request_time > (time_to_usec(now()) - 10000000)         
      and request_time < time_to_usec(now()) 
group by plan_type ;


-- OceanBase 4.0.0.0以下版本,请替换tenant_id的值为实际租户的值
select count(*), plan_type    
from oceanbase.gv$sql_audit     
where tenant_id = 1001          
      and IS_EXECUTOR_RPC = 0          
      and request_time > (time_to_usec(now()) - 10000000)         
      and request_time < time_to_usec(now()) 
group by plan_type ;

3.6. 查询全表扫描的SQL

思路:sql_audit的TABLE_SCAN字段是标识语句是否走了全表扫描,=1 表示全表扫描了。可以进一步分析一下SQL是否可以添加索引来防止全表扫描:

语句:

-- OceanBase 4.0.0.0及以上版本,请替换tenant_id的值为实际租户的值
select query_sql 
from oceanbase.gv$ob_sql_audit 
where table_scan = 1 and tenant_id = 1001 
group by sql_id;

-- OceanBase 4.0.0.0以下版本,请替换tenant_id的值为实际租户的值
select query_sql 
from oceanbase.gv$sql_audit 
where table_scan = 1 and tenant_id = 1001 
group by sql_id;

3.7 如何分析RT突然抖动的SQL?

    在线上如果出现RT抖动,但RT并不是持续很高的情况,可以考虑在抖动出现后,立刻将sql audit关闭(alter system set ob_enable_sql_audit = 0),从而确保该抖动的SQL请求在sql audit中存在;然后通过3.3章节的【某个时间段请求次数排在 TOP-N 的 SQL】,分析有异常的SQL。

   如果在sql_audit中找到了对应的RT异常请求,则可以分析该请求在sql audit中记录:

  • 查看retry次数是否很多(RETRY_CNT, 如果次数很多,则是否考虑是否有锁冲突或切主等情况)
  • 查看queue time是不是很大(QUEUE_TIME字段)
  • 查看获取执行计划时间(GET_PLAN_TIME), 如果时间很长,一般会伴随IS_HIT_PLAN = 0, 表示没有命中plan cache)
  • 查看EXECUTE_TIME是否很长,如果很长,则

     a. 查看是否有很长等待事件耗时

     b. 查看访问的行数是否很多, 看SSSTORE_READ_ROW_COUNT, MEMSTORE_READ_ROW_COUNT两个字段, 比如大小账号场景可能导致rt抖动。

第一篇“神医”的修炼秘籍——《OceanBase诊断系列》之一
第二篇一起走进sql_audit性能视图——《OceanBase诊断系列》之二

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/424138.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

基于 Amazon EKS 的 Stable Diffusion ComfyUI 部署方案

01 背景介绍 Stable Diffusion 作为当下最流行的开源 AI 图像生成模型在游戏行业有着广泛的应用实践&#xff0c;无论是 ToC 面向玩家的游戏社区场景&#xff0c;还是 ToB 面向游戏工作室的美术制作场景&#xff0c;都可以发挥很大的价值&#xff0c;如何更好地使用 Stable Dif…

Day10:基础入门-HTTP数据包Postman构造请求方法请求头修改状态码判断

目录 数据-方法&头部&状态码 案例-文件探针 案例-登录爆破 工具-Postman自构造使用 思维导图 章节知识点&#xff1a; 应用架构&#xff1a;Web/APP/云应用/三方服务/负载均衡等 安全产品&#xff1a;CDN/WAF/IDS/IPS/蜜罐/防火墙/杀毒等 渗透命令&#xff1a;文件…

数字图像处理 SUJOY滤波器:用于图像边缘检测的通用一阶导数滤波器

1、前言 因为是比较旧的论文,但是据论文作者说SUJOY滤波器为图像边缘检测提供了比其他常用的一阶导数方法(如 Robert 算子、Prewitt 算子、Sobel 算子等)更好的方法。 经过测试感觉并没有作者说的那么好,很水的东西,另外图像领域的事情很少有绝对的,通常都是某些方法适合…

【二分】第k个缺失的数

第K个缺失的数 链接 . - 力扣&#xff08;LeetCode&#xff09;. - 备战技术面试&#xff1f;力扣提供海量技术面试资源&#xff0c;帮助你高效提升编程技能,轻松拿下世界 IT 名企 Dream Offer。https://leetcode.cn/problems/kth-missing-positive-number/ 题目 题解 二段…

医学大数据|文献阅读|有关“胃癌+机器学习”的研究记录

目录 1.基于32基因特征构建的机器学习模型可有效预测胃癌患者的预后和治疗反应 2.胃癌患者术后90天死亡率的机器学习风险预测模型 3.使用机器学习模型预测幽门螺杆菌根除患者胃癌患病风险 4.利用初始内窥镜检查和组织学结果进行个性化胃癌发病率预测 1.基于32基因特征构建的…

ABAP - SALV教程07 斑马纹显示和SALV标题

SALV设置斑马纹和标题 METHOD set_layout.DATA: lo_display TYPE REF TO cl_salv_display_settings. * 取得显示对象lo_display co_alv->get_display_settings( ).* 设置ZEBRA显示lo_display->set_striped_pattern( X ). * 设置Titlelo_display->set_list_he…

探讨倒排索引Elasticsearch面试与实战:从理论到实践

在当前大数据时代&#xff0c;Elasticsearch&#xff08;以下简称为ES&#xff09;作为一种强大的搜索和分析引擎&#xff0c;受到了越来越多企业的青睐。因此&#xff0c;对于工程师来说&#xff0c;掌握ES的面试准备和实战经验成为了必备技能之一。本文将从ES的面试准备和实际…

【MCAL】TC397+EB-tresos之CAN配置实战 - (CAN/CANFD)

本篇文章介绍了在TC397平台使用EB-tresos对CAN驱动模块进行配置的实战过程,不仅介绍了标准CAN的发送与接收&#xff0c;还介绍了CANFD的实现与调试以及扩展帧的使用。M_CAN是德国博世公司开发的IP&#xff0c;因为英飞凌的芯片完整的集成了这个IP&#xff0c;所以整体的配置都比…

leetcode 热题 100_三数之和

题解一&#xff1a; 双指针遍历&#xff1a;暴力解法的三层遍历会超时&#xff0c;因此需要优化遍历的过程。首先是需要对结果进行去重&#xff0c;这里采用排序跳过重复值的做法&#xff0c;在指针遍历时跳过已经遍历过的相同值。在第一层循环确定第一个值后&#xff0c;剩下两…

【RT-Thread基础教程】邮箱的使用

文章目录 前言一、邮箱的特性二、邮箱操作函数2.1 创建邮箱创建动态邮箱创建静态邮箱 2.2 删除邮箱2.3 发邮件2.4 取邮件 三、示例代码总结 前言 RT-Thread是一个开源的实时嵌入式操作系统&#xff0c;广泛应用于各种嵌入式系统和物联网设备。在RT-Thread中&#xff0c;邮箱是…

阶跃信号与冲击信号

奇异信号&#xff1a;信号与系统分析中&#xff0c;经常遇到函数本身有不连续点&#xff08;跳变电&#xff09;或其导函数与积分有不连续点的情况&#xff0c;这类函数称为奇异函数或奇异信号&#xff0c;也称之为突变信号。以下为一些常见奇异函数。 奇异信号 单位斜变信号 …

java-ssm-jsp-宠物护理预定系统

java-ssm-jsp-宠物护理预定系统 获取源码——》公主号&#xff1a;计算机专业毕设大全

xxl-job--01--简介

提示&#xff1a;文章写完后&#xff0c;目录可以自动生成&#xff0c;如何生成可参考右边的帮助文档 文章目录 1.xxl-job1. 1 发展历史1.2 XXL-JOB的系统架构1.3 xxl-job与其他框架对比 2. XXL-JOB的使用2.1 准备工作- 配置调度中心XXL-JOB的数据表 2.2 配置执行器1 引入依赖包…

一个脚本两步计算材料Raman谱(附数据处理和绘图脚本)

在以往推送中已经介绍了相当多的计算材料Raman的方法&#xff0c;使用的软件主要为Phonopy-Spectroscopy&#xff0c;相关软件还有vasp&#xff0c;phonopy&#xff0c;phono3py等。 Phonopy-Spectroscopy计算材料红外和Raman光谱 Phonopy-Spectroscopy 计算红外和拉曼光谱 也…

Github配置SSH免密认证

以Ubuntu Server为例 生成SSH ssh-keygen -t ed25519 -C "your_emailexample.com" 如果系统不支持Ed25519算法&#xff0c;使用旧的命令&#xff1a; ssh-keygen -t rsa -b 4096 -C "your_emailexample.com" 根据提示生成公私钥文件&#xff0c;记下位置…

深度学习_17_丢弃法调整过拟合

除了权重衰退法调整过拟合&#xff0c;还有丢弃法调整模型得过拟合现象 过拟合&#xff1a; 丢弃法如果直接丢弃会导致新期望的不确定性&#xff0c;为了防止这个不确定被模型学到&#xff0c;所以要保证丢弃后的期望和丢弃前的期望一样&#xff08;个人观点&#xff09; 顾…

微服务笔记

什么是微服务? 微服务是一种经过良好架构设计的分布式架构方案&#xff0c;微服务架构特征: 1.单一职责:微服务拆分粒度更小&#xff0c;每一个服务都对应唯一的业务能力&#xff0c;做到单一职责&#xff0c;避免重复业发。 2.面向服务:微服务对外暴露业务接口 3.自治:团…

基于Springboot的人事管理系统 (有报告)。Javaee项目,springboot项目。

演示视频&#xff1a; 基于Springboot的人事管理系统 &#xff08;有报告&#xff09;。Javaee项目&#xff0c;springboot项目。 项目介绍&#xff1a; 采用M&#xff08;model&#xff09;V&#xff08;view&#xff09;C&#xff08;controller&#xff09;三层体系结构&am…

数据结构与算法学习【算法思想之二分法基础】

文章目录 数据结构与算法学习【算法思想之二分查找基础】本文学习目标或巩固的知识点 最基础的二分查找&#x1f7e2;通过题目可知题解结果验证 数据结构与算法学习【算法思想之二分查找基础】 本文学习目标或巩固的知识点 学习二分法类题目 巩固基础的二分法 提前说明&#…

Matlab 机器人工具箱 Link类

文章目录 1 Link类1.1 机械臂Link类1.2 构造函数1.3 信息/显示方法1.4 转换方法1.5 操作方法1.6 测试方法1.7 重载操作1.8 属性(读/写)1.9 例子2 Link.Link2.1 创建机器人连杆对象2.2 OPTIONS2.3 注意2.4 旧语法2.5 例子3 Link的其他函数3.1 Link.A3.2 Link.char3.3 Link.displ…