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

发表于 作者 laozizhu

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

Post Views: 691

相关文章:

  1. GBase8a MPP Cluster集群状态gcadmin输出说明
  2. GBase8a 集群查看某张表占用的磁盘空间大小
  3. GBase8a 集群查看数据库参数的当前值
  4. GBase 8a集群查看当前运行状态，内存使用情况
  5. GBase 8a集群查看所有节点正在运行的SQL
  6. GBase 8a集群查看加载进度的方法
  7. GBase 8a数据库集群show processlist中state不同状态含义说明
  8. GBase 8a数据库集群show processlist内command数值含义说明
  9. GBase 8a通过集群日志查看执行计划和每个阶段的整体耗时和各个节点的耗时做性能排查
  10. GBase 8a 自定义存储过程 sp_cluster_processlist 查看集群任务SQL状态