Greenplum中的VACUUM和VACUUM FULL
来源:互联网 发布:比特币 产生原理 算法 编辑:程序博客网 时间:2024/04/27 22:08
VACUUM会把由于delete和update操作造成的空洞重复利用,但是不会释放空间。而VACUUM FULL则会释放相应的空间。下面是测试过程
[gpadmin1@hadoop5 ~]$ psql
psql (8.2.13)
Type "help" for help.
template1=# create table ttt2(id int);
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
template1=# insert into ttt2 select generate_series(1,1000000);
INSERT 0 1000000
template1=# select pg_relation_size('ttt2')/1024/1024;
?column?
----------
34
(1 row)
template1=# select oid,relname,relfilenode from pg_class where relname='ttt2';
oid | relname | relfilenode
-------+---------+-------------
55309 | ttt2 | 55309
(1 row)
这个GP集群总共两个节点,因此这里看到的大小不会是34M
[gpadmin1@hadoop5 1]$ ll -h | grep 55309
-rw------- 1 gpadmin1 gpadmin1 18M Feb 20 21:06 55309
template1=# delete from ttt2 where id<500000;
DELETE 499999
template1=# vacuum ttt2;
VACUUM
template1=# select pg_relation_size('ttt2')/1024/1024;
?column?
----------
34
(1 row)
大小并没有什么变化
[gpadmin1@hadoop5 1]$ ll -h | grep 55309
-rw------- 1 gpadmin1 gpadmin1 18M Feb 20 21:07 55309
template1=# insert into ttt2 select generate_series(1,200000);
INSERT 0 200000
template1=# select pg_relation_size('ttt2')/1024/1024;
?column?
----------
34
(1 row)
这里看到,当再次插入200000万条数据的时候大小没变,之前删除的500000数据所使用的空间被重新利用了
[gpadmin1@hadoop5 1]$ ll -h | grep 55309
-rw------- 1 gpadmin1 gpadmin1 18M Feb 20 21:07 55309
template1=# vacuum full ttt2;
NOTICE: 'VACUUM FULL' is not safe for large tables and has been known to yield unpredictable runtimes.
HINT: Use 'VACUUM' instead.
VACUUM
template1=# select pg_relation_size('ttt2')/1024/1024;
?column?
----------
24
(1 row)
使用vacuum full,可以看到剩余空间得到了释放
[gpadmin1@hadoop5 1]$ ll -h | grep 55309
-rw------- 1 gpadmin1 gpadmin1 13M Feb 20 21:08 55309
下面附上一段GP文档上对两者的说明
Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained. VACUUM FULL does more extensive processing, including moving of tuples across blocks to try to compact the table to the minimum number of disk blocks. This form is much slower and requires an exclusive lock on each table while it is being processed.
#################
今天在另外一篇文章里看到的一段话,红色是重点
Note that VACUUM does not shrink a table when it runs, unless there is a large run of space
at the end of a table, and nobody is accessing the table when we try to shrink it. To properly
shrink a table, you need VACUUM FULL. That locks up the whole table for a long time, and
should be avoided, if possible. VACUUM FULL will literally rewrite every row of the table, and
completely rebuild all indexes. That process is faster in 9.0 than it used to be, though it's still a
long time for larger tables.
- Greenplum中的VACUUM和VACUUM FULL
- Greenplum中的vacuum和analyze
- vacuum和vacuum full区别
- Greenplum vacuum ao表和heap表的区别
- PostgreSQL的autovacuum 与 vacuum full
- Greenplum数据库的vacuum操作与max_fsm_pages参数的关系
- Greenplum定期vacuum系统表以及定期删除日志
- greenplum中vacuum如何计算ao表的冗余度
- postgresql中快速对系统表实现vacuum full
- SQLite语法 VACUUM
- PostgreSQL 自动Vacuum配置
- sqlite文件大小问题 VACUUM
- postgresql之vacuum
- How vacuum template0
- postgresql vacuum操作
- SQLite---VACUUM命令分析
- postgresql关于auto vacuum
- SQLite的VACUUM命令
- VC中Combo Box控件使用大全
- DVD刻盘惊险
- Build ACE via VC++ 2010
- IE 8 安全和隐私
- Ubuntu centos安装Lua(zz)
- Greenplum中的VACUUM和VACUUM FULL
- Vector、ArrayList和List的异同
- 十个最好的翻译学习网站
- Linux实现无密码登录
- java基础加强--泛型(Generic)的应用
- TechDiary_20110220
- SQL Server 2008 安装过程中遇到“性能计数器注册表配置单元一致性”检查失败 问题的解决方法
- 音色与音质训练
- 如何通过IP地址分辨公网、私网、内网、外网