表大小和空洞率批量查询

一、目的

由于Gbase 8a中查询表大小和空洞率，查询系统表时需要制定库名和表名，只能一张表一张表的查询，效率低并且不方便，故更新一版存储过程可以批量查询所有表大小和空洞率。

二、创建过程和使用方法

1. 创建基表

create table gclusterdb.tb_size
 (table_vc varchar(200),table_schema varchar(200), table_name varchar(400),table_rows bigint(20) , storage_size bigint,tb_delete_ratio double );

2. 创建存储过程

DELIMITER //
create  procedure tb_collect ()
begin 
	declare v_table_vc varchar(200);
	declare v_table_schema varchar(200);
	declare v_table_name varchar(400);
    declare v_table_rows bigint(20);
    declare v_storage_size varchar(100);
	declare done int default false;
	declare cur cursor for select table_vc,table_schema,table_name from information_schema.tables where table_schema not in ('information_schema','performance_schema','gbase','gctmpdb','gclusterdb') and TABLE_TYPE ='BASE TABLE';
	declare continue HANDLER for not found set done = true;
		open cur ;
read_loop:loop
			fetch cur into v_table_vc,v_table_schema,v_table_name;
			if done then
				leave read_loop;
			end if;
			set @analy_sql1 = concat('insert into vcname000001.gclusterdb.tb_size ', 'select table_vc,table_schema,table_name,table_rows,storage_size,delete_ratio from performance_schema.tables where table_vc=''',v_table_vc,''' and table_schema=''',v_table_schema,''' and table_name= ''',v_table_name,''';');
			PREPARE analy1 FROM @analy_sql1; 
			-- 如果有变量 EXECUTE analy1 using @var; 
			EXECUTE analy1 ; 
			deallocate prepare analy1; 
			-- select @analy_sql1;
end loop;
		close cur;
end;
//
DELIMITER ;

3. 执行存储过程，记录所有表大小

###每次执行需要进行truncate基表，不然基表中数据会重复###
truncate table gclusterdb.tb_size;

###设置参数，使系统表和数据分部表能够关联查询插入###
set _gbase_query_path=on;
###执行存储过程###
call tb_collect ();

4. 查询表大小,筛选出大小超过200G的表,并且删除率超过80%的表即可

select table_vc,table_schema,table_name,table_rows,storage_size/1024/1024/1024 "storage_size(GB)",tb_delete_ratio from vcname000001.gclusterdb.tb_size where storage_size>214748364800 and tb_delete_ratio>80 order by 5 desc limit 20;

5. 降低上述查出表的空洞率

###执行收缩命令会锁表，并且无法中断，需要安排适当空闲时间执行###
set gcluster_shrink_to_rebalance=1;
alter table table_name shrink space full;