Message-ID: <51E85320.8080708@optionshouse.com>
Date: Thu, 18 Jul 2013 15:42:08 -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/20130623 Thunderbird/17.0.7
MIME-Version: 1.0
To: Greg Smith <greg@2ndQuadrant.com>
CC: Josh Berkus <josh@agliodbs.com>, <pgsql-performance@postgresql.org>
Subject: Re: PostgreSQL settings for running on an SSD drive
References: <51C35E6C.1090705@consistentstate.com>
 <51C36256.6040502@optionshouse.com>
 <WM!b9e5afbe8d527efb5c0f49ce7515ef211125a953db249e1e3ff58e04ad0791f3381fd39085635a2bb537ff66f740d4f2!@asav-1.01.com>
 <51C366CE.10206@agliodbs.com> <51E74D31.2010601@2ndQuadrant.com>
In-Reply-To: <51E74D31.2010601@2ndQuadrant.com>
Content-Type: text/plain; charset="UTF-8"; format=flowed
content-transfer-encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 07/17/2013 09:04 PM, Greg Smith wrote:

> 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.

Me too. It's interesting that they seem to be focusing more on using the 
cards as a caching layer instead of a directly readable device. I still 
need to test that use case.

> This involves a modified PostgreSQL though.  It's not for the
> squeamish or for a production system.

I'd volunteer, but we're actually using EDB. Unless you can convince EDB 
to supply similar binaries as you have, I can't get equivalent tests. :(

> 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.

Very, very true. I actually prefer using different values, and before 
9.1, we had random at 1.5, and sequential at 1.0. Some of our query 
plans were being adversely affected, and didn't go back to normal until 
I reduced random cost to 1.0. I can't explain why that would happen, but 
it's not too surprising given that we jumped from 8.2 to 9.1.

Since we're mainly focused on stability right now, getting the old 
performance back was the main concern. I haven't revisited the setting 
since that initial upgrade and correction, so it's hard to know what the 
"right" setting really is. Like you said, there is a lot of room for 
tuning based on system usage.

> 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.

Doh, yeah. It also depends on the FusionIO generation and tier you're 
working with. Some of their newer/bigger cards with more controller 
chips can (purportedly) push upwards of 6GB/s, which is a tad faster 
than the 800MB/s (measured) of our ancient gen-1 cards.

Too many variables. -_-

-- 
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