SSD Drives

have you seen this?

http://it-blog.5amsolutions.com/2010/08/performance-of-postgresql-ssd-vs.html

Brent Wood

Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529 | 301 Evans Bay Parade, Greta Point, Wellington | www.niwa.co.nz<http://www.niwa.co.nz&gt;
[NIWA]<http://www.niwa.co.nz&gt;
________________________________________
From: pgsql-general-owner@postgresql.org [pgsql-general-owner@postgresql.org] on behalf of Bret Stern [bret_stern@machinemanagement.com]
Sent: Thursday, April 3, 2014 8:37 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] SSD Drives

Any opinions/comments on using SSD drives with postgresql?

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

On 04/02/2014 02:37 PM, Bret Stern wrote:

Any opinions/comments on using SSD drives with postgresql?

Using SSDs with PostgreSQL is fine, provided they have an onboard
capacitor to ensure data integrity. The main concern with SSD drives, is
that they essentially lie about their sync status. There is an inherent
race-condition between the time data reaches the drive, and how long it
takes for the write balancing and NVRAM commit overhead.

Most common drives only have a volatile RAM chip that acts as a buffer
space while writes are synced to the physical drive. Without a capacitor
backing, the state of this buffer is erased on power loss, resulting in
a corrupt database.

There are upcoming technologies which may solve this (see ReRAM) but for
now, it's a requirement for any sane system.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

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

On 04/02/2014 02:50 PM, Brent Wood wrote:

http://it-blog.5amsolutions.com/2010/08/performance-of-postgresql-ssd-vs.html

While interesting, these results are extremely out of date compared to
current drives. Current chips and firmware regularly put out 2-10 times
better performance than even the best graphs on this page, depending on
what you buy.

We moved all of our performance-critical servers to NVRAM-based storage
years ago. For us, it was well worth the added expense.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

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

Care to share the SSD hardware you're using?

I've used none to date, and have some critical data I would like
to put on a development server to test with.

Regards,

Bret Stern

On Wed, 2014-04-02 at 15:31 -0500, Shaun Thomas wrote:

On 04/02/2014 02:50 PM, Brent Wood wrote:

http://it-blog.5amsolutions.com/2010/08/performance-of-postgresql-ssd-vs.html

While interesting, these results are extremely out of date compared to
current drives. Current chips and firmware regularly put out 2-10 times
better performance than even the best graphs on this page, depending on
what you buy.

We moved all of our performance-critical servers to NVRAM-based storage
years ago. For us, it was well worth the added expense.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

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

On 04/02/2014 04:55 PM, Bret Stern wrote:

Care to share the SSD hardware you're using?

We use these:

http://www.fusionio.com/products/iodrive2/

The older versions of these cards can read faster than a RAID-10 of
80x15k RPM SAS drives, based on our tests from a couple yeas ago. Writes
aren't *quite* as fast, but still much better than even a large RAID array.

They ain't cheap, though. You can expect to pay around $15k USD per TB,
I believe. There are other similar products from other vendors which may
have different cost/performance ratios, but I can only vouch for stuff
I've personally tested.

Our adventure with these cards was a presentation at Postgres Open in
2011. Slides are here:

https://wiki.postgresql.org/images/c/c5/Nvram_fun_profit.pdf

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

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

On Wed, Apr 2, 2014 at 4:09 PM, Shaun Thomas <sthomas@optionshouse.com> wrote:

On 04/02/2014 04:55 PM, Bret Stern wrote:

Care to share the SSD hardware you're using?

We use these:

http://www.fusionio.com/products/iodrive2/

The older versions of these cards can read faster than a RAID-10 of 80x15k
RPM SAS drives, based on our tests from a couple yeas ago. Writes aren't
*quite* as fast, but still much better than even a large RAID array.

They ain't cheap, though. You can expect to pay around $15k USD per TB, I
believe. There are other similar products from other vendors which may have
different cost/performance ratios, but I can only vouch for stuff I've
personally tested.

Our adventure with these cards was a presentation at Postgres Open in 2011.
Slides are here:

https://wiki.postgresql.org/images/c/c5/Nvram_fun_profit.pdf

Where I work we use the MLC based FusionIO cards and they are quite
fast. It's actually hard to push them to their max with only 24 or 32
cores in a fast machine. My favorite thing about them is their
fantastic support.

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

We used 4x OCZ Deneva 2 in a RAID configuration. Worked well for us for
over 2 years with no hardware issues. We switched to SSD because we had
a very write-intensive application (30 million rows/day) that spinning
disks just couldn't keep up with.

On 4/2/2014 6:09 PM, Shaun Thomas wrote:

On 04/02/2014 04:55 PM, Bret Stern wrote:

Care to share the SSD hardware you're using?

We use these:

http://www.fusionio.com/products/iodrive2/

The older versions of these cards can read faster than a RAID-10 of
80x15k RPM SAS drives, based on our tests from a couple yeas ago. Writes
aren't *quite* as fast, but still much better than even a large RAID array.

They ain't cheap, though. You can expect to pay around $15k USD per TB,
I believe. There are other similar products from other vendors which may
have different cost/performance ratios, but I can only vouch for stuff
I've personally tested.

Our adventure with these cards was a presentation at Postgres Open in
2011. Slides are here:

https://wiki.postgresql.org/images/c/c5/Nvram_fun_profit.pdf

--
Guy Rouillier

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

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

While I have two friends who work at FusionIO, and have great confidence
in their products, we like to deploy more conventional SATA SSDs at
present in our servers. We have been running various versions of Intel's
enterprise and data center SSDs in production for several years now and
couldn't be happier with their performance. The oldest in service at
present are 710 series that have been subjected to a ~500wtps PG load
7*24 for the past 28 months. They still show zero wearout indication in
the SMART stats.

As others have mentioned, power-fail protection (supercap) is the thing
to look for, and also some sort of concrete specification for drive
write endurance unless you have made a deliberate decision to trade off
endurance vs. cost in the context of your deployment.

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

On Wed, Apr 2, 2014 at 12:37 PM, Bret Stern <
bret_stern@machinemanagement.com> wrote:

Any opinions/comments on using SSD drives with postgresql?

Related, anyone have any thoughts on using postgresql on Amazon's EC2 SSDs?
Been looking at
http://aws.amazon.com/about-aws/whats-new/2013/12/19/announcing-the-next-generation-of-amazon-ec2-high-i/o-instance

On 4/3/2014 9:26 AM, Joe Van Dyk wrote:

Related, anyone have any thoughts on using postgresql on Amazon's EC2
SSDs? Been looking at
http://aws.amazon.com/about-aws/whats-new/2013/12/19/announcing-the-next-generation-of-amazon-ec2-high-i/o-instance

if your data isn't very important, by all means, keep it on someone
elses virtualized infrastructure with no performance or reliability
guarantees.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

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

On Apr 3, 2014, at 12:47 PM, John R Pierce <pierce@hogranch.com> wrote:

On 4/3/2014 9:26 AM, Joe Van Dyk wrote:

Related, anyone have any thoughts on using postgresql on Amazon's EC2 SSDs? Been looking at http://aws.amazon.com/about-aws/whats-new/2013/12/19/announcing-the-next-generation-of-amazon-ec2-high-i/o-instance

if your data isn't very important, by all means, keep it on someone elses virtualized infrastructure with no performance or reliability guarantees.

Well that’s not quite fair. AWS guarantees performance for those instances (http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/i2-instances.html#i2-instances-diskperf). They also guarantee their instances will fail sooner or later, with or without warning (at which point you will loose all your data unless you’ve been putting copies onto a different system).

On Wed, Apr 2, 2014 at 2:37 PM, Bret Stern
<bret_stern@machinemanagement.com> wrote:

Any opinions/comments on using SSD drives with postgresql?

Here's a single S3700 smoking an array of 16 15k drives (poster didn't
realize that; was to focused on synthetic numbers):
http://dba.stackexchange.com/questions/45224/postgres-write-performance-on-intel-s3700-ssd

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 Thu, Apr 3, 2014 at 12:13 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Wed, Apr 2, 2014 at 2:37 PM, Bret Stern
<bret_stern@machinemanagement.com> wrote:

Any opinions/comments on using SSD drives with postgresql?

