Thread: Slow vacuum performance

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

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


TIA

Patrick Hatcher
Macys.Com

On Thu, 17 Jun 2004, Patrick Hatcher wrote:

> 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

You could try to run normal (non full) vacuum every hour or so. If you do
normal vacuum often enough you probably don't need to run vacuum full at
all.

--
/Dennis Björklund

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

On Fri, 2004-06-18 at 19:51 -0700, Patrick Hatcher wrote:
>
> Thanks!
>
> My effective_cache_size = 625000
>
> I thought that having the shared_buffers above 2k or 3k didn't gain
> any performance and may in fact degrade it?

Hi Patrick,


Quoting from:
http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html

shared_buffers
        Sets the size of PostgreSQL's' memory buffer where queries are
        held before being fed into the Kernel buffer of the host system.
        It's very important to remember that this is only a holding
        area, and not the total memory available for the server. As
        such, resist the urge to set this number to a large portion of
        your RAM, as this will actually degrade performance on many
        operating systems. Members of the pgsql-performance mailing list
        have found useful values in the range of 1000-6000, depending on
        available RAM, database size, and number of concurrent queries.
        For servers with very large amounts of available RAM (more than
        1 GB) increasing this setting to 6-15% or available RAM has
        worked well for some users. The real analysis of the precise
        best setting is not fully understood and is more readily
        determined through testing than calculation.

        As a rule of thumb, observe shared memory usage of PostgreSQL
        with tools like ipcs and determine the setting. Remember that
        this is only half the story. You also need to set
        effective_cache_size so that postgreSQL will use available
        memory optimally.

Using this conservatively, on an 8G system, 6% would be roughly 60,000
pages - considerably higher than 2-3000...

One day when I wasn't timid (well, OK, I was desperate :-), I did see a
_dramatic_ performance improvement in a single very narrow activity by
setting shared_buffers to 300000 on a 4G RAM system (I was rolling back
a transaction involving an update to 2.8 million rows) , but afterwards
I set shared_buffers back to 10000, which I have now increased to 20000
on that system.


You may also want to look at:
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html

Or indeed, peruse the articles regularly as they appear:
http://www.varlena.com/varlena/GeneralBits/

Regards,
                    Andrew McMillan

-------------------------------------------------------------------------
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
           Tomorrow will be cancelled due to lack of interest.
-------------------------------------------------------------------------