Anatomy of MySQL on the GRID

April 5, 2007 – UPDATE:
The new MySQL technologies referenced in this post have been released from beta and are now available to all (gs) Grid-Service customers. For more information, please see our two new products: MySQL SmartPool v.2 and MySQL GridContainers. Thank you.

An in-depth look at the MySQL issues on (mt) Media Temple’s Grid platform.

Despite early problems with the system, (mt) Media Temple’s recently launched GRID hosting system has been an overall success for our customers and the company as a whole. The system has helped a new generation of site owners successfully accomplish hosting projects that were previously impossible with normal hosting. With scalability now at a price point unobtainable in the general hosting industry, users are getting more results for less money. In just 3 months the system has become home to over 40,000 domains and a plethora of amazing applications that simply were not sustainable with old Shared Hosting and competing hosting solutions. The GRID however has not been without its trials and has produced several reported bugs and issues of temporary downtime.

Only a few weeks after launch, (mt) Media Temple moved quickly toward release of (GMR v.1.1) an upgrade which alleviated a majority of issues, fixed hundreds of bugs and stabilized most aspects of the system. As the system, and the customers who use it, grow and evolve, newer and more esoteric anomalies are found which are cause for further instability. These new conditions are diagnosed and engineers are quickly responding, literally every 24 hours, with micro-patches that are enhancing the system. One segment of the system continues to be a nuisance for its customers and the architects of the system however; the MySQL databases. Hence, this article was created to provide insight as to why the MySQL issue is the longest outstanding matter on the GRID system and ultimately what is being done to address the situation.

Bluntly, MySQL has been a continuous headache for (mt) and many database users on the GRID. While we have made numerous tuning and configuration changes and hardware revisions, which have resulted in dramatic improvements, we still have fallen short of our own internal quality standards. We’ve furthered our efforts by adding hundreds of thousands of dollars worth of new hardware, and we’ve also hired some of the world’s most highly regarded MySQL experts – all with the goal of trying to stabilize this segment of the GRID. Unfortunately our remedies have still proved to be imperfect. We’ve been forced to go back to the whiteboard and completely reevaluate our approach. The good news is that we have the re-architected our approach to MySQL, this time with improved intelligence and more actual experience, which has resulted in a more robust design which we are confident will quickly solve the ongoing issues.

What follows is a recap of how our system was originally designed, disclosure of some of the attempted remedies, and what ultimately resulted in the permanent fix. To those who continue reading we want to thank you in advance for your time.

The original MySQL design.

Fundamentally it is important to understand that the MySQL segments are not clustered or redundant at an application level within the GRID. The machines, storage, power, networking, web, email, ftp and related applications are n+1 redundant – but the MySQL application itself is not. Although it is possible using commercial and open-source software to cluster MySQL at an application level, such technologies have shown to be counter-effective in multi-tenant hosting environments such as the (mt) GRID. Further to this point, MySQL is not inherently designed to scale and deal with the random usage behaviors of thousands of different users all running different applications and query types. In layman’s terms, there are ways to cluster MySQL if you’re just running one application, even a very large one, if you know how it’s going to behave. However, in a hosting environment where you cannot know ahead of time what users will try and do, there is not an acceptable solution using existing clustering technologies that allows us to “just add more hardware” and scale up automatically.

The MySQL shortcomings being a known issue when planning our implementation, we approached the scalability problem by designing a load-leveling system combined with a server-farm model inside the GRID. The load-leveler system named Tahiti, a special technology created by (mt), analyzes database usage patterns, load, query times, etc. and balances databases into healthy resources within the segment (in this case a server farm). Tahiti works automatically in near real-time and performs its operations with little to no material interruption to database applications. The end result is a server farm of MySQL servers which can move around databases intelligently and automatically keep the system as a whole functional and stable. It’s a great system and we are proud of its technical achievement.

The load requirement plan for the initial MySQL segment at launch was developed by analyzing the current database usage of our previous (ss) Shared-Server system; which we believed was a great data-set to use as a benchmark. This analysis took into account the database behavior patterns of approximately 75,000 active sites. Based on our findings, (mt) Media Temple constructed a system sufficient to handle twice the load of our findings as well as the load from an additional 10,000 new sites (number of anticipated early adopters of the GRID). We also validated our measurements during a 4 month beta test of the GRID which consisted of over 100 users so that we could further develop our baseline performance metrics. We over-built the hardware, based on our internal metrics, so that we could accommodate nearly any load condition until Tahiti was launched and the second or third Cluster went online. We believed this plan was more than adequate to handle our launch as we knew the rate of new customers and migrating customers was lower than this system’s limitations. It was a great plan – on the whiteboard – but it had shortcomings.

What went wrong?

We conclude there were two primary factors which caused the issues experienced with the first generation MySQL segment. First, not having Tahiti ready at launch proved to be a mistake. The 24/7 “human orchestrated balancing system” was not planned for, nor did it scale well. Our DBA team was not able to respond quickly enough to mitigate the unforeseen load issues as indicated in the remainder of this report.

Secondly, our initial database segment became hundreds of times more overloaded than we ever planned for, and did so in an incredibly short period of time. This problem mainly derived from another mistake; which is that we didn’t anticipate attracting a new breed of user – the hosting industry “orphan.” Having an 8-year history of catering to high-demand websites we thought we had seen it all but this new level of load “requirement” was blindsiding. Our new offering quickly became a refuge for sites that were kicked off their old hosting company; a common industry practice. Because of their high database load “requirements” and need of resources, these site owners were shut down immediately and told to leave other hosts. Many of these “orphaned” users had applications, code, and query instructions that were grossly inefficient for even a massive dedicated server. A number of these users came running to (mt) Media Temple with the promise that their applications, despite all of their deficiencies, would be accepted and not turned off. These users are radically different, by orders of magnitude, from anything we had previously analyzed or benchmarked.

So, in keeping with our original promise to sustain our customer’s ability to keep sites up regardless, we needlessly taxed our system, asked no one to leave and relied on our GPU billing model to account for unimaginable database usage. We continued to add hardware, analyze data, add more hardware, analyze data, and add more hardware yet again. In some situations were still unable to sustain these users. No matter what load-leveling we did, these systematically pathological applications overloaded every database we put them on – no matter how big or resourceful.

What is the current status?

Tahiti, which was previously discussed, is currently keeping a mass majority of the MySQL segments stable and operating at speeds that continue to outperform older shared hosting environments. Unfortunately, we have been forced to manually balance a small number of databases onto quarantine servers, and they’re still having issues, until their code is optimized to scale up effectively. Also, there is an infrequent occurrence of a “Too many connections” error message, which is a remaining fragment of the combined issues discussed here within. We’ve updated and installed Tahiti (v.0.6) into the GRID with improved MySQL load-leveling capabilities compared to previous versions. The system is automatically keeping the MySQL segments intelligently and properly loaded which will ensure that 99% of all MySQL dependent sites are functioning at exceptional levels.

THE PERMANENT FIX.

With a near obsession to figure out how to offer reliable, high-performing, low-cost MySQL services, the (mt) Media Temple GRID architects went back to the whiteboard to completely redevelop the database strategy. What resulted is a new container system which will give each GRID customer their own dedicated MySQL server. This is great news for our customers because they will now have their own unshared copy of MySQL with their own dedicated RAM and CPU space which allows for a more stable, predictable, understandable MySQL application environment. The technology is based on the same proven approach that is successfully powering the RoR container system. It eliminates the ambiguous and disruptive nature of the shared MySQL model and allows customers the confidence that any performance issues derive only from the RAM and CPU limitations of the container itself, not from competing customers.

Below is a summary of the benefits of the new MySQL container system:

