Error

An error occurred.

Sorry, the page you are looking for is currently unavailable.
Please try again later.

If you are the system administrator of this resource then you should check the error log for details.

Faithfully yours, nginx.

帮助文档(华北一、二)

  • ● 方法一:

    vacuum full table 注意,vacuum full不能回收索引的膨胀空间。vacuum full 加载的锁与DDL锁类似,是排它锁。建议在没有业务的时候执行,不要堵塞业务。 使用vacuum full回收垃圾的建议操作流程:

    1 记录下表的索引;

    2 删除索引;

    3 vacuum full 表;

    4 重建索引。

    示例:

     

    dev=# create table test(id int , name text);

    NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.

    HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.

    CREATE TABLE

    dev=# insert into test select generate_series(1,100000000), 'test';

    INSERT 0 100000000

    dev=# create index idx_test on test(id);

    CREATE INDEX

    dev=# update test set name='nice';

    UPDATE 100000000

    查看表格数据:

    dev=# select pg_size_pretty(pg_relation_size('test'));

     pg_size_pretty

    ----------------

     8401 MB

    (1 row)

    查看索引数据:

    dev=# select pg_size_pretty(pg_relation_size('idx_test'));

     pg_size_pretty

    ----------------

     6377 MB

    (1 row)

    先回收表数据(此方法不能回收索引数据):

    dev=# vacuum full test ;

    VACUUM

    dev=# select pg_size_pretty(pg_relation_size('test'));

     pg_size_pretty

    ----------------

     4200 MB

    (1 row)

    Time: 4.278 ms

    dev=# select pg_size_pretty(pg_relation_size('idx_test'));

     pg_size_pretty

    ----------------

     6377 MB

    (1 row)

    回收索引和表的数据:

    dev=# drop index idx_test ;

    DROP INDEX

    dev=# vacuum full test ;

    VACUUM

    dev=# create index idx_test on test(id);

    CREATE INDEX

    dev=# select pg_size_pretty(pg_relation_size('test'));

     pg_size_pretty

     4200 MB

    (1 row)

    dev=# select pg_size_pretty(pg_relation_size('idx_test'));

     pg_size_pretty

    ----------------

     2126 MB

    (1 row)

    ● 方法二:通过修改分布键释放空间

    修改分布键可以回收索引的膨胀空间。修改分布键加载的锁与DDL锁类似,是排它锁。建议在没有业务的时候执行,不要影响业务。

     

    alter table test set with (reorganize=true) distributed randomly;

    alter table test set with (reorganize=true) distributed by (id);

    实例:

    dev=# update test set name='back';

    UPDATE 100000000

    查看数据

    dev=# select pg_size_pretty(pg_relation_size('test'));

     pg_size_pretty

    ----------------

     8401 MB

    (1 row)

    dev=# select pg_size_pretty(pg_relation_size('idx_test'));

     pg_size_pretty

    ----------------

     4251 MB

    (1 row)

    查看表格的分布键,如下所示Distributed by: (id),分布键为id

    dev=# \d+ test

                      Table "public.test"

     Column |  Type   | Modifiers | Storage  | Description

    --------+---------+-----------+----------+-------------

     id     | integer |           | plain    |

     name   | text    |           | extended |

    Indexes:

        "idx_test" btree (id)

    Has OIDs: no

    Distributed by: (id)

    按照原有的分布键重新分布

    dev=# alter table test set with (reorganize=true) distributed by (id);

    ALTER TABLE

    查看数据

    dev=# select pg_size_pretty(pg_relation_size('test'));

     pg_size_pretty

    ----------------

     4200 MB

    (1 row)

    dev=# select pg_size_pretty(pg_relation_size('idx_test'));

     pg_size_pretty

    ----------------

     2126 MB

    ● 方法三:创建新表,导入数据

    CREATE TABLE…AS SELECT命令把该表拷贝为一个新表,新建的表将不会出现膨胀现象。然后删除原始表并且重命名拷贝的表。

    参考: https://gp-docs-cn.github.io/docs/best_practices/bloat.html

    参考:DW开发指南


文档是否已解决您的问题?

  已解决   未解决

如您有其它疑问,您也可以与我们技术专家联系探讨。

联系技术专家