Message-ID: <4E662912.90609@peak6.com>
Date: Tue, 6 Sep 2011 09:07:14 -0500
From: Shaun Thomas <sthomas@peak6.com>
Reply-To: <sthomas@peak6.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux i686;
	rv:5.0) Gecko/20110624 Thunderbird/5.0
MIME-Version: 1.0
To: Stefan Keller <sfkeller@gmail.com>
CC: <pgsql-performance@postgresql.org>
Subject: Re: Fwd: Summaries on SSD usage?
References: 
 <CAFcOn28h1-uD9Q11rKve2W3BxuUajHX96mXvMcQ=tQsn3pvKgg@mail.gmail.com>
	<CAOR=d=3CmzYAGGgwKRJLdWugX+AanCK7CR9L2zQfnxVP64BeHA@mail.gmail.com>
	<CAFcOn2_P6TQS2exBgsFe9xY296WPe4tHs2_tYaaJWbLg0tu3-A@mail.gmail.com>
	<4E61CDF7.30505@krogh.cc>
	<CAFcOn28FqOXKzgBmt5cmCQ=UA4+2Zwqs800Dnw1k9xvgn0J2tQ@mail.gmail.com>
	<CAFcOn28=4frEHbaOz4y5ptYpMbxjCaZjRLKvZ9AP00BfOSoxow@mail.gmail.com>
In-Reply-To: 
 <CAFcOn28=4frEHbaOz4y5ptYpMbxjCaZjRLKvZ9AP00BfOSoxow@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"; format=flowed
content-transfer-encoding: 7bit

On 09/06/2011 08:45 AM, Stefan Keller wrote:

> Do you think my problem would now be solved with NVRAM PCI card?

That's a tough call. Part of the reason I'm doing the presentation is 
because there are a lot of other high OLTP databases out there which 
have (or will) reached critical mass where cache can't fulfill generic 
database requests anymore.

As an example, we were around 11k database transactions per second on 
250GB of data with 32GB of RAM. The first thing we tried was bumping it 
up to 64GB, and that kinda worked. But what you'll find, is that an 
autovacuum, or a nightly vacuum, will occasionally hit a large table and 
flush all of that handy cached data down the tubes, and then your 
database starts choking trying to keep up with the requests.

Even a large, well equipped RAID can only really offer 2500-ish TPS 
before you start getting into the larger and more expensive SANs, so you 
either have to pre-load your memory with dd or pgfincore, or if your 
random access patterns actually exceed your RAM, you need a bigger disk 
pool or tiered storage. And by tiered storage, I mean tablespaces, with 
critical high-TPS tables located on a PCIe card or a pool of modern 
(capacitor-backed, firmware GC) SSDs.

Your case looks more like you have just a couple big-ass queries/tables 
that occasionally give you trouble. If optimizing the queries, index 
tweaks, and other sundry tools can't help anymore, you may have to start 
dragging ou the bigger guns. But if you can afford it, having some NVRam 
storage around as a top-tier tablespace for critical-need data is 
probably good practice these days.

They're expensive, though. Even the cheap ones start around $5k. Just 
remember you're paying for the performance in this case, and not storage 
capacity. Some vendors have demo hardware they'll let you use to 
determine if it applies to your case, so you might want to contact 
FusionIO, RAMSAN, Virident, or maybe OCZ.

-- 
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com

______________________________________________

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email