GBase 8a运维管理用自定义存储过程汇总

本文汇总了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