Thread

1. Slow vacuum performance

   Patrick Hatcher <phatcher@macys.com> — 2004-06-17T20:09:47Z

   
   
   
   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
   
   
   

2. Re: Slow vacuum performance

   Dennis Björklund <db@zigo.dhs.org> — 2004-06-18T04:30:12Z

   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
   
   
   

3. Re: Slow vacuum performance

   Andrew McMillan <andrew@catalyst.net.nz> — 2004-06-18T08:15:44Z

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