From: Karl Wright <kwright@metacarta.com>
To: Alvaro Herrera <alvherre@commandprompt.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 10:06:25 -0400
Message-ID: <4677E2E1.2060902@metacarta.com> (raw)
In-Reply-To: <20070619134637.GG4265@alvh.no-ip.org>
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>
<20070619134637.GG4265@alvh.no-ip.org>
Alvaro Herrera wrote:
> 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.
>
The database is humongus, and the machine is under intense load. On the
instance where this long vacuum occurred, there were several large
tables - one with 7,000,000 rows, one with 14,000,000, one with
140,000,000, and one with 250,000,000.
> 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).
Yeah, sorry, that doesn't apply here.
>
> 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.
>
Ok - that's something I should be able to do once we can go to debian's
etch release. There's a backport of 8.2 available there. (The one for
sarge is still considered 'experimental').
Karl
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: kwright@metacarta.com, alvherre@commandprompt.com, tgl@sss.pgh.pa.us
Subject: Re: Performance query about large tables, lots of concurrent access
In-Reply-To: <4677E2E1.2060902@metacarta.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