Help Center/ GaussDB/ Distributed_8.x/ Storage Engine/ Data Lifecycle Management: OLTP Table Compression/ O&M Tips
Updated on 2024-06-07 GMT+08:00
View PDF

O&M Tips

Before using the advanced compression feature, contact Huawei technical support to purchase a license. Otherwise, an error will be reported when you run related commands.

  1. Manually trigger compression once. (In the example, 102400 MB data is compressed once.)
    1. Add the cold and hot separation policy for a table.
      gaussdb=#  DROP TABLE IF EXISTS ILM_TABLE;
gaussdb=#  CREATE TABLE ILM_TABLE(a int);
gaussdb=# ALTER TABLE ILM_TABLE ILM ADD POLICY ROW STORE COMPRESS ADVANCED
    ROW AFTER 3 MONTHS OF NO MODIFICATION;
    2. Manually trigger compression.
      DECLARE
  v_taskid number;
BEGIN
  DBE_ILM_ADMIN.CUSTOMIZE_ILM(11, 1);
DBE_ILM_ADMIN.CUSTOMIZE_ILM(13, 102400);
  DBE_ILM.EXECUTE_ILM(OWNER        => '$schema_name',
                    OBJECT_NAME    => 'ilm_table',
                    TASK_ID        => v_taskid,
                    SUBOBJECT_NAME => NULL,
                    POLICY_NAME    => 'ALL POLICIES',
                    EXECUTION_MODE => 2);
  RAISE INFO 'Task ID is:%', v_taskid;
END;
/
    3. Check whether the compression job is complete. You can view the detailed execution information.
      gaussdb=# SELECT * FROM gs_adm_ilmresults ORDER BY task_id desc;

 task_id |                 job_name                 |     start_time      |   completion_time   |                       statistics                       
---------+------------------------------------------+---------------------+---------------------+--------------------------------------------------------
   17267 | ilmjob$_2 | 2023-03-29 08:11:25 | 2023-03-29 08:11:25 | SpaceSaving=453048,BoundTime=1680145883,LastBlkNum=128
  2. Manually stop compression.
    gaussdb=# DBE_ILM.STOP_ILM (task_id => V_TASK, p_drop_running_Jobs => FALSE, p_Jobname => V_JOBNAME);
    Table 1 DBE_ILM.STOP_ILM input parameters

    Name

    Description

    task_id

    Specifies the descriptor ID of the ADO task to be stopped.

    p_drop_running_Jobs

    Specifies whether to stop a running job.

    p_Jobname

    Specifies the name of the job to be stopped, which can be queried in the GS_MY_ILMEVALUATIONDETAILS view.
  3. Generate a policy for a table and schedule compression tasks in the background.
    1. Add the cold and hot separation policy for a table.
      gaussdb=# DROP TABLE IF EXISTS ILM_TABLE;
gaussdb=# CREATE TABLE ILM_TABLE(a int);
gaussdb=# ALTER TABLE ILM_TABLE ILM ADD POLICY ROW STORE COMPRESS ADVANCED
    ROW AFTER 3 MONTHS OF NO MODIFICATION;
    2. Set the parameters related to ILM execution.
      BEGIN
  DBE_ILM_ADMIN.CUSTOMIZE_ILM(11, 1);
  DBE_ILM_ADMIN.CUSTOMIZE_ILM(12, 10);
  DBE_ILM_ADMIN.CUSTOMIZE_ILM(1, 1);
  DBE_ILM_ADMIN.CUSTOMIZE_ILM(13, 512);
END;
/
    3. Enable scheduled scheduling in the background.
      gaussdb=# CALL DBE_ILM_ADMIN.DISABLE_ILM();
