Thread: Considering Solid State Drives

Considering Solid State Drives

From
Allan Kamau
Date:
Hi,
As part of datamining activity. I have some plpgsql functions
(executed in parallel, up to 6 such concurrent calls) that perform
some reads and writes of large number of (maybe 10000) records at a
time to a table having multi-column primary key.
It seems the writing of these few thousands records is taking a long
time (up to 5mins in some cases).
Running vmstat reports %iowait between 15 and 24 on a single 7200rpm
SATA drive, six core (Phenom II) "server".

I am now thinking of investing in a SSD (Solid State Drive), and maybe
choosing between "Crucial Technology 256GB Crucial M225 Series
2.5-Inch Solid State Drive (CT256M225)" and "Intel X25-M G2 (160GB) -
Intel MLC". I have looked at the comparison statistics given here
"http://www.anandtech.com/bench/Product/137?vs=126" that suggests that
the Intel offering is more geared for small random write operations.

After googling I found little resent content (including survival
statistics) of using SSDs in a write intensive database environment.

Kindly advice,

Allan.

Re: Considering Solid State Drives

From
"Marc Mamin"
Date:
SSD caveats are well described here:
http://www.postgresql.org/about/news.1249

HTH,

Marc Mamin

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Allan Kamau
Sent: Donnerstag, 11. November 2010 07:42
To: Postgres General Postgres General
Subject: [GENERAL] Considering Solid State Drives

Hi,
As part of datamining activity. I have some plpgsql functions
(executed in parallel, up to 6 such concurrent calls) that perform
some reads and writes of large number of (maybe 10000) records at a
time to a table having multi-column primary key.
It seems the writing of these few thousands records is taking a long
time (up to 5mins in some cases).
Running vmstat reports %iowait between 15 and 24 on a single 7200rpm
SATA drive, six core (Phenom II) "server".

I am now thinking of investing in a SSD (Solid State Drive), and maybe
choosing between "Crucial Technology 256GB Crucial M225 Series
2.5-Inch Solid State Drive (CT256M225)" and "Intel X25-M G2 (160GB) -
Intel MLC". I have looked at the comparison statistics given here
"http://www.anandtech.com/bench/Product/137?vs=126" that suggests that
the Intel offering is more geared for small random write operations.

After googling I found little resent content (including survival
statistics) of using SSDs in a write intensive database environment.

Kindly advice,

Allan.

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

Re: Considering Solid State Drives

From
Vick Khera
Date:
On Thu, Nov 11, 2010 at 1:42 AM, Allan Kamau <kamauallan@gmail.com> wrote:
> After googling I found little resent content (including survival
> statistics) of using SSDs in a write intensive database environment.
>

We use the Texas Memory RAMSan-620 external disk units.  It is
designed specifically to survive high write loads, and uses internal
monitoring and load leveling and spare parts with internal RAID
configuration to keep from losing data in the eventual case when the
SSD chips wear out.  When that happens, you just swap out the failed
piece and it keeps humming along.

I wouldn't trust an SSD as a stand-alone drive for a DB.  At minimum,
I'd RAID1 them using the SATA controller... but be sure to get SATA3
or even better SAS connected drives if you want to maximize the speed.

Re: Considering Solid State Drives

From
Radosław Smogura
Date:
Hello,

When choosing SSD drive you need to consider
* number of writes to particular sector which is about 100k to 200k and then
sector will fail
* in case of DB grow, limitied size of those dirvers.

> As part of datamining activity. I have some plpgsql functions
> (executed in parallel, up to 6 such concurrent calls) that perform
> some reads and writes of large number of (maybe 10000) records at a
> time to a table having multi-column primary key.
> It seems the writing of these few thousands records is taking a long
> time (up to 5mins in some cases).

This time is realy long, it is about 30 seconds to insert one row!
You should check if your functions dosen't generate locks, check if some
triggers are executed. Your functions can query on non-indexed columns, as
well. Try to execute above functions in one, non-paraller thread, if the time
per row will be less then paraller execution it means you generate locks.

You can as well try to disable fsync in PSQL config.

Kind regards,
Radek

Re: Considering Solid State Drives

From
David Siebert
Date:
ZFS has an option to use an SSD as cache for the spinning drives. ZFS
under Solaris has turned in some really good IO numbers. The problem is
with the new Sun I am not feeling so good about the open nature of
Solaris. ZFS performance under BSD I have read does not match ZFS under
Solaris.

On 11/11/2010 8:35 AM, Vick Khera wrote:
> On Thu, Nov 11, 2010 at 1:42 AM, Allan Kamau <kamauallan@gmail.com> wrote:
>> After googling I found little resent content (including survival
>> statistics) of using SSDs in a write intensive database environment.
>>
> We use the Texas Memory RAMSan-620 external disk units.  It is
> designed specifically to survive high write loads, and uses internal
> monitoring and load leveling and spare parts with internal RAID
> configuration to keep from losing data in the eventual case when the
> SSD chips wear out.  When that happens, you just swap out the failed
> piece and it keeps humming along.
>
> I wouldn't trust an SSD as a stand-alone drive for a DB.  At minimum,
> I'd RAID1 them using the SATA controller... but be sure to get SATA3
> or even better SAS connected drives if you want to maximize the speed.
>


