Re: Configuration Recommendations

From: Shaun Thomas <sthomas(at)optionshouse(dot)com>
To: Robert Klemme <shortcutter(at)googlemail(dot)com>
Cc: Jan Nielsen <jan(dot)sture(dot)nielsen(at)gmail(dot)com>, <sthomas(at)peak6(dot)com>, <pgsql-performance(at)postgresql(dot)org>, John Lister <john(dot)lister(at)kickstone(dot)co(dot)uk>
Subject: Re: Configuration Recommendations
Date: 2012-05-03 13:05:33
Message-ID: 4FA2829D.5030409@optionshouse.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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(at)optionshouse(dot)com

______________________________________________

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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jan Nielsen 2012-05-03 13:14:25 Re: Configuration Recommendations
Previous Message Jonathan 2012-05-03 13:05:29 Re: Query got slow from 9.0 to 9.1 upgrade