Re: Slow vacuum performance

От: Andrew McMillan
Тема: Re: Slow vacuum performance
Дата: ,
(см: обсуждение, исходный текст)
Ответ на: Slow vacuum performance  (Patrick Hatcher)
Список: pgsql-performance

Скрыть дерево обсуждения

Slow vacuum performance  (Patrick Hatcher, )
 Re: Slow vacuum performance  (Dennis Bjorklund, )
 Re: Slow vacuum performance  (Andrew McMillan, )
 Re: Slow vacuum performance  (Andrew McMillan, )
  Re: Slow vacuum performance  (Patrick Hatcher, )

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

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


В списке pgsql-performance по дате сообщения:

От: Richard Huxton
Сообщение: Re: memory allocation
От: Tom Lane
Сообщение: Re: *very* inefficient choice made by the planner (regarding