• 10
name Punditsdkoslkdosdkoskdo

Database I/O acceleration using solid state drives

What is the experience improving database performance using solid-state drives (particularly for PostgreSQL databases) such as HP's IO Accelerator? Did you see any marked improvements?

      • 2
    • We have enterprise-class SSDs in some of our HP XP's, they're very different from regular SSDs though. Big fan of the IOA/FusionIO for some things. With either of them I'd be worried about the DB's constant writing killing the SSD but for a heavy-read DB they will help.

Here's a great blog post with lots of research and data links from James Hamilton, an architect at Microsoft I used to work with.

SSD versus Enterprise SATA and SAS disks

I discussed this briefly with Richard and Carl in a .NET Rocks recording on Tuesday. If you have an IO bottleneck with a random read workload, then using SSDs should give you a big boost as it flattens the latency and seek time.

Hope this helps

[Edit: PS, of course my data/viewpoint is all around SQL Server, but an IO subsystem is an IO subsystem, and the results should translate fairly well across any major RDBMS)]

  • 6
Reply Report

To clarify some of the points already mentioned:

MLC drives store more data per cell, which increases the write latency slightly. They also have significantly lower (typically 1/10th) the expected write cycle count of SLC drives. For anything enterprise related, I'd stay well away from MLC drives.

Regarding the overall lifespan of a drive, all SSDs use wear levelling algorithms to balance out hot write areas. Some drives retain spare flash area for internal use (the Intel X25-E does this, and the FusionIO drive let's you determine the reserved area when you partition the drive), which extends the write capacity - if you've got 80 GB of flash to wear level 60 GB of partition over, your drive lasts longer.

All SSDs also require an erase cycle before a block can be overwritten. (Blocks don't get erased when a file is deleted, as a file deletion is a FS operation, and the block device doesn't see it). This can cause a massive performance hit during heavy write periods for drives with poor controllers, as this operation can take a while and in some cases block access. Intel drives, I believe, make use of their spare flash to accelerate this - they use spare flash to provide a clean block to write to, then erase the dirty block and move it into the spare pool in the background. There is a new ATA TRIM command that is being implemented in newer drives, with support coming in linux and windows 7, which will allow the OS to trigger a background erase of all dirty blocks, which will improve performance.

The long-term "performance degradation" of SSDs is generally in reference to the erase/write cycle mentioned above, so decent drives and the TRIM command should essentially eliminate this as a problem.

If you're going to get SSDs for an enterprise system, do the research and only get good quality enterprise-level SLC drives. The Intel X25-E (not -M, these are desktop drives), and Fusion-IO drives are the only ones I'd recommend at this stage.

  • 1
Reply Report

We did some Oracle benchmarking with a Fusion IO card. It was pretty fast.

Took one of our 35 minute processes down to 11 minutes.

It wasn't a SSD drive per se but a PCI card with NAND memory on it.

The SLC type is supposedly better than the MLC type.

Unfortunately only 64 bit OS supported so 32 bit has to get migrated or wait for a 32 bit driver (from Fusion IO).

  • 0
Reply Report
      • 2
    • I can actually see changes in how DB's are managed in a SSD environment - you split your DB from your Log files because you worry about speeds, etc - with SSD being so fast - you can pile them on the same volume (30,000 IOPS!!!). You also split them up because of drive failures (SSD drives don't fail) most HA environments are going to RAID 1 one them but the industry isn't expecting SSD's to fail (its a RAM chip). The one big caveat has to be performance and if they will perform at the highlest levels 1-3 years in, etc.
      • 2
    • SSD drives do fail due to having a maximum write cycle. Over time new models should reduce this limit.

Warm tip !!!

This article is reproduced from Stack Exchange / Stack Overflow, please click

Trending Tags