GBase 8a MPP Cluster 脏页自动清理

存储过程+event ，按照空洞率自动清理

-- 1、创建存储过程
DELIMITER $$
CREATE PROCEDURE auto_clean_dirty_pages(IN ratio INT)
BEGIN
    DECLARE v_schema VARCHAR(64);
    DECLARE v_table VARCHAR(64);
    DECLARE v_current_ratio DECIMAL(5,2);
    DECLARE done BOOLEAN DEFAULT FALSE;

    DECLARE cur CURSOR FOR 
    SELECT table_schema, table_name
    FROM information_schema.tables 
    WHERE table_schema NOT IN (
        'information_schema','gbase','gclusterdb',
        'performance_schema','gctmpdb'
    );

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    
    IF ratio NOT BETWEEN 0 AND 100 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Ratio must be 0-100';
    END IF;

    -- 初始化用户变量
    SET @clean_sql = NULL;
    SET @size_check = 0;

    OPEN cur;

    clean_loop: LOOP
        FETCH cur INTO v_schema, v_table;
        IF done THEN
            LEAVE clean_loop;
        END IF;

        -- 使用用户变量传递参数（GBase要求）
        SET @schema_var = v_schema;
        SET @table_var = v_table;
        
        -- 动态SQL获取脏页率
        SET @sql = CONCAT(
            'SELECT DELETE_RATIO INTO @current_ratio ',
            'FROM performance_schema.tables ',
            'WHERE table_schema = ? AND table_name = ?'
        );
        
        PREPARE stmt FROM @sql;
        EXECUTE stmt USING @schema_var, @table_var;
        DEALLOCATE PREPARE stmt;

        -- 执行清理
        IF @current_ratio >= ratio THEN
            SET @clean_sql = CONCAT('ALTER TABLE ', v_schema, '.', v_table, 
                                  ' SHRINK SPACE FULL');
            PREPARE clean_stmt FROM @clean_sql;
            EXECUTE clean_stmt;
            DEALLOCATE PREPARE clean_stmt;
        END IF;
    END LOOP;

    CLOSE cur;
END
$$

DELIMITER ;

-- 2、创建event事件
delimiter //
CREATE EVENT IF NOT EXISTS auto_clean_dirty_pages_event 
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP 
ON COMPLETION PRESERVE
ENABLE
GLOBAL  
COMMENT 'Daily clean tables with DELETE_RATIO>=30%'
DO

    CALL auto_clean_dirty_pages(30); -- 阈值设为30%
//
delimiter ;