GBase 8a提供了查看分布表各分片数据量的功能,在新版9.5里又提供了segment_id来查看分片分片数据,本文提供了2个自定义存储过程,来方便试用。
目录导航
参考
GBase 8a 从集群层通过segment_id直接查询分片数据排查倾斜
存储过程
公共函数和存储过程
executeSQL通用动态SQL
因为是通用的,所以无需每次都删除老的。
delimiter //
create procedure executeSQL(
S_SQL_TMP VARCHAR(21000)
)
BEGIN
set @executeSQL_sql = S_SQL_TMP;
PREPARE executeSQL_s1 FROM @executeSQL_sql ;
EXECUTE executeSQL_s1;
DEALLOCATE PREPARE executeSQL_s1;
END //
delimiter ;
check_version 检查版本
drop function if exists check_version;
delimiter //
create function check_version(ver varchar) returns boolean
begin
select instr(version(),ver) into @rtn;
return @rtn;
end //
delimiter ;
sp_table_segment_size 分片数据量
drop procedure if exists sp_table_segment_size;
delimiter //
create procedure sp_table_segment_size(dbname varchar,tbname varchar)
begin
set @sql=concat('select SUFFIX,HOST,TABLE_DATA_SIZE,TABLE_STORAGE_SIZE,DATA_PERCENT from information_schema.CLUSTER_TABLE_SEGMENTS a where table_schema=\''
,dbname
,'\' and table_name=\''
,tbname
,'\'');
call executeSQL(@sql);
end
//
delimiter ;
call sp_table_segment_size('testdb','t1');
sp_table_segment_count 分片行数
要求9.5+版本才支持
drop procedure if exists sp_table_segment_count;
delimiter //
create procedure sp_table_segment_count(dbname varchar,tbname varchar)
main:begin
select check_version('9.5.3') into @rtn from dual;
if !@rtn then
select 'this function need 9.5.3 +';
leave main;
end if;
call executeSQL('set gcluster_segment_id_replace=1');
set @sql=concat('select segment_id,count(*) from '
,dbname
,'.'
,tbname
,' group by segment_id');
call executeSQL(@sql);
end
//
delimiter ;
call sp_table_segment_count('testdb','t1');