Understanding MySQL Appliances and Hardware Acceleration

Believe it or not, there are servers specifically designed to run MySQL -- not to mention other hardware that help to accelerate your databases. Here's a look at what's out there.

In the last year a handful of MySQL and memcached appliances have appeared on the market. These tend to be 2U rackmount servers that you treat as a black box (or something approaching one) and reap some sort of substantial performance benefit along the way. Most of these appliances have a mix of commodity hardware and some “special sauce” on both the hardware and software layers. In addition, there are specialized storage devices aimed squarely at improving MySQL performance that you can drop into your commodity servers, thus making your own appliance.

How do you know if one would help you? What are they good at? What are the pros and cons of using an appliance versus trying to tune things yourself on slightly more exotic hardware? How do you choose one?

This week, I’d like to shed a little light on these new devices.

The Pressure Points

If you’ve been paying attention to the last few months worth of articles in this column, you know that MySQL (like most RDBMSes) can be used in either transaction processing (OLTP) or data mining and analytics (OLAP) applications. OLTP applications tend to be bound by disk I/O (seek time), while OLAP applications tend to be far more CPU intensive while doing large sequential reads.

Not surprisingly, there are ways to enhance commodity hardware to help with both bottlenecks. Any time a very CPU intensive task needs to be accelerated, a co-processor can help out quite a bit. This is quite common. In fact, it’s so common that you may forget about the fancy CPU built in to that Nvidia or ATI-based graphics card–the one that makes all those crazy games play so smoothly without overloading your CPU. Some folks used to call those graphics co-processors. We’ve also seen math co-processors (back in the 286/287 and 386/387 CPU era) and more recently there were add-on boards to accelerate encryption on web servers that handle high volumes of SSL connections. Heck, even high-end network and RAID cards have on-board processors that work to take a lot off the main CPU.

So why should the database server be left out? As we’ll soon see, it’s not–anymore.

As I’ve noted in several columns recently (XtraDB: InnoDB on Steroids, for example), using more disks in a RAID configuration or Solid-State Disks (SSDs) can provide a dramatic boost for workloads dominated by random I/O. But the top of the line SSDs commonly available today are still relatively small. That means you either need to combine several of them in a RAID array or find another memory-based storage system that offers greater capacity and similar performance. That’s exactly what a few vendors have chosen to do.

The Vendors and Their Gear

So, what’s out there today?

In the analytics camp is Kickfire and their Analytic Appliance for MySQL. The Kickfire team has designed a custom ASIC that specifically offloads some of the data-intensive processing that would otherwise bog down analytical queries. Just think of it as a SQL co-processor or an SQL accelerator. They pack it into a custom designed server that turns MySQL into a data warehouse workhorse.

Schooner Information Technology produces a 2U MySQL Appliance that packs in as much as 512GB of Intel SSD capacity along with an Intel Nehalem processor and 64GB of DRAM.

Virident also offers a 2U rackmount server with up to 512MB of storage as well. However, unlike Schooner, they’re using a custom memory based storage system that has properties similar to off-the-shelf SSDs. Like Schooner, it also packs a Nehalem processor and 64GB of RAM.

Fusion-io provides a PCIe card that looks like a SSD to the operating system but doesn’t suffer from the bandwidth limits of the SATA interface (SATA-II makes out at a theoretical 3Gbps). Benchmarks have shown it to have performance on par with a RAID array of the fastest available SSDs on the market. That means you can move hundreds of megabytes per second without seek latency and do this in your existing hardware.

As these are niche products being sold by relatively new companies, pricing tends to be negotiated on a per-sale basis. But they all offer ways to test out their hardware in your own environment to see what it can do. If you need the extra performance and have the money, you can buy a fair amount of headroom using these solutions.

Software Changes

All this acceleration that comes with specialized hardware is really an exercise in moving the bottleneck. After all, that’s what we do in performance tuning: fix one bottleneck and discover the next one! Given the substantial I/O capabilities that come with memory based storage, we often find that MySQL itself cannot fully take advantage of it. This shouldn’t be too surprising, given the limitations we’ve discussed here before–and how Percona’s XtraDB and the Google patches have worked to close the gap.

As a result, the appliance vendors have all made changes to the internals of MySQL and, in some cases, the Linux kernel as well. In the case of Kickfire, there’s a whole specialized processor available for radically accelerating a whole class of queries. That means MySQL won’t have to tax your main CPU anymore. And in the case of Virident and Schooner, data is available without the seek times normally associated with disk access. Some of InnoDB’s built-in assumptions go out the window. Both companies have really worked to find those places in MySQL/InnoDB where a little bit of tuning can make a very big difference in performance.

Users of Fusion-io cards don’t receive a customized version of MySQL to go with it, but it does require special drivers at the OS level. Given the amazing performance it provides, you’re almost certainly going to need XtraDB or the recently released MySQL 5.4 to make the most of it.

Making the Decision

While I don’t have the space here to go in-depth on any of the options, I’ve spoken to database and operations folks at numerous companies that have evaluated and, in some cases, purchased some of this gear. By and large they are very satisfied with their investments. But the decision involves more than just compatibility and performance.

Many of the companies that need a non-commodity solution to help with performance are already pushing the envelope a bit and probably have many servers dedicated to serving up data fast. In an organization like that, management of servers becomes increasingly important. Having standardized and easy to manage server farms helps keep costs down, service predictable and reliable, and just makes life easier.

But when looking at any sort of appliance (storage, database, networking, etc.) you have to consider how it will fit into the way you currently work. Do you need root access on the machine? Will your existing tools talk to it easily? Are you willing to lend a vendor dictate your software upgrade cycle to some degree?

Only after thinking through how this new hardware will affect your operations and flexibility can you really decide if it’s worth it or not. The good new is that for many application, these solutions can easily replace a dozen–or even dozens of your existing servers (depending on where your bottlenecks are today), so it may well be worth changing how you operate to some degree–especially when that kind of improvement is on the line.

In the end, it’s all going to come down to economics. Will spending extra money on custom hardware and the associated operation costs be significantly outweighed by the performance and reduction in hardware you’ll need to get it? If so, the choice should be pretty easy.

In any case, if you find yourself battling performance bottlenecks on your existing infrastructure, you may now have a few more options that you didn’t now about before.

Fatal error: Call to undefined function aa_author_bios() in /opt/apache/dms/b2b/linux-mag.com/site/www/htdocs/wp-content/themes/linuxmag/single.php on line 62