Re: PostgreSQL settings for running on an SSD drive

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