Chinaunix首页 | 论坛 | 博客
  • 博客访问: 305347
  • 博文数量: 81
  • 博客积分: 3813
  • 博客等级: 中校
  • 技术积分: 945
  • 用 户 组: 普通用户
  • 注册时间: 2005-08-24 18:14
文章分类

全部博文(81)

文章存档

2013年(1)

2012年(2)

2011年(54)

2010年(15)

2009年(9)

分类: Mysql/postgreSQL

2011-05-05 20:22:52


VACUUM 是PostgreSQL提供的回收垃圾空间的SQL命令。

8.4的语法如下:
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

9.0的语法如下:
VACUUM [ ( { FULL | FREEZE | VERBOSE | ANALYZE } [, ...] ) ] [ table [ (column [, ...] ) ] ] VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

而8.4以后,pg_autovacuum的表已经没有了

9.0的vacuum full 可以回收INDEX的PAGES,而8.3 & 8.4是不可以的,下面看一个例子:

创建测试表

create table tbl_test1 (id serial8 primary key,pid int8);

插入测试数据

postgres=#  insert into tbl_test1 (pid) select generate_series(333333,100000000000);

^C 中途退出

Cancel request sent

ERROR:  canceling statement due to user request

回收垃圾

postgres=#  VACUUM  VERBOSE tbl_test1;

INFO:  vacuuming "public.tbl_test1"

INFO:  scanned index "tbl_test1_pkey" to remove 2458496 row versions

DETAIL:  CPU 0.00s/0.00u sec elapsed 0.01 sec.

INFO:  "tbl_test1": removed 2458496 row versions in 13290 pages

DETAIL:  CPU 0.00s/0.06u sec elapsed 0.06 sec.

INFO:  index "tbl_test1_pkey" now contains 0 row versions in 7635 pages

DETAIL:  0 index row versions were removed.

7631 index pages have been deleted, 7631 are currently reusable.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  "tbl_test1": found 0 removable, 0 nonremovable row versions in 15047 out of 15047 pages

DETAIL:  0 dead row versions cannot be removed yet.

There were 325045 unused item pointers.

0 pages are entirely empty.

CPU 0.02s/0.12u sec elapsed 0.15 sec.

INFO:  "tbl_test1": truncated 15047 to 0 pages

DETAIL:  CPU 0.03s/0.03u sec elapsed 0.07 sec.

VACUUM


再来一次

postgres=#  VACUUM  VERBOSE tbl_test1;

INFO:  vacuuming "public.tbl_test1"

INFO:  index "tbl_test1_pkey" now contains 0 row versions in 7635 pages

DETAIL:  0 index row versions were removed.

7631 index pages have been deleted, 7631 are currently reusable.

CPU 0.00s/0.01u sec elapsed 0.01 sec.

INFO:  "tbl_test1": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages

DETAIL:  0 dead row versions cannot be removed yet.

There were 0 unused item pointers.

0 pages are entirely empty.

CPU 0.00s/0.01u sec elapsed 0.01 sec.

VACUUM

可看出 VACUUM 不回收index的空闲页面

postgres=# vacuum full verbose tbl_test1;

INFO:  vacuuming "public.tbl_test1"

VACUUM

postgres=#  VACUUM  VERBOSE tbl_test1;

INFO:  vacuuming "public.tbl_test1"

INFO:  index "tbl_test1_pkey" now contains 0 row versions in 1 pages

DETAIL:  0 index row versions were removed.

0 index pages have been deleted, 0 are currently reusable.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  "tbl_test1": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages

DETAIL:  0 dead row versions cannot be removed yet.

There were 0 unused item pointers.

0 pages are entirely empty.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

VACUUM

从 VACUUM FULL可以看出9.0已经可以回收了index的空闲空间。


8.4用例:

postgres=# VACUUM VERBOSE tbl_test1;

INFO:  vacuuming "public.tbl_test1"

INFO:  scanned index "tbl_test1_pkey" to remove 1355152 row versions

DETAIL:  CPU 0.04s/0.29u sec elapsed 0.59 sec.

INFO:  "tbl_test1": removed 1355152 row versions in 7326 pages

DETAIL:  CPU 0.06s/0.06u sec elapsed 2.12 sec.

INFO:  index "tbl_test1_pkey" now contains 0 row versions in 3718 pages

DETAIL:  929860 index row versions were removed.

3706 index pages have been deleted, 1166 are currently reusable.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  "tbl_test1": found 0 removable, 0 nonremovable row versions in 7326 out of 7326 pages

DETAIL:  0 dead row versions cannot be removed yet.

There were 0 unused item pointers.

0 pages are entirely empty.

CPU 0.14s/0.36u sec elapsed 2.76 sec.

INFO:  "tbl_test1": truncated 7326 to 0 pages

DETAIL:  CPU 0.04s/0.00u sec elapsed 0.06 sec.

VACUUM

postgres=# VACUUM VERBOSE tbl_test1;

INFO:  vacuuming "public.tbl_test1"

INFO:  index "tbl_test1_pkey" now contains 0 row versions in 3718 pages

DETAIL:  0 index row versions were removed.

3714 index pages have been deleted, 3714 are currently reusable.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  "tbl_test1": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages

DETAIL:  0 dead row versions cannot be removed yet.

There were 0 unused item pointers.

0 pages are entirely empty.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

VACUUM


postgres=# VACUUM FULL VERBOSE tbl_test1;

INFO:  vacuuming "public.tbl_test1"

INFO:  "tbl_test1": found 0 removable, 0 nonremovable row versions in 0 pages

DETAIL:  0 dead row versions cannot be removed yet.

Nonremovable row versions range from 0 to 0 bytes long.

There were 0 unused item pointers.

Total free space (including removable row versions) is 0 bytes.

0 pages are or will become empty, including 0 at the end of the table.

0 pages containing 0 free bytes are potential move destinations.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "tbl_test1_pkey" now contains 0 row versions in 3718 pages

DETAIL:  3714 index pages have been deleted, 3714 are currently reusable.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

VACUUM

与9.0的功能一样,但输出比9.0少,并且VACCUM FULL没有回收空闲的index空间。

8.4,8.3 在TRUNCATE 后回收了表空间。
postgres=# TRUNCATE table tbl_test1;
TRUNCATE TABLE
Time: 53.141 ms
postgres=# VACUUM VERBOSE tbl_test1;
INFO:  vacuuming "public.tbl_test1"
INFO:  index "tbl_test1_pkey" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "tbl_test1": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
Time: 11.222 ms
阅读(1468) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~