[PERFORM] Vacuum and FSM page size

[Thomas Lozza]

hi

We have an installation of Postgres 8.1.2 (32bit on Solaris 9) with a DB
size of about 250GB on disk. The DB is subject to fair amount of
inserts, deletes and updates per day. 

Running VACUUM VERBOSE tells me that I should allocate around 20M pages
to FSM (max_fsm_pages)! This looks like a really large amount to me. 

Has anyone gone ever that high with max_fsm_pages?

The other question is why such a large number is required in the first
place. 
Auto vacuum is enabled. Here are the settings:

autovacuum = true   
autovacuum_naptime = 900
autovacuum_vacuum_threshold = 2000
autovacuum_analyze_threshold = 1000
autovacuum_vacuum_scale_factor = 0.25
autovacuum_analyze_scale_factor = 0.18
autovacuum_vacuum_cost_delay = 150
autovacuum_vacuum_cost_limit = 120

A manual vacuum takes very long (around 4 days), so maybe the cost delay
and limit or too high.
Any suggestions anyone?

Cheers,
-- Tom.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PERFORM] Vacuum and FSM page size

[Thomas Lozza]

Thanks for the advice. 
I used the default settings before, thought though that vacuum was a bit
aggressive, ie, using too many resources. Now its taking very long. So
will have to find reasonable settings in between I guess.

On the other hand, if I keep the fsm_page number high enough, the system
should be fine with a low number of vacuum cycles, right. As memory is
not really scarce (16G, 32 bit PG though) an x million fsm_page entry
should be ok. Any thoughts on that?

cheers,
-- tom.



-Original Message-
From: Scott Marlowe [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 24. January, 2008 10:48
To: Thomas Lozza
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Vacuum and FSM page size

On Jan 23, 2008 12:29 PM, Thomas Lozza <[EMAIL PROTECTED]>
wrote:
> Auto vacuum is enabled. Here are the settings:
>
> autovacuum = true
> autovacuum_naptime = 900
> autovacuum_vacuum_threshold = 2000
> autovacuum_analyze_threshold = 1000
> autovacuum_vacuum_scale_factor = 0.25
> autovacuum_analyze_scale_factor = 0.18 autovacuum_vacuum_cost_delay = 
> 150 autovacuum_vacuum_cost_limit = 120
>
> A manual vacuum takes very long (around 4 days), so maybe the cost 
> delay and limit or too high.

Your autovacuum_vacuum_cost_delay is REALLY high.  Try setting it to 10
or 20 and see if that helps.

What is your plain old vacuum_cost_delay set to?

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings