Re: Slow vacuum performance - Mailing list pgsql-performance

From Andrew McMillan
Subject Re: Slow vacuum performance
Date
Msg-id 1087546544.4942.50.camel@lamb.mcmillan.net.nz
Whole thread Raw
In response to Slow vacuum performance  (Patrick Hatcher <PHatcher@macys.com>)
List pgsql-performance
On Thu, 2004-06-17 at 13:09 -0700, Patrick Hatcher wrote:
>
>
>
> Pg:  7.4.2
> RedHat 7.3
> Ram: 8gig
>
> I have 6 million row table that I vacuum full analyze each night.  The time
> seems to be streching out further and further as I add more rows.  I read
> the archives and Josh's annotated pg.conf guide that setting the FSM higher
> might help.  Currently, my memory settings are set as such.  Does this seem
> low?
>
> Last reading from vaccum verbose:
>   INFO:  analyzing "cdm.cdm_ddw_customer"
> INFO:  "cdm_ddw_customer": 209106 pages, 3000 rows sampled, 6041742
> estimated total rows
> >>I think I should now set my max FSM to at least 210000 but wanted to make
> sure

Yes, that's my interpretation of those numbers too.  I would set
max_fsm_pages to 300000 (or more) in that case.

If you have 8G of RAM in the machine your shared_buffers seems very low
too.  Depending on how it is used I would increase that to at least the
recommended maximum (10000 - 80M).

You don't quote your setting for effective_cache_size, but you should
probably look at what "/usr/bin/free" reports as "cached", divide that
by 10, and set it to that as a quick rule of thumb...

Regards,
                    Andrew McMillan


> shared_buffers = 2000           # min 16, at least max_connections*2, 8KB
> each
> sort_mem = 12288                # min 64, size in KB
>
> # - Free Space Map -
>
> max_fsm_pages = 100000          # min max_fsm_relations*16, 6 bytes each
> #max_fsm_relations = 1000       # min 100, ~50 bytes each
>

-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/            PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201      MOB: +64(272)DEBIAN      OFFICE: +64(4)499-2267
      Make things as simple as possible, but no simpler -- Einstein
-------------------------------------------------------------------------

Attachment

pgsql-performance by date:

Previous
From: Dennis Bjorklund
Date:
Subject: Re: Slow vacuum performance
Next
From: SZUCS Gábor
Date:
Subject: Re: *very* inefficient choice made by the planner (regarding