Message-ID: <4FA2829D.5030409@optionshouse.com>
Date: Thu, 3 May 2012 08:05:33 -0500
From: Shaun Thomas <sthomas@optionshouse.com>
Reply-To: <sthomas@optionshouse.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux i686;
	rv:11.0) Gecko/20120329 Thunderbird/11.0.1
MIME-Version: 1.0
To: Robert Klemme <shortcutter@googlemail.com>
CC: Jan Nielsen <jan.sture.nielsen@gmail.com>, <sthomas@peak6.com>,
	<pgsql-performance@postgresql.org>,
	John Lister <john.lister@kickstone.co.uk>
Subject: Re: Configuration Recommendations
References: 
 <CANxH4hGW+RYXsB8BdAY9hoGzje26EapX1H6H-jrNZAMQKZ+hMQ@mail.gmail.com>
	<4F96FFE1.2030809@peak6.com> <4F97ABBB.1000708@kickstone.co.uk>
	<4F986CAC.4060904@peak6.com>
	<CANxH4hE2WTvLJeVXz-o4npvOdoHvh1GLQjt=eYjErSQKdo7anQ@mail.gmail.com>
	<CAM9pMnPkgq8TkFYqa-jZczLW=e4FwqH3EJPMNGFTLtna93KHyw@mail.gmail.com>
In-Reply-To: 
 <CAM9pMnPkgq8TkFYqa-jZczLW=e4FwqH3EJPMNGFTLtna93KHyw@mail.gmail.com>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit

On 05/03/2012 02:28 AM, Robert Klemme wrote:

> Maybe this also has some additional input:
> http://www.fccps.cz/download/adv/frr/hdd/hdd.html

Be careful with that link. His recommendations for dirty_ratio and 
dirty_background_ratio would be *very bad* in a database setting. Note 
this from the actual article:

"I am aware that my tuning values are probably quite insane in some 
respects, may cause occasional longer periods of high read latency, may 
cause other problems. Still I guess the exercise was worth it - the 
tests did show some interesting results."

That's putting it lightly. With some of those settings in a very large 
memory server, you could see *minutes* of synchronous IO waits if 
dirty_ratio gets saturated. I like to follow this:

http://www.westnet.com/~gsmith/content/linux-pdflush.htm

As a note, there are actually new tunables for some of this: 
dirty_bytes, and dirty_background_bytes. With them, you can match them 
better to the actual size of your controller write cache so you can 
avoid page flush storms causing IO stalls. It's unfortunate, but 
database servers are not the target platform for most of the kernel 
devs, and really have a much different profile from everyday systems. We 
need to address latency more than throughput, though both are important.

I think Greg mentioned something that setting these too low can cause 
VACUUM to lag, but I'm willing to take that tradeoff. We've had IO 
stalls in the past when our background ratio was too high, and it wasn't 
pretty. Ironically, we never had a problem until we tripled our system 
memory, and suddenly our drive controllers were frequently getting 
choked to death.

Mr. Nielsen's setup actually looks pretty darn good. It's my personal 
opinion he might run into some IO waits if he plans to use this for 
heavy OLTP, thanks to having only 8 spindles in his RAID1+0, but he may 
eventually grow into a SAN. That's fine. It's a good starting point.

-- 
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email