Here's a single S3700 smoking an array of 16 15k drives (poster didn't
realize that; was to focused on synthetic numbers):
http://dba.stackexchange.com/questions/45224/postgres-write-performance-on-intel-s3700-ssd

I just ran a quick test earlier this week on an old Dell 2970 (2
Opteron 2387, 16GB RAM) comparing a 6-disk RAID10 with 10k 147GB SAS
disks to a 2-disk RAID1 with 480GB Intel S3500 SSDs and found the SSDs
are about 4-6x faster using pgbench and a scaling factor of 1100. Some
sort of MegaRAID controller according to lspci and has BBU. TPS
numbers below are approximate.

RAID10 disk array:
8 clients: 350 tps
16 clients: 530 tps
32 clients: 800 tps

RAID1 SSD array:
8 clients: 2100 tps
16 clients: 2500 tps
32 clients: 3100 tps

So yeah, even the slower, cheaper S3500 SSDs are way fast. If your
write workload isn't too high, the S3500 can work well. We'll see how
the SMART drive lifetime numbers do once we get into production, but
right now we estimate they should last at least 5 years and from what
we've seen it seems that SSDs seem to wear much better than expected.
If not, we'll pony up and go for the S3700 or perhaps move the xlog
back on to spinning disks.

-Dave

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

Hi David,

Does the RAID 1 array give any performance benefits over a single drive? I'd guess that writes may be slower, reads may be faster (if balanced) but data security is improved.

Brent Wood

Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529 | 301 Evans Bay Parade, Greta Point, Wellington | www.niwa.co.nz<http://www.niwa.co.nz&gt;
[NIWA]<http://www.niwa.co.nz&gt;
________________________________________
From: pgsql-general-owner@postgresql.org [pgsql-general-owner@postgresql.org] on behalf of David Rees [drees76@gmail.com]
Sent: Friday, April 4, 2014 8:32 AM
To: Merlin Moncure
Cc: bret_stern@machinemanagement.com; PostgreSQL General
Subject: Re: [GENERAL] SSD Drives

On Thu, Apr 3, 2014 at 12:13 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Wed, Apr 2, 2014 at 2:37 PM, Bret Stern
<bret_stern@machinemanagement.com> wrote:

Any opinions/comments on using SSD drives with postgresql?

Here's a single S3700 smoking an array of 16 15k drives (poster didn't
realize that; was to focused on synthetic numbers):
http://dba.stackexchange.com/questions/45224/postgres-write-performance-on-intel-s3700-ssd

I just ran a quick test earlier this week on an old Dell 2970 (2
Opteron 2387, 16GB RAM) comparing a 6-disk RAID10 with 10k 147GB SAS
disks to a 2-disk RAID1 with 480GB Intel S3500 SSDs and found the SSDs
are about 4-6x faster using pgbench and a scaling factor of 1100. Some
sort of MegaRAID controller according to lspci and has BBU. TPS
numbers below are approximate.

RAID10 disk array:
8 clients: 350 tps
16 clients: 530 tps
32 clients: 800 tps

RAID1 SSD array:
8 clients: 2100 tps
16 clients: 2500 tps
32 clients: 3100 tps

So yeah, even the slower, cheaper S3500 SSDs are way fast. If your
write workload isn't too high, the S3500 can work well. We'll see how
the SMART drive lifetime numbers do once we get into production, but
right now we estimate they should last at least 5 years and from what
we've seen it seems that SSDs seem to wear much better than expected.
If not, we'll pony up and go for the S3700 or perhaps move the xlog
back on to spinning disks.

-Dave

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

On Thu, Apr 3, 2014 at 12:44 PM, Brent Wood <Brent.Wood@niwa.co.nz> wrote:

Does the RAID 1 array give any performance benefits over a single drive? I'd guess
that writes may be slower, reads may be faster (if balanced) but data security is improved.

Unfortunately I didn't test a single drive as that's not a
configuration we would run our systems in. I expect that it would
reduce read performance and thus pgbench results some, but I can't
tell you how much in this case.

-Dave

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

On Thu, Apr 3, 2014 at 1:32 PM, David Rees <drees76@gmail.com> wrote:

On Thu, Apr 3, 2014 at 12:13 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Wed, Apr 2, 2014 at 2:37 PM, Bret Stern
<bret_stern@machinemanagement.com> wrote:

