On Fri, May 10, 2013 at 1:23 PM, Steven Schlansker <ste...@likeness.com> wrote: > > On May 10, 2013, at 7:14 AM, Matt Brock <m...@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 on a 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 of this 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" tunable that 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 get acceptable 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 incoming data, and then go > through an awful stall as soon as it calls fsync() to write all that data to > disk. We had multi-second delays all the way through to the application > because of this. We had to change the zone_reclaim_mode and the dirty buffer > limits. > http://www.postgresql.org/message-id/500616cb.3070...@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 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
that's good info, but it should be noted that synchronous_commit trades a risk of some data loss (but not nearly as much risk as volatile storage) for a big increase in commit performance. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
