OB判断哪些表应该改为QUEUING表的思路

BUFFER表介绍

Queuing 表(又称 buffer 表)是业务上因使用场景而诞生的名称，意为业务上"像使用 buffer一样使用一张表"，即全表数据有大比例的更新或者增删。该场景具有以下特点：

• 触发条件：表数据频繁大比例更新

  当表中大量插入的同时大量连续删除（或者大量更新，因为 OB 更新的本质也是 delete+insert ）时，一张表看起来只有几千行数据，但实际上可能已经发生了几百万的插入和删除操作。

• 直接现象：表行数不大，但查询很慢

  buffer表效应的一个明显特征就是数据量很小的表（例如几千行），查询起来却非常慢。这是因为对于buffer表来说，查询的SQL在内核处理时，实际需要扫描的行数量可能远大于这个量级（可能是几百到上千万）。默认设置下，一张表中删除的行在 OB 每日合并前并不是真的删除，而只是在内存里打了个删除标记，OB major freeze/merge期间才会真正处理为删除。

• 问题原因：执行计划跳变，全表扫描耗时翻倍

  这种 “mark for delete” 的处理方式, 是采用了 LSM tree 架构的存储引擎的共同问题。而且因为buffer表的删除会在合并期间处理为真正的删除，而 OceanBase 在合并期间会收集统计信息，更新执行计划，此时部分表的数据量因为很少，OceanBase的 CBO 优化器可能根据代价计算而为某些 SQL 生成全表扫描的计划。这个执行计划在白天随着业务访问不断增加，表中的实际数据量不断加大，SQL 性能会出现较大滑坡。

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

对于大量增删改的表来说
ALTER TABLE t1 TABLE_MODE = 'queuing';
如果需要改回去
alter table t1 table_mode='normal';

BUFFR表性能问题实例

下边通过一个生产上的实际例子来看BUFFER表的问题及哪些表应该做queuing 表优化。

涉及DW_TEST_RECORD表的一个简单查询，走了索引，但性能不及预期，通过分析可以看到表里只有几百行记录，但实际扫描记录数达400万以上。

obclient [TBCS]> explain extended select status  from DW_TEST_RECORD  where status=0\G
*************************** 1. row ***************************
Query Plan: ========================================================================================
|ID|OPERATOR               |NAME                                     |EST. ROWS|COST   |
----------------------------------------------------------------------------------------
|0 |PX COORDINATOR         |                                         |16172    |1592870|
|1 | EXCHANGE OUT DISTR    |:EX10000                                 |16172    |1592268|
|2 |  PX PARTITION ITERATOR|                                         |16172    |1592268|
|3 |   TABLE SCAN          |DW_TEST_RECORD(IDX_DW_TEST_RECORD_STATUS)|16172    |1592268|
========================================================================================

Outputs & filters: 
-------------------------------------
  0 - output([INTERNAL_FUNCTION(DW_TEST_RECORD.STATUS(0xffa0231d69a0))(0xffa0232436c0)]), filter(nil)
  1 - output([INTERNAL_FUNCTION(DW_TEST_RECORD.STATUS(0xffa0231d69a0))(0xffa0232436c0)]), filter(nil), dop=1
  2 - output([DW_TEST_RECORD.STATUS(0xffa0231d69a0)]), filter(nil), 
      force partition granule.
  3 - output([DW_TEST_RECORD.STATUS(0xffa0231d69a0)]), filter(nil), 
      access([DW_TEST_RECORD.STATUS(0xffa0231d69a0)]), partitions(p[0-12]), 
      is_index_back=false, 
      range_key([DW_TEST_RECORD.STATUS(0xffa0231d69a0)], [DW_TEST_RECORD.VOICEORQPS(0xffa02322cd80)], [DW_TEST_RECORD.REGION(0xffa0231d59b0)], [DW_TEST_RECORD.__pk_increment(0xffa023229dc0)]), range(0,MIN,MIN,MIN ; 0,MAX,MAX,MAX), 
      range_cond([DW_TEST_RECORD.STATUS(0xffa0231d69a0) = 0(0xffa0231d6280)])


Optimization Info:
-------------------------------------
DW_TEST_RECORD:table_rows:77, physical_range_rows:4116463, logical_range_rows:16172, index_back_rows:0, output_rows:16172, est_method:remote_storage, optimization_method=cost_based, avaiable_index_name[IDX_DW_TEST_RECORD_STATUS], pruned_index_name[PK_DW_URGE,IDX_DW_URGE_ACCOUNT,IDX_DW_URGE_TEL,DW_TEST_RECORD], estimation info[table_id:1100611139506771, (table_type:1, version:0-1719943210853892-1719943210853892, logical_rc:60, physical_rc:60), (table_type:7, version:1719943206265033-1719943206265033-1719943248931125, logical_rc:-6, physical_rc:28), (table_type:7, version:1719943248931125-1719948627686714-1719950424938033, logical_rc:-4, physical_rc:832), (table_type:7, version:1719950424938033-1719994853771688-1719996656456737, logical_rc:261, physical_rc:257029), (table_type:5, version:1719950424938033-1719994853771688-1719996656456737, logical_rc:0, physical_rc:338), (table_type:0, version:1719996656456737-1719996656456737-9223372036854775807, logical_rc:933, physical_rc:58364)]

physical_range_rows:4116463 
 

physical_range_rows:4116463 <<<<实际返回只有几百行，这里存储估行400+万（OB存储估行基于索引存储信息，比较准确）

obclient [TBCS]> select count(*) from DW_TEST_RECORD;
+----------+
| COUNT(*) |
+----------+
|      333 |
+----------+
1 row in set (0.899 sec)

obclient [TBCS]> select count(*) from DW_TEST_RECORD;
+----------+
| COUNT(*) |
+----------+
|      419 |
+----------+
1 row in set (0.888 sec)

通过上面的分析，推测DW_TEST_RECORD为一个典型的BUFFER表，我们可以通过__all_virtual_partition_audit视图查询来这个表的增删改的记录数进行判断。

MySQL [oceanbase]> select table_id from __all_virtual_table where table_name='DW_TEST_RECORD';
+------------------+
| table_id         |
+------------------+
| 1100611139505480 |
+------------------+
1 row in set (0.101 sec)



MySQL [oceanbase]> select partition_id,partition_status,insert_row_count,delete_row_count,update_row_count  from __all_virtual_partition_audit where table_id=1100611139505480 and delete_row_count >0 order by delete_row_count desc;
+--------------+------------------+------------------+------------------+------------------+
| partition_id | partition_status | insert_row_count | delete_row_count | update_row_count |
+--------------+------------------+------------------+------------------+------------------+
|            5 |                6 |         10634348 |         10632545 |         21680918 |
|            5 |                1 |               19 |               11 |                0 |
+--------------+------------------+------------------+------------------+------------------+
2 rows in set (0.196 sec)

识别BUFFER表思路

通过这个例子我们这样一个规则找出疑似BUFFER表进行分析优化。

删除的行数大于合并时的行数，按删除行数/表行数进行倒序排序，取TOP

select a.tenant_id,a.table_id,a.partition_id,a.row_count,b.insert_row_count,b.delete_row_count,b.update_row_count 
from __all_virtual_meta_table a,__all_virtual_partition_audit b 
where a.tenant_id=b.tenant_id and a.table_id=b.table_id 
and a.partition_id=b.partition_id and b.tenant_id>1000 and b.delete_row_count >0 
and b.delete_row_count >a.row_count   order by delete_row_count/row_count desc limit 10;

+-----------+------------------+--------------+-----------+------------------+------------------+------------------+
| tenant_id | table_id         | partition_id | row_count | insert_row_count | delete_row_count | update_row_count |
+-----------+------------------+--------------+-----------+------------------+------------------+------------------+
|      1001 | 1100611139504172 |            0 |         3 |         34222726 |         34222351 |         34214666 |
|      1001 | 1100611139504172 |            0 |         3 |         34222726 |         34222351 |         34214666 |
|      1001 | 1100611139504172 |            0 |         3 |         34222726 |         34222351 |         34214666 |
|      1001 | 1100611139504163 |            5 |         3 |         34215028 |         34214652 |         34214666 |
|      1001 | 1100611139504163 |            5 |         3 |         34215028 |         34214652 |         34214666 |
|      1001 | 1100611139504163 |            5 |         3 |         34215028 |         34214652 |         34214666 |
|      1001 | 1100611139505158 |            5 |         1 |               10 |          3401464 |                0 |
|      1001 | 1100611139505158 |            5 |         1 |               10 |          3401464 |                0 |
|      1001 | 1100611139505158 |            5 |         1 |               10 |          3401464 |                0 |
|      1001 | 1100611139503088 |          200 |         9 |         14220488 |         14054273 |              505 |
+-----------+------------------+--------------+-----------+------------------+------------------+------------------+
10 rows in set (2.407 sec)