MySQL, long known as the little open source web database that could, has grown enormously popular as the back end database for modest blog and websites like ours. But when it comes to the large transaction loads common to mission-critical systems, nobody has taken MySQL seriously. Fans of Postgres and its descendants, EnterpriseDB and latter-day Ingres, point to it as the open source alternative for mission-critical transaction workloads.

A new benchmarking study being released by Hyperic this week at JavaOne shows that, when you do the right optimizations, MySQL actually can scale.

The impetus for the study, according to Hyperic senior marketing director Stacey Schneider, was demand by customers who were already using MySQL to see if it could work for Hyperic as well, so they could keep their web infrastructure technology stack consistent. The rub is that Hyperic, which tracks performance of web apps, is itself a transaction-intensive application that, for larger websites, might store millions of rows of data with requirements for low latency – just the type of deployment for which MySQL has traditionally run out of gas.

The key, said Schneider, is that like any other database, MySQL must be properly tuned. That meant paying special attention to tuning queries to avoid the need to scan entire tables; optimizing indexes so that you had just the right ones for the queries that you run, but not so many queries that they cluttered up all your tables. You also want to cluster related indexes so that you aren't conducting a wild goose chase all over disk farms while processing queries. Other key design points are limiting use of temporary tables that stage data for specific queries (or at least making them as small as possible).

The idea of tuning MySQL is not that unusual given that every major relational database has unique quirks that require specialized tuning and configuration for obtaining maximum performance. In large part, that’s why DBAs for Oracle, DB2, or SQL Server are not easily interchangeable, and why most companies do not casually swap out databases like they change Dell blades for HP or vice versa. Of course for MySQL, some of the limitations are because it lacks certain enterprise features, such as cost-based query optimization, which helps you tune queries for resource consumption.

Probably the most surprising aspect of the study was that it was conducted by Hyperic rather than Sun, which has obvious vested interested in growing MySQL’s scalability (larger installs will drive server sales).

Admittedly, MySQL may still not be the first choice for transactional web database. For instance it still lacks many of the transaction management features of incumbent products, most of which have been on the market for 15 – 25 years. But this study indicates that if you really want to simplify your web architecture so that the databases for your content or e-commerce system are also the ones utilized for monitoring performance, Hyperic says it’s possible.

---