How to Identify SQL Statements Causing Frequent Mini Compactions in OceanBase

1. Background

1.1 Customer Question

A tenant is frequently performing mini compactions; how can we find which SQL statements cause it?

1.2 Environment

OceanBase version 4.2.1.10

2. Fundamentals

2.1 LSM Tree Storage Architecture

OceanBase tenant memory is divided into two categories: scalable memory (e.g., KVCache) and non‑scalable memory (e.g., MemStore).

Write DML operations first go to the MemStore. memstore_limit_percentage sets the percentage of total memory that MemStore may use; the default is 50%.

2.2 MemTable Data Structures

2.2.1 Characteristics of Two Data Structures

MemStore can be regarded as a container composed of multiple MemTables.

One partition corresponds to one MemTable (non‑partitioned tables have one MemTable, partitioned tables have many).

Insert/Update/Delete operations use either a HashTable or a B‑Tree to locate or modify data.

HashTable advantages : faster conflict checking and row‑lock lookup during inserts or updates.

HashTable disadvantages : not suitable for range queries.

B‑Tree advantages : efficient range scans because data are ordered.

B‑Tree disadvantages : single‑row lookups require many key comparisons, making it slower than HashTable.

2.3 Mini Compaction (Dump)

When a tenant’s MemTable size exceeds freeze_trigger_percentage (default 20%), the data are frozen and dumped to a Mini SSTable, releasing MemStore memory.

Compaction is triggered at the tenant level, not at the cluster level.

Compaction runs on each OBServer based on its local MemStore usage; all partitions of a MemTable are frozen together.

3. Experiment

3.1 Create Tables and Insert Data

Insert 100 000 rows into test_table and 1 000 rows into test_users (SQL shown below).

-- create test_table
CREATE TABLE test_table (
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) DEFAULT NULL,
  create_time DATETIME DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMPRESSION='zstd_1.3.8' REPLICA_NUM=3 BLOCK_SIZE=16384 USE_BLOOM_FILTER=FALSE TABLET_SIZE=134217728 PCTFREE=0;

CALL insert_test_data();

-- create test_users
CREATE TABLE test_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  register_date DATETIME NOT NULL
);
CALL insert_test_users();

3.2 Trigger Tenant Compaction

alter system minor freeze;

3.3 Query Ongoing Compactions

tablet_id

values 49401 and 49402 belong to internal system tables and can be ignored.

Short tablet_id values usually indicate system tables; longer ones (typically six digits) correspond to user tables. ACTION = MINI_MERGE denotes a Mini Compaction. MACRO_BLOCK_COUNT shows the number of macro blocks affected by the compaction.

3.4 Find Table Names by tablet_id

Run the following query in the SYS tenant (replace CON_ID with the actual tenant ID):

SELECT CON_ID, OWNER, OBJECT_NAME, OBJECT_TYPE, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID
FROM oceanbase.CDB_OBJECTS
WHERE CON_ID = 1002 AND DATA_OBJECT_ID = '200006';

The result shows that tablet_id 200006 belongs to test_table. A similar query with DATA_OBJECT_ID = '200007' reveals that tablet_id 200007 belongs to test_users.

3.5 Retrieve DML SQL for the Two Tables

Query GV$OB_SQL_AUDIT for INSERT/UPDATE/DELETE statements on each table, ordered by elapsed time.

SELECT svr_ip, trace_id, sql_id, usec_to_time(request_time), query_sql
FROM gv$ob_sql_audit
WHERE tenant_id = 1002
  AND (query_sql LIKE 'insert%test_table%' OR query_sql LIKE 'INSERT%test_table%'
       OR query_sql LIKE 'delete%test_table%' OR query_sql LIKE 'DELETE%test_table%'
       OR query_sql LIKE 'update%test_table%' OR query_sql LIKE 'UPDATE%test_table%')
ORDER BY elapsed_time DESC
LIMIT 1;

Repeat the same pattern for test_users.

3.6 Verify Effectiveness

Insert an additional 200 000 rows into test_users and repeat the compaction query.

CALL insert_test_users();

3.7 Re‑check Compaction Statistics

After the extra inserts, the MACRO_BLOCK_COUNT for tablet_id 200007 increases from 1 to 2‑3, confirming the impact of the new DML workload.

4. Conclusion

Frequent tenant compactions are usually caused by heavy DML operations; pure read queries mainly use KVCache and have little effect on MemStore, so they rarely trigger compactions.

When a compaction involves many macro blocks, the corresponding DML should be examined and optimized.

5. References

MemTable documentation: https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000002014004

GV$OB_MERGE_INFO reference: https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000002014914