Hello All,
Recently I experienced the strange PG behaviour with the large tables in
PG 8.2.3 which I didn't have in 8.1:
I have the large table: around 1 million records and around 400 columns.
When I try to run VACUUM ANALYZE on it, I'm getting the message:
WARNING: relation "public.xx" contains more than "max_fsm_pages" pages
with useful free space
HINT: Consider compacting this relation or increasing the configuration
parameter "max_fsm_pages".
Which was strange to me , because I never updated or deleted from that
table.
I tried to run VACUUM FULL -- but it didn't change anything ... The
plain VACUUM still was producing the warning about the FSM...
I even tried to recreate the table doing
CREATE new_table as SELECT * FROM old_table
But it didn't change anything ... Still the warning is produced by VACUUM.
Which is strange for me, because I thought that the recreation of table
should garantee the absence of the free space ...
Below I inserted the output of VACUUM FULL VERBOSE ANALYZE and
VACUUM FULL VERBOSE ANALYZE
The value of max_fsm_pages is 204800
So. Is the observed behaviour expected (especially the warning after
executing VACUUM FULL and recreation of the table CREATE TABLE AS SELECT *
FROM ) ? Or is this a bug ?
BTW, it is strange that VACUUM FULL doesn't produce the warning ... ,
while plain VACUUM does.
Thanks,
Sergey
Output of VACUUM and VACUUM FULL...
cas=# VACUUM FULL VERBOSE ANALYZE xx;
INFO: vacuuming "public.xx"
INFO: "xx": found 0 removable, 1647599 nonremovable row versions in
554353
pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 292 to 3092 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 268302952 bytes.
0 pages are or will become empty, including 0 at the end of the table.
327022 pages containing 224924296 free bytes are potential move
destinations.
CPU 2.51s/1.90u sec elapsed 4.42 sec.
INFO: "xx": moved 0 row versions, truncated 554353 to 554353 pages
DETAIL: CPU 0.00s/0.01u sec elapsed 0.01 sec.
INFO: vacuuming "pg_toast.pg_toast_845776"
INFO: "pg_toast_845776": 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 "pg_toast_845776_index" now contains 0 row versions in 1
pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.xx"
INFO: "xx": scanned 3000 of 554353 pages, containing 8918 live rows and 0
dead rows; 3000 rows in sample, 1647907 estimated total rows
VACUUM
cas=# VACUUM VERBOSE ANALYZE xx;
INFO: vacuuming "public.xx"
INFO: "xx": found 0 removable, 1647599 nonremovable row versions in
554353 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
364358 pages contain useful free space.
0 pages are entirely empty.
CPU 4.29s/2.32u sec elapsed 39.88 sec.
WARNING: relation "public.xx" contains more than "max_fsm_pages" pages
with useful free space
HINT: Consider compacting this relation or increasing the configuration
parameter "max_fsm_pages".
INFO: vacuuming "pg_toast.pg_toast_845776"
INFO: index "pg_toast_845776_index" 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: "pg_toast_845776": found 0 removable, 0 nonremovable row versions
in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.xx"
INFO: "xx": scanned 3000 of 554353 pages, containing 8907 live rows and 0
dead rows; 3000 rows in sample, 1645874 estimated total rows
VACUUM
*******************************************************************
Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: math@xxxxxxxxxx
