From: Karl Wright <kwright@metacarta.com>
To: Francisco Reyes <lists@stringsutils.com>
Cc: 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: Wed, 20 Jun 2007 13:28:08 -0400
Message-ID: <467963A8.2060703@metacarta.com> (raw)
In-Reply-To: <cone.1182267949.154012.90929.5001@35st.simplicato.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>
<20070619134637.GG4265@alvh.no-ip.org>
<cone.1182267949.154012.90929.5001@35st.simplicato.com>
Francisco Reyes wrote:
> Alvaro Herrera writes:
>
>> 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.
>
> Specially with 16GB of RAM.
>
> I have a setup with several databases (the largest of which is 1TB
> database) and I do a nightly vacuum analyze for ALL databases. It takes
> about 22 hours. And this is with constant updates to the large 1TB
> database. This is with Postgresql 8.1.3
Okay - I started a VACUUM with the 8.1 database yesterday morning,
having the database remain under load. As of 12:30 today (~27 hours),
the original VACUUM was still running. At that point:
(a) I had to shut it down anyway because I needed to do another
experiment having to do with database export/import performance, and
(b) the performance of individual queries had already degraded
significantly in the same manner as what I'd seen before.
So, I guess this means that there's no way I can keep the database
adequately vacuumed with my anticipated load and hardware. One thing or
the other will have to change.
Is the VACUUM in 8.2 significantly faster than the one in 8.1? Or, is
the database less sensitive performance-wise to delayed VACUUM commands?
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, lists@stringsutils.com, alvherre@commandprompt.com, tgl@sss.pgh.pa.us
Subject: Re: Performance query about large tables, lots of concurrent access
In-Reply-To: <467963A8.2060703@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