Nimble Data with Solid State Drives (SSDs)

Posted By Chris Stinson in Hardware,MySQL on September 1, 2008

One of the challenges with a traditional SATA or SAS hard disk based server is data manipulation.

Small databases are able to run entirely in memory, while large databases need to be stored on disk and pulled into memory when needed. One of the challenges with database servers is having a storage subsystem that can handle the raw throughput and read/write IOPS required to manipulate gigabytes or even terabytes worth of data.

As you may have seen in my benchmarks with the Dell MD1000 DAS with 14 – 15k SAS drives, the throughput is pretty good (500MB/s in a RAID 10 array) but the read and write IOPS just aren’t there yet. Hard disk access times increase with more complex RAID arrays – which is the exact opposite of what we want in a quick and powerful storage system.

SSDs are considerably more expensive than a similarly-sized SAS drive. The SSDs have IOPS that are 8-14 times that of an SAS drive. The new line of Intel SSDs (X25-E) have read IOPS in the range of 30, 000 and write IOPS in the 3000 range. If you are working with small (4K) reads and writes in a database system, you can replace 8 SAS drives with 1 SSD and achieve similar performance.

Databases with large queries will rely more heavily on the raw throughput numbers of a hard disk to measure performance. In this area SSDs still outperform SAS drives 3-to-1. I’ve worked with many companies that require reports and data collaboration that run into the gigabytes per query. It takes several minutes for some queries to be performed, something that can be drastically reduced with an SSD subsystem.

Not all large databases have large data requirements. Some large databases have mostly ‘small’ queries that move small amounts of data. Likewise, some small databases move large amounts of data with very few queries.

When sizing a disk and memory system for a database server, you must take into consideration the type of data to be stored, the main purpose of the data (for storage archive or retrieval), and the number of queries/users.

SSDs are still too expensive for most databases that have large data speed requirements. Although SSDs outperform the SAS disks 3-to-1 or slightly more in MB/s, the cost for a reliable setup is still running about 5-to-1 or more.

It is a different story when sizing a system for a web server, forum or search archive (or a similar high-use, small data transfer) database. With systems that have a large number of users pulling small data blocks, an SSD can perform read IOPS way beyond that of an SAS drive. A typical LAMP server running a vBulletin forum can handle more than 30 times the traffic of an SAS drive.

How nimble is your data? With the new crop of SSDs, ease of data manipulation will increase substantially. It is far too taxing on database servers to run various “what-if” scenarios. It simply takes too long to perform. MySQL, Oracle, Sybase, MSSQL all run well until they run out of memory and need to page the hard disks. This is where SSDs will be leveraged. Large enterprises will be able to manipulate their data faster and more effectively without their servers grinding to a halt.

Related posts: