Re: [PERFORM] Upgrade to dual processor machine?

On 14 Nov 2002 at 21:36, Henrik Steffen wrote:

> do you seriously think that I should vacuum frequently updated/inserted
> tables every 120 seconds ?

Its not about 120 seconds. Its about how many new and dead tuples your server
is generating.

Here is a quick summary

insert: New tuple:vacuum analyse updates that statistics.
update: Causes a dead tuple: Vacuum analyse marks dead tuple for reuse saving
buffer space.
delete: Causes a dead unusable tuple: Vacuum full is required to reclaim the
space on the disk.

Vacuum analyse is nonblocking and vacuum full is blocking.

If you are generating 10 dead pages i.e. 80K of data in matter of minutes.
vacuum is warranted for optimal performance..

> I have many UPDATEs and INSERTs on my log-statistics. For each http-request
> there will be an INSERT into the logfile. And if certain customer pages
> are downloaded there will even be an UPDATE in a customer-statistics table
> causing a hits column to be set to hits+1... I didn't think this was a
> dramatical change so far.

OK.. Schedule a cron job that would vacuum analyse every 5/10 minutes.. And see
if that gives you overall increase in throughput

> Still sure to run VACUUM ANALYZE on these tables so often?

IMO you should..

Also have a look at http://gborg.postgresql.org/project/pgavd/projdisplay.php.

I have written it but I don't know anybody using it. If you use it, I can help
you with any bugfixes required. I haven't done too much testing on it. It
vacuums things based on traffic rather than time. So your database performance
should ideally be maintained automatically..

Let me know if you need anything on this.. And use the CVS version please..

Bye
Shridhar

--
love, n.: When, if asked to choose between your lover and happiness, you'd skip
happiness in a heartbeat.