PG 8.2.3, FSM warning & VACUUM

From: "Sergey E(dot) Koposov" <math(at)sai(dot)msu(dot)ru>
To: pgsql-general(at)postgresql(dot)org
Cc: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
Subject: PG 8.2.3, FSM warning & VACUUM
Date: 2007-04-04 23:22:38
Message-ID: Pine.LNX.4.64.0704050220350.6741@lnfm1.sai.msu.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)sai(dot)msu(dot)ru

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2007-04-04 23:37:07 Re: dropping role w/dependent objects
Previous Message Ed L. 2007-04-04 23:08:28 Re: dropping role w/dependent objects