pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew McMillan <andrew@catalyst.net.nz>
To: Patrick Hatcher <PHatcher@macys.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow vacuum performance
Date: Fri, 18 Jun 2004 20:15:44 +1200
Message-ID: <1087546544.4942.50.camel@lamb.mcmillan.net.nz> (raw)
In-Reply-To: <OFCFC284F5.4676AEEF-ON88256EB6.006D7820-88256EB6.006F9082@fds.com>
References: <OFCFC284F5.4676AEEF-ON88256EB6.006D7820-88256EB6.006F9082@fds.com>

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

Attachments:

  signature.asc (application/pgp-signature, 188B) download

view thread (12+ messages)  latest in thread

Message-ID: <1087546544.4942.50.camel@lamb.mcmillan.net.nz>
Permalink:  ../1087546544.4942.50.camel@lamb.mcmillan.net.nz/
Also on:    postgresql.org/message-id/1087546544.4942.50.camel@lamb.mcmillan.net.nz

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: andrew@catalyst.net.nz, PHatcher@macys.com
  Subject: Re: Slow vacuum performance
  In-Reply-To: <1087546544.4942.50.camel@lamb.mcmillan.net.nz>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox