Horton

Thread

  1. Vacuum and FSM page size

    Thomas Lozza <thomas.lozza@nexustelecom.com> — 2008-01-23T18:29:16Z

    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.

  2. Re: Vacuum and FSM page size

    Decibel! <decibel@decibel.org> — 2008-01-23T19:02:21Z

    On Wed, Jan 23, 2008 at 07:29:16PM +0100, Thomas Lozza wrote:
> 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?

No, that's telling me that you have a lot of bloat. A 250G database is
about 31M pages. If you have 20M pages with free space then you've got a
lot of bloat. Ideally, with a autovac_vacuum_scale_factor of .25 you
should only need 4M FSM pages. At most you should only need 8M.

> 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
Why'd you change that? That's pretty high.

> autovacuum_vacuum_threshold = 2000
> autovacuum_analyze_threshold = 1000

Both of those seem high...

> autovacuum_vacuum_scale_factor = 0.25
That means that 12.5% of your database (on average) will be dead
space... I'd probably cut that back to 0.2.

> autovacuum_analyze_scale_factor = 0.18
This also seems pretty high.

> autovacuum_vacuum_cost_delay = 150

Woah, that's *really* high. That means at most you'll get 6 vacuum
rounds in per second; with default cost settings that means you'd be
able to actually vacuum about 50 dirty pages per second, tops. Of course
not all pages will be dirty, but still...

I normally use between 10 and 20 for cost_delay (lower values for faster
drive arrays).

> autovacuum_vacuum_cost_limit = 120
Why'd you reduce this? I'd put it back to 200...
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

  3. Re: Vacuum and FSM page size

    Vivek Khera <khera@kcilink.com> — 2008-01-23T19:22:20Z

    On Jan 23, 2008, at 1:29 PM, Thomas Lozza wrote:

> 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?

wow.  you must have a *lot* of pages with empty space in them....

it sounds to me like your autovacuum is not running frequently enough.

  4. Re: Vacuum and FSM page size

    Tom Lane <tgl@sss.pgh.pa.us> — 2008-01-23T20:25:50Z

    Vivek Khera <khera@kcilink.com> writes:
> On Jan 23, 2008, at 1:29 PM, Thomas Lozza wrote:
>> We have an installation of Postgres 8.1.2 (32bit on Solaris 9) with  
>> ...

> it sounds to me like your autovacuum is not running frequently enough.

Yeah.  The default autovac settings in 8.1 are extremely conservative
(in the direction of not letting autovac eat many cycles), so a
high-traffic installation will need to adjust them to keep from falling
behind.

			regards, tom lane

  5. Re: Vacuum and FSM page size

    Scott Marlowe <scott.marlowe@gmail.com> — 2008-01-24T15:48:04Z

    On Jan 23, 2008 12:29 PM, Thomas Lozza <thomas.lozza@nexustelecom.com> 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?

  6. Re: Vacuum and FSM page size

    Thomas Lozza <thomas.lozza@nexustelecom.com> — 2008-01-27T23:01:30Z

    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:scott.marlowe@gmail.com] 
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 <thomas.lozza@nexustelecom.com>
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?

  7. Re: Vacuum and FSM page size

    Scott Marlowe <scott.marlowe@gmail.com> — 2008-01-27T23:54:12Z

    On Jan 27, 2008 5:01 PM, Thomas Lozza <thomas.lozza@nexustelecom.com> wrote:
> 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?

The issue you then run into is bloat, where you have a table or index
that is 90% or so empty space, and performance on selects might
suffer, especially on larger tables or indexes.

What often works best is to let autovacuum handle most of your tables,
then schedule individual tables to be vacuumed by cron, setting the
nap time for vacuum at 20 or 30 milliseconds so they don't chew up all
of your I/O