Message-ID: <42DB2D72.8090401@telogis.com>
Date: Mon, 18 Jul 2005 16:17:54 +1200
From: David Mitchell <david.mitchell@telogis.com>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: Robert Creager <Robert_Creager@LogicalChaos.org>
Cc: "Matthew T. O'Connor" <matthew@zeut.net>,
	PGPerformance <pgsql-performance@postgresql.org>
Subject: Re: Huge performance problem between 7.4.1 and 8.0.3 -
References: <20050717213416.59dcb52e@thunder.logicalchaos.org>
	<42DB2684.9020706@zeut.net>
	<20050717221050.7c4ac241@thunder.logicalchaos.org>
In-Reply-To: <20050717221050.7c4ac241@thunder.logicalchaos.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Sounds like either someone is holding a lock on your pg8 db, or maybe 
you need a vacuum full. No amount of normal vacuuming will fix a table 
that needs a vacuum full. Although if that were the case I'd expect you 
to have slow queries regardless of the number of concurrent connections. 
Maybe you should check who is holding locks.

David

Robert Creager wrote:
> I am, and it is.  It's ANALYZING and VACUUM'ing tables every interval (5 minutes
> - 8.0.3).  Right now, for that last 4 hours, I'm not VACUUMing the 7.4.1
> database and it's still clicking along at < .2 second queries.  Last year
> (7.4.1), I noticed that it took about a week of heavy activity (for this DB)
> before I'd really need a vacuum.  That's when I put in the 5 min cron.
> 
> When I first switched over to 8.0.3, I was still running the cron vacuum.  I got
> into big trouble when I had vacuum's backed up for 6 hours.  That's when I
> started noticing the query problem, and the CS numbers being high.  7.4.1
> vacuums every 5 minutes always take < 30 seconds (when I'm watching).
> 
> Cheers,
> Rob
> 
> When grilled further on (Sun, 17 Jul 2005 23:48:20 -0400),
> "Matthew T. O'Connor" <matthew@zeut.net> confessed:
> 
> 
>>Robert Creager wrote:
>>
>>
>>>For 8.03, pg_autovacuum is running.  On 7.4.1, I set up a cron job to vacuum
>>>analyze every 5 minutes.
>>> 
>>>
>>
>>Are you sure that pg_autovacuum is doing it's job?  Meaning are you sure 
>>it's vacuuming as often as needed?  Try to run it with -d2 or so and 
>>make sure that it is actually doing the vacuuming needed.
> 
> 
> 


-- 
David Mitchell
Software Engineer
Telogis