# 运维TIPS ![](https://support.huaweicloud.com/distributed-devg-v10-gaussdb/public_sys-resources/notice_3.0-zh-cn.png) 高级压缩特性使用前必须先联系工作人员申请License，否则执行相关命令会报错。 1. 手动触发一次压缩（示例中一次压缩102400MB）。 1. 给表加上冷热分离策略： ``` 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. 手动触发压缩： ``` 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. 查看压缩JOB是否完成，可以看到具体的执行信息： ``` gaussdb=# SELECT * FROM gs_adm_ilmresults order by task_id desc limit 1; task_id | job_name | job_state | start_time | completion_time | comments | statistics | node_name ---------+-----------------------------------------------------+------------------------+-------------------------------+-------------------------------+----------+--------------------------------------------------------------------------+------------- - 20004 | ilmjob$_postgres_test_202503062047417126137 | COMPLETED SUCCESSFULLY | 2025-03-06 20:47:41.980633+08 | 2025-03-06 20:47:59.136747+08 | | SpaceSaving=98503180,BoundTime=1741265260,StartBlkNum=0,LastBlkNum=40709 | gs_datanode3 (1 row) ``` 4. 手动触发索引压缩： ``` gaussdb=# CREATE INDEX ILM_INDEX ON ILM_TABLE(a); gaussdb=# CALL DBE_ILM_ADMIN.CUSTOMIZE_ILM(27, 1); -- insert ticker function CREATE OR REPLACE FUNCTION hex_to_int(hexval varchar) RETURNS bigint AS $$ DECLARE result bigint; BEGIN EXECUTE 'SELECT x' || quote_literal(hexval) || '::bigint' INTO result; RETURN result; END; $$ LANGUAGE plpgsql; create or replace procedure insert_ticker() as declare current_lsn varchar; temp_res text[]; part_1 bigint; part_2 bigint; transformed_lsn bigint; begin select pg_current_xlog_location() into current_lsn; select string_to_array(current_lsn, '/') INTO temp_res; part_1 = hex_to_int(temp_res[1]); part_2 = hex_to_int(temp_res[2]); transformed_lsn = part_1 * 2 ^ 32 + part_2; insert into gs_ilm_ticker VALUES(transformed_lsn, CURRENT_TIMESTAMP - interval '30 seconds'); end; / gaussdb=# CALL insert_ticker(); insert_ticker --------------- (1 row) DECLARE v_taskid number; BEGIN DBE_ILM_ADMIN.CUSTOMIZE_ILM(11, 1); DBE_ILM_ADMIN.CUSTOMIZE_ILM(26, 102400); DBE_ILM.EXECUTE_ILM(OWNER => '$schema_name', OBJECT_NAME => 'ilm_index', TASK_ID => v_taskid, SUBOBJECT_NAME => NULL, POLICY_NAME => 'ALL POLICIES', EXECUTION_MODE => 2); RAISE INFO 'Task ID is:%', v_taskid; END; / gaussdb=# SELECT * FROM gs_adm_ilmresults order by task_id desc; task_id | job_name | job_state | start_time | completion_time | comments | statistics | node_name ---------+-----------------------------------------------------------------------------+------------------------+-------------------------------+-------------------------------+----------+---------------------------------------------------------------- -----------------------------------------------+-------------- 37 | ilmindexjob$_postgres_test_idx3_202503041713161816797 | COMPLETED SUCCESSFULLY | 2025-03-04 17:13:16.412746+08 | 2025-03-04 17:13:16.423239+08 | | ScanBlocks=32,CompressBlocks=0,MergeBlocks=0,RecycleBlocks=97,N extScanBeginBlkNum=0 | gs_datanode1 ``` 5. 触发索引build时压缩，若ENABLE_INDEX_COMPRESS已经设置为1，且表添加了ILM策略，此时若在该表CREATE INDEX，则会触发索引build compress，减少索引build时所生成的页面数。 2. 手动停止压缩。 ``` gaussdb=# CALL DBE_ILM.STOP_ILM (1, FALSE, 'test_job'); -- task_id为1，p_drop_running_Jobs为FALSE，p_Jobname = 'test_job'。 ``` 表1DBE_ILM.STOP_ILM输入参数 | 名称 | 描述 | |:---|:---| | TASK_ID | 指定待停止ADO task的描述符ID。 | | P_DROP_RUNNING_JOBS | 是否停止正在执行中的任务，true为强制停止，false为不停止正在执行的任务。 | | P_JOBNAME | 标识待停止的特定JobName，通过GS_MY_ILMEVALUATIONDETAILS视图可以查询。 | 3. 为表生成策略及后台调度压缩任务。 1. 给表加上冷热分离策略： ``` 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. 设置ILM执行相关参数： ``` 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. 开启后台的定时调度： ``` gaussdb=# CALL DBE_ILM_ADMIN.DISABLE_ILM(); gaussdb=# CALL DBE_ILM_ADMIN.ENABLE_ILM(); ``` 4. 用户可以根据需要，调用DBE_SCHEDULER.set_attribute设置后台维护窗口的开启时间。当前默认22:00开启。 5. 若需开启索引压缩自动调度任务，需要将ENABLE_INDEX_COMPRESS设置为1，即DBE_ILM_ADMIN.CUSTOMIZE_ILM(27, 1)。 4. 设置ILM执行相关参数。 控制ADO的条件单位是天还是秒，秒仅用来做测试用。取值为：ILM_POLICY_IN_SECONDS = 1或ILM_POLICY_IN_DAYS = 0（默认值）： ``` gaussdb=# CALL DBE_ILM_ADMIN.CUSTOMIZE_ILM(11, 1); ``` 控制一次ADO Task最多生成多少个ADO Job。取值范围大于等于0小于等于2147483647的整数或浮点数，作用时向下取整： ``` gaussdb=# CALL DBE_ILM_ADMIN.CUSTOMIZE_ILM(12, 10); ``` ADO Task的执行频率，单位分钟，默认值15。取值范围大于等于1小于等于2147483647的整数或浮点数，作用时向下取整： ``` gaussdb=# CALL DBE_ILM_ADMIN.CUSTOMIZE_ILM(1, 1); ``` 控制单个ADO Job可以处理的最大字节数，单位兆。取值范围大于等于0小于等于2147483647的整数或浮点数，作用时向下取整： ``` gaussdb=# CALL DBE_ILM_ADMIN.CUSTOMIZE_ILM(13, 512); ``` 5. 评估一张表是否适合压缩及评估压缩后带来多少收益（索引压缩不支持）。 ``` DBE_COMPRESSION.GET_COMPRESSION_RATIO( SCRATCHTBSNAME IN VARCHAR2, OWNNAME IN VARCHAR2, OBJNAME IN VARCHAR2, SUBOBJNAME IN VARCHAR2, COMPTYPE IN NUMBER, BLKCNT_CMP OUT INTEGER, BLKCNT_UNCMP OUT INTEGER, ROW_CMP OUT INTEGER, ROW_UNCMP OUT INTEGER, CMP_RATIO OUT NUMBER, COMPTYPE_STR OUT VARCHAR2, SAMPLE_RATIO IN NUMBER DEFAULT 20, OBJTYPE IN INTEGER DEFAULT 1) ``` 表2DBE_COMPRESSION.GET_COMPRESSION_RATIO输入参数 | 名称 | 描述 | |:---|:---| | SCRATCHTBSNAME | 数据对象所属表空间。 | | OWNNAME | 数据对象所有者（所属模式）。 | | OBJNAME | 数据对象名称。 | | SUBOBJNAME | 数据子对象名称。 | | COMPTYPE | - 1：未压缩 - 2：高级压缩 | | SAMPLE_RATIO | 采样比例，输入为0-100的整数或浮点数，对应为百分之N的采样比例。默认为20，即对20%的行数进行采样。 | | OBJTYPE | 对象类型，支持： 1：表对象 | 表3DBE_COMPRESSION.GET_COMPRESSION_RATIO输出参数 | 名称 | 描述 | |:---|:---| | BLKCNT_CMP | 样本被压缩后占用的块数。 | | BLKCNT_UNCMP | 样本未压缩占用的块数。 | | ROW_CMP | 样本被压缩后单个块内可容纳的行数。 | | ROW_UNCMP | 样本未被压缩时单个数据块可容纳的行数。 | | CMP_RATIO | 压缩比，blkcnt_uncmp除以blkcnt_cmp。 | | COMPTYPE_STR | 描述压缩类型的字符串。 | 示例： ``` 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, '零食大礼包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. 查询每一行的最后修改时间（索引压缩不支持）。 ``` DBE_HEAT_MAP.ROW_HEAT_MAP( OWNER IN VARCHAR2, SEGMENT_NAME IN VARCHAR2, PARTITION_NAME IN VARCHAR2 DEFAULT NULL, CTID IN TEXT, V_DEBUG IN BOOL DEFAULT FALSE); ``` 表4DBE_HEAT_MAP.ROW_HEAT_MAP输入参数 | 名称 | 描述 | |:---|:---| | OWNER | 数据对象所属Schema。 | | SEGMENT_NAME | 数据对象名称。 | | PARTITION_NAME | 数据对象分区名，可选参数，默认为 NULL。 | | CTID | 目标行的ctid，即block_id或row_id。 | | V_DEBUG | debug调试，增加日志打印。 | 表5DBE_HEAT_MAP.ROW_HEAT_MAP输出参数 | 名称 | 描述 | |:---|:---| | OWNER | 数据对象的所有者。 | | SEGMENT_NAME | 数据对象名称。 | | PARTITION_NAME | 数据对象分区名称，可选参数。 | | TABLESPACE_NAME | 数据所属的表空间名称。 | | FILE_ID | 行所属的绝对文件ID。 | | RELATIVE_FNO | 行所属的相对文件ID（GaussDB中无此逻辑，因此取值同上）。 | | CTID | 行的ctid，即block_id或row_id。 | | WRITETIME | 行的最后修改时间。 | 示例： ``` 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. 查询ILM调度与执行的相关环境参数。 ``` 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 ENABLE_META_COMPRESSION | 0 SAMPLE_MIN | 10 SAMPLE_MAX | 10 CONST_PRIO | 40 CONST_THRESHOLD | 90 EQVALUE_PRIO | 60 EQVALUE_THRESHOLD | 80 ENABLE_DELTA_ENCODE_SWITCH | 1 LZ4_COMPRESSION_LEVEL | 0 ENABLE_LZ4_PARTIAL_DECOMPRESSION | 1 INDEXJOB_SIZELIMIT | 1024 ENABLE_INDEX_COMPRESS | 0 (20 rows) ``` 8. 查询ILM策略的概要信息，包含策略名称、类型、启用禁用状态、删除状态。 ``` 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. 查询ILM策略的数据移动概要信息，包含策略名称、动作类型、条件等。 ``` 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. 查询所有存在ILM策略应用的数据对象与相应策略的概要信息，包含策略名称、数据对象名称、策略的来源、策略的启用删除状态。 ``` 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. 查询ADO Task的概要信息，包含Task ID，Task Owner，状态以及时间信息。 ``` 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. 查询ADO Task的评估详情信息，包含Task ID，策略信息、对象信息、评估结果以及ADO JOB名称。 ``` 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. 查询ADO JOB的执行详情信息，包含Task ID，JOB名称、JOB状态、JOB时间信息等。 ``` 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,StartBlkNum=0,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,StartBlkNum=0,LastBlkNum=40 (1 row) ```