Re: Vacuum DB

On Thu, 31 Aug 2000 ghaverla@freenet.edmonton.ab.ca wrote:

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

Bundling many updates into a single transaction will help. I'm not an
expert but I guess that's because every update statement is a transaction
of its own, will all locking overhead. So you will notice a performance
improvement.

But you'll have to VACUUM you db on a regular base anyway. The vacuum
issue is unrelated to how many updates you pack into a transition,
i think...

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

.TM.
--
      ____/  ____/   /
     /      /       /            Marco Colombo
    ___/  ___  /   /              Technical Manager
   /          /   /             ESI s.r.l.
 _____/ _____/  _/               Colombo@ESI.it