Message-ID: <51C36256.6040502@optionshouse.com>
Date: Thu, 20 Jun 2013 15:13:10 -0500
From: Shaun Thomas <sthomas@optionshouse.com>
Reply-To: <sthomas@optionshouse.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20130510 Thunderbird/17.0.6
MIME-Version: 1.0
To: CS DBA <cs_dba@consistentstate.com>
CC: <pgsql-performance@postgresql.org>
Subject: Re: PostgreSQL settings for running on an SSD drive
References: <51C35E6C.1090705@consistentstate.com>
In-Reply-To: <51C35E6C.1090705@consistentstate.com>
Content-Type: text/plain; charset="UTF-8"; format=flowed
content-transfer-encoding: 8bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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