Message-ID: <467963A8.2060703@metacarta.com>
Date: Wed, 20 Jun 2007 13:28:08 -0400
From: Karl Wright <kwright@metacarta.com>
User-Agent: Thunderbird 2.0.0.4 (Windows/20070604)
MIME-Version: 1.0
To: Francisco Reyes <lists@stringsutils.com>
Cc: Alvaro Herrera <alvherre@commandprompt.com>,
	Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
Subject: Re: Performance query about large tables, lots of     
	concurrent access
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>
In-Reply-To: <cone.1182267949.154012.90929.5001@35st.simplicato.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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