Skip site navigation (1) Skip section navigation (2)

Re: Vacuum DB

From: ghaverla(at)freenet(dot)edmonton(dot)ab(dot)ca
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Vacuum DB
Date: 2000-08-31 19:04:19
Message-ID: Pine.A41.3.95.1000831130051.42584D-100000@freenet.edmonton.ab.ca (view raw or flat)
Thread:
Lists: pgsql-novice
On Thu, 31 Aug 2000, Sokel, John wrote:

>                   It will typically make
> 10000-20000 SQL updates/inserts per hour.  This is fine at first, but as the
> day progresses, the updates start falling behind, and the CPU usage by the
> postmaster process starts heading toward 70-80%.  When I do a vacuum, the
> updates speed up dramatically, and the cpu usage goes back down to about
> 10%.  
> 
> The questions are:
> Is there a general rule on how often to run a Vacuum?

Probably, but you should be able to calculate this.  It takes
X amount of time to do a vacuum (which is probably a function
of how much work is needed by the vacuum), and you have the
dbase response time as a function of how many inserts since
the last vacuum.  You do inserts until you (at least) gain
enough time back by doing a vacuum.

> Is there an Auto Vacuum setting to have the postmaster to this
> automatically?

Cron.

> Is there a better approach to keeping this situation from happening (like
> bundling many updates into a single transaction)?

No idea.

Matter Realisations     http://www.materialisations.com/
Gordon Haverland, B.Sc. M.Eng. President
101  9504 182 St. NW    Edmonton, AB, CA  T5T 3A7
780/481-8019            ghaverla @ freenet.edmonton.ab.ca




In response to

  • Vacuum DB at 2000-08-31 18:31:11 from Sokel, John

Responses

pgsql-novice by date

Next:From: ghaverlaDate: 2000-08-31 19:26:55
Subject: More Re: 7.0.2 regressions testing on Sparc running 2.5.1
Previous:From: Marc ZandvlietDate: 2000-08-31 18:43:03
Subject: Modifying tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group