From: Shaun Thomas <sthomas@optionshouse.com>
To: CS DBA <cs_dba@consistentstate.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL settings for running on an SSD drive
Date: Thu, 20 Jun 2013 15:13:10 -0500
Message-ID: <51C36256.6040502@optionshouse.com> (raw)
In-Reply-To: <51C35E6C.1090705@consistentstate.com>
References: <51C35E6C.1090705@consistentstate.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 06/20/2013 02:56 PM, CS DBA wrote:
> They have a PostgreSQL setup guide from Fusion recommending the
> following settings:
> effective_io_concurrency=0
> bgwriter_lru_maxpages=0
> random_page_cost=0.1
> sequential_page_cost=0.1
Well, since FusionIO drives have a limited write cycle (5PB?), I can
somewhat see why they would recommend turning off the background writer.
We were a bit more conservative in our settings, though:
seq_page_cost = 1.0 # Default
random_page_cost = 1.0 # Reduce to match seq_page_cost
Yep. That's it. Just the one setting. 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. And while they can serve very
aggressive sequential reads, they're not orders of magnitude faster than
spindles in anything but IOPS.
Knowing that, we reduced random page fetches to be the same speed as
sequential page fetches. This has served our heavy OLTP system (and its
FusionIO underpinnings) very well so far.
But like I said, these are pretty conservative. I'd start at 1 and
reduce in 0.2 increments and run tests to see if there's a beneficial
change.
If it helps, here's our system stats, some only relevant during
financial hours:
* A billion queries per day
* Sustained 500+ write queries per second
* Average 7000-ish transactions per second.
* Average 35,000-ish queries per second.
* pg_xlog and pgdata on same FusionIO device
* 2 years, 3 months in operation
* 1.29PB written
* 1.75PB read
The load on our system right now is 3.7 on a 24 CPU box while serving
4100 TPS after active trading hours. The FusionIO drive is basically the
only reason we can do all of that without a lot of excessive contortions.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
--
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: sthomas@optionshouse.com, cs_dba@consistentstate.com
Subject: Re: PostgreSQL settings for running on an SSD drive
In-Reply-To: <51C36256.6040502@optionshouse.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