25

I hope that many of you are working with high traffic database-driven websites, and chances are that your main scalability issues are in the database. I noticed a couple of things lately:

  1. Most large databases require a team of DBAs in order to scale. They constantly struggle with limitations of hard drives and end up with very expensive solutions (SANs or large RAIDs, frequent maintenance windows for defragging and repartitioning, etc.) The actual annual cost of maintaining such databases is in $100K-$1M range which is too steep for me :)

  2. Finally, we got several companies like Intel, Samsung, FusionIO, etc. that just started selling extremely fast yet affordable SSD hard drives based on SLC Flash technology. These drives are 100 times faster in random read/writes than the best spinning hard drives on the market (up to 50,000 random writes per second). Their seek time is pretty much zero, so the cost of random I/O is the same as sequential I/O, which is awesome for databases. These SSD drives cost around $10-$20 per gigabyte, and they are relatively small (64GB).

So, there seems to be an opportunity to avoid the HUGE costs of scaling databases the traditional way by simply building a big enough RAID 5 array of SSD drives (which would cost only a few thousand dollars). Then we don't care if the database file is fragmented, and we can afford 100 times more disk writes per second without having to spread the database across 100 spindles. .

Is anybody else interested in this? I've been testing a few SSD drives and can share my results. If anybody on this site has already solved their I/O bottleneck with SSDs, I would love to hear your war stories!

PS. I know that there are plenty of expensive solutions out there that help with scalability, for example the time proven RAM-based SANs. I want to be clear that even $50K is too expensive for my project. I have to find a solution that costs no more than $10K and does not take much time to implement.


Dave, NXC, and Burly,

Thanks for your replies! I'd like to clarify that the word "cheap" is very important in my situation. So, I have to use cheap Dell servers ($4K 2950s that have only 8 memory banks). I already have 32GB of RAM installed, so I cannot keep scaling this way. Besides, adding RAM does not save you from disk WRITE bottlenecks, which is my main problem right now.

I used to be concerned with lifetime of SSDs, but after reading about the modern wear leveling algorithms I am pretty sure that these drives will last long enough. My database writes 300GB per day, and projected to go over 1TB per day in 2009. The Enterprise SSDs are designed to handle around 10TB of writes per day over multiple years.

I would disagree with Burly's point that it takes too much labor to migrate from SAS to SSD. My database is a synchronous mirror, so I can upgrade one side of the mirror, then watch it for a few months, and if it blows I can failover to the second server which still has the old good SAS hard drives...

Mark Henderson
  • 68,316
  • 31
  • 175
  • 255
Dennis Kashkin
  • 391
  • 3
  • 5
  • 2
    BTW, while you do state how the improved performance would potentially reduce the hardware costs, you don't clearly express how the SSDs would reduce your major cost - labor. I'm assuming that probably are getting at the fact that a reduction in installation size may reduce your staffing reqs – Burly Nov 19 '08 at 13:54
  • 2
    My database was happily running on production for 3 years without any full time DBAs or consultants. Then the load increased to the point where we're bumping into I/O bottlnecks. So, I might have to pay a lot of money to DBAs for partitioning and defragging the database. Or just get some cheap SSDs. – Dennis Kashkin Nov 19 '08 at 14:14
  • I updated my answer to discuss the cost constraints you added. Depending on the space, size, performance, maintenance, and modification requirements of your DB, SSDs certainly may offer a cost effective solution. The solution design and cost analysis are beyond our scope here. Best of luck! – Burly Nov 20 '08 at 14:01
  • You've been drinking too much koolaid, SSD is, at best, 1.5x faster for reading than a RAID drive, but writes are slower than magnetic disks. A fiber based SANS with a high speed RAID will destroy any SSD no matter how good it is. – TravisO Dec 21 '08 at 06:28
  • Just wanted to share - we've been running a 400GB database on SSDs for 5 months now. This database gets a lot of write activity (up to 1200 transactions per second). We had no issues so far, and performance has been dramatically better compared to RAID10s with 15K rpm SAS drives. The disks stay 96% idle. So, considering that SSDs are becoming amazingly cheap now ($600 for 160GB Intel drive), I would claim that this is a better way to scale I/O than SANs. – Dennis Kashkin May 29 '09 at 16:20
  • Dennis, many DB I/O issues tend to go away when you just get more RAM. – Seun Osewa Apr 09 '10 at 00:52
  • @Dennis - I'm glad everything is working out for with the SSDs. I would however, alter the claim to be "SSDs are a better way to scale I/O than magnetic hard drives". An SSD is not a functional equivalent for comparison to a SAN which includes all sorts of additional features like high-availablity, disk utilization, management, and backup. In fact, you can even build a SAN with SSDs rather than spindles to get the best of both worlds. – Burly Jun 02 '10 at 15:25

6 Answers6

20

Potential Issues

I have a couple points of issue with using SSDs for production databases at the present time

  • The majority of database transactions on a the majority of websites are reads not writes. As Dave Markle said, you maximize this performance with RAM first.
  • SSDs are new to the mainstream and enterprise markets and no admin worth his salt is going to move a production database that currently requires 15K RPM U320 disks in RAID5 communicating via fibrechannel to an unproven technology.
  • The cost of the research and testing of moving to this new technology, vetting it in their environment, updating the operating procedures, and so forth is a larger up front cost, both in terms of time and money, than most shops have to spare.

Proposed Benefits

That said, there are a number of items, at least on paper, in favor of SSDs in the future:

  • Lower power consumption compared to a HDD
  • Much lower heat generation
  • Higher performance per watt compared to a HDD
  • Much higher throughput
  • Much lower latency
  • Most current generation SSDs have on the order of millions of cycles of write endurance, so write endurance is not an issue as it once was. See a somewhat dated article here

So for a given performance benchmark, when you factor total cost of ownership including direct power and indirect cooling costs, the SSDs could become very attractive. Additionally, depending on the particulars of your environment, the reduction in the number of required devices for a given level of performance could also result in a reduction of staffing requirements, reducing labor costs.

Cost and Performance

You've added that you have a cost constraint under $50K USD and you really want to keep it under $10K. You've also stated in a comment that you can get some "cheap" SSDs, eluding that the SSDs will be cheaper than the DBAs or consultants. This may be true depending on the number of hours you would need a DBA and whether it is a reoccuring cost or not. I can't do the cost analysis for you.

However, one thing you must be very careful of is the kind of SSD you get. Not all SSDs are created equal. By and large the "cheap" SSDs you see for sale in the $200-400 dollar (2008/11/20) are intended for low power/heat environments like laptops. These drives actually have lower performance levels compared to a 10K or 15K RPM HDD - especially for writes. The enterprise level drives that have the killer performance you speak of - like the Mtron Pro series - are quite expensive. Currently they are around:

  • 400 USD for 16GB
  • 900 USD for 32GB
  • 1400 USD for 64GB
  • 3200 USD for 128GB

Depending on your space, performance, and redundancy requirements, you could easily blow your budget.

For example, if your requirements necessitated a total of 128GB of available storage then RAID 0+1/10 or RAID 5 with 1 hotspare would be ~$5600

If you needed a TB of available storage however, then RAID 0+1/10 would be ~$51K and RAID 5 with 2 hotspares would be ~$32K.

Big Picture

That said, the installation, configuration, and maintenance of a large production database requires a highly skilled individual. The data within the DB and the services provided from that data are of extremely high value to companies with this level of performance requirements. Additionally, there are many things that just cannot be solved by throwing hardware at the problem. An improperly configured DBMS, a poor database schema or indexing strategy can /wreck/ a DB's performance. Just look at the issues Stackoverflow experienced in their migration to SQL Server 2008 here and here. The fact of the matter is, a database is a strenuous application on not only disk but RAM and CPU as well. Balancing the multi-variate performance issue along with data integrity, security, redundancy, and backup is a tricky bit.

In summary, while I do think any and all improvements to both the hardware and software technology are welcomed by the community, large scale database administration - like software development - is a hard problem and will continue to require skilled workers. A given improvement may not reap the labor reduction costs you or a company might hope for.

A good jumping point for some research might be Brent Ozar's website/blog here. You might recognize his name - he's the one who has assisted the stackoverflow crew with their MS SQL Server 2008 performance issues. His blog and resources he links to offer quite a bit of breadth and depth.

Update

Stackoverflow themselves are going the consumer SSD-based route for their storage. Read about it here: http://blog.serverfault.com/post/our-storage-decision/

References

Burly
  • 379
  • 2
  • 7
4

If you have a really, really high-traffic site which can benefit from an SSD for increased write performance, you will probably have an issue with the lifetime of the SSD, so I'm not sold on them yet for that.

With that in mind, what to do with databases which have high levels of reads? The answer is simple: jam the server with as much RAM as you can stomach. You'll find that the hottest tables are almost always kept in RAM cache anyway, and any large hit to disk will probably be due to a big table or index scan, which can often be optimized away with proper indexing.

Dave Markle
  • 378
  • 2
  • 11
  • I would revisit your comment about the concern of the lifetime of the SSD. In terms of MTBF, the SSD has a much higher rating than a HDD. In terms of write-cycle endurance - previously an issue, the current generation is >1million write cycles, making this a non-issue, especially in RAID configs. – Burly Nov 19 '08 at 13:32
  • (Out of characters) ... It's not that you shouldn't be concerned about the lifetime of an SSD, it's just that the current technical ratings suggest that SSDs are equal or superior to a HDD counterpart. The fact that SSDs don't have the decades of experience in production means they are unproven. – Burly Nov 19 '08 at 13:33
  • SSDs are slower at writing than HDs – TravisO Dec 21 '08 at 06:29
  • SSDs are generally substantially faster at random writes, particularly 4K random writes. They may be slower for sequential writes, but that's not necessarily important for database servers. – ChrisInEdmonton Nov 28 '09 at 05:08
1

I've worked as a DBA for 5+ years and thinking about ways to improve DB performance is always at the back of my mine. I've been watching the SSD space and I think that they are definitely becoming more and more of a viable option.

Check this out;

http://i.gizmodo.com/5166798/24-solid-state-drives-open-all-of-microsoft-office-in-5-seconds

There is also a new product produced by Acard called the ANS-9010 which is an improved version of the GC-Ramdisc which allows you to use DDR2 ram to create a SATA Drive (up to 64gig) using DDR2 sticks with a 400MB/s theoretical maximum.

http://techreport.com/articles.x/16255/3

^^ But the other thing that's useful in that article is that it compares the ANS-9010 against all the players on the SSD market and it turns out that Intel have 64GB x25-E SSD that's pretty much comparable to having a hardware ramdisk.

The thing that would worry me about the SSD is wearing them out with all the stress that a large DB would put them through, and so you'd have to use raid to mirror the drives which means that you're paying twice as much;

And the downside with the hardware ramdisk is that the battery, in the case of a power cut only powers it for so long so you'd have to work out some fancy way to back it up. I believe that you can also purchase a mains plug for them but then that still relies on your UPS.

I suggest that you use the hardware ram disk for the temp DB and windows swap file - and put the database on the Intel X25-E Extreme (approx 600 USD for 64 gig).

Anyway it would scream and make all the rest of us very jealous.

(Also consider using another ANS-9010 for hosting the website)

Cheers, Dave

1

We just put together a w2k3 r2 64bit Sql 2008 server on dual 2.5in Seagate Momentus XT hybrid mirror - 1/4 stroke for OS, and 1/4 stroke for DB. So were using 125GB for OS and 125GB for DB. were getting 1500MB/s to 1900MB/s seq reads. On an Intel i7 2600K 3.4Ghz 8GB

Adam Smith
  • 11
  • 1
0

There are products on the market such as This one that do this sort of thing. Also, as the other poster says, adding extra RAM to the DB server will give you better cache hit rates, which will reduce disk traffic.

8-socket Opteron servers such as a Sun X4600 will let you put up to 256GB of RAM in them for prices that are still cheaper than a large DBA team. You could also consider using flat files rather than a DBMS (as this company did), which will give you better performance than a DBMS. In this case, a SAN will give you a degree of data integrity. However, you will have to design your data access strategy carefully to avoid getting yourself into a mess. Apparently quite a few large-volume dot-com outfits do this. It's considerably more efficient than a DBMS, allowing fairly pedestrian hardware to handle large loads, and avoids DBMS licencing fees.

-1

SSD drives are based on NAND flash memory (MLC or SLC). If you are buying SSD drives in a SATA (2 or 3) form factor, you are limiting the performance you can get out of them. Typically, SSD drives based on the fast Sandforce SF-1200 controller yield 220MB / second reads and 205MB / second writes - much faster than an old-fashioned mechanical rotating disk.

However, if you move to a PCIe solution such as FusioIO, that does not have the slow SATA 2 or SATA 3 connector involved, you are looking at solutions that are 10-50 times faster than rotating mechanical bulls (I mean disks).

So for your "cheap" solution, go with a SATA 2/3 SDD based on the Sandforce SF-1200 controller. This will get you about a 3-5 times over speed improvement (based on real world experience). If you have the budget, then go for the FusioIO. Nothing will beat it in terms of performance. It is insanely fast. Expect to spend $20,000 to $50,000 though.

  • 2
    Fallacy. A modern SSD is good for about 50.000 IOPS, deliveing 580mb throughput.A SAS makes less than 500 IOPS. Databases are not file servers. – TomTom Jun 26 '11 at 05:51