Deploying PostgreSQL on CentOS with SSD and Hardware RAID

On Fri, May 10, 2013 at 9: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 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?

The specific hardware we're planning to use is the HP ProLiant DL360 Gen8 server with P420i RAID controller, and two MLC SSDs in RAID 1 for the OS, and two SLC SSDs in RAID 1 for the database - but I guess it isn't necessary to have used this specific hardware setup in order to have experience with these general issues. The P420i controller appears to be compatible with recent versions of CentOS, so drivers should not be a concern (hopefully).

The specific drive models play a huge impact on SSD performance. In
fact, the point you are using SLC drives suggests you might be using
antiquated (by SSD standards) hardware. All the latest action is on
MLC now (see here:
http://www.intel.com/content/www/us/en/solid-state-drives/solid-state-drives-dc-s3700-series.html).

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

After googling this for a while, it seems that High Endurance MLC is only starting to rival SLC for endurance and write performance in the very latest, cutting-edge hardware. In general, though, it seems it would be fair to say that SLCs are still a better bet for databases than MLC?

The number and capacity of drives is small in this instance, and the price difference between the two for HP SSDs isn't very wide, so cost isn't really an issue. We just want to use whichever is better for the database.

On 10 May 2013, at 15:20, Merlin Moncure <mmoncure@gmail.com> wrote:

On Fri, May 10, 2013 at 9: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 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?

The specific hardware we're planning to use is the HP ProLiant DL360 Gen8 server with P420i RAID controller, and two MLC SSDs in RAID 1 for the OS, and two SLC SSDs in RAID 1 for the database - but I guess it isn't necessary to have used this specific hardware setup in order to have experience with these general issues. The P420i controller appears to be compatible with recent versions of CentOS, so drivers should not be a concern (hopefully).

The specific drive models play a huge impact on SSD performance. In
fact, the point you are using SLC drives suggests you might be using
antiquated (by SSD standards) hardware. All the latest action is on
MLC now (see here:
http://www.intel.com/content/www/us/en/solid-state-drives/solid-state-drives-dc-s3700-series.html).

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 5/10/2013 9:19 AM, Matt Brock wrote:

After googling this for a while, it seems that High Endurance MLC is only starting to rival SLC for endurance and write performance in the very latest, cutting-edge hardware. In general, though, it seems it would be fair to say that SLCs are still a better bet for databases than MLC?

I've never looked at SLC drives in the past few years and don't know
anyone who uses them these days.

The number and capacity of drives is small in this instance, and the price difference between the two for HP SSDs isn't very wide, so cost isn't really an issue. We just want to use whichever is better for the database.

Could you post some specific drive models please ? HP probably doesn't
make the drives, and it really helps to know what devices you're using
since they are not nearly as generic in behavior and features as
magnetic drives.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Not sure of your space requirements, but I'd think a RAID 10 of 8x or more
Samsung 840 Pro 256/512 GB would be the best value. Using a simple mirror
won't get you the reliability that you want since heavy writing will burn
the drives out over time, and if you're writing the exact same content to
both drives, they could likely fail at the same time. Regardless of the
underlying hardware you should still follow best practices for provisioning
disks, and raid 10 is the way to go. I don't know what your budget is
though. Anyway, mirrored SSD will probably work fine, but I'd avoid using
just two drives for the reasons above. I'd suggest at least testing RAID 5
or something else to spread the load around. Personally, I think the ideal
configuration would be a RAID 10 of at least 8 disks plus 1 hot spare. The
Samsung 840 Pro 256 GB are frequently $200 on sale at Newegg. YMMV but
they are amazing drives.

On Fri, May 10, 2013 at 11:25 AM, David Boreham <david_list@boreham.org>wrote:

On Fri, May 10, 2013 at 10:19 AM, Matt Brock <mb@mattbrock.co.uk> wrote:

After googling this for a while, it seems that High Endurance MLC is only starting to rival SLC for endurance and write performance in the very latest, cutting-edge hardware. In general, though, it seems it would be fair to say that SLCs are still a better bet for databases than MLC?

The number and capacity of drives is small in this instance, and the price difference between the two for HP SSDs isn't very wide, so cost isn't really an issue. We just want to use whichever is better for the database.

Well, it's more complicated than that. While SLC drives were indeed
inherently faster and had longer lifespans, all flash drives basically
have the requirement of having to carefully manages writes in order to
get good performance. Unfortunately, this means that for database
use the drives must have some type of non-volatile cache and/or
sufficient back up juice in a capacitor to spin out pending write in
the event of sudden loss of power. Many drives, including (famously)
the so-called Intel X25-E "enterprise" lines, did not do this and
where therefore unsuitable for database use.

As it turns out the list of flash drives are suitable for database use
is surprisingly small. The s3700 I noted upthread seems to be
specifically built with databases in mind and is likely the best
choice for new deployments. The older Intel 320 is also a good
choice. I think that's pretty much it until you get into expensive
pci-e based gear. There might be some non-intel drives out there
that are suitable but be very very careful and triple verify that the
drive has on-board capacitor and has gotten real traction in
enterprise database usage.

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, May 10, 2013 at 11:11 AM, Evan D. Hoffman
<evandhoffman@gmail.com> wrote:

Not sure of your space requirements, but I'd think a RAID 10 of 8x or more
Samsung 840 Pro 256/512 GB would be the best value. Using a simple mirror
won't get you the reliability that you want since heavy writing will burn
the drives out over time, and if you're writing the exact same content to
both drives, they could likely fail at the same time. Regardless of the
underlying hardware you should still follow best practices for provisioning
disks, and raid 10 is the way to go. I don't know what your budget is
though. Anyway, mirrored SSD will probably work fine, but I'd avoid using
just two drives for the reasons above. I'd suggest at least testing RAID 5
or something else to spread the load around. Personally, I think the ideal
configuration would be a RAID 10 of at least 8 disks plus 1 hot spare. The
Samsung 840 Pro 256 GB are frequently $200 on sale at Newegg. YMMV but they
are amazing drives.

Samsung 840 has no power loss protection and is therefore useless for
database use IMO unless you don't care about data safety and/or are
implementing redundancy via some other method (say, by synchronous
replication).

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I'd expect to use a RAID controller with either BBU or NVRAM cache to
handle that, and that the server itself would be on UPS for a production
DB. That said, a standby replica DB on conventional disk is definitely a
good idea in any case.

On Fri, May 10, 2013 at 12:25 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Fri, May 10, 2013 at 11:34 AM, Evan D. Hoffman
<evandhoffman@gmail.com> wrote:

I'd expect to use a RAID controller with either BBU or NVRAM cache to handle
that, and that the server itself would be on UPS for a production DB. That
said, a standby replica DB on conventional disk is definitely a good idea in
any case.

Sadly, NVRAM cache doesn't help (unless the raid controller is
managing drive writes down to the flash level and no such products
exist that I am aware of). The problem is that provide guarantees the
raid controller still needs to be able to tell the device to flush
down to physical storage. While flash drives can be configured to do
that (basically write-through mode), it's pretty silly to do so as it
will ruin performance and quickly destroy the drive.

Trusting UPS is up to you, but if your ups does, someone knocks the
power cable, etc you have data loss. With on-drive capacitor you only
get data loss via physical damage or corruption on the drive.

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 5/10/2013 10:21 AM, Merlin Moncure wrote:

As it turns out the list of flash drives are suitable for database use
is surprisingly small. The s3700 I noted upthread seems to be
specifically built with databases in mind and is likely the best
choice for new deployments. The older Intel 320 is also a good choice.
I think that's pretty much it until you get into expensive pci-e based
gear.

This may have been a typo : did you mean Intel 710 series rather than 320 ?

While the 320 has the supercap, it isn't specified for high write endurance.
Definitely usable for a database, and a better choice than most of the
alternatives, but I'd have listed the 710 ahead of the 320.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, May 10, 2013 at 12:03 PM, David Boreham <david_list@boreham.org> wrote:

On 5/10/2013 10:21 AM, Merlin Moncure wrote:

As it turns out the list of flash drives are suitable for database use is
surprisingly small. The s3700 I noted upthread seems to be specifically
built with databases in mind and is likely the best choice for new
deployments. The older Intel 320 is also a good choice. I think that's
pretty much it until you get into expensive pci-e based gear.

This may have been a typo : did you mean Intel 710 series rather than 320 ?

While the 320 has the supercap, it isn't specified for high write endurance.
Definitely usable for a database, and a better choice than most of the
alternatives, but I'd have listed the 710 ahead of the 320.

It wasn't a typo. The 320 though is perfectly fine although it will
wear out faster -- so it fills a niche for low write intensity
applications. I find the s3700 to be superior to the 710 in just
about every way (although you're right -- it is suitable for database
use).

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, May 10, 2013 at 10:20 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Fri, May 10, 2013 at 12:03 PM, David Boreham <david_list@boreham.org> wrote:

On 5/10/2013 10:21 AM, Merlin Moncure wrote:

As it turns out the list of flash drives are suitable for database use is
surprisingly small. The s3700 I noted upthread seems to be specifically
built with databases in mind and is likely the best choice for new
deployments. The older Intel 320 is also a good choice. I think that's
pretty much it until you get into expensive pci-e based gear.

This may have been a typo : did you mean Intel 710 series rather than 320 ?

While the 320 has the supercap, it isn't specified for high write endurance.
Definitely usable for a database, and a better choice than most of the
alternatives, but I'd have listed the 710 ahead of the 320.

It wasn't a typo. The 320 though is perfectly fine although it will
wear out faster -- so it fills a niche for low write intensity
applications. I find the s3700 to be superior to the 710 in just
about every way (although you're right -- it is suitable for database
use).

There's also the 520 series, which has better performance than the 320
series (which is EOL now).

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 5/10/2013 11:20 AM, Merlin Moncure wrote:

I find the s3700 to be superior to the 710 in just about every way
(although you're right -- it is suitable for database use). merlin

The s3700 series replaces the 710 so it should be superior :)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 5/10/2013 11:23 AM, Lonni J Friedman wrote:

There's also the 520 series, which has better performance than the 320
series (which is EOL now).

I wouldn't use the 520 series for production database storage -- it has
the Sandforce controller and apparently no power failure protection.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Steve Clark escribió:

Well we have dual redundant power supplies on separate UPS so could something go wrong yes, but a tornado could
come along and destroy the building also.

.. hence your standby server across the country?

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 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.
/messages/by-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 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

Hope that helps,
Steven

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, May 10, 2013 at 11:23 AM, Steven Schlansker <steven@likeness.com> wrote:

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 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.
/messages/by-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 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)

thanks

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, May 10, 2013 at 1:23 PM, Steven Schlansker <steven@likeness.com> wrote:

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 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.
/messages/by-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 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

On May 10, 2013, at 11:38 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

PostgreSQL configuration changes:
synchronous_commit = off

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.

Yes, that is a choice we consciously made. If our DB server crashes losing the last few ms worth of transactions is an acceptable loss to us. But that may not be OK for everyone :-)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On May 10, 2013, at 11:35 AM, Lonni J Friedman <netllama@gmail.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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general