On Thu, 21 Sep 2006, Tom Lane wrote:
We've seen reports occasionally of how the system reports that
max_fsm_pages needs to be increased to at least X, and then when the DBA
does so, it complains that some larger amount is needed --- one recent
report is here:
http://archives.postgresql.org/pgsql-admin/2006-06/msg00176.php
I finally got around to looking at this, and I see what is going on.
The reported number is based on the aggregate of "request" page counts
passed by VACUUM to the FSM module. However, vacuumlazy.c is
prefiltering its requests to at most MaxFSMPages, because it knows that
no more than that many pages will be accepted anyway. So if you have a
single table containing more than max_fsm_pages pages with interesting
amounts of free space, the reported total is being artificially
constrained, and then when you relax the constraint, the reported total
jumps up. In the case Jeff describes where it kept saying max_fsm_pages
plus 2608 were needed, I suppose he had boatloads of free space in one
table (probably pg_largeobject) and exactly 2608 interesting pages in
all other tables.
It was indeed pg_largeobject that caused all the grief. I have since put
these settings in pg_autovacuum:
vsl_cs=# select * from pg_autovacuum ;
vacrelid | enabled | vac_base_thresh | vac_scale_factor | anl_base_thresh |
anl_scale_factor | vac_cost_delay | vac_cost_limit
----------+---------+-----------------+------------------+-----------------+------------------+----------------+----------------
2613 | t | 150 | 0.1 | 75 |
0.05 | -1 | -1
(1 row)
And it seems much happier these days:
INFO: free space map contains 299025 pages in 144 relations
DETAIL: A total of 296160 page slots are in use (including overhead).
296160 page slots are required to track all free space.
Current limits are: 5366499 page slots, 2000 relations, using 31572 KB.
Before the more aggressive autovacuum settings, we would have the problem crop
up again when a researcher would delete several large objects at once.
Apparently, each large object is around 80MB in size, so I suspect it's not
difficult to overwhelm autovacuum if you remove quite a few of these at one
time.
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
