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

Is there a reason _not_ to vacuum continuously?

From: "Matt Clark" <matt(at)ymogen(dot)net>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Is there a reason _not_ to vacuum continuously?
Date: 2003-09-17 19:40:16
Message-ID: LFEIJBEOKGPDHCEMDGNFCECFCDAA.matt@ymogen.net (view raw or flat)
Thread:
Lists: pgsql-performance
I'm running a load of stress scripts against my staging environment to
simulate user interactions, and watching the various boxen as time goes by.

I noticed that the CPU utilisation on the DB server (PG 7.2.3, RH7.3, Dual
PII 550MHz, 1GB RAM, 1GB database on disk, Single 10k SCSI drive) was
increasing over time, and manually launched a vacuum analyze verbose.

A typical output from the VAV is:

NOTICE:  --Relation mobilepm--
NOTICE:  Index mobilepm_ownerid_idx: Pages 1103; Tuples 32052: Deleted
46012.
        CPU 0.15s/0.66u sec elapsed 14.82 sec.
NOTICE:  Index mobilepm_id_idx: Pages 1113; Tuples 32143: Deleted 46012.
        CPU 0.33s/1.08u sec elapsed 45.89 sec.
NOTICE:  Index mobilepm_ownerid_status_idx: Pages 1423; Tuples 32319:
Deleted 46
012.
        CPU 0.52s/1.05u sec elapsed 54.59 sec.
NOTICE:  Index mobilepm_number_idx: Pages 1141; Tuples 32413: Deleted 46012.
        CPU 0.26s/0.61u sec elapsed 16.13 sec.
NOTICE:  Removed 46012 tuples in 2548 pages.
        CPU 0.88s/0.79u sec elapsed 75.57 sec.
NOTICE:  Pages 3188: Changed 10, Empty 0; Tup 32007: Vac 46012, Keep 11,
UnUsed
0.
        Total CPU 2.56s/4.25u sec elapsed 216.50 sec.
NOTICE:  --Relation pg_toast_112846940--
NOTICE:  Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
        Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE:  Analyzing mobilepm

So you can see that some tables are seeing a hell of a lot of updates.
That's life, and yes, I do need all those indexes :-)

Now I see no drop in performance while the VAV is running, the CPU
utilisation gradually drops from 80% to 30% on the DB server, and life in
general improves.

On the live server (PG 7.2.3, RH7.3, Quad Xeon 700Mhz 1MB cache, 4Gb RAM,
256MB write-back RAID10 over 4 10K disks) I vacuum analyze daily, and vacuum
analyze a couple of key tables every 15 minutes, but my question is...

*** THE QUESTION(S) ***
Is there any reason for me not to run continuous sequential vacuum analyzes?
At least for the 6 tables that see a lot of updates?
I hear 10% of tuples updated as a good time to vac-an, but does my typical
count of 3 indexes per table affect that?

Cheers

Matt


Postscript:  I may have answered my own question while writing this mail.
Under the current stress test load about 10% of the key tables' tuples are
updated between sequential vacuum-analyzes, so the received wisdom on
intervals suggests '0' in my case anyway...




Responses

pgsql-performance by date

Next:From: scott.marloweDate: 2003-09-17 19:54:42
Subject: Re: Is there a reason _not_ to vacuum continuously?
Previous:From: Tom LaneDate: 2003-09-17 19:08:33
Subject: Re: inferior SCSI performance

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