OB表删除后多久释放空间

OCEANBASE 删除表后空间并不会立即释放，需要等待GC完成，GC机制参考

OceanBase 数据库的 GC 回收处理机制-OceanBase知识库

测试验证

1. 查看当前存储实用情况

   SELECT
   svr_ip,
   svr_port,
   CONCAT(ROUND(total_size / 1024 / 1024 / 1024, 2), 'G') AS TOTAL,
   CONCAT(ROUND(free_size / 1024 / 1024 / 1024, 2), 'G') AS FREE,
   CONCAT(ROUND((total_size - free_size)  / 1024 / 1024 / 1024, 2), 'G') AS USED,
   CONCAT(ROUND((total_size - free_size) / total_size * 100, 2),'%') AS USED_PCT
   FROM
   oceanbase.__all_virtual_disk_stat order by 1;
   
   
   +---------------+----------+---------+---------+-------+----------+
   | svr_ip        | svr_port | TOTAL   | FREE    | USED  | USED_PCT |
   +---------------+----------+---------+---------+-------+----------+
   | 192.168.56.36 |     2882 | 831.02G | 830.41G | 0.61G | 0.07%    |
   | 192.168.56.37 |     2882 | 831.02G | 830.41G | 0.61G | 0.07%    |
   | 192.168.56.38 |     2882 | 831.02G | 830.96G | 0.07G | 0.01%    |
   +---------------+----------+---------+---------+-------+----------+
   3 rows in set (0.074 sec)
   
   复制

2. 创建测试表，生成测试数据

   obclient [SYS]>  create table tta(id number,c varchar2(1000));
   Query OK, 0 rows affected (0.151 sec)
   
   obclient [SYS]> insert into tta select rownum,rpad('a',1000,'b') from dual connect by rownum<1000000;
   Query OK, 999999 rows affected (22.560 sec)
   Records: 999999  Duplicates: 0  Warnings: 0
   
   obclient [SYS]> insert into tta select * from tta;
   Query OK, 999999 rows affected (18.958 sec)
   Records: 999999  Duplicates: 0  Warnings: 0
   
   obclient [SYS]> insert into tta select * from tta;
   Query OK, 1999998 rows affected (38.497 sec)
   Records: 1999998  Duplicates: 0  Warnings: 0
   
   obclient [SYS]> commit;
   insert into tta select * from tta;Query OK, 0 rows affected (10.078 sec)
   
   obclient [SYS]> insert into tta select * from tta;
   Query OK, 3999996 rows affected (1 min 17.757 sec)
   Records: 3999996  Duplicates: 0  Warnings: 0
   
   obclient [SYS]> commit;
   Query OK, 0 rows affected (10.041 sec)
   
   复制

3. 合并

   obclient [oceanbase]> alter system major freeze;
   Query OK, 0 rows affected (0.016 sec)
   
   
   复制

4. 查看表信息及大小

   obclient [oceanbase]> select table_id from __all_virtual_table where table_name='TTA';
   +------------------+
   | table_id         |
   +------------------+
   | 1103909674337118 |
   +------------------+
   1 row in set (0.021 sec)
   
   
   select SVR_IP,table_name,sum(size)/1024/1024 
   from  oceanbase.gv$sstable  where  table_id=1103909674337118
   group by SVR_IP,table_name order by  sum(size)/1024/1024;
   +---------------+------------+---------------------+
   | SVR_IP        | table_name | sum(size)/1024/1024 |
   +---------------+------------+---------------------+
   | 192.168.56.36 | TTA        |        231.66237354 |
   | 192.168.56.37 | TTA        |        232.02638817 |
   +---------------+------------+---------------------+
   2 rows in set (0.096 sec)
   
   
   
   obclient [oceanbase]> select svr_ip,round(sum(occupy_size)/1024/1024) data_size,count(*)*2  macroblock_size from __all_virtual_partition_sstable_macro_info where table_id=1103909674337118 and partition_id=0  group by svr_ip;
   +---------------+-----------+-----------------+
   | svr_ip        | data_size | macroblock_size |
   +---------------+-----------+-----------------+
   | 192.168.56.36 |       232 |             234 |
   | 192.168.56.37 |       232 |             234 |
   +---------------+-----------+-----------------+
   2 rows in set (0.061 sec)
   
   
   
   
   复制

5. 查看当前存储使用情况

   0.97G-0.61G=0.36G

   增长了360M

   SELECT
   svr_ip,
   svr_port,
   CONCAT(ROUND(total_size / 1024 / 1024 / 1024, 2), 'G') AS TOTAL,
   CONCAT(ROUND(free_size / 1024 / 1024 / 1024, 2), 'G') AS FREE,
   CONCAT(ROUND((total_size - free_size)  / 1024 / 1024 / 1024, 2), 'G') AS USED,
   CONCAT(ROUND((total_size - free_size) / total_size * 100, 2),'%') AS USED_PCT
   FROM
   oceanbase.__all_virtual_disk_stat order by 1;
   
   +---------------+----------+---------+---------+-------+----------+
   | svr_ip        | svr_port | TOTAL   | FREE    | USED  | USED_PCT |
   +---------------+----------+---------+---------+-------+----------+
   | 192.168.56.36 |     2882 | 831.02G | 830.05G | 0.97G | 0.12%    |
   | 192.168.56.37 |     2882 | 831.02G | 829.93G | 1.10G | 0.13%    |
   | 192.168.56.38 |     2882 | 831.02G | 830.95G | 0.08G | 0.01%    |
   +---------------+----------+---------+---------+-------+----------+
   3 rows in set (0.031 sec)
   
   复制

6. 删除表

   obclient [SYS]> drop table tta purge;
   Query OK, 0 rows affected (0.143 sec)
   
   
   复制

7. 查看已删除表sstable占用

   gv$sstable SStable中已查不到该表的信息
   obclient [oceanbase]> select SVR_IP,table_name,sum(size)/1024/1024
       -> from  oceanbase.gv$sstable  where  table_id=1103909674337118
       -> group by SVR_IP,table_name order by  sum(size)/1024/1024;
   Empty set (0.093 sec)
   
   
   宏块占用还可以查到
   
   select svr_ip,round(sum(occupy_size)/1024/1024) data_size,count(*)*2  macroblock_size from __all_virtual_partition_sstable_macro_info where table_id=1103909674337118 and partition_id=0  group by svr_ip;
   
   +---------------+-----------+-----------------+
   | svr_ip        | data_size | macroblock_size |
   +---------------+-----------+-----------------+
   | 192.168.56.36 |       232 |             234 |
   | 192.168.56.37 |       232 |             234 |
   +---------------+-----------+-----------------+
   2 rows in set (0.059 sec)
   
   复制

8. 等待GC完成

   直到从__all_virtual_tenant_gc_partition_info查不到该表
    select * from __all_virtual_tenant_gc_partition_info where table_id=1103909674337118;
   +-----------+------------------+--------------+----------------------------+----------------------------+
   | tenant_id | table_id         | partition_id | gmt_create                 | gmt_modified               |
   +-----------+------------------+--------------+----------------------------+----------------------------+
   |      1004 | 1103909674337118 |            0 | 2024-10-22 16:23:19.497538 | 2024-10-22 16:23:19.497538 |
   +-----------+------------------+--------------+----------------------------+----------------------------+
   
   
   经过一段时间后，查不到该表的宏块占用
   obclient [oceanbase]> select  table_id,role,round(sum(required_size)/1024/1024/1024,2) gb  from __all_virtual_meta_table where (table_id,partition_id) in (select TABLE_ID,partition_id from __all_virtual_tenant_gc_partition_info
       -> where (TABLE_ID,partition_id ) not in (select TABLE_ID,partition_id  from __all_virtual_table))
       -> group by table_id,role order by round(sum(required_size)/1024/1024/1024,2) desc limit 10;
   Empty set (0.008 sec)
   
   
   obclient [oceanbase]> select svr_ip,round(sum(occupy_size)/1024/1024) data_size,count(*)*2  macroblock_size from __all_virtual_partition_sstable_macro_info where table_id=1103909674337118 and partition_id=0  group by svr_ip;
   Empty set (0.060 sec)
   
   
   查__all_virtual_disk_stat中存储使用信息，仍然未释放
   SELECT
   svr_ip,
   svr_port,
   CONCAT(ROUND(total_size / 1024 / 1024 / 1024, 2), 'G') AS TOTAL,
   CONCAT(ROUND(free_size / 1024 / 1024 / 1024, 2), 'G') AS FREE,
   CONCAT(ROUND((total_size - free_size)  / 1024 / 1024 / 1024, 2), 'G') AS USED,
   CONCAT(ROUND((total_size - free_size) / total_size * 100, 2),'%') AS USED_PCT
   FROM
   oceanbase.__all_virtual_disk_stat order by 1;
   
   +---------------+----------+---------+---------+-------+----------+
   | svr_ip        | svr_port | TOTAL   | FREE    | USED  | USED_PCT |
   +---------------+----------+---------+---------+-------+----------+
   | 192.168.56.36 |     2882 | 831.02G | 830.05G | 0.97G | 0.12%    |
   | 192.168.56.37 |     2882 | 831.02G | 830.29G | 0.74G | 0.09%    |
   | 192.168.56.38 |     2882 | 831.02G | 830.95G | 0.08G | 0.01%    |
   +---------------+----------+---------+---------+-------+----------+
   3 rows in set (0.027 sec)
   
   复制

9. 继续等一段时间后查看存储使用情况，已释放，存储使用已释放，但未释放到创建表之前的水平

   0.97G-0.74G=0.23G

   释放230M，与TTA SSTABLE大小一致

   obclient [oceanbase]> select svr_ip,round(sum(occupy_size)/1024/1024) data_size,count(*)*2  macroblock_size from __all_virtual_partition_sstable_macro_info where table_id=1103909674337118 and partition_id=0  group by svr_ip;
   Empty set (0.061 sec)
   
   SELECT
   svr_ip,
   svr_port,
   CONCAT(ROUND(total_size / 1024 / 1024 / 1024, 2), 'G') AS TOTAL,
   CONCAT(ROUND(free_size / 1024 / 1024 / 1024, 2), 'G') AS FREE,
   CONCAT(ROUND((total_size - free_size)  / 1024 / 1024 / 1024, 2), 'G') AS USED,
   CONCAT(ROUND((total_size - free_size) / total_size * 100, 2),'%') AS USED_PCT
   FROM
   oceanbase.__all_virtual_disk_stat order by 1;
   
   +---------------+----------+---------+---------+-------+----------+
   | svr_ip        | svr_port | TOTAL   | FREE    | USED  | USED_PCT |
   +---------------+----------+---------+---------+-------+----------+
   | 192.168.56.36 |     2882 | 831.02G | 830.29G | 0.74G | 0.09%    |
   | 192.168.56.37 |     2882 | 831.02G | 830.29G | 0.74G | 0.09%    |
   | 192.168.56.38 |     2882 | 831.02G | 830.95G | 0.08G | 0.01%    |
   +---------------+----------+---------+---------+-------+----------+
   3 rows in set (0.030 sec)
   
   
   
   复制