Clean up WR data

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

1. 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_table tables 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_size specifies 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_table tables 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.

2. 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.

   1. 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].

   2. 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)
      

   3. 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_ID in the return result is 276, indicating that the specified snapshots are deleted.

3. Initiate a major compaction to release the space. For more information about major compactions, see Major compaction.

   1. Initiate a major compaction.

      Note

      We recommend that you do not initiate a major compaction during peak hours.

      ```sql obclient [oceanbase]> ALTER SYSTEM MAJOR FREEZE TENANT = sys; Query OK, 0 rows affected (0.049 sec) ```

   2. Query the oceanbase.CDB_OB_ZONE_MAJOR_COMPACTION view to check whether the major compaction is completed. If the value of the STATUS field changes to IDLE, the major compaction is completed.

      obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_ZONE_MAJOR_COMPACTION where tenant_id = 1;
      

      The value of the STATUS field is COMPACTING, 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)
      

4. 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_table tables 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_table tables 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)
     

References

• Overview
• Manage WR
• Monitor historical session performance