Queuing 表（buffer表）的优化实践 | OceanBase 性能优化实践

标签：删除 buffer 扫描实践转储 OceanBase SQL 优化

案例问题描述

该案例来自一个金融行业客户的问题：他们发现某个应用对一个数据量相对较小的表（仅包含数千条记录）访问时，频繁遇到性能下降的情况。为解决此问题，客户向我们求助进行分析。我们发现这张表有频繁的批量插入与删除操作，起初，性能基本正常，但不久后性能就会出现了下降。为深入探究原因，我们通过该应用的 sql_audit 审计日志，进行进一步的分析。

问题复现

1679899488

SQL_AUDIT审计日志分析

1679899550

查询结果仅有2行数据，但访问存储路径很长，查询耗时13秒

EXECUTE_TIME: 13130625 #执行时间13秒

RETURN_ROWS: 2 #查询结果集大小

MEMSTORE_READ_ROW_COUNT: 472142 #OceanBase的内存结构读取的行数，从内存中读取了47万行

SSSTORE_READ_ROW_COUNT: 501954 #OceanBase的基线数据读取行，读取了50万条；

DATA_BLOCK_READ_CNT: 35963

DATA_BLOCK_CACHE_HIT: 21565

通过对业务场景的梳理和审计日志分析，可以初步判断，应用遇到了OB的 Queuing 表的问题，Queuing表(又称buffer表) 意为业务上"像使用 buffer一样使用一张表"，即全表数据有大比例的更新或者增删。该场景具有以下特点：

直接现象：表行数不大，但查询很慢
buffer表效应的一个明显特征就是数据量很小的表（例如几千行），查询起来却非常慢。这是因为对于buffer表来说，查询的SQL在内核处理时，实际需要扫描的行数量可能远大于这个量级（可能是几百到上千万）。默认设置下，一张表中删除的行在 OB 每日合并前并不是真的删除，而只是在内存里打了个删除标记，OB major freeze/merge期间才会真正处理为删除。
触发条件：表数据频繁大比例更新
当表中大量插入的同时大量连续删除（或者大量更新，因为 OB 更新的本质也是 delete+insert ）时，一张表看起来只有几千行数据，但实际上可能已经发生了几百万的插入和删除操作。
产生场景
- 业务逻辑有大量的插入、删除操作。
- 业务代码只有插入，但是删除历史数据时，出现大量插入、删除
- OMS数据同步Replace操作，导致buffer表
问题原因：执行计划跳变，全表扫描耗时翻倍
这种 "mark for delete" 的处理方式, 是采用了 LSM tree 架构的存储引擎的共同问题。而且因为buffer表的删除会在合并期间处理为真正的删除，而OceanBase在合并期间会收集统计信息，更新执行计划，此时部分表的数据量因为很少，OceanBase的CBO优化器可能根据代价计算而为某些SQL生成全表扫描的计划。这个执行计划在白天随着业务访问不断增加，表中的实际数据量不断加大，SQL性能会出现较大滑坡。

应急处理方案

Buffer表出现时多数情况下系统已经运行在线上，此时需要的是快速止血，常见处理方式如下：

对于存在可用索引，但OB优化器计划生成为全表扫描的场景。需要进行执行计划binding来固定计划。
如果sql查询的主要过滤字段无可用索引，此时推荐在线创建可用索引并绑定该计划。
如果业务场景暂时无法创建索引，或者执行的SQL多为范围扫描，此时可根据业务场景需要决定是否手动【触发合并】，将删除或更新的数据版本进行清理，降低全表扫描的数据量，提升速度。

注：Buffer表最快、最有效的手段还是通过索引来解决，如果无法快速定位到有效索引，需要合并，合并一般都比较慢。因此在合并的同时，为了尽快恢复DB，可以有以下两个补充手段：

扩容(尽可能大的规格)。
对问题SQL限流(尽可能小的流量，甚至限停)。

Buffer表最有效的防止异常手段还是在事前，面对Buffer表的场景，把控SQL质量。

历史数据删除时，需要评估是否有触发buffer表风险的SQL，如果SQL有风险，禁止做历史数据删除。比如如下SQL:

Limit从大表取一条数据: select * from table_name limit 1;
全表扫描: select * from table_name;
未全表扫，但执行计划不明确，走错风险大, 复杂SQL

OceanBase对Queuing表的优化

OceanBase为了优化buffer表效应，在memtable和sstable两个层面，对表数据连续删除的"空洞"设定了一个阈值（如256行），当这些空洞被查询扫描过一次时，存储层就会在上面打上"可跳过"的标记。这样就能使相同SQL下次再查询时，可以直接跳过这些无需扫描的行，实现快速查询。

默认场景下，当OB在转储/合并发生冻结的瞬间，这些空洞的range打标会失效，必须依赖下一次"成功的慢查询(全表扫描)"才能够将标记再次打上去。所以多数情况下，如果用户对buffer表的sql的执行计划创建合适的索引并且进行了执行计划绑定，后面即使不做其他干预，经历一次超长耗时的请求，后面即可恢复正常。

但是这些方法均为应急止血方案，从2.2.7版本开始，OceanBase引入了buffer minor merge设计，实现对queuing表的特殊转储机制，彻底解决无效扫描问题。对于设计阶段已经明确的Queuing表场景，推荐开启该特性作为长期解决方案

alter table user_table table_mode = 'queuing';

关于Queuing表转储

OceanBase的自适应的buffer表转储策略，由存储层在每次转储时根据转储的统计信息来自主判断是否需要对该表采用buffer表转储策略，当发现一个表存在类似buffer表行为时，接下来会尝试对这个表做buffer minor merge的调度, 对这个表基于Major SSTable和最新的增量数据以当前的读快照时间生成一个Buf Minor SSTable, 这次Compaction动作会消除掉增量数据里的所有Delete标记, 后续查询基于新生成的Buf Minor SSTable就可以避免原有的大量无效扫描动作。

客户的解决方案

1、根据业务SQL条件添加了联合索引 KEY `idx_status_gmtmodify` (`status`, `gmt_modify`) ，更好的选择度，减少回表数据，即使频繁更新，扫描存储的量级也不大，sql能在ms级响应.

2、给业务表增加queuing 标签，加快转储

#queuing打标
ALTER TABLE table_name TABLE_MODE = 'queuing'; 
手动转储操作
# 系统租户操作是全局
alter system minor freeze;
# 全部转储
ALTER SYSTEM MINOR FREEZE TENANT =ALL;
# 系统租户
ALTER SYSTEM MINOR FREEZE tenant = sys;
# 用户租户
ALTER SYSTEM MINOR FREEZE TENANT =tenant1;
# zone级
ALTER SYSTEM MINOR FREEZE ZONE = zone1;
#server级
ALTER SYSTEM MINOR FREEZE SERVER = ('10.10.10.10:2882');
# 分区级
ALTER SYSTEM MINOR FREEZE tenant = t1 tablet_id = 60000;

# 普通租户触发转储，只能是自己租户的
# 本租户级
ALTER SYSTEM MINOR FREEZE;

标签：删除,buffer,扫描,实践,转储,OceanBase,SQL,优化
From： https://blog.csdn.net/OceanBaseGFBK/article/details/140797978