GaussDB(DWS) 云端运维系列第七期：数据库维护（VACUUM，ANALYZE，倾斜检查）

VACUUM

功能描述

VACUUM回收表或B-Tree索引中已经删除的行所占据的存储空间。在一般的数据库操作里，那些已经DELETE的行并没有从它们所属的表中物理删除；在完成VACUUM之前它们仍然存在。因此有必要周期地运行VACUUM，特别是在经常更新的表上。

注意事项

如果没有参数，VACUUM处理当前数据库里用户拥有相应权限的每个表。如果参数指定了一个表，VACUUM只处理指定的那个表。

要对一个表进行VACUUM操作，通常用户必须是表的所有者或系统管理员。数据库的所有者允许对数据库中除了共享目录以外的所有表进行VACUUM操作（该限制意味着只有系统管理员才能真正对一个数据库进行VACUUM操作）。VACUUM命令会跳过那些用户没有权限的表进行垃圾回收操作。

VACUUM不能在事务块内执行。

建议生产数据库经常清理（至少每晚一次），以保证不断地删除失效的行。尤其是在增删了大量记录之后，对受影响的表执行VACUUM ANALYZE命令是一个很好的习惯。这样将更新系统目录为最近的更改，并且允许查询优化器在规划用户查询时有更好的选择。

不建议日常使用FULL选项，但是可以在特殊情况下使用。例如在用户删除了一个表的大部分行之后，希望从物理上缩小该表以减少磁盘空间占用。VACUUM FULL通常要比单纯的VACUUM收缩更多的表尺寸。FULL选项并不清理索引，所以推荐周期性的运行REINDEX命令。实际上，首先删除所有索引，再运行VACUUM FULL命令，最后重建索引通常是更快的选择。如果执行此命令后所占用物理空间无变化（未减少），请确认是否有其他活跃事务（删除数据事务开始之前开始的事务，并在VACUUM FULL执行前未结束）存在，如果有等其他活跃事务退出进行重试。

VACUUM会导致I/O流量的大幅增加，这可能会影响其他活动会话的性能。因此，有时候会建议使用基于开销的VACUUM延迟特性。

如果指定了VERBOSE选项，VACUUM将打印处理过程中的信息，以表明当前正在处理的表。各种有关当前表的统计信息也会打印出来。但是对于列存表执行VACUUM操作，指定了VERBOSE选项，无信息输出。

当含有带括号的选项列表时，选项可以以任何顺序写入。如果没有括号，则选项必须按语法显示的顺序给出。

VACUUM和VACUUM FULL时，会根据参数vacuum_defer_cleanup_age延迟清理行存表记录，即不会立即清理刚刚删除的元组。

VACUUM ANALYZE先执行一个VACUUM操作，然后给每个选定的表执行一个ANALYZE。对于日常维护脚本而言，这是一个很方便的组合。

简单的VACUUM（不带FULL选项）只是简单地回收空间并且令其可以再次使用。这种形式的命令可以和对表的普通读写并发操作，因为没有请求排他锁。VACUUM FULL执行更广泛的处理，包括跨块移动行，以便把表压缩到最少的磁盘块数目里。这种形式要慢许多并且在处理的时候需要在表上施加一个排他锁。

VACUUM列存表内部执行的操作包括三个：迁移delta表中的数据到主表、VACUUM主表的delta表、VACUUM主表的desc表。该操作不会回收delta表的存储空间，如果要回收delta表的冗余存储空间，需要对该列存表执行VACUUM DELTAMERGE。

如果有长查询访问系统表，此时执行VACUUM FULL，长查询可能会阻塞VACUUM FULL连接访问系统表，导致连接超时报错。

语法格式

回收空间并更新统计信息，对关键字顺序无要求。

VACUUM [ ( { FULL | FREEZE | VERBOSE | {ANALYZE | ANALYSE }} [,...] ) ] [ table_name [ (column_name [, ...] ) ] ] [ PARTITION ( partition_name ) ];

