南大通用数据库-Gbase-8a-空洞率查询与解决方法

自定义存储过程实现：查询全库（不计算系统库）中所有表的空洞率。

drop procedure if exists "CheckALLDbVoidRate";
 
DELIMITER //
CREATE PROCEDURE "CheckALLDbVoidRate"(VoidRate varchar(30))
begin
    DECLARE SelectALLTableSql   text;
    DECLARE SelectTableVoidRate text;
    DECLARE ExitFlag            INT; 
    DECLARE TmpDB               VARCHAR(100);
    DECLARE TmpTab              VARCHAR(100);
    DECLARE cur REF CURSOR; 
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET ExitFlag = 1;
    
    set ExitFlag            = 0;
    
    set SelectALLTableSql   = 'select TABLE_SCHEMA ,TABLE_NAME from information_schema.tables 
                               where TABLE_TYPE = \'BASE TABLE\' AND TABLE_SCHEMA not in 
                               (\'information_schema\',\'performance_schema\',\'gbase\',\'gclusterdb\')';
                               
    set SelectTableVoidRate = 'select TABLE_SCHEMA,TABLE_NAME,truncate(STORAGE_SIZE/1024/1024) as STORAGESIZE,DELETE_RATIO 
                               from performance_schema.tables 
                               where table_schema=? and table_name=? and DELETE_RATIO >= '||VoidRate||';';
    
    
    set @ExecuteSql         = SelectALLTableSql;
    set @ExecuteSql_1       = SelectTableVoidRate;
    
    prepare stmt from @ExecuteSql_1;
    
    OPEN cur FOR @ExecuteSql;
    WHILE ExitFlag = 0 DO
        FETCH cur INTO TmpDB,TmpTab; 
        if ExitFlag = 0 then
            set @Tab    = TmpTab;
            set @DB     = TmpDB;
            # select @DB,@Tab,@ExecuteSql_1;
            EXECUTE stmt using @DB,@Tab ; 
        end if;
    END WHILE;    
    CLOSE cur; 
    
    deallocate prepare stmt;
end;//
DELIMITER ;
 
call CheckALLDbVoidRate('0');

虚拟机测试:

gbase> drop procedure if exists "CheckALLDbVoidRate";
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
 
gbase> 
gbase> DELIMITER //
gbase> CREATE PROCEDURE "CheckALLDbVoidRate"(VoidRate varchar(30))
    -> begin
    ->     DECLARE SelectALLTableSql   text;
    ->     DECLARE SelectTableVoidRate text;
    ->     DECLARE ExitFlag            INT; 
    -> DECLARE TmpDB               VARCHAR(100);
    -> DECLARE TmpTab              VARCHAR(100);
    ->     DECLARE cur REF CURSOR; 
    ->     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET ExitFlag = 1;
    ->     
    ->     set ExitFlag            = 0;
    -> 
    ->     set SelectALLTableSql   = 'select TABLE_SCHEMA ,TABLE_NAME from information_schema.tables 
    '>                            where TABLE_TYPE = \'BASE TABLE\' AND TABLE_SCHEMA not in 
    '>                                (\'information_schema\',\'performance_schema\',\'gbase\',\'gclusterdb\')';
    ->    
    -> set SelectTableVoidRate = 'select TABLE_SCHEMA,TABLE_NAME,truncate(STORAGE_SIZE/1024/1024) as STORAGESIZE,DELETE_RATIO 
    '>                            from performance_schema.tables 
    '>                                where table_schema=? and table_name=? and DELETE_RATIO >= '||VoidRate||';';
    -> 
    -> 
    ->     set @ExecuteSql         = SelectALLTableSql;
    ->     set @ExecuteSql_1       = SelectTableVoidRate;
    -> 
    -> prepare stmt from @ExecuteSql_1;
    -> 
    ->     OPEN cur FOR @ExecuteSql;
    ->     WHILE ExitFlag = 0 DO
    ->     FETCH cur INTO TmpDB,TmpTab; 
    ->         if ExitFlag = 0 then
    ->     set @DB     = TmpDB;
    -> set @Tab    = TmpTab;
    -> # select @DB,@Tab,@ExecuteSql_1;
    ->             EXECUTE stmt using @DB,@Tab ; 
    ->         end if;
    ->     END WHILE;
    ->     CLOSE cur; 
    -> 
    -> deallocate prepare stmt;
    -> end;//
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
 
gbase> DELIMITER ;
gbase> 
gbase> call CheckALLDbVoidRate('0');
+--------------+--------------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME         | STORAGESIZE | DELETE_RATIO |
+--------------+--------------------+-------------+--------------+
| bd_db_a141   | t_hn_customer_list |           0 |            0 |
+--------------+--------------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.00)
 
+--------------+-------------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME        | STORAGESIZE | DELETE_RATIO |
+--------------+-------------------+-------------+--------------+
| bd_db_a141   | t_hn_stage_target |           0 |            0 |
+--------------+-------------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.00)
 
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| t1           | a          |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.01)
 
+--------------+---------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME    | STORAGESIZE | DELETE_RATIO |
+--------------+---------------+-------------+--------------+
| t1           | alldbvoidrate |           0 |            0 |
+--------------+---------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.01)
 
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| t1           | b          |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.01)
 
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| t1           | czg        |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.01)
 
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| t1           | czg_test   |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.01)
 
