You can check the storage usage by Workload Repository (WR) by using SQL statements. If WR occupies too much storage space, you can use the database management system (DBMS) package to delete data.
Procedure
Check the storage space occupied by WR.
Query the SSTable space occupied by WR.
Query the
__all_virtual_table,__all_virtual_tablet_meta_table, and__all_virtual_ls_meta_tabletables for the SSTable space occupied by WR.select sum(b.data_size) from __all_virtual_table a inner join __all_virtual_tablet_meta_table b on a.tenant_id=b.tenant_id and a.tablet_id=b.tablet_id left join __all_virtual_ls_meta_table c on b.tenant_id=c.tenant_id and b.ls_id = c.ls_id and b.svr_ip=c.svr_ip and b.svr_port = c.svr_port where a.table_name like "__wr_%" and a.tablet_id != 0;The return result is as follows:
+------------------+ | sum(b.data_size) | +------------------+ | 1196806 | +------------------+ 1 row in set (0.079 sec)In the syntax,
data_sizespecifies the total size of data stored in the table, in bytes.Query the MemTable space occupied by WR.
Query the
__all_virtual_table,__all_virtual_memstore_info, and__all_virtual_ls_meta_tabletables for the MemTable space occupied by WR.select sum(mem_used), sum(hash_mem_used), sum(btree_mem_used) from __all_virtual_table a inner join __all_virtual_memstore_info b on a.tenant_id=b.tenant_id and a.tablet_id=b.tablet_id left join __all_virtual_ls_meta_table c on b.tenant_id=c.tenant_id and b.ls_id = c.ls_id and b.svr_ip=c.svr_ip and b.svr_port = c.svr_port where a.table_name like "__wr%" and a.tablet_id != 0;The return result is as follows:
+---------------+--------------------+---------------------+ | sum(mem_used) | sum(hash_mem_used) | sum(btree_mem_used) | +---------------+--------------------+---------------------+ | 46137344 | 4207936 | 352 | +---------------+--------------------+---------------------+ 1 row in set (0.087 sec)The following table describes the fields.
Field Description mem_used The total size of memory occupied by the table, in bytes. hash_mem_used The total size of memory occupied by the hash table, in bytes. btree_mem_used The total size of memory occupied by tree indexes, in bytes.
Clean up the space occupied by WR. After you use the DBMS package to clean up the space occupied by WR, you must initiate a major compaction.
Query all snapshot IDs in the tenant.
obclient [oceanbase]> select count(1),min(snap_id), max(snap_id) from oceanbase.DBA_WR_SNAPSHOT;The return result is as follows:
+----------+--------------+--------------+ | count(1) | min(snap_id) | max(snap_id) | +----------+--------------+--------------+ | 230 | 47 | 276 | +----------+--------------+--------------+ 1 row in set (0.051 sec)The range of snapshot IDs queried is [47, 276].
Delete snapshot data within the a specified snapshot ID range. For example, to delete data whose snapshot IDs fall in the range of [47,275], run the following command:
obclient [oceanbase]> CALL DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE('47','275'); Query OK, 0 rows affected, 4 warnings (4.129 sec)Query whether the snapshots are successfully deleted.
obclient [oceanbase]> select count(1),min(snap_id), max(snap_id) from oceanbase.DBA_WR_SNAPSHOT;The return result is as follows:
+----------+--------------+--------------+ | count(1) | min(snap_id) | max(snap_id) | +----------+--------------+--------------+ | 1 | 276 | 276 | +----------+--------------+--------------+ 1 row in set (0.043 sec)The value of
SNAP_IDin the return result is276, indicating that the specified snapshots are deleted.
Initiate a major compaction to release the space. For more information about major compactions, see Major compaction.
Initiate a major compaction.
Note
We recommend that you do not initiate a major compaction during peak hours.
Query the
oceanbase.CDB_OB_ZONE_MAJOR_COMPACTIONview to check whether the major compaction is completed. If the value of theSTATUSfield changes toIDLE, the major compaction is completed.obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_ZONE_MAJOR_COMPACTION where tenant_id = 1;The value of the
STATUSfield isCOMPACTING, indicating that the major compaction is in progress.+-----------+-------+---------------------+---------------------+----------------------------+----------------------------+------------+ | TENANT_ID | ZONE | BROADCAST_SCN | LAST_SCN | LAST_FINISH_TIME | START_TIME | STATUS | +-----------+-------+---------------------+---------------------+----------------------------+----------------------------+------------+ | 1 | zone1 | 1695798402011671173 | 1695751201399410304 | 2023-09-27 02:02:14.339229 | 2023-09-27 15:06:42.095810 | COMPACTING | +-----------+-------+---------------------+---------------------+----------------------------+----------------------------+------------+ 1 row in set (0.051 sec)In this case, wait for 1 to 2 minutes until the major compaction is completed.
+-----------+-------+---------------------+---------------------+----------------------------+----------------------------+--------+ | TENANT_ID | ZONE | BROADCAST_SCN | LAST_SCN | LAST_FINISH_TIME | START_TIME | STATUS | +-----------+-------+---------------------+---------------------+----------------------------+----------------------------+--------+ | 1 | zone1 | 1695798402011671173 | 1695798402011671173 | 2023-09-27 15:08:36.406838 | 2023-09-27 15:06:42.095810 | IDLE | +-----------+-------+---------------------+---------------------+----------------------------+----------------------------+--------+ 1 row in set (0.043 sec)
Query the storage space occupied by WR.
Query the SSTable space occupied by WR.
Query the
__all_virtual_table,__all_virtual_tablet_meta_table, and__all_virtual_ls_meta_tabletables for the SSTable space occupied by WR.select sum(b.data_size) from __all_virtual_table a inner join __all_virtual_tablet_meta_table b on a.tenant_id=b.tenant_id and a.tablet_id=b.tablet_id left join __all_virtual_ls_meta_table c on b.tenant_id=c.tenant_id and b.ls_id = c.ls_id and b.svr_ip=c.svr_ip and b.svr_port = c.svr_port where a.table_name like "__wr_%" and a.tablet_id != 0;The following return result indicates that the SSTable space occupied by WR has been released.
+------------------+ | sum(b.data_size) | +------------------+ | 482353 | +------------------+ 1 row in set (0.065 sec)- Query the MemTable space occupied by WR.
Query the
__all_virtual_table,__all_virtual_memstore_info, and__all_virtual_ls_meta_tabletables for the MemTable space occupied by WR.select sum(mem_used), sum(hash_mem_used), sum(btree_mem_used) from __all_virtual_table a inner join __all_virtual_memstore_info b on a.tenant_id=b.tenant_id and a.tablet_id=b.tablet_id left join __all_virtual_ls_meta_table c on b.tenant_id=c.tenant_id and b.ls_id = c.ls_id and b.svr_ip=c.svr_ip and b.svr_port = c.svr_port where a.table_name like "__wr%" and a.tablet_id != 0;The following return result indicates that the MemTable space occupied by WR has been released.
+---------------+--------------------+---------------------+ | sum(mem_used) | sum(hash_mem_used) | sum(btree_mem_used) | +---------------+--------------------+---------------------+ | 12582912 | 1053952 | 128 | +---------------+--------------------+---------------------+ 1 row in set (0.076 sec)
