空洞率采集部署

1.创建存库的库表
采用下面的语句创建数据库和库表
create database tmp_db_size;
create table tmp_db_size.delete_ratio_statistics
(
 statistics_date  datetime,
 "TABLE_VC" varchar(64),
 "TABLE_SCHEMA" varchar(64),
 "TABLE_NAME" varchar(64),
 "MAX_ROWID" bigint(21),
 "DELETE_ROWS" bigint(21),
 "TABLE_ROWS" bigint(21),
 "STORAGE_SIZE" bigint(21),
 "DELETABLE_SIZE" bigint(21),
 "SHRINKABLE_SIZE" bigint(21),
 "DELETE_RATIO" double
);

2.创建存储过程
delimiter ;
drop procedure if exists tmp_db_size.delete_ratio_statistics;
DELIMITER //

CREATE PROCEDURE tmp_db_size.delete_ratio_statistics()

BEGIN
DECLARE tableschema varchar(64);  #变量定义
DECLARE tableName varchar(64);
DECLARE done INT DEFAULT 0;
DECLARE CUR_PM_POINFO CURSOR FOR  select b.dbname,b.tbname from gbase.table_distribution b where b.dbname not in ('information_schema','performance_schema','gbase','gctmpdb','gclusterdb');
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN CUR_PM_POINFO;
REPEAT
FETCH CUR_PM_POINFO INTO tableschema, tableName;

IF NOT done THEN
set _gbase_query_path=1;
insert into tmp_db_size.delete_ratio_statistics  select sysdate(),a.* from performance_schema.tables a where a.table_schema=tableschema and a.table_name=tableName;
end if;
UNTIL done END REPEAT;
CLOSE CUR_PM_POINFO;

END //
delimiter ;

3.测试存储
调用存储
call tmp_db_size.delete_ratio_statistics();

查询空洞率采集表
select * from tmp_db_size.delete_ratio_statistics;
查询时可根据自己的需求限定where条件

4.将表赋权给普通用户
grant select on tmp_db_size.delete_ratio_statistics to test;  --test为赋权的用户，根据实际提供的用户赋权。

5.数据库部署定时任务
查看当前事件调度，值为on表示打开的
若为off，通过设置event_scheduler参数为1，启用事件调度功能。
set global event_scheduler=1;

部署定时事件：每天00:40:00运行
create event if not exists  tmp_db_size.delete_ratio_statistics_event
on schedule  every 1 day starts '2024-06-22 00:40:00'
on completion  preserve
do call tmp_db_size.delete_ratio_statistics();