gaussdb=# CALL DBE_ILM_ADMIN.ENABLE_ILM();
    4. You can call DBE_SCHEDULER.set_attribute to set the opening time of the maintenance window as required. By default, this function is enabled at 22:00.
  4. Set the parameters related to ILM execution.

    Specifies whether the time unit of ADO is day or second. The time unit second is used only for testing. The setting can be ILM_POLICY_IN_SECONDS = 1 or ILM_POLICY_IN_DAYS = 0 (default value).

    gaussdb=# CALL DBE_ILM_ADMIN.CUSTOMIZE_ILM(11, 1);

    Specifies the maximum number of ADO jobs generated by an ADO task. The value is an integer or floating-point number greater than or equal to 0 and less than or equal to 2147483647. The value is rounded down.

    gaussdb=# CALL DBE_ILM_ADMIN.CUSTOMIZE_ILM(12, 10);

    Specifies the frequency of executing an ADO task, in minutes. The default value is 15. The value is an integer or floating-point number greater than or equal to 1 and less than or equal to 2147483647. The value is rounded down.

    gaussdb=# CALL DBE_ILM_ADMIN.CUSTOMIZE_ILM(1, 1);

    Specifies the maximum number of bytes that can be processed by a single ADO job. The unit is MB. The value is an integer or floating-point number greater than or equal to 0 and less than or equal to 2147483647. The value is rounded down.

    gaussdb=# CALL DBE_ILM_ADMIN.CUSTOMIZE_ILM(13, 512);
  5. Check whether a table is suitable for compression and evaluate the compression benefits.
    gaussdb=# DBE_COMPRESSION.GET_COMPRESSION_RATIO (
   scratchtbsname        IN     VARCHAR2, 
   ownname               IN     VARCHAR2, 
   objname               IN     VARCHAR2,
   subobjname            IN     VARCHAR2,
   comptype              IN     NUMBER,
   blkcnt_cmp            OUT    PLS_INTEGER,
   blkcnt_uncmp          OUT    PLS_INTEGER,
   row_cmp               OUT    PLS_INTEGER,
   row_uncmp             OUT    PLS_INTEGER,
   cmp_ratio             OUT    NUMBER,
   comptype_str          OUT    VARCHAR2,
   sample_ratio    IN  INTEGER DEFAULT 20,
   objtype               IN     PLS_INTEGER DEFAULT OBJTYPE_TABLE);
    Table 2 Input parameters of DBE_COMPRESSION.GET_COMPRESSION_RATIO

    Name

    Description

    scratchtbsname

    Name of the space where data is stored.

    ownname

    Owner name of a data object.

    objname

    Data object name.

    subobjname

    Name of the data partition. The default value is NULL.

    comptype

    Compression type: COMP_NOCOMPRESS and COMP_ADVANCED.

    sample_ratio

    Sampling ratio. The value is an integer or floating-point number ranging from 0 to 100, corresponding to the sampling ratio of N percent. The default value is 20, indicating that 20% of the rows are sampled.

    objtype

    Object type. The supported type is `OBJTYPE_TABLE`.
    Table 3 Output parameters of DBE_COMPRESSION.GET_COMPRESSION_RATIO

    Name

    Description

    blkcnt_cmp

    Number of blocks occupied by compressed samples.

    blkcnt_uncmp

    Number of blocks occupied by uncompressed samples.

    row_cmp

    Number of rows that can be contained in a single block after samples are compressed.

    row_uncmp

    Number of rows that can be contained in a single block when samples are not compressed.

    cmp_ratio

    Compression ratio, that is, blkcnt_uncmp divided by blkcnt_cmp.

    comptype_str

    Character string that describes the compression type.

    Example:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
    		 
    gaussdb=# ALTER DATABASE set ilm = on;
gaussdb=# CREATE user user1 IDENTIFIED BY '********';
gaussdb=# CREATE user user2 IDENTIFIED BY '********';
gaussdb=# SET ROLE user1 PASSWORD '********';
gaussdb=# CREATE TABLE TEST_DATA (ORDER_ID INT, GOODS_NAME TEXT, CREATE_TIME TIMESTAMP)
  ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 1 DAYS OF NO MODIFICATION;
INSERT INTO TEST_DATA VALUES (1,'Snack package A', NOW());

DECLARE
o_blkcnt_cmp      integer;
o_blkcnt_uncmp    integer;
o_row_cmp         integer;
o_row_uncmp       integer;
o_cmp_ratio       number;
o_comptype_str    varchar2;
begin
dbe_compression.get_compression_ratio(
    SCRATCHTBSNAME  =>  NULL,
    OWNNAME         =>  'user1',
    OBJNAME         =>  'test_data',
    SUBOBJNAME      =>  NULL,
    COMPTYPE        =>  2,
    BLKCNT_CMP      =>  o_blkcnt_cmp,
    BLKCNT_UNCMP    =>  o_blkcnt_uncmp,
    ROW_CMP         =>  o_row_cmp,
    ROW_UNCMP       =>  o_row_uncmp,
    CMP_RATIO       =>  o_cmp_ratio,
    COMPTYPE_STR    =>  o_comptype_str,
    SAMPLE_RATIO    =>  100,
    OBJTYPE         =>  1);
