Re: PostgreSQL settings for running on an SSD drive

On 6/20/13 4:32 PM, Josh Berkus wrote:
> First, cc'ing Greg Smith to see if he can address this with the Fusion
> folks so that they stop giving out a bad guide.

I'm working on a completely replacement of that guide, one that actually
gives out a full set of advice.  Right now I'm between their product
cycles, I'm expecting new hardware again here soon.

The main thing that no one has done a good guide to is how to reduce SSD
flash cell wear in a PostgreSQL database.  If there's anyone out there
who is burning through enough FusionIO cells at your site where a) you
care about wear, and b) you can spare a drive for testing at your site,
please contact me off list if you'd like to talk about that.  I have a
project aiming at increased lifetimes that's specific to FusionIO
hardware, and it could use more testers.  This involves a modified
PostgreSQL though.  It's not for the squeamish or for a production
system.  If I can't crash the kernel on the test server and that's fine,
move along, this will not help you for a while yet.

 >>> They have a PostgreSQL setup guide from Fusion recommending the
 >>> following settings:
 >>> effective_io_concurrency=0
 >>> bgwriter_lru_maxpages=0

I finally tracked down where this all came from.  As a general advisory
on what their current guide addresses, validation of its settings
included things like testing with pgbench.  It's a little known property
of the built-in pgbench test that the best TPS *throughput* numbers come
from turning the background writer off.  That's why their guide suggests
doing that.  The problem with that approach is that the background
writer is intended to improve *latency*, so measuring its impact on
throughput isn't the right approach.

Enabling or disabling the background writer doesn't have a large impact
on flash wear.  That wasn't why turning it off was recommended.  It came
out of the throughput improvement.

Similarly, effective_io_concurrency is untested by pgbench, its queries
aren't complicated enough.  I would consider both of these settings
worse than the defaults, and far from optimal for their hardware.

>>> random_page_cost=0.1
>>> sequential_page_cost=0.1


As Shaun already commented on a bit--I agree with almost everything he
suggested--the situation with random vs. sequential I/O on this hardware
is not as simple as it's made out to be sometimes.  Random I/O certainly
is not the same speed as sequential even on their hardware.  Sequential
I/O is not always 10X as fast as traditional drives.  Using values
closer to 1.0 as he suggested is a lot more sensible to me.

I also don't think random_page_cost = seq_page_cost is the best setting,
even though it did work out for Shaun.  The hardware is fast enough that
you can make a lot of mistakes without really paying for them though,
and any query tuning like that is going to be workload dependent.  It's
impossible to make a single recommendation here.

>> FusionIO drives are fast, but
>> they're not infinitely fast. My tests (and others) show they're about
>> 1/2 the speed of memory, regarding IOPS.

This part needs a disclaimer on it.  Memory speed varies significantly
between servers.  The range just on new servers right now goes from
5GB/s to 40GB/s.  There are a good number of single and dual socket
Intel systems where "1/2 the speed of memory" is about right.  There are
systems where the ratio will be closer to 1:1 or 1:4 though.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com