技術分享 | OceanBase 裡的 BUFFER 表

作者：楊濤濤
資深資料庫專家，專研 MySQL 十餘年。擅長 MySQL、PostgreSQL、MongoDB 等開源資料庫相關的備份恢復、SQL 調優、監控運維、高可用架構設計等。目前任職於愛可生，為各大運營商及銀行金融企業提供 MySQL 相關技術支援、MySQL 相關課程培訓等工作。
本文來源：原創投稿
*愛可生開源社群出品，原創內容未經授權不得隨意使用，轉載請聯絡小編並註明來源。

之前在學習 OBCP 的培訓材料時，有一項名為 BUFFER 表的解釋，當時也沒太在意（主要是 OBCP 考試裡沒有涉及到），之後當別人考我時，我 ......！

什麼是BUFFER 表（OceanBase 裡也叫 Queuing 表）？之前我以為 BUFFER 表就是全域性臨時表或者說是類似 MySQL 一次性整體匯入的 MyISAM 表，對這類表的操作無非是劃出一塊記憶體區域對其進行讀寫，來避免頻繁的 IO 操作。經過查閱 OceanBase 官方檔案後，這個 BUFFER 表並非我理解的那樣（我的理解完全錯誤）。

所謂 BUFFER 表指的是某張表（表記錄數可能並不多）被頻繁的全量更新（所有記錄被執行批次 DML 操作）、按一定比率更新（比如20%的記錄被執行批次DML操作），短時間又對這張表進行全量檢索，效能急劇下降或者說比之前效能有明顯降低的這樣一種現象。對於 OceanBase 來講，對錶的 DML 操作都是隻打標記（比如 UPDATE ，變為 DELETE 打標記+INSERT），後臺再慢慢非同步清理舊的資料。所以必然會在更新頻率過快並且後臺執行緒清理資料不及時導致的嚴重讀寫放大問題、或者統計資訊嚴重不準確（OceanBase 統計資訊在合併時觸發）導致的執行計劃非最優的問題，最終影響檢索效能。

可以透過如下操作來進行補救：

繫結執行計劃，人為引導最佳化器選擇最優執行路徑。比如建立 OUTLINE 讓 SQL 語句繫結固定執行計劃。
手工進行轉儲或者合併來清理無用資料。
給表加屬性 table_mode='queuing' 。這個屬性是 OceanBase 專門為 BUFFER 表做的最佳化，具體流程簡單概述為：當 BUFFER 表更新記錄數超過一定閾值時，自動對這張表進行單獨轉儲以消除大量無效檢索，從而實現對 BUFFER 表的快速檢索。這個表屬性針對 MySQL 租戶和 Oracle 租戶都有效。Oracle 租戶可以自定義 BUFFER 錶轉儲閾值：一個是觸發基於全量資料的轉儲百分比（_ob_queuing_fast_freeze_min_threshold）;另外一個是觸發快速轉儲的記錄數（_ob_queuing_fast_freeze_min_count）。MySQL 租戶目前沒看到對應配置項，但是其對BUFFER表的批次更新請求，後臺也會有對應的轉儲操作（對應表：gv$merge_info）。

比如在 MySQL 、Oracle 租戶下都建立一張表t2，指定表 table_mode='queuing' 。

<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 租戶下執行下面語句：

<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)
Records: 20000  Duplicates: 0  Warnings: 0

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

Oracle 租戶下可以執行下面語句：

<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)
Records: 20000  Duplicates: 0  Warnings: 0

Query OK, 20000 rows affected (0.088 sec)

多次執行上面這些語句後，可以在sys租戶下檢視MySQL租戶、Oracle租戶後臺針對表t2的單獨轉儲記錄：欄位 action 為 buf minor merge 的行。

<mysql:5.6.25:oceanbase>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; 
+-------------+------------+-------+-----------------+------------------+----------------------------+
| tenant_name | table_name | type  | action          | version          | start_time                 |
+-------------+------------+-------+-----------------+------------------+----------------------------+
| mysql       | t2         | minor | buf minor merge | 1678867962096191 | 2023-03-15 16:13:14.774809 |
| oracle      | T2         | minor | buf minor merge | 1678871458311991 | 2023-03-15 17:11:38.426437 |
+-------------+------------+-------+-----------------+------------------+----------------------------+
2 rows in set (0.008 sec)

技術分享 | OceanBase 裡的 BUFFER 表

相關文章