Thread: 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.
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
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.
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
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. >
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
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
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
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