Re: What setup would you choose for postgresql 9.2 installation?

On 04/03/13 13:52, Niels Kristian Schjødt wrote:
> LSI MegaRAID SAS 9260-4i with four Intel SSDSC2CW240A3K5 SSDs OR four Hitachi Ultrastar 15K600 SAS drives?
>
> My app is pretty write heavy and I have a lot of concurrent connections 300 - (though considering adding pgpool2 in front to increase throughput).
>

If you can afford it, there's no question in my mind that SSDs are the
way to go. They can be 1000 times faster for random reads.

May I suggest that you do some experiments though - perhaps with just
one disk of each type - you can get some pretty good illustrative tests
with ordinary SATA drives in an ordinary laptop/desktop (but not a USB
adapter). I did this originally when evaluating the (then new) Intel X25
SSD.

The other things to note are:

* The filesystem matters. For the important thing, fdatasync(), ext2 is
2x as fast as ext4, which itself is much faster than ext3. BUT ext2's
fsck is horrid, so we chose ext4.

* Will you enable the disk (or RAID controller) write cache?

* Have you enough RAM for your key tables (and indexes) to fit in
memory? If not, 64GB of RAM is cheap these days.

* In some applications, you can get a speed boost by turning
synchronous_commit off - this would mean that in a database crash, the
last few seconds are potentially lost, even through they application
thinks they were committed. You may find this an acceptable tradeoff.

* Postgres doesn't always write straight to the tables, but uses the WAL
(write-ahead-log). So the benefit of SSD performance for "random writes"
is less relevant than for "random reads".

Lastly, don't overdo the concurrent connections. You may end up with
less thoughput than if you let postgres devote more resources to each
request and let it finish faster.

Hope that helps,

Richard