On May 10, 2013, at 7:14 AM, Matt Brock <mb@mattbrock.co.uk> wrote:
> Hello.
>
> We're intending to deploy PostgreSQL on Linux with SSD drives which would be in a RAID 1 configuration with Hardware
RAID.
>
> My first question is essentially: are there any issues we need to be aware of when running PostgreSQL 9 on CentOS 6
ona server with SSD drives in a Hardware RAID 1 configuration? Will there be any compatibility problems (seems
unlikely)?Should we consider alternative configurations as being more effective for getting better use out of the
hardware?
>
> The second question is: are there any SSD-specific issues to be aware of when tuning PostgreSQL to make the best use
ofthis hardware and software?
>
A couple of things I noticed with a similar-ish setup:
* Some forms of RAID / LVM break the kernel's automatic disk tuning mechanism. In particular, there is a "rotational"
tunablethat often does not get set right. You might end up tweaking read ahead and friends as well.
http://www.mjmwired.net/kernel/Documentation/block/queue-sysfs.txt#112
* The default Postgres configuration is awful for a SSD backed database. You really need to futz with checkpoints to
getacceptable throughput.
The "PostgreSQL 9.0 High Performance" book is fantastic and is what I used to great success.
* The default Linux virtual memory configuration is awful for this configuration. Briefly, it will accept a ton of
incomingdata, and then go through an awful stall as soon as it calls fsync() to write all that data to disk. We had
multi-seconddelays all the way through to the application because of this. We had to change the zone_reclaim_mode and
thedirty buffer limits.
http://www.postgresql.org/message-id/500616CB.3070408@2ndQuadrant.com
I am not sure that these numbers will end up being anywhere near what works for you, but these are my notes from tuning
a4xMLC SSD RAID-10. I haven't proven that this is optimal, but it was way better than the defaults. We ended up with
thefollowing list of changes:
* Change IO scheduler to "noop"
* Mount DB volume with nobarrier, noatime
* Turn blockdev readahead to 16MiB
* Turn sdb's "rotational" tuneable to 0
PostgreSQL configuration changes:
synchronous_commit = off
effective_io_concurrency = 4
checkpoint_segments = 1024
checkpoint_timeout = 10min
checkpoint_warning = 8min
shared_buffers = 32gb
temp_buffers = 128mb
work_mem = 512mb
maintenance_work_mem = 1gb
Linux sysctls:
vm.swappiness = 0
vm.zone_reclaim_mode = 0
vm.dirty_bytes = 134217728
vm.dirty_background_bytes = 1048576
Hope that helps,
Steven
