June 21st, 2014 by Brian Beeler
If Your Databases Aren't on Flash, You're Doing it Wrong
The title isn't meant to be incendiary, though it may come of as such. It's simply meant to communicate the point that the benefits of flash in the data center are so proven that if you're not taking advantage of flash in some capacity there are two main reasons why. Either one, you're afraid, or two, your data is low value and no one wants to access it. That's about it, the long-standing concerns over price and endurance have been mitigated and for certain applications like databases, flash delivers a better experience with lower cost of ownership.
The team at StorageReview takes flash for granted, we have SSDs and arrays stacked to the ceiling in the lab and we're so flash rich that we use Fusion-io ION for primary and sometimes backup storage. Organizations that have adopted flash in the data center feel the same way, after the initial glow wears off, they just accept flash is there doing its thing and long gone are the memories of fighting latency within applications. But not everyone has had a flash experience as I'm reminded by a local business we helped out this week by showing them the light.
The application in question is a multi-tenant web-based custom application with Microsoft SQL Server database back end. The database isn't large by most standards, but the problem that started to pop up was related to reporting, a common slug within many applications. The complex queries in custom reporting engines pull data from all sorts of tables in a largely painful way for hard drives. In Cincinnati we're very friendly; after a discussion on capacity, performance and budget concerns we loaned them a PCIe SSD that fit their scenario.
The PCIe SSD went inside a standard bare metal server, and within a day the drive was configured and full SQL Server restore done on the flash. Of course the database admin expected the drive to be faster than the 10K HDD array they had in place, but the email I received was titled "database. so wow." and this particular excerpt is meaningful:
I took one of the queries from our live environment, ran them on a freshly restored copy of live data on the new box with SQL Server installed on the SSD. Time to run on live, 2 minutes 22 seconds. Time to run in the office... 9 seconds. I knew IO was our bottleneck before, I had no idea it was... this... significant.
The subsequent emails included a bit of virtual laughter back and forth followed by the database admin wanting a sales contact to purchase a few cards for their production SQL environment. A 16X reduction in report request time and resulting improved user experience mean these improvements would literally be night and day once in production. Of course with more users and more weight on the database server, the report may take longer live than in test/dev, but the point is pretty clear, flash alleviates IO pain and sometimes you just have to see it to believe it. For a modest investment in technology, with no code rewrite or other systematic changes, this application is faster and more competitive which directly translates into something even marketing guys understand, more sales. The database admin is a new internal hero and everyone goes home happy.
Back to the initial point, if you're not using flash with databases it's probably because of fear or data no one cares about. For the latter, leave it on high capacity, slow speed disk. If fear is still the concern, use this example and the thousands of others out there to get acquainted with flash and all the ways it can be leveraged. It doesn't have to be expensive, while you may long for an all-flash array for all workloads, sometimes that budget requirement doesn't align with reality. There are many other ways to go though, SATA/SAS/PCIe in host, a little flash in host with caching software, a small amount of flash in a hybrid array...the choices are endless. If you have a database find one that works for your budget, you won't regret it.
If you need advice or have questions about flash, ask us, we'll help. Post a question to our enterprise storage forums or drop us an email or even a Tweet, either way it's free, we just want to see good buying decisions being made. We're also pretty well connected with the major players and can faciliate a conversation directly if needed.