Any opinions/comments on using SSD drives with postgresql?

Here's a single S3700 smoking an array of 16 15k drives (poster didn't
realize that; was to focused on synthetic numbers):
http://dba.stackexchange.com/questions/45224/postgres-write-performance-on-intel-s3700-ssd

I just ran a quick test earlier this week on an old Dell 2970 (2
Opteron 2387, 16GB RAM) comparing a 6-disk RAID10 with 10k 147GB SAS
disks to a 2-disk RAID1 with 480GB Intel S3500 SSDs and found the SSDs
are about 4-6x faster using pgbench and a scaling factor of 1100. Some
sort of MegaRAID controller according to lspci and has BBU. TPS
numbers below are approximate.

RAID10 disk array:
8 clients: 350 tps
16 clients: 530 tps
32 clients: 800 tps

RAID1 SSD array:
8 clients: 2100 tps
16 clients: 2500 tps
32 clients: 3100 tps

So yeah, even the slower, cheaper S3500 SSDs are way fast. If your
write workload isn't too high, the S3500 can work well. We'll see how
the SMART drive lifetime numbers do once we get into production, but
right now we estimate they should last at least 5 years and from what
we've seen it seems that SSDs seem to wear much better than expected.
If not, we'll pony up and go for the S3700 or perhaps move the xlog
back on to spinning disks.

On a machine with 16 cores with HT (appears as 32 cores) and 8 of the
3700 series Intel SSDs in a RAID-10 under an LSI MegaRAID with BBU, I
was able to get 6300 to 7500 tps on a decent sized pgbench db
(-s1000).

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

On Thu, 2014-04-03 at 12:32 -0700, David Rees wrote:

On Thu, Apr 3, 2014 at 12:13 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Wed, Apr 2, 2014 at 2:37 PM, Bret Stern
<bret_stern@machinemanagement.com> wrote:

Any opinions/comments on using SSD drives with postgresql?

Here's a single S3700 smoking an array of 16 15k drives (poster didn't
realize that; was to focused on synthetic numbers):
http://dba.stackexchange.com/questions/45224/postgres-write-performance-on-intel-s3700-ssd

I just ran a quick test earlier this week on an old Dell 2970 (2
Opteron 2387, 16GB RAM) comparing a 6-disk RAID10 with 10k 147GB SAS
disks to a 2-disk RAID1 with 480GB Intel S3500 SSDs and found the SSDs
are about 4-6x faster using pgbench and a scaling factor of 1100. Some
sort of MegaRAID controller according to lspci and has BBU. TPS
numbers below are approximate.

RAID10 disk array:
8 clients: 350 tps
16 clients: 530 tps
32 clients: 800 tps

RAID1 SSD array:
8 clients: 2100 tps
16 clients: 2500 tps
32 clients: 3100 tps

So yeah, even the slower, cheaper S3500 SSDs are way fast. If your
write workload isn't too high, the S3500 can work well.

Is a write cycle anywhere on the drive different than a re-write?

Or is a write a write!

They feedback/comments are awesome. I'm shopping..

We'll see how
the SMART drive lifetime numbers do once we get into production, but
right now we estimate they should last at least 5 years and from what
we've seen it seems that SSDs seem to wear much better than expected.
If not, we'll pony up and go for the S3700 or perhaps move the xlog
back on to spinning disks.

-Dave

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

On 4/3/2014 12:32 PM, David Rees wrote:

So yeah, even the slower, cheaper S3500 SSDs are way fast. If your
write workload isn't too high, the S3500 can work well. We'll see how
the SMART drive lifetime numbers do once we get into production, but
right now we estimate they should last at least 5 years and from what
we've seen it seems that SSDs seem to wear much better than expected.
If not, we'll pony up and go for the S3700 or perhaps move the xlog
back on to spinning disks.

an important thing in getting decent wear leveling life with SSDs is to
keep them under about 70% full.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

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

On 4/3/2014 2:00 PM, John R Pierce wrote:

an important thing in getting decent wear leveling life with SSDs is
to keep them under about 70% full.

This depends on the drive : drives with higher specified write endurance
already have significant overprovisioning, before the user sees the space.

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