• On demand upgrades. At any time a customer is able to upgrade to a higher level container. Although the base container given to each customer will be sufficient for the vast majority of customers – there are clearly some who require more. If a customer is experiencing poor MySQL performance they can easily upgrade the container (by clicking a button) to a level which meets their application requirements.
• Reboot capable. In the event a customer exceeds the RAM/CPU limitations of their container a customer can reboot on-the-fly and reset a crashed MySQL environment. This allows for quick site restoration and true application troubleshooting and realistic performance benchmarking.
• Single instance of MySQL for each customer. It is unshared, more reliable, and the only limitation is the container resources themselves (RAM, CPU). No bad neighbor effect.
• Better disk I/O management. Container system is optimized to handle disk I/O for the single container, not an entire server. Results in an improvement in speed and reliability.
• Repair MySQL tool for a clean repair of corrupted databases should you experience out of resource issues.
• Increased insight into application performance. Because you have guaranteed resources that are not shared with others, the use of existing MySQL analysis tools to gain insight into your database activity is much more meaningful.
• Does not count against GPUs. Use as much as you want within the limits of your container resources. No usage fees or confusing billing procedures.

Despite the high costs associated with giving every customer their own container for MySQL, (mt) Media Temple sees no other choice as we have exhausted and ruled out all other possibilities. The container system has proven to be highly effective in our testing and has worked near-flawlessly with other applications. We’re confident that our obsession to correct this deficiency will result in the GRID becoming what (mt) set out to build; a platform that will exceed your expectations.

The current plan is to roll out the new MySQL container system on March 1st. We will also be involving some customers in a private beta from now until this time. The roll-out plan does not require customers to do anything special in order to begin using the newly improved system. Customer databases will start out in the current server farm system (non-container) and will be monitored by a new version of Tahiti (v.0.7). When Tahiti detects that the resource needs of a database resource expand, it will move the databases into the dedicated container allocated as part of the normal (gs) Grid-Server hosting plan. This framework will help conserve resources within the GRID as only a very small number of databases technically require a dedicated MySQL container. As soon as a customer does require the resources of a MySQL container, our system will automatically provision a container and will handle all of aspects of the one-time move. As previously noted, the Tahiti system is extremely fast and sophisticated allowing it to handle database moves with little or no visible disruption to customer site operations.

Other things we are doing.

Although we believe the MySQL container design is the best of all worlds when it comes to multi-tenant MySQL hosting, we will remain committed to providing resources and continued improvement of the system. Here we have outlined some of the additional things we will be doing to help you keep your MySQL databases running clean and performing as best as possible.

• Container +PLUS. With the new MySQL container system, (mt) Media Temple has already begun developing an enhanced version of the container which will allow for cool new features such as; Version switching between MySQL 4 and 5, my.cnf editing for ultimate configuration control, and also future MySQL clustering capability between multiple containers.
• Container Auto-Upgrade. In our quest to provide true scalability (mt) Media Temple is looking to provide you the option of an automatic upgrade to your MySQL container. If you need more resources, they’ll be automatically provided 24/7. After the burst has subsided, your container would then be taken back to your original setting. A micro-billing system would keep track of your usage and notify your email account when it has occurred.
• MySQL coding resources. As we have witnessed the growing popularity of MySQL usage in user applications, we have also seen the spectrum of good coding technique widen (for the worse). In an effort to help our users write better code, (mt) Media Temple will begin a MySQL resource center where our DBAs will provide database tips, links to articles, and write-ups regarding code optimization and MySQL best practices. This will help our users understand better how to write efficient code and define what we mean by “good” and “bad”.

Conclusion

We are emphatically sorry to all of our customers impacted by this aspect of the new GRID system. Your disappointment and frustration has been heard and noted by us. We have never accepted the status-quo in ourselves and what we provide to our customers, which is why we’ve never been remotely satisfied with this situation and have acted as swiftly as we could to correct it. Since 1998 our company’s success has come from providing a great product and great service; not slick ads, referral payoffs and ultra low pricing. (mt) Media Temple is fiercely proud of the high standing we’ve earned in the hosting industry and we’ll continue to do so by working hard to exceed what you demand from your provider. Whether you are someone who has been with us for 9 years or 1 day, it is our hope that your patience will be rewarded by our efforts in this matter and in our pursuit to offer the most advanced hosting platforms in the industry.