Re: PostgreSQL settings for running on an SSD drive

Folks,

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.

On 06/20/2013 01:13 PM, Shaun Thomas wrote:
> 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.

Did you compare setting RPC to 1.0 vs. setting it to 1.1, or something
else just slightly higher than SPC?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com