Re: Considering Solid State Drives

From
Joachim Worringen
Date:
Am 11.11.2010 16:40, schrieb David Siebert:
> ZFS has an option to use an SSD as cache for the spinning drives. ZFS
> under Solaris has turned in some really good IO numbers. The problem is
> with the new Sun I am not feeling so good about the open nature of
> Solaris. ZFS performance under BSD I have read does not match ZFS under
> Solaris.

Very true. Also, we experienced (with a 32-core/64GB Sun X4600 machine
and a direct-attached storage array with 24 10k-spindles) that
PostgreSQL scaled *badly* (with the number of active, long-running
queries) when using ZFS. Installing CentOS with XFS, the same hardware
delivered very good scaling and higher absolute performance.

Admitted, this were more or less out-of-the-box configurations (but we
did some tuning with ZFS, which resulted in a 2x speedup - still not
enough). But the difference was drastic. We went with Linux/XFS then.

  Joachim




Re: Considering Solid State Drives

From
"mark"
Date:
Search the PG performance mailing list archive. There has been some good
posts about SSD drives there related to PG use.


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Allan Kamau
Sent: Wednesday, November 10, 2010 11:42 PM
To: Postgres General Postgres General
Subject: [GENERAL] Considering Solid State Drives

Hi,
As part of datamining activity. I have some plpgsql functions
(executed in parallel, up to 6 such concurrent calls) that perform
some reads and writes of large number of (maybe 10000) records at a
time to a table having multi-column primary key.
It seems the writing of these few thousands records is taking a long
time (up to 5mins in some cases).
Running vmstat reports %iowait between 15 and 24 on a single 7200rpm
SATA drive, six core (Phenom II) "server".

I am now thinking of investing in a SSD (Solid State Drive), and maybe
choosing between "Crucial Technology 256GB Crucial M225 Series
2.5-Inch Solid State Drive (CT256M225)" and "Intel X25-M G2 (160GB) -
Intel MLC". I have looked at the comparison statistics given here
"http://www.anandtech.com/bench/Product/137?vs=126" that suggests that
the Intel offering is more geared for small random write operations.

After googling I found little resent content (including survival
statistics) of using SSDs in a write intensive database environment.

Kindly advice,

Allan.

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


Re: Considering Solid State Drives

From
Allan Kamau
Date:
On Thu, Nov 11, 2010 at 4:35 PM, Vick Khera <vivek@khera.org> wrote:
> On Thu, Nov 11, 2010 at 1:42 AM, Allan Kamau <kamauallan@gmail.com> wrote:
>> After googling I found little resent content (including survival
>> statistics) of using SSDs in a write intensive database environment.
>>
>
> We use the Texas Memory RAMSan-620 external disk units.  It is
> designed specifically to survive high write loads, and uses internal
> monitoring and load leveling and spare parts with internal RAID
> configuration to keep from losing data in the eventual case when the
> SSD chips wear out.  When that happens, you just swap out the failed
> piece and it keeps humming along.
>
> I wouldn't trust an SSD as a stand-alone drive for a DB.  At minimum,
> I'd RAID1 them using the SATA controller... but be sure to get SATA3
> or even better SAS connected drives if you want to maximize the speed.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

From what I see from the responses it would be advisable to try ignore
the SSDs for now as there are other disk based alternatives such as
the VelociRaptor that seem in some cases to match half of the
performance of the SSDs not to mention that my application requires
expensive data writing.

Thank you all.

Allan

Re: Considering Solid State Drives

From
Greg Smith
Date:
Allan Kamau wrote:
> I am now thinking of investing in a SSD (Solid State Drive), and maybe
> choosing between "Crucial Technology 256GB Crucial M225 Series
> 2.5-Inch Solid State Drive (CT256M225)" and "Intel X25-M G2 (160GB) -
> Intel MLC".

Both of these are worthless for database applications if you care about
your data.  In order to perform well, SSDs need to have a write cache to
buffer small writes.  For PostgreSQL to work as intended, that write
cache needs to be non-volatile.  It is not in either of those drives.  I
hear tales of lost PostgreSQL data on Intel SSDs every month, the
database is lucky to survive a single power outage.

The only relatively inexpensive SSD we've heard about on the Performance
list that's survived all of the durability on crash tests thrown at it
is the OCZ Vertex 2 Pro; see
http://archives.postgresql.org/pgsql-performance/2010-07/msg00449.php
for a summary.  That avoids this problem by having an Ultracapacitor
integrated with the drive, to allow orderly processing of the write
cache if power is lost.  There are other SSD devices that are similarly
reliable, but the costs are quite a bit higher.

More background about this topic at
http://wiki.postgresql.org/wiki/Reliable_Writes

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services and Support        www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books