自动调度

登录GaussDB管理控制台。在“实例管理”页面，单击目标实例名称，进入“基本信息”页面。
找到“高级特性”字段，单击“查看并修改”，将“高级压缩”的值设置为on。

连接数据库，使用具有sysadmin权限的用户（如root）创建维护窗口。如何连接数据库可参考通过gsql连接到数据库。

集中式执行语句

\c template1
DECLARE
    V_HOUR INT := 22;
    V_MINUTE INT := 0;
    V_SECOND INT := 0;
    C_ADO_WINDOW_SCHEDULE_NAME TEXT := 'ado_window_schedule';
    C_ADO_WINDOW_PROGRAM_NAME TEXT := 'ado_window_program';
    C_MAINTENANCE_WINDOW_JOB_NAME TEXT := 'maintenance_window_job';
    V_MAINTENANCE_WINDOW_REPEAT TEXT;
    V_MAINTENANCE_WINDOW_START TIMESTAMPTZ;
    V_BE_SCHEDULE_ENABLE BOOL;
    V_MAINTENANCE_WINDOW_EXIST INT;
BEGIN
    SELECT COUNT(*) INTO V_MAINTENANCE_WINDOW_EXIST FROM PG_CATALOG.PG_JOB WHERE JOB_NAME = 'maintenance_window_job' AND DBNAME = 'template1';
    IF CURRENT_DATABASE() != 'template1' THEN
        RAISE EXCEPTION 'Create maintenance_window FAILED, current database is not template1';
    END IF;
    IF V_MAINTENANCE_WINDOW_EXIST = 0 AND CURRENT_DATABASE() = 'template1' THEN
        SELECT
            CASE
                WHEN NOW() < CURRENT_DATE + INTERVAL '22 HOUR' THEN CURRENT_DATE + INTERVAL '22 HOUR'
                ELSE CURRENT_DATE + INTERVAL '1 DAY 22 HOUR'
            END INTO V_MAINTENANCE_WINDOW_START;
 
        --1. prepare for maintenance window schedule
        SELECT 'freq=daily;interval=1;byhour='||V_HOUR||';byminute='||V_MINUTE||';bysecond='||V_SECOND INTO V_MAINTENANCE_WINDOW_REPEAT;
        BEGIN
            SELECT
                CASE
                    WHEN VALUE = 1 THEN TRUE -- DBE_ILM_ADMIN.ILM_ENABLED
                    ELSE FALSE
                END INTO V_BE_SCHEDULE_ENABLE
                FROM PG_CATALOG.GS_ILM_PARAM WHERE IDX = 7; -- DBE_ILM_ADMIN.ENABLED
        EXCEPTION
            WHEN OTHERS THEN
                V_BE_SCHEDULE_ENABLE := FALSE;
        END;
 
        --2. Create ado window schedule
        DBE_SCHEDULER.CREATE_SCHEDULE(
            SCHEDULE_NAME => C_ADO_WINDOW_SCHEDULE_NAME,
            START_DATE => '9999-01-01 00:00:01',
            REPEAT_INTERVAL => NULL,
            END_DATE => NULL,
            COMMENTS => 'ado window schedule');
 
        --3. Create ado window program
        DBE_SCHEDULER.CREATE_PROGRAM(
            PROGRAM_NAME => C_ADO_WINDOW_PROGRAM_NAME,
            PROGRAM_TYPE => 'plsql_block',
            PROGRAM_ACTION => 'call prvt_ilm.be_execute_ilm();',
            NUMBER_OF_ARGUMENTS => 0,
            ENABLED => TRUE,
            COMMENTS => NULL);
 
        --4. Create maintenance window master job
        DBE_SCHEDULER.CREATE_JOB(
            JOB_NAME => C_MAINTENANCE_WINDOW_JOB_NAME,
            START_DATE => V_MAINTENANCE_WINDOW_START,
            REPEAT_INTERVAL => V_MAINTENANCE_WINDOW_REPEAT,
            END_DATE => NULL,
            JOB_TYPE => 'STORED_PROCEDURE'::TEXT,
            JOB_ACTION => 'prvt_ilm.be_active_ado_window'::TEXT,
            NUMBER_OF_ARGUMENTS => 0,
            ENABLED => V_BE_SCHEDULE_ENABLE,
            AUTO_DROP => FALSE,
            COMMENTS => 'maintenance window job');
    END IF;
END;
/

分布式执行语句

\c template1
DECLARE
    V_HOUR INT := 22;
    V_MINUTE INT := 0;
    V_SECOND INT := 0;
    C_ADO_WINDOW_SCHEDULE_NAME TEXT := 'ado_window_schedule';
    C_ADO_WINDOW_PROGRAM_NAME TEXT := 'ado_window_program';
    C_MAINTENANCE_WINDOW_JOB_NAME TEXT := 'maintenance_window_job';
    V_MAINTENANCE_WINDOW_REPEAT TEXT;
    V_MAINTENANCE_WINDOW_START TIMESTAMPTZ;
    V_BE_SCHEDULE_ENABLE BOOL;
    V_MAINTENANCE_WINDOW_EXIST INT;
