GaussDB(DWS) 云端运维系列第七期:数据库维护(VACUUM,ANALYZE,倾斜检查)

举报
chinawjb 发表于 2020/11/29 01:32:57 2020/11/29
【摘要】 VACUUM回收表或B-Tree索引中已经删除的行所占据的存储空间。 ANAL用于收集与数据库中普通表内容相关的统计信息,统计结果存储在系统表PG_STATISTIC下。执行计划生成器会使用这些统计数据,以确定最有效的执行计划。 数据倾斜会造成查询表性能下降。

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选项,无信息输出。
  • 当含有带括号的选项列表时,选项可以以任何顺序写入。如果没有括号,则选项必须按语法显示的顺序给出。
  • VACUUMVACUUM 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操作。

COMPACTPARTITION参数不能同时使用。

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

  • FREEZE

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

  • VERBOSE

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

  • ANALYZE | ANALYSE

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

  • table_name

要清理的表的名称(可以有模式修饰)。

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

  • column_name

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

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

  • PARTITION

HDFS表不支持PARTITION参数,COMPACTPARTITION参数不能同时使用。

  • 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_namecolumnpartition_name参数把分析限定在特定的表、列或分区表中。

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

注意事项

由于现版本ANALYZE非临时表是一个多事务的行为,因此ANALYZE非临时表不能在一个匿名块、事务块、函数或存储过程内被执行,且当ANALYZE与部分DML语句,例如ALTER TABLEDROP TABLEUPDATE等并发时,可能会引发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

    启用显示进度信息。

    说明:

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

    • table_name

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

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

    取值范围:已有的表名。

    • column_namecolumn_1_namecolumn_2_name

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

    取值范围:已有的列名。

    • partition_name

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

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

    • foreign_table_name

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

    取值范围:已有的表名。

    • FOREIGN TABLES

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

    • index_name

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

    取值范围:已有的表名。

    • FAST|COMPLETE

    对于行存表,FAST模式下主要对于行存表的CRCpage header进行校验,如果校验失败则会告警; COMPLETE模式下,则主要对行存表的指针、tuple进行解析校验。 对于列存表,FAST模式下主要对于列存表的CRCmagic进行校验,如果校验失败则会告警; COMPLETE模式下,则主要对列存表的CU进行解析校验。

    • CASCADE

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

    示例

    --- 创建表。

    --- 创建分区表。

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

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

    说明:

    若环境若有故障,需查看CNlog

    --- 删除表。

     

    倾斜检查

    操作场景

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

    背景信息

    GaussDB(DWS)是采用Shared-nothing架构的MPPMassive 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_IDFIRST_NAMELAST_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_IDFIRST_NAMELAST_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_IDFIRST_NAMELAST_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. 导入全量数据,以完成数据迁移

    【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
    • 点赞
    • 收藏
    • 关注作者

    评论(0

    0/1000
    抱歉,系统识别当前为高风险访问,暂不支持该操作

    全部回复

    上滑加载中

    设置昵称

    在此一键设置昵称,即可参与社区互动!

    *长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

    *长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。