仅回收空间，不更新统计信息。

VACUUM [ FULL [COMPACT] ] [ FREEZE ] [ VERBOSE ] [ table_name ] [ PARTITION ( partition_name ) ];

回收空间并更新统计信息，且对关键字顺序有要求。

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] { ANALYZE | ANALYSE } [ VERBOSE ] [ table_name [ (column_name [, ...] ) ] ] [ PARTITION ( partition_name ) ];

针对HDFS表和列存表，将delta table中的数据转移到主表存储。

VACUUM DELTAMERGE [ table_name ];

针对HDFS表，删除HDFS表在HDFS存储上的空值分区目录。

VACUUM HDFSDIRECTORY [ table_name ];

参数说明

FULL

选择“FULL”清理，这样可以恢复更多的空间，但是需要耗时更多，并且在表上施加了排他锁。

FULL选项还可以带有COMPACT参数，该参数只针对HDFS表，指定该参数的VACUUM FULL操作性能要好于未指定该参数的VACUUM FULL操作。

COMPACT和PARTITION参数不能同时使用。

说明：使用FULL参数会导致统计信息丢失，如果需要收集统计信息，请在VACUUM FULL语句中加上analyze关键字。

FREEZE

指定FREEZE相当于执行VACUUM时将vacuum_freeze_min_age参数设为0。

VERBOSE

为每个表打印一份详细的清理工作报告。

ANALYZE | ANALYSE

更新用于优化器的统计信息，以决定执行查询的最有效方法。

table_name

要清理的表的名称（可以有模式修饰）。

取值范围：要清理的表的名称。缺省时为当前数据库中的所有表。

column_name

要分析的具体的字段名称。

取值范围：要分析的具体的字段名称。缺省时为所有字段。

PARTITION

HDFS表不支持PARTITION参数，COMPACT和PARTITION参数不能同时使用。

partition_name

要清理的表的分区名称。缺省时为所有分区。

DELTAMERGE

只针对HDFS表和列存表，将HDFS表或者列存表的delta table中的数据转移到主表存储上。对HDFS表而言，当delta表中数据量小于六万行，则不作迁移，只有在大于或者等于六万行数据时，将delta表中所有数据迁移到HDFS上，并通过truncate清理delta表的存储空间。对列存表而言，此操作受enable_delta_store和参数说明中的deltarow_threshold控制。

说明：

为了检查列存delta表中的信息（HDFS表的delta表数据可以通过explain analyze查到），提供下述DFX函数，用于获取某个列存表的delta表中数据存储情况：

pgxc_get_delta_info(TEXT)，传入参数为列存表名，搜集并显示各个节点上的对应delta表信息，包括当前存活tuple数量、表大小、使用的最大block ID。

get_delta_info(TEXT)，传入参数为列存表名，汇总pgxc_get_delta_info得到的结果，返回其delta表整体的当前存活tuple数量、表大小、使用的最大block ID。

HDFSDIRECTORY

只针对HDFS表，删除HDFS表在HDFS存储上表目录下的空值分区目录。

示例

--在表tpcds.reason上创建索引 CREATE UNIQUE INDEX ds_reason_index1 ON tpcds.reason(r_reason_sk); --对带索引的表tpcds.reason执行VACUUM操作。 VACUUM (VERBOSE, ANALYZE) tpcds.reason; --删除索引 DROP INDEX ds_reason_index1 CASCADE; DROP TABLE tpcds.reason;

ANALYZE

功能描述

用于收集与数据库中普通表内容相关的统计信息，统计结果存储在系统表PG_STATISTIC下。执行计划生成器会使用这些统计数据，以确定最有效的执行计划。

如果没有指定参数，ANALYZE会分析当前数据库中的每个表和分区表。同时也可以通过指定table_name、column和partition_name参数把分析限定在特定的表、列或分区表中。

