Message-ID: <49271E95.5080200@fuzzy.cz>
Date: Fri, 21 Nov 2008 21:48:21 +0100
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Thunderbird 2.0.0.9 (X11/20080213)
MIME-Version: 1.0
CC: pgsql-performance@postgresql.org
Subject: Re: Hash join on int takes 8..114 seconds
References: <gg1sv3$27fr$1@news.hub.org> <49253FE1.3070706@archonet.com>
	<0205611B528C4C2CBA99AFC64E57D6E8@andrusnotebook>
	<op.ukw669uvcigqcu@soyouz>
	<75A918DFCF25470FAF96B3FE3D25A38B@andrusnotebook>
	<4926E2B2.9050202@archonet.com>
	<8A78204AA6D44C04B053E6412B244E13@andrusnotebook>
	<op.ukzdioivcigqcu@soyouz>
	<8D7F64CF7F614E13AC554D6CF86F39B2@andrusnotebook>
	<20081121191646.GH5210@alvh.no-ip.org>
	<A44943E272DF48CC9BC306C513387020@andrusnotebook>
In-Reply-To: <A44943E272DF48CC9BC306C513387020@andrusnotebook>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

> Thank you.
> My 8.1.4 postgresql.conf does not contain such option. So 
> vacuum_cost_delay is off probably.
> Since doc does not recommend any value, I planned to use 2000
> 
> Will value of 30 allow other clients to work when VACUUM FULL is running ?

No, as someone already noted the VACUUM FULL is blocking anyway (and 
does not use this value at all).

> Uncommented relevant values in postgresql.conf file are:
> 
> shared_buffers = 15000
> work_mem = 512

I'd consider increasing this value a little - 0.5 MB seems too low to me 
(but not necessarily).

> maintenance_work_mem = 131072
> fsync = on
> effective_cache_size= 70000

Well, your server has 2GB of RAM and usually it's recommended to set 
this value to about 60-70% of your RAM, so using 540MB (25%) seems quite 
low. Anyway this is just a hint to PostgreSQL, it does not increase 
memory consumption or so - it's just an estimate of how much data are 
cached by kernel.

Anyway, I don't expect these values have significant effect in case of 
the issue solved in this thread.

regards
Tomas