Date: Fri, 25 May 2007 09:56:52 -0400
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Bastian Voigt <post@bastian-voigt.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: My quick and dirty "solution" (Re: Performance Problem with
	Vacuum of bytea table (PG 8.0.13))
Message-ID: <20070525135652.GB4239@alvh.no-ip.org>
References: <46569E6A.2060509@bastian-voigt.de>
 <4656BB5E.3000109@archonet.com>
	<4656BEF1.4050609@bastian-voigt.de> <4656C1C7.3000003@archonet.com>
	<4656C411.8010509@bastian-voigt.de> <4656CDC2.6030106@archonet.com>
	<4656D6EC.5060506@bastian-voigt.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4656D6EC.5060506@bastian-voigt.de>
User-Agent: Mutt/1.5.13 (2006-08-11)

Bastian Voigt wrote:
> No, this did not help. The vacuum process is still running far too long 
> and makes everything slow. It is even worse than before, cause now the 
> system is slow almost all the time while when vacuuming only every 3 
> hours it is only slow once every three hours.....
> 
> 
> I now did the following. Well, no comment.....

Killing the vacuum mid-process doesn't help you, because the table will
be in a sorrier state than it was when it started.

I think it would be better if you:

1. Revert pg_autovacuum changes so that it processes every 3 hours or
whatever, like you had at the start of this thread.  Or maybe less.
That one will take care of the _other_ tables.

2. Vacuum the bytea table manually more often, say every 10 minutes or
so (vacuum, sleep 10m, goto start).  Make sure this is done with an
appropriate vacuum_cost_delay setting (and related settings).

3. Raise max_fsm_pages so that a lot of pages with free space can be
recorded for that table


The point here is that vacuuming the bytea table can take a long time
due to vacuum_cost_delay, but it won't affect the rest of the system;
regular operation will continue to run at (almost) normal speed.  Having
a big number of free pages ensures that the free space in the table is
not "lost".

Also, you may want to reindex that table once, because with so many
killing vacuums you have probably screwed up the indexes big time (maybe
cluster it once instead of reindexing, because that will compact the
heap as well as the indexes).

Another recommendation is to upgrade to 8.2.4 which is faster and has
a better autovacuum.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.