Re: Vacuum DB

From: Marco Colombo <marco(at)esi(dot)it>
To: ghaverla(at)freenet(dot)edmonton(dot)ab(dot)ca
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Vacuum DB
Date: 2000-08-31 21:18:22
Message-ID: Pine.LNX.4.10.10008312311280.783-100000@NOC.ESI
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

On Thu, 31 Aug 2000 ghaverla(at)freenet(dot)edmonton(dot)ab(dot)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(at)ESI(dot)it

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2000-08-31 21:52:54 Re: Failing to boot Postgres on Red Hat Linux
Previous Message ghaverla 2000-08-31 19:26:55 More Re: 7.0.2 regressions testing on Sparc running 2.5.1