From: Greg Smith <greg@2ndQuadrant.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL settings for running on an SSD drive
Date: Wed, 17 Jul 2013 22:04:33 -0400
Message-ID: <51E74D31.2010601@2ndQuadrant.com> (raw)
In-Reply-To: <51C366CE.10206@agliodbs.com>
References: <51C35E6C.1090705@consistentstate.com>
<51C36256.6040502@optionshouse.com>
<WM!b9e5afbe8d527efb5c0f49ce7515ef211125a953db249e1e3ff58e04ad0791f3381fd39085635a2bb537ff66f740d4f2!@asav-1.01.com>
<51C366CE.10206@agliodbs.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndQuadrant.com, josh@agliodbs.com
Subject: Re: PostgreSQL settings for running on an SSD drive
In-Reply-To: <51E74D31.2010601@2ndQuadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox