Updated on 2025-09-04 GMT+08:00

Automatic Scheduling

  1. Log in to the GaussDB management console. On the Instances page, click the name of the target instance to go to the Basic Information page.
  2. Locate the Advanced Features field and click View and Modify. On the displayed page, set the value of Advanced compression to on.
  3. Connect to the database. Create a data table with an ILM policy, and insert data into the table. For details about how to connect to the database, see Using gsql to Connect to an Instance.

    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);

    The following is an example of the command output.

  4. Configure system parameters for enabling automatic ILM scheduling.

    Call the DBE_ILM_ADMIN.CUSTOMIZE_ILM interface to modify the scheduling-related system parameters by passing the corresponding parameter IDs and values. In this example, three parameters are modified: Set the automatic scheduling frequency to once per minute; change the time unit for identifying cold data rows to seconds; set the maximum data size compressed per job to 10 MB. Retain the default values of other parameters.
    \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);

    The following is an example of the command output.

  5. Wait 3 seconds for data rows to become cold, and then enable automatic scheduling.

    \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());

    The following is an example of the command output.

  6. Check the compression results of scheduling tasks.

    \c adb
    select * from gs_adm_ilmresults;

    The following is an example of the command output.

    Automatic scheduling runs once per minute, so a new compression job is generated every minute. SpaceSaving indicates the amount of space saved by compression. A larger value indicates a greater compression benefit.

    If you want the table to be compressed more quickly, you can increase the data size processed by each compression job, for example, set it to 1024. Then, restart the scheduling task and check the compression results again after one minute.
    \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());

    The following is an example of the command output.