pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-admin@postgresql.org
Subject: Re: When will my database crash?
Date: Tue, 07 Sep 2004 23:22:31 -0400
Message-ID: <m3d60xxx54.fsf@wolfe.cbbrowne.com> (raw)
References: <20040908020214.M78932@eg-1.com>

After takin a swig o' Arrakan spice grog, sc@eg-1.com ("S. C.") belched out:
> We have a postgres 7.4 database which never vacuum for 4 months. I try to 
> vacuum one time. But my manager can't bear the low performance of website. So 
> I had to kill the vacuum before it finished.  Is it ok for a postgres 7.4 
> database never do vacuuming? 
>
> We have 10,000 trans every day. That would be 10,000 inserts and 10,000 
> updates each day.

No, what you need to do is to _regularly_ vacuum it.  That is, _ALL
THE TIME_.

Not vacuuming for four months was a severe error, which means that you
now need to run a really full maintenance cycle.  At the next possible
opportunity, you really need to do a VACUUM FULL on the whole
database.

It's a little like running an automobile and not bothering to change
the oil or to do any other maintenance for several years.  You'd
discover that you need to take the automobile entirely out of service
for an extended period in order to do _major_ repair work, and the
fact that this inconveniences someone is irrelevant.

_Perhaps_ you can do the maintenance a table at a time, here and
there.

But whenever you kill a vacuum, you are discarding any work it has
done.  Don't waste everyone's time by watching it for a while, and
then stopping it; keep vacuuming until the tables are DONE.

And then put in a proper maintenance regimen, perhaps using
pg_autovacuum, or at least doing a vacuum of the whole database at low
periods either daily, or at worst, weekly.

In some of our systems, we have tables that need to get vacuumed every
five minutes so that the cost of vacuuming never gets unbearable.
Vacuuming every five minutes goes in a flash because there are
normally only a few hundred tuples, with only a couple hundred of them
"live."  For those tables, vacuuming once an hour would be
unacceptable, and would destroy performance of the systems.
-- 
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','acm.org').
http://www3.sympatico.ca/cbbrowne/wp.html
'Mounten'  wird  fuer  drei  Dinge benutzt:  'Aufsitzen'  auf  Pferde,
'einklinken'  von Festplatten in  Dateisysteme, und,  nun, 'besteigen'
beim Sex.



view thread (5+ messages)  latest in thread

Message-ID: <m3d60xxx54.fsf@wolfe.cbbrowne.com>
Permalink:  ../m3d60xxx54.fsf@wolfe.cbbrowne.com/
Also on:    postgresql.org/message-id/m3d60xxx54.fsf@wolfe.cbbrowne.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: cbbrowne@acm.org
  Subject: Re: When will my database crash?
  In-Reply-To: <m3d60xxx54.fsf@wolfe.cbbrowne.com>
  References: <20040908020214.M78932@eg-1.com> <m3d60xxx54.fsf@wolfe.cbbrowne.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox