From: Alvaro Herrera <alvherre@commandprompt.com>
To: Karl Wright <kwright@metacarta.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance query about large tables, lots of concurrent access
Date: Tue, 19 Jun 2007 09:46:37 -0400
Message-ID: <20070619134637.GG4265@alvh.no-ip.org> (raw)
In-Reply-To: <4677DC06.3000300@metacarta.com>
References: <4676DD57.7070304@metacarta.com>
<14291.1182221811@sss.pgh.pa.us>
<4677B831.8060704@metacarta.com>
<4677D298.2010109@metacarta.com>
<20070619132643.GC4265@alvh.no-ip.org>
<4677DC06.3000300@metacarta.com>
Karl Wright wrote:
> Alvaro Herrera wrote:
> >Karl Wright wrote:
> >
> >>This particular run lasted four days before a VACUUM became essential.
> >>The symptom that indicates that VACUUM is needed seems to be that the
> >>CPU usage of any given postgresql query skyrockets. Is this essentially
> >>correct?
> >
> >Are you saying you weren't used to run VACUUM all the time? If so,
> >that's where the problem lies.
>
> Postgresql 7.4 VACUUM runs for so long that starting it with a cron job
> even every 24 hours caused multiple instances of VACUUM to eventually be
> running in my case. So I tried to find a VACUUM schedule that permitted
> each individual vacuum to finish before the next one started. A vacuum
> seemed to require 4-5 days with this particular database - or at least
> it did for 7.4. So I had the VACUUM schedule set to run every six days.
How large is the database? I must admit I have never seen a database
that took 4 days to vacuum. This could mean that your database is
humongous, or that the vacuum strategy is wrong for some reason.
You know that you can run vacuum on particular tables, right? It would
be probably a good idea to run vacuum on the most updated tables, and
leave alone those that are not or little updated (hopefully the biggest;
this would mean that an almost-complete vacuum run would take much less
than a whole day).
Or maybe vacuum was stuck waiting on a lock somewhere.
> I will be experimenting with 8.1 to see how long it takes to complete a
> vacuum under load conditions tonight.
You can also turn autovacuum on in 8.1, which might help quite a bit
with finding a good vacuum schedule (you would need a bit of tuning it
though, of course).
In any case, if you are struggling for performance you are strongly
adviced to upgrade to 8.2.
--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"No single strategy is always right (Unless the boss says so)"
(Larry Wall)
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alvherre@commandprompt.com, kwright@metacarta.com, tgl@sss.pgh.pa.us
Subject: Re: Performance query about large tables, lots of concurrent access
In-Reply-To: <20070619134637.GG4265@alvh.no-ip.org>
* 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