+--------------+------------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME       | STORAGESIZE | DELETE_RATIO |
+--------------+------------------+-------------+--------------+
| t1           | d_admin_kpi_code |           0 |            0 |
+--------------+------------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.02)
 
+--------------+--------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | STORAGESIZE | DELETE_RATIO |
+--------------+--------------+-------------+--------------+
| t1           | hash_tb_like |           0 |            0 |
+--------------+--------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.02)
 
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| t1           | moon       |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.02)
 
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| t1           | moon_copy  |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.02)
 
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| t1           | sun        |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.02)
 
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| t1           | test       |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.02)
 
+--------------+--------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | STORAGESIZE | DELETE_RATIO |
+--------------+--------------+-------------+--------------+
| t1          | test20230302 |           0 |            0 |
+--------------+--------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.03)
 
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| t1           | test_12_05 |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.03)
 
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| t1           | test_table |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.03)
 
+--------------+--------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | STORAGESIZE | DELETE_RATIO |
+--------------+--------------+-------------+--------------+
| t1           | test_table_1 |           0 |            0 |
+--------------+--------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.03)
 
+--------------+--------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | STORAGESIZE | DELETE_RATIO |
+--------------+--------------+-------------+--------------+
| t1           | test_table_2 |           0 |            0 |
+--------------+--------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.04)
 
+--------------+--------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | STORAGESIZE | DELETE_RATIO |
+--------------+--------------+-------------+--------------+
| t1           | test_table_3 |           0 |            0 |
+--------------+--------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.04)
 
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| t1           | testtab    |           9 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.04)
 
+--------------+--------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | STORAGESIZE | DELETE_RATIO |
+--------------+--------------+-------------+--------------+
| t1           | testtab_copy |           4 |            0 |
+--------------+--------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.04)
 
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| t1           | a          |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.04)
 
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| t1           | b          |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.05)
 
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| t1           | ttt        |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.05)
 
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| t2           | qqqqqqqq   |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.05)
 
+--------------+------------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME       | STORAGESIZE | DELETE_RATIO |
+--------------+------------------+-------------+--------------+
| t2           | d_admin_kpi_code |           0 |            0 |
+--------------+------------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.05)
 
+--------------+--------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | STORAGESIZE | DELETE_RATIO |
+--------------+--------------+-------------+--------------+
| t2           | hash_tb_like |           0 |            0 |
+--------------+--------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.06)
 
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| t2           | moon       |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.06)
 
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| t2           | moon_copy  |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.06)
 
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| t2           | sun        |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.06)
 
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| t2           | test_12_05 |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.07)
 
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| t2           | test_table |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.07)
 
+--------------+--------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | STORAGESIZE | DELETE_RATIO |
+--------------+--------------+-------------+--------------+
| t2           | test_table_1 |           0 |            0 |
+--------------+--------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.07)
 
+--------------+--------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | STORAGESIZE | DELETE_RATIO |
+--------------+--------------+-------------+--------------+
| t2           | test_table_2 |           0 |            0 |
+--------------+--------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.07)
 
+--------------+--------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | STORAGESIZE | DELETE_RATIO |
+--------------+--------------+-------------+--------------+
| t2           | test_table_3 |           0 |            0 |
+--------------+--------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.08)
 
+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| t2           | testtab    |           1 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.08)
 
+--------------+--------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | STORAGESIZE | DELETE_RATIO |
+--------------+--------------+-------------+--------------+
| t2           | testtab_copy |           0 |            0 |
+--------------+--------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.08)
 
Query OK, 0 rows affected (Elapsed: 00:00:00.08)

解决方法:

1、重建表

gbase> create table t1.test_new like t1.test;   
Query OK, 0 rows affected (Elapsed: 00:00:00.13)
 
gbase> insert into t1.test_new select * from  t1.test;
Query OK, 0 rows affected (Elapsed: 00:00:00.02)
Records: 0  Duplicates: 0  Warnings: 0

2、shrink space

建议在有gcluster_shrink_to_rebalance这个参数的版本使用shrink space full，避免出现一些不必要的麻烦。

gbase> set gcluster_shrink_to_rebalance=1;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
 
gbase> alter table t1.test shrink space full ;
Query OK, 0 rows affected (Elapsed: 00:00:00.20)

这种耗时会较短在实际操作中，但释放空间效率不高，因为gbase按照列进行存储，一个列存储成一个文件，文件大小为2G，也就是下面的这个参数，需要这个文件中的每一条数据都打上删除标记，才能删除文件来释放空间。

base> show variables like'_gbase_segment_size';
+---------------------+------------+
| Variable_name       | Value      |
+---------------------+------------+
| _gbase_segment_size | 2147483648 |
+---------------------+------------+
1 row in set (Elapsed: 00:00:00.00)
 
gbase> select 2147483648 / 1024 / 1024 / 1024;
+---------------------------------+
| 2147483648 / 1024 / 1024 / 1024 |
+---------------------------------+
|                  2.000000000000 |
+---------------------------------+
1 row in set (Elapsed: 00:00:00.00)