RAISE INFO 'Number of blocks used by the compressed sample of the object	    : %', o_blkcnt_cmp;
RAISE INFO 'Number of blocks used by the uncompressed sample of the object	    : %', o_blkcnt_uncmp;
RAISE INFO 'Number of rows in a block in compressed sample of the object	    : %', o_row_cmp;
RAISE INFO 'Number of rows in a block in uncompressed sample of the object	    : %', o_row_uncmp;
RAISE INFO 'Estimated Compression Ratio of Sample                       	    : %', o_cmp_ratio;
RAISE INFO 'Compression Type							                        : %', o_comptype_str;
end;
/
INFO:  Number of blocks used by the compressed sample of the object	    : 0
INFO:  Number of blocks used by the uncompressed sample of the object	    : 0
INFO:  Number of rows in a block in compressed sample of the object	    : 0
INFO:  Number of rows in a block in uncompressed sample of the object	    : 0
INFO:  Estimated Compression Ratio of Sample                       	    : 1
INFO:  Compression Type							                        : Compress Advanced
  6. Query the last modification time of each line.
    gaussdb=# DBE_HEAT_MAP.ROW_HEAT_MAP(
 owner    IN VARCHAR2,
 segment_name  IN VARCHAR2,
 partition_name  IN VARCHAR2 DEFAULT NULL,
ctid    IN  VARCHAR2,);
    Table 4 Input parameters of DBE_HEAT_MAP.ROW_HEAT_MAP

    Name

    Description

    owner

    Owner of a data object.

    segment_name

    Data object name.

    partition_name

    Name of a data object partition. This parameter is optional.

    ctid

    ctid of the target row, that is, block_id or row_id.
    Table 5 Output parameters of DBE_HEAT_MAP.ROW_HEAT_MAP

    Name

    Description

    owner

    Owner of a data object.

    segment_name

    Data object name.

    partition_name

    Name of a data object partition. This parameter is optional.

    tablespace_name

    Name of the tablespace to which data belongs.

    file_id

    ID of the absolute file to which a row belongs.

    relative_fno

    ID of the relative file to which a row belongs. (GaussDB does not have this logic. Therefore, the value is the same as the preceding value.)

    ctid

    ctid of a row, that is, block_id or row_id.

    writetime

    Last modification time of a row.

    Example:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
    		 
    gaussdb=# ALTER DATABASE set ilm = on;
gaussdb=# CREATE Schema HEAT_MAP_DATA;
gaussdb=# SET current_schema=HEAT_MAP_DATA;

gaussdb=# CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace1';
gaussdb=# CREATE TABLE HEAT_MAP_DATA.heat_map_table(id INT, value TEXT) TABLESPACE example1;
gaussdb=# INSERT INTO HEAT_MAP_DATA.heat_map_table VALUES (1, 'test_data_row_1');

gaussdb=# SELECT * from DBE_HEAT_MAP.ROW_HEAT_MAP(
    owner         =>  'heat_map_data',
    segment_name  =>  'heat_map_table',
    partition_name  => NULL,
    ctid          =>  '(0,1)');
     owner     |  segment_name  | partition_name | tablespace_name | file_id | relative_fno | ctid  | writetime 
---------------+----------------+----------------+-----------------+---------+--------------+-------+-----------
 heat_map_data | heat_map_table |                | example1        |   17291 |        17291 | (0,1) | 
(1 row)
  7. Query the environment parameters related to ILM scheduling and execution.
    gaussdb=# SELECT * FROM GS_ADM_ILMPARAMETERS;
        name        | value 
--------------------+-------
 EXECUTION_INTERVAL |    15
 RETENTION_TIME     |    30
 ENABLED            |     1
 POLICY_TIME        |     0
 ABS_JOBLIMIT       |    10
 JOB_SIZELIMIT      |   1024
 WIND_DURATION      |   240
 BLOCK_LIMITS       |    40
(8 rows)
  8. Query the brief information about an ILM policy, including the policy name, type, enabling status, disabling status, and deletion status.
    gaussdb=# SELECT * FROM GS_ADM_ILMPOLICIES;
policy_name |  policy_type  | tablespace | enabled | deleted 
-------------+---------------+------------+---------+---------
 p1          | DATA MOVEMENT |            | YES     | NO


gaussdb=# SELECT * FROM GS_MY_ILMPOLICIES;
 policy_name |  policy_type  | tablespace | enabled | deleted 
-------------+---------------+------------+---------+---------
 p1          | DATA MOVEMENT |            | YES     | NO
  9. Query the brief data movement information about an ILM policy, including the policy name, action type, and condition.
    gaussdb=# SELECT * FROM GS_ADM_ILMDATAMOVEMENTPOLICIES;
 policy_name | action_type | scope | compression_level | tier_tablespace | tier_status |     condition_type     | condition_days | custom_function | policy_subtype | action_clause | tier_to 
-------------+-------------+-------+-------------------+-----------------+-------------+------------------------+----------------+-----------------+----------------+---------------+---------
 p1          | COMPRESSION | ROW   | ADVANCED          |                 |             | LAST MODIFICATION TIME |             90 |                 |                |               | 

gaussdb=# SELECT * FROM GS_MY_ILMDATAMOVEMENTPOLICIES;
 policy_name | action_type | scope | compression_level | tier_tablespace | tier_status |     condition_type     | condition_days | custom_function | policy_subtype | action_clause | tier_to 
