本文汇总了GBase 8a常见方便运维的自定义存储过程,包括整个集群运行的SQL,表的磁盘空间,分片空间,分片行数等。后续逐渐增加中。
目录导航
executeSQL 【通用】动态执行SQL
动态生成SQL的字符串,然后执行。注意长度最大10K。
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_cluster_processlist 集群层正在运行的SQL任务
drop procedure if exists sp_cluster_processlist;
delimiter //
create procedure sp_cluster_processlist(showSize int,topN int)
begin
set @sql=concat('select COORDINATOR_NAME, ID, user, host, command, start_time, time, state, substring(info,0,'
,showSize
,') info from information_schema.COORDINATORS_TASK_INFORMATION where command=\'query\' and info is not null and info not like \'%information_schema.processlist%\' order by time desc limit '
,topN);
call executeSQL(@sql);
end
//
delimiter ;
call sp_cluster_processlist(100,10);
sp_node_processlist 计算层正在运行的SQL任务
drop procedure if exists sp_node_processlist;
delimiter //
create procedure sp_node_processlist(showSize int,topN int)
begin
set @sql=concat('select NODE_NAME, ID, user, host, command, start_time, time, state,substring(info,0,'
,showSize
,') info from information_schema.GNODES_TASK_INFORMATION where command=\'query\' and info is not null and info not like \'%information_schema.processlist%\' order by time desc limit '
,topN);
call executeSQL(@sql);
end
//
delimiter ;
call sp_node_processlist(100,10);
sp_table_size 表占用的磁盘空间大小汇总
drop procedure if exists sp_table_size;
delimiter //
create procedure sp_table_size(dbname varchar,tbname varchar)
begin
set @sql=concat('select * from information_schema.cluster_tables a where table_schema=\''
,dbname
,'\' and table_name=\''
,tbname
,'\'');
call executeSQL(@sql);
end
//
delimiter ;
call sp_table_size('testdb','t1');
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 表在各个数据节点分片的数据行数
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');
汇总
-- 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_cluster_processlist 集群层正在运行的SQL任务
drop procedure if exists sp_cluster_processlist;
delimiter //
create procedure sp_cluster_processlist(showSize int,topN int)
begin
set @sql=concat('select COORDINATOR_NAME, ID, user, host, command, start_time, time, state, substring(info,0,'
,showSize
,') info from information_schema.COORDINATORS_TASK_INFORMATION where command=\'query\' and info is not null and info not like \'%information_schema.processlist%\' order by time desc limit '
,topN);
call executeSQL(@sql);
end
//
delimiter ;
-- call sp_cluster_processlist(100,10);
-- sp_node_processlist 计算层正在运行的SQL任务
drop procedure if exists sp_node_processlist;
delimiter //
create procedure sp_node_processlist(showSize int,topN int)
begin
set @sql=concat('select NODE_NAME, ID, user, host, command, start_time, time, state,substring(info,0,'
,showSize
,') info from information_schema.GNODES_TASK_INFORMATION where command=\'query\' and info is not null and info not like \'%information_schema.processlist%\' order by time desc limit '
,topN);
call executeSQL(@sql);
end
//
delimiter ;
-- call sp_node_processlist(100,10);
-- sp_table_size 表占用的磁盘空间大小汇总
drop procedure if exists sp_table_size;
delimiter //
create procedure sp_table_size(dbname varchar,tbname varchar)
begin
set @sql=concat('select * from information_schema.cluster_tables a where table_schema=\''
,dbname
,'\' and table_name=\''
,tbname
,'\'');
call executeSQL(@sql);
end
//
delimiter ;
-- call sp_table_size('testdb','t1');
-- 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 表在各个数据节点分片的数据行数
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');
-- 查看当前库的存储过程
show procedure status