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. 导入全量数据,以完成数据迁移
- 点赞
- 收藏
- 关注作者
评论(0)