Re: Slow vacuum 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
-------------------------------------------------------------------------