ANALYZE|ANALYSE VERIFY用于检测数据库中普通表（行存表、列存表）的数据文件是否损坏，目前此命令暂不支持HDFS表

注意事项

由于现版本ANALYZE非临时表是一个多事务的行为，因此ANALYZE非临时表不能在一个匿名块、事务块、函数或存储过程内被执行，且当ANALYZE与部分DML语句，例如ALTER TABLE，DROP TABLE，UPDATE等并发时，可能会引发ANALYZE流程报错，报错信息例如找不到目标列，对于这种情况，只需要再次ANALYZE收集统计信息即可。现版本支持存储过程中ANALYZE临时表，不支持统计信息回滚操作。

ANALYZE VERIFY 操作处理的大多为异常场景检测需要使用RELEASE版本。ANALYZE VERIFY 场景不触发远程读，因此远程读参数不生效。对于关键系统表出现错误被系统检测出页面损坏时，将直接报错不再继续检测。

语法格式

收集表的统计信息。

{ ANALYZE | ANALYSE } [ VERBOSE ] [ table_name [ ( column_name [, ...] ) ] ];

收集分区表的统计信息。

{ ANALYZE | ANALYSE } [ VERBOSE ] [ table_name [ ( column_name [, ...] ) ] ] PARTITION ( patrition_name ) ;

说明：

普通分区表目前支持针对某个分区的统计信息的语法，但功能上不支持针对某个分区的统计信息收集。

收集外表的统计信息。

{ ANALYZE | ANALYSE } [ VERBOSE ] { foreign_table_name | FOREIGN TABLES };

收集多列统计信息

{ANALYZE | ANALYSE} [ VERBOSE ] table_name (( column_1_name, column_2_name [, ...] ));

说明：

收集多列统计信息时，请设置GUC参数default_statistics_target为负数，以使用百分比采样方式。

每组多列统计信息最多支持32列。

不支持收集多列统计信息的表：系统表、HDFS外表复制表。检测当前库的数据文件

检测当前库的数据文件

{ANALYZE | ANALYSE} VERIFY {FAST|COMPLETE};

说明：

支持对全库进行操作，由于涉及的表较多，建议以重定向保存结果gsql -d database -p port -f "verify.sql"> verify_warning.txt 2>&1。

不支持HDFS表（内表和外表），不支持临时表和unlog表。

对外提示NOTICE只核对外可见的表，内部表的检测会包含在它所依赖的外部表，不对外显示和呈现。

此命令的处理可容错ERROR级别的处理。由于debug版本的Assert可能会导致core无法继续执行命令，建议在release模式下操作。

对于全库操作时，当关键系统表出现损坏则直接报错，不再继续执行。

检测表和索引的数据文件

{ANALYZE | ANALYSE} VERIFY {FAST|COMPLETE} table_name|index_name [CASCADE];

说明：

支持对普通表的操作和索引表的操作，但不支持对索引表index使用CASCADE操作。原因是由于CASCADE模式用于处理主表的所有索引表，当单独对索引表进行检测时，无需使用CASCADE模式。

不支持HDFS表（内表和外表），不支持临时表和unlog表。

对于主表的检测会同步检测主表的内部表，例如toast表、cudesc表等。

当提示索引表损坏时，建议使用reindex命令进行重建索引操作。

检测表分区的数据文件

检测表和索引的数据文件

说明：

支持对表的单独分区进行检测操作，但不支持对索引表index使用CASCADE操作。

不支持HDFS表（内表和外表），不支持临时表和unlog表.

参数说明

VERBOSE

启用显示进度信息。

说明：

如果指定了VERBOSE，ANALYZE发出进度信息，表明目前正在处理的表。各种有关表的统计信息也会打印出来。

table_name

需要分析的特定表的表名（可能会带模式名），如果省略，将对数据库中的所有表（非外部表）进行分析。

