Re: Deploying PostgreSQL on CentOS with SSD and Hardware RAID

On May 10, 2013, at 11:35 AM, Lonni J Friedman <netllama(at)gmail(dot)com> wrote:
>>
>> I am not sure that these numbers will end up being anywhere near what works for you, but these are my notes from tuning a 4xMLC SSD RAID-10. I haven't proven that this is optimal, but it was way better than the defaults. We ended up with the following 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
>
> Can you provide more details about your setup, including:
> * What kind of filesystem are you using?
> * Linux distro and/or kernel version
> * hardware (RAM, CPU cores etc)
> * database usage patterns (% writes, growth, etc)

Yes, as long as you promise not to just use my configuration without doing proper testing on your own system, even if it seems similar!

Linux version 2.6.32.225 (gcc version 4.4.6 20110731 (Red Hat 4.4.6-3) (GCC) ) #2 SMP Thu Mar 29 16:43:20 EDT 2012
DMI: Supermicro X8DTN/X8DTN, BIOS 2.1c 10/28/2011
CPU0: Intel(R) Xeon(R) CPU X5670 @ 2.93GHz stepping 02
Total of 24 processors activated (140796.98 BogoMIPS). (2 socket x 2 hyperthread x 6 cores)
96GB ECC RAM

Filesystem is ext4 on LVM on hardware RAID 1+0 Adaptec 5405

Database is very much read heavy, but there is a base load of writes and bursts of much larger writes. I don't have specifics regarding how it breaks down. The database is about 400GB and is growing moderately, maybe a few GB/day. More of the write traffic is re-writes rather than writes.

Hope that helps,
Steven