Message-ID: <4E671521.3090406@gmail.com>
Date: Wed, 07 Sep 2011 09:54:25 +0300
From: Vitalii Tymchyshyn <tivv00@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.18) Gecko/20110617 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
To: Richard Shaw <richard@aggress.net>
CC: Alan Hodgson <ahodgson@simkin.ca>, pgsql-performance@postgresql.org
Subject: Re: Rather large LA
References: <F757E48F-2B4F-496D-951B-B04ED98F8FD6@aggress.net>
	<4E64D0FE.6020704@squeakycode.net>
	<97BCA6CA-A1DB-43C7-B710-C2836B656FD6@aggress.net>
	<201109051305.07010@hal.medialogik.com>
	<9BFA2771-CA9B-4EEF-B671-78FF67F749C6@aggress.net>
In-Reply-To: <9BFA2771-CA9B-4EEF-B671-78FF67F749C6@aggress.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hello.

As it turned out to be iowait, I'd recommend to try to load at least 
some hot relations into FS cache with dd on startup. With a lot of RAM 
on FreeBSD I even sometimes use this for long queries that require a lot 
of index scans.
This converts random IO into sequential IO that is much much faster.
You can try it even while your DB starting - if it works you will see 
IOwait drop and user time raise.
What I do on FreeBSD (as I don't have enough RAM to load all the DB into 
RAM) is:
1) ktrace on backend process[es]. Linux seems to have similar tool
2) Find files that take a lot of long reads
3) dd this files to /dev/null

In this way you can find hot files. As soon as you have them (or if you 
can afford to load everything), you can put dd into startup scripts. Or 
I can imagine an automatic script that will do such things for some time 
after startup.

Best regards, Vitalii Tymchyshyn