GBase 8a 统计表大小

GBase 8a 统计表大小用于分析表增量

#!/bin/bash
# 0 0 * * * source $HOME/.gbase_profile && /home/gbase/tmp_db_size/statistic_tablesize.sh 定时任务执行
# Version : 1.2.1   update date: 2024-03-25

# 操作系统用户
dbaUser=gbase
home_path=/home/gbase/tmp_db_size
# 数据库可操作用户
dbUser=gbase
dbapassword=gbase20110531
sql_path=$home_path/sqlfile
sql_file=$sql_path/sqlfile_`date +'%F'`.sql
sql_file_bak=$sql_path/sqlfile_`date +'%F'`.sql_bak
run_log=$home_path/log
log_file=$run_log/log_`date +'%F'`.log
now_date=`date +'%F %T'`

# dba用户判断
if [ `whoami` != "$dbaUser" ];then
	echo "Please use $dbaUser run me!" 
	exit 1
fi
# SQL文件路径
if [ ! -d "$sql_path" ]
then
	mkdir -p "$sql_path" 
fi
# 日志目录
if [ ! -d "$run_log" ]
then
	mkdir -p "$run_log" 
fi

echo "Run time: `date +'%F %T'`" | tee -a $log_file

# 写sql文件
echo "insert into tmp_db_size.tmp_cluster_tables values " > $sql_file

# 方法：通过查找系统表，使用group by去查找数据
count=0
total=$(gccli -u$dbUser -p$dbapassword -Dgbase -N -e "
select COUNT(*)
from information_schema.tables 
where table_type !='SYSTEM VIEW' and table_type != 'VIEW' and engine != 'MEMORY' and engine != 'GsSYS' and TABLE_SCHEMA != 'gclusterdb';
")

echo "总行数为${total}" | tee -a $log_file

gccli -u$dbUser -p$dbapassword -Dgbase -N -e "
select TABLE_SCHEMA ,TABLE_NAME from information_schema.tables 
where table_type !='SYSTEM VIEW' and table_type != 'VIEW' and engine != 'MEMORY' and engine != 'GsSYS' and TABLE_SCHEMA != 'gclusterdb'
group by TABLE_SCHEMA ,TABLE_NAME ; 
" | while read -r DbTbName;
do
	database=`echo $DbTbName | awk -F ' ' '{print $1}' `;
	tablename=`echo $DbTbName | awk -F ' ' '{print $2}' `;
	((count++))
	# 返回结果里已经包含了副本空间。单位是字节
	# table_storage_size是全部占用的空间，包括元数据和数据等
	gccli -u$dbUser -p$dbapassword -D$database -N -e "
		select TABLE_VC,TABLE_SCHEMA,TABLE_NAME,CREATE_TIME,UPDATE_TIME,TABLE_STORAGE_SIZE,round(TABLE_STORAGE_SIZE/1024/1024,2) TABLE_STORAGE_SIZE_MB,TABLE_TYPE
		from information_schema.cluster_tables 
		where table_schema='$database' and TABLE_NAME='$tablename';
	" | while read -r clustertable;
	do
		# echo $clustertable;
		table_vc=`echo $clustertable | awk -F ' ' '{print $1}' `;
		table_schema=`echo $clustertable | awk -F ' ' '{print $2}' `;
		table_name=`echo $clustertable | awk -F ' ' '{print $3}' `;
		create_time=`echo $clustertable | awk -F ' ' '{print $4 " " $5}' `;
		update_time=`echo $clustertable | awk -F ' ' '{print $6 " " $7}' `;
		table_stroage_size=`echo $clustertable | awk -F ' ' '{print $8}' `;
		table_stroage_size_mb=`echo $clustertable | awk -F ' ' '{print $9}' `;
		table_type=`echo $clustertable | awk -F ' ' '{print $10}' `;
		
		# 输出变量
		# echo "('$table_vc','$table_schema','$table_name','$create_time','$update_time','$table_stroage_size','$table_stroage_size_mb','$table_type','$now_date'),"  >> $sql_file
		# 判断是否是最后一个变量
        if [[ $count -eq $total ]]; then
            # 输出变量
            echo "('$table_vc','$table_schema','$table_name','$create_time','$update_time','$table_stroage_size','$table_stroage_size_mb','$table_type','$now_date');" >> "$sql_file"
        else
            echo "('$table_vc','$table_schema','$table_name','$create_time','$update_time','$table_stroage_size','$table_stroage_size_mb','$table_type','$now_date')," >> "$sql_file"
        fi
	done
done




# 创建临时库表
gccli -u$dbUser -p$dbapassword -Dgbase -e '
create database if not exists tmp_db_size;
CREATE table if not exists tmp_db_size."tmp_cluster_tables" (
  "TABLE_VC" varchar(64) ,
  "TABLE_SCHEMA" varchar(64) ,
  "TABLE_NAME" varchar(64) ,
  "CREATE_TIME" datetime ,
  "UPDATE_TIME" datetime ,
  "TABLE_STORAGE_SIZE" bigint(21) ,
  "TABLE_STORAGE_SIZE_MB" DECIMAL(20,2) ,
  "TABLE_TYPE" varchar(64) ,
  "INSERT_TIME" datetime 
) ;
'  | tee -a $log_file

# 写入临时表
gccli -u$dbUser -p$dbapassword -Dgbase -vvv -e "source $sql_file" | tee -a $log_file