-------------+-------------+-------+-------------------+-----------------+-------------+------------------------+----------------+-----------------+----------------+---------------+---------
 p1          | COMPRESSION | ROW   | ADVANCED          |                 |             | LAST MODIFICATION TIME |             90 |                 |                |               | 
(1 row)
  10. Query the brief information about all data objects to which ILM policies are applied and the corresponding policies, including the policy name, data object name, policy source, and policy enabling/disabling status.
    gaussdb=# SELECT * FROM  GS_ADM_ILMOBJECTS;
 policy_name | object_owner | object_name | subobject_name | object_type |    inherited_from    | tbs_inherited_from | enabled | deleted 
-------------+--------------+-------------+----------------+-------------+----------------------+--------------------+---------+---------
 p1          | public       | lineitem    |                | TABLE       | POLICY NOT INHERITED |                    | YES     | NO

gaussdb=# SELECT * FROM  GS_MY_ILMOBJECTS;
 policy_name | object_owner | object_name | subobject_name | object_type |    inherited_from    | tbs_inherited_from | enabled | deleted 
-------------+--------------+-------------+----------------+-------------+----------------------+--------------------+---------+---------
 p1          | public       | lineitem    |                | TABLE       | POLICY NOT INHERITED |                    | YES     | NO
  11. Query the brief information about an ADO task, including the task ID, task owner, status, and time.
    gaussdb=# SELECT * FROM GS_ADM_ILMTASKS;
 task_id | task_owner |   state   |         creation_time         |          start_time           |        completion_time        
---------+------------+-----------+-------------------------------+-------------------------------+-------------------------------
       1 | omm        | COMPLETED | 2023-10-16 12:03:55.113296+08 | 2023-10-16 12:03:55.113296+08 | 2023-10-16 12:03:56.326864+08
(1 row)

gaussdb=# SELECT * FROM GS_MY_ILMTASKS;
 task_id | task_owner |   state   |         creation_time         |          start_time           |        completion_time        
---------+------------+-----------+-------------------------------+-------------------------------+-------------------------------
       1 | omm        | COMPLETED | 2023-10-16 12:03:55.113296+08 | 2023-10-16 12:03:55.113296+08 | 2023-10-16 12:03:56.326864+08
(1 row)
  12. Query the evaluation details of an ADO task, including the task ID, policy information, object information, evaluation result, and ADO job name.
    gaussdb=# SELECT * FROM GS_ADM_ILMEVALUATIONDETAILS;
 task_id | policy_name | object_owner | object_name | subobject_name | object_type | selected_for_execution |     job_name      | comments

---------+-------------+--------------+-------------+----------------+-------------+------------------------+-------------------+---------
-
       1 | p2          | public       | ilm_table_1 |                | TABLE       | SELECTED FOR EXECUTION | ilmjob$_postgres1 | 
(1 row)

gaussdb=# SELECT * FROM GS_MY_ILMEVALUATIONDETAILS;
 task_id | policy_name | object_owner | object_name | subobject_name | object_type | selected_for_execution |     job_name      | comments

---------+-------------+--------------+-------------+----------------+-------------+------------------------+-------------------+---------
-
       1 | p2          | public       | ilm_table_1 |                | TABLE       | SELECTED FOR EXECUTION | ilmjob$_postgres1 | 
(1 row)
  13. Query the execution details of an ADO job, including the task ID, job name, job status, and job time.
    gaussdb=# SELECT * FROM GS_ADM_ILMRESULTS;
 task_id |     job_name      |       job_state        |          start_time           |        completion_time        | comments |                    statistics                    
---------+-------------------+------------------------+-------------------------------+-------------------------------+----------+--------------------------------------------------
       1 | ilmjob$_postgres1 | COMPLETED SUCCESSFULLY | 2023-10-16 12:03:56.290176+08 | 2023-10-16 12:03:56.319829+08 |          | SpaceSaving=0,BoundTime=1697429033,LastBlkNum=40
(1 row)

gaussdb=# SELECT * FROM GS_MY_ILMRESULTS;
 task_id | job_name | job_state | start_time | completion_time | comments | statistics 
---------+----------+-----------+------------+-----------------+----------+------------
(0 rows)
 task_id |     job_name      |       job_state        |          start_time           |        completion_time        | comments |                    statistics                    
---------+-------------------+------------------------+-------------------------------+-------------------------------+----------+--------------------------------------------------
       1 | ilmjob$_postgres1 | COMPLETED SUCCESSFULLY | 2023-10-16 12:03:56.290176+08 | 2023-10-16 12:03:56.319829+08 |          | SpaceSaving=0,BoundTime=1697429033,LastBlkNum=40
(1 row)