对于ANALYZE收集统计信息，目前仅支持行存表、列存表、HDFS表、ORC格式的OBS外表、CARBONDATA格式的OBS外表、协同分析的外表。

取值范围：已有的表名。

column_name，column_1_name，column_2_name

需要分析特定列的列名，默认为所有列。

取值范围：已有的列名。

partition_name

如果table为分区表，在关键字PARTITION后面指定分区名partition_name表示分析该分区表的统计信息。目前语法上支持分区表做ANALYZE，但功能实现上暂不支持对指定分区统计信息的分析。

取值范围：表的某一个分区名。

foreign_table_name

需要分析的特定表的表名（可能会带模式名），该表的数据存放于HDFS分布式文件系统中。

取值范围：已有的表名。

FOREIGN TABLES

分析所有当前用户权限下，数据位于HDFS分布式文件系统中的HDFS外表。

index_name

需要分析的特定索引表的表名（可能会带模式名）。

取值范围：已有的表名。

FAST|COMPLETE

对于行存表，FAST模式下主要对于行存表的CRC和page header进行校验，如果校验失败则会告警; 而COMPLETE模式下，则主要对行存表的指针、tuple进行解析校验。 对于列存表，FAST模式下主要对于列存表的CRC和magic进行校验，如果校验失败则会告警; 而COMPLETE模式下，则主要对列存表的CU进行解析校验。

CASCADE

CASCADE模式下会对当前表的所有索引进行检测处理。

示例

--- 创建表。

--- 创建分区表。

--- 使用ANALYZE语句更新统计信息。

--- 使用ANALYZE VERBOSE语句更新统计信息，并输出表的相关信息。

说明：

若环境若有故障，需查看CN的log。

--- 删除表。

倾斜检查

操作场景

数据倾斜会造成查询表性能下降。对于记录数超过千万条的表，建议在执行全量数据导入前，先导入部分数据，以进行数据倾斜检查和调整分布列，避免导入大量数据后发现数据倾斜，调整成本高。

背景信息

GaussDB(DWS)是采用Shared-nothing架构的MPP（Massive Parallel Processor，大规模并发处理）系统，采用水平分布的方式，将业务数据表的元组按合适的分布策略分散存储在所有的DN。

当前产品支持复制（Replication）和散列（Hash）两种用户表分布策略。

Replication方式：在每一个DN上存储一份全量表数据。对于数据量比较小的表建议采取Replication分布策略。

Hash方式：采用这种分布方式，需要为用户表指定一个分布列（distribute key）。当插入一条记录时，系统会根据分布列的值进行hash运算后，将数据存储在对应的DN中。对于数据量比较大的表建议采取Hash分布策略。

对于Hash分布策略，如果分布列选择不当，可能导致数据倾斜。因此在采用Hash分布策略之后会对用户表的数据进行数据倾斜性检查，以确保数据在各个DN上是均匀分布的。一般情况下分布列都是选择键值重复度小，数据分布比较均匀的列。

操作步骤

1. 分析数据源特征，选择若干个键值重复度小，数据分布比较均匀的备选分布列。

2. 从步骤1中选择一个备选分布列创建目标表。

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ({ column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ] | table_constraint | LIKE source_table [ like_option [...] ] } [, ... ]) [ WITH ( {storage_parameter = value} [, ... ] ) ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ COMPRESS | NOCOMPRESS ] [ TABLESPACE tablespace_name ] [ DISTRIBUTE BY { REPLICATION | { HASH ( column_name [,...] ) } } ];

3. 参照前面章节中的办法向目标表中导入小批量数据。

对于单个数据源文件，在导入时，可通过均匀切割，导入部分切割后的数据源文件来验证数据倾斜性。

4. 检验数据倾斜性。命令中的table_name ，请填入实际的目标表名。

