To: Bastian Voigt <post@bastian-voigt.de>
cc: pgsql-performance@postgresql.org
Subject: Re: Performance Problem with Vacuum of bytea table (PG 8.0.13) 
In-reply-to: <46569E6A.2060509@bastian-voigt.de> 
References: <46569E6A.2060509@bastian-voigt.de>
Comments: In-reply-to Bastian Voigt <post@bastian-voigt.de>
	message dated "Fri, 25 May 2007 10:29:30 +0200"
Date: Fri, 25 May 2007 10:18:45 -0400
Message-ID: <8588.1180102725@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Bastian Voigt <post@bastian-voigt.de> writes:
> Now my big big problem is that the database gets really really slow 
> during these 20 minutes and after the vacuum process is running for a 
> short time, many transactions show state "UPDATE waiting" in the process 
> list. In my Java application server I sometimes get tons of deadlock 
> Exceptions (waiting on ShareLock blahblah). The web frontend gets nearly 
> unusable, logging in takes more than 60 seconds, etc. etc.

Hmm.  That's a bit weird --- what are they waiting on exactly?  Look in
pg_locks to see what the situation is.  A vacuum per se ought not be
blocking any updates.

Aside from the recommendation to make the vacuums happen more frequently
instead of less so, you should experiment with vacuum_cost_delay and
related parameters.  The idea is to reduce vacuum's I/O load so that it
doesn't hurt foreground response time.  This means any individual vacuum
will take longer, but you won't need to care.

			regards, tom lane