BEGIN
    SELECT COUNT(*) INTO V_MAINTENANCE_WINDOW_EXIST FROM PG_CATALOG.PG_JOB WHERE JOB_NAME = 'maintenance_window_job' AND DBNAME = 'template1';
    IF CURRENT_DATABASE() != 'template1' THEN
        RAISE EXCEPTION 'Create maintenance_window FAILED, current database is not template1';
    END IF;
    IF V_MAINTENANCE_WINDOW_EXIST = 0 AND CURRENT_DATABASE() = 'template1' THEN
        SELECT
            CASE
                WHEN NOW() < CURRENT_DATE + INTERVAL '22 HOUR' THEN CURRENT_DATE + INTERVAL '22 HOUR'
                ELSE CURRENT_DATE + INTERVAL '1 DAY 22 HOUR'
            END INTO V_MAINTENANCE_WINDOW_START;
 
        --1. prepare for maintenance window schedule
        SELECT 'freq=daily;interval=1;byhour='||V_HOUR||';byminute='||V_MINUTE||';bysecond='||V_SECOND INTO V_MAINTENANCE_WINDOW_REPEAT;
        BEGIN
            SELECT
                CASE
                    WHEN VALUE = 1 THEN TRUE -- DBE_ILM_ADMIN.ILM_ENABLED
                    ELSE FALSE
                END INTO V_BE_SCHEDULE_ENABLE
                FROM PG_CATALOG.GS_ILM_PARAM WHERE IDX = 7; -- DBE_ILM_ADMIN.ENABLED
        EXCEPTION
            WHEN OTHERS THEN
                V_BE_SCHEDULE_ENABLE := FALSE;
        END;
 
        --2. Create ado window schedule
        DBE_SCHEDULER.CREATE_SCHEDULE(
            SCHEDULE_NAME => C_ADO_WINDOW_SCHEDULE_NAME,
            START_DATE => '9999-01-01 00:00:01',
            REPEAT_INTERVAL => NULL,
            END_DATE => NULL,
            COMMENTS => 'ado window schedule');
 
        --3. Create ado window program
        DBE_SCHEDULER.CREATE_PROGRAM(
            PROGRAM_NAME => C_ADO_WINDOW_PROGRAM_NAME,
            PROGRAM_TYPE => 'plsql_block',
            PROGRAM_ACTION => 'call prvt_ilm.be_execute_ilm(0);',
            NUMBER_OF_ARGUMENTS => 0,
            ENABLED => TRUE,
            COMMENTS => NULL);
 
        --4. Create maintenance window master job
        DBE_SCHEDULER.CREATE_JOB(
            JOB_NAME => C_MAINTENANCE_WINDOW_JOB_NAME,
            START_DATE => V_MAINTENANCE_WINDOW_START,
            REPEAT_INTERVAL => V_MAINTENANCE_WINDOW_REPEAT,
            END_DATE => NULL,
            JOB_TYPE => 'STORED_PROCEDURE'::TEXT,
            JOB_ACTION => 'prvt_ilm.be_active_ado_window'::TEXT,
            NUMBER_OF_ARGUMENTS => 0,
            ENABLED => V_BE_SCHEDULE_ENABLE,
            AUTO_DROP => FALSE,
            COMMENTS => 'maintenance window job',
            destination_name=> 'CCN');

        UPDATE PGXC_NODE SET NODEIS_CENTRAL = 't' WHERE NODE_NAME = GET_NODEINFO('node_name');
    END IF;
END;
/

执行结果示例如下：

点击放大

创建带ilm策略的数据表，并插入数据。

create database adb;
\c adb
alter database set ilm = on;
create table t1 (id int) with (orientation=row, compression=no, storage_type=astore) ilm add policy row store compress advanced row after 3 days of no modification;
insert into t1 select * from generate_series(1, 10000000);

执行结果示例如下：

点击放大

配置ilm自动调度系统参数。
调用DBE_ILM_ADMIN.CUSTOMIZE_ILM接口来修改调度相关的系统参数，分别传入参数对应的id以及value。这里修改了三条数据，分别是将自动调度执行频率修改为1分钟1次、将数据变冷行的时间单位修改成秒、将每个压缩job每次压缩的数据量大小设置为10M。其他参数可以不用修改，使用默认配置即可。
```
\c adb
CALL DBE_ILM_ADMIN.CUSTOMIZE_ILM(1, 1);
CALL DBE_ILM_ADMIN.CUSTOMIZE_ILM(11, 1);
CALL DBE_ILM_ADMIN.CUSTOMIZE_ILM(13, 10);
```
执行结果示例如下：

等待3秒让数据变冷行之后，开启自动调度。

\c adb
select pg_sleep(3);
CALL DBE_ILM_ADMIN.DISABLE_ILM();
CALL DBE_ILM_ADMIN.ENABLE_ILM();
\c template1
call DBE_SCHEDULER.set_attribute('maintenance_window_job','start_date',NOW());

执行结果示例如下：

点击放大

查询调度任务的压缩结果。
```
\c adb
select * from gs_adm_ilmresults;
```
执行结果示例如下：

调度任务一分钟执行一次，所以每隔一分钟才会生成一个新的压缩job。SpaceSaving为压缩收益大小，值越大表示压缩收益越大。
如果想让表尽快地压缩完成，可以将每个压缩job每次压缩的数据量大小设置得更大一些，比如1024，然后重新启动调度任务，并隔一分钟再次查看压缩结果。
```
\c adb
CALL DBE_ILM_ADMIN.CUSTOMIZE_ILM(13, 1024);
select pg_sleep(3);
CALL DBE_ILM_ADMIN.DISABLE_ILM();
CALL DBE_ILM_ADMIN.ENABLE_ILM();
\c template1
call DBE_SCHEDULER.set_attribute('maintenance_window_job','start_date',NOW());
```
执行结果示例如下：