SELECT a.count,b.node_name FROM (SELECT count(*) AS count,xc_node_id FROM table_name GROUP BY xc_node_id) a, pgxc_node b WHERE a.xc_node_id=b.node_id ORDER BY a.count desc;

5. 若各DN上数据分布差小于10%，表明数据分布均衡，选择的分布列合适。请清理已导入小批量数据，导入全量数据，以完成数据迁移。

若各DN上数据分布差大于等于10%，表明数据分布倾斜，请从步骤1的备选分布列中删除该列，删除目标表，并重复步骤2 、步骤3 、步骤4 和步骤5。

6. （可选）如果上述步骤不能选出适合的分布列，需要从备选分布列选择多个列的组合作为分布列来完成数据迁移。

示例

对目标表staffs选择合适的分布列。

1. 分析表staffs的数据源特征，选择数据重复度低且分布均匀的备选分布列staff_ID、FIRST_NAME和LAST_NAME。

2. 先选择staff_ID作为分布列，创建目标表staffs。

CREATE TABLE staffs ( staff_ID NUMBER(6) not null, FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25), EMAIL VARCHAR2(25), PHONE_NUMBER VARCHAR2(20), HIRE_DATE DATE, employment_ID VARCHAR2(10), SALARY NUMBER(8,2), COMMISSION_PCT NUMBER(2,2), MANAGER_ID NUMBER(6), section_ID NUMBER(4) ) DISTRIBUTE BY hash(staff_ID);

3. 向目标表staffs中导入部分数据。

根据以下查询所得，集群环境中主DN数为8个，则建议导入的记录数为80000条。

SELECT count(*) FROM pgxc_node where node_type='D'; count ------- 8 (1 row)

4. 校验以staff_ID为分布列的目标表staffs的数据倾斜性。

SELECT a.count,b.node_name FROM (select count(*) as count,xc_node_id FROM staffs GROUP BY xc_node_id) a, pgxc_node b WHERE a.xc_node_id=b.node_id ORDER BY a.count desc; count | node_name ------+----------- 11010 | datanode4 10000 | datanode3 12001 | datanode2 8995 | datanode1 10000 | datanode5 7999 | datanode6 9995 | datanode7 10000 | datanode8 (8 rows)

5. 根据上一步骤查询所得，各DN上数据分布差大于10%，数据分布倾斜。所以从步骤1的备选分布列中删除该列，并删除目标表staffs。

DROP TABLE staffs;

6. 尝试选择staff_ID、FIRST_NAME和LAST_NAME的组合作为分布列，创建目标表staffs。

CREATE TABLE staffs ( staff_ID NUMBER(6) not null, FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25), EMAIL VARCHAR2(25), PHONE_NUMBER VARCHAR2(20), HIRE_DATE DATE, employment_ID VARCHAR2(10), SALARY NUMBER(8,2), COMMISSION_PCT NUMBER(2,2), MANAGER_ID NUMBER(6), section_ID NUMBER(4) ) DISTRIBUTE BY hash(staff_ID,FIRST_NAME,LAST_NAME);

7. 校验以staff_ID、FIRST_NAME和LAST_NAME的组合为分布列的目标表staffs的数据倾斜性。

SELECT a.count,b.node_name FROM (select count(*) as count,xc_node_id FROM staffs GROUP BY xc_node_id) a, pgxc_node b WHERE a.xc_node_id=b.node_id ORDER BY a.count desc; count | node_name ------+----------- 10010 | datanode4 10000 | datanode3 10001 | datanode2 9995 | datanode1 10000 | datanode5 9999 | datanode6 9995 | datanode7 10000 | datanode8 (8 rows)

8. 根据上一步骤查询所得，各DN上数据分布差小于10%，数据分布均衡，选择的分布列合适。

9. 清理已导入小批量数据。

TRUNCATE TABLE staffs;

10. 导入全量数据，以完成数据迁移

EI企业智能 Gauss AP 数据仓库服务 GaussDB(DWS)

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。