Database I/O acceleration using solid state drives

Question

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?

Answer 1

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)]

Answer 2

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.

Answer 3

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).