分类: Mysql/postgreSQL
2011-05-05 20:22:52
创建测试表
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