Understanding and Optimizing BUFFER (Queuing) Tables in OceanBase

In OceanBase, a BUFFER (also called Queuing) table refers to a table that is frequently updated in bulk or partially (e.g., 20% of rows) and then quickly queried in full, leading to a sharp drop in performance due to asynchronous delete‑mark‑insert handling, delayed background cleanup, and inaccurate statistics.

To mitigate this issue, three main approaches are recommended:

Bind an execution plan manually (e.g., using an OUTLINE) to guide the optimizer toward the optimal path.

Perform manual data transfer or merging to clean up unused data.

Set the table attribute table_mode='queuing' , which triggers automatic dumping of the table when update volume exceeds a threshold, improving query speed for both MySQL and Oracle tenants.

Example of creating a table with the queuing mode:

<mysql:5.6.25:ytt>create table t2 (id int primary key, r1 int, r2 varchar(100)) table_mode='queuing';
Query OK, 0 rows affected (0.032 sec)

MySQL tenant bulk insert and delete example:

<mysql:5.6.25:ytt>insert into t2 with recursive tmp(a,b,c) as (select 1,1,'mysql' union all select a+1,ceil(rand()*200),'actionsky' from tmp where a < 20000) select * from tmp;
Query OK, 20000 rows affected (0.916 sec)

<mysql:5.6.25:ytt>delete from t2;
Query OK, 20000 rows affected (0.056 sec)

Oracle tenant bulk insert and delete example:

<mysql:5.6.25:SYS>insert into t2 select level,100,'oracle' from dual connect by level <= 20000;
delete from t2;
Query OK, 20000 rows affected (0.070 sec)

After multiple executions, the background dump records for the table can be inspected with the following query, which shows entries where the action is buf minor merge :

SELECT *
    -> FROM (
    ->   SELECT c.tenant_name, a.table_name, d.type, d.action, d.version, d.start_time
    ->   FROM __all_virtual_table a
    ->   JOIN __all_virtual_meta_table b USING(table_id)
    ->   JOIN __all_tenant c ON (b.tenant_id=c.tenant_id) AND c.tenant_name IN ('mysql','oracle')
    ->   JOIN gv$merge_info d ON d.table_id = a.table_id
    ->   WHERE d.action LIKE 'buf minor merge'
    ->   ORDER BY d.start_time DESC LIMIT 2
    -> ) T
    -> ORDER BY start_time ASC;

This query returns recent merge actions for both MySQL and Oracle tenants, confirming that the queuing mode successfully triggers automatic dumps and improves performance.