How the MySQL Enterprise Upgrade Advisor Helps DBAs Avoid Being Bitten by Known Bugs

In an earlier article I described how MySQL Enterprise takes the guesswork out of deciding which version of the MySQL server customers should be running by providing alerts around regularly scheduled Monthly Rapid Update and Quarterly Service Pack releases of the Enterprise Server. Being of an old school "if it ain't broke don't fix it" mindset, I understand the conservative approach most DBAs take when deciding if a new release of any software is relevant to their environment. In fact, given the monthly frequency of Enterprise maintenance releases and the work involved with upgrading, I completely understand how recipients can begin to ignore Update Alerts (unless of course a known fix is on the way). Based on feedback from customers, MySQL colleagues, and my own field experience, I recognize that while notifications around the regular Enterprise Server drops is a good thing, upgrading an existing MySQL implementation is no small task and that a major part of removing guesswork around new releases involves helping those receiving notifications better understand how they are affected.

While many customers pour over the regular Update Alerts we shoot out, our challenge as a collective Engineering team has been to help all customers standardize their applications on the most up to date, bug-free version of MySQL, meaning we need to provide an additional safety net for those who are too busy to review the Update Alerts they receive. Also, what about older, version specific bugs that lay in wait ready to create a security breach or crash condition based on certain combinations of user, database, SQL or data activity? Even smooth running systems can be impacted by bugs that have been previously reported and fixed in newer releases of MySQL. How can we help ensure such bugs are proactively identified and squashed before they surface?

In an effort to provide DBAs with a belt-and-suspenders support and alert system and to further help take the guesswork out of deciding which version of the MySQL server they should be running, MySQL Enterprise now provides the new Upgrade Advisor. The premise is simple; the Upgrade Advisor provides monitoring for specific security and crash inducing bugs that affect current versions of the MySQL Community or Enterprise Server running in an Enterprise Monitor protected environment. It also provides the recommended upgrade path to a version of MySQL that fixes any identified problems.

What is MySQL Enterprise?

Before we delve into the Upgrade Advisor, let's get a quick understanding of the MySQL Enterprise components and how they work. In short, MySQL Enterprise is MySQL's commercial offering for businesses who want to standardize on the most up to date, secure version of MySQL. To this end, it provides the software and services needed by these companies to be successful with MySQL. MySQL Enterprise is comprised of the following:

1. Database. The MySQL Enterprise Server represents a version of MySQL that has been validated by both the MySQL Community as well as MySQL internal QA processes. In addition, it is updated with regular Monthly Update and quarterly Service Pack releases to ensure the highest levels of quality and performance. Monthly and Quarterly releases of the Enterprise Server are announced to MySQL Enterprise subscribers via version specific Update Alerts, which provide access to the binary downloads and a detailed summary of the bugs fixed in the new release.
2. Monitoring. The MySQL Enterprise Monitor is a distributed web application that is deployed within the safety of the customer firewall. The Monitor provides a global, consolidated view into the health, performance and availability of all MySQL servers and related topologies. The Monitor helps customers enforce MySQL (and customer) best practices and proactively avoid problems and outages, primarily through the deployment of automated Advisors and rules. You can learn more about the MySQL Enterprise Monitor and Advisors here: http://www.mysql.com/products/enterprise/monitor.html.
3. Support. Production Support provides 7X24 technical resources and support directly from MySQL engineers for those who have deployed MySQL as part of their business critical applications. Product support services include:
   • Self help support (Online Knowledge Base)
   • Problem Resolution Support
   • Consultative Support

For this particular article I will focus on the MySQL Enterprise Monitor, specifically the Upgrade Advisor, which is new in the Spring 2008 Release.

What's the Problem To Be Fixed?

When I first started using MySQL I downloaded the Community version of the server (4.0.1), built and tested my online application, and then deployed it into production. It wasn't long before increased user demand and reporting requirements "encouraged" me to explore and to upgrade my server to 4.1 and to implement MySQL replication and the concept of the read-only slave. In fact, adding read slaves proved to be a great way to service higher user loads so I quickly found that my modest single server deployment had quickly turned into a multi-server farm running several versions of MySQL. Jumping to my current lab, despite my best attempts at standardization, I now have implemented an environment that I believe to be representative of many a MySQL shop, namely I have several different versions of the server running with no plans to upgrade unless I absolutely have to! The problem with this scenario is like myself, most DBAs do not have time to standardize all of their MySQL servers on a specific version or do not schedule upgrades until they have a project or problem that dictates they do so. The inherent problem is that with different versions of MySQL running in the datacenter, how does a DBA know when a multi-version environment is vulnerable to specific bugs? Further, if they do plan to upgrade how do they know which minimal version of the Community or Enterprise Server they should consider before taking on the work? Enter the Enterprise Monitor and the new Upgrade Advisor!

How the Enterprise Monitor Works

To understand the Upgrade Advisor it helps to first explore how the Enterprise Monitor is typically deployed within the datacenter. As mentioned earlier, the Enterprise Monitor is a distributed web application that runs within the corporate firewall. It is comprised of the following components:

• Service Agent – The Service Agent is written in C, is installed on all monitored servers and collects performance and security metrics on MySQL and the OS.
• Service Manager – The Service Manager is written in Java servlets exposed as web services. It collects and monitors data from all agents and sends out proactive alerts on problems and tuning opportunities via the Enterprise Dashboard and defined notification options.
• Repository – The Repository is a MySQL Enterprise database that holds the MySQL and OS performance and security data collected by all agents.
• Enterprise Dashboard – Written in JSP, the dashboard provides a graphical interface to all monitored MySQL servers and related Advisor reported events.

At the lowest level, the agent collects data about the MySQL server it is monitoring and reports the data back to the Service Manager via a regular "heartbeat" in the form of xml serviced over HTTPS:

The packet of data returned for each of the monitored servers includes a full inventory of MySQL and OS specific "gear", including information on the versions of MySQL servers running on various ports. This information is stored in the MySQL Enterprise repository and is used by the Service Manager to populate the Enterprise Dashboard and by the MySQL Advisors and rules to monitor the health of the MySQL instances reporting in. Each of the Advisors provides rules that key on specific metrics stored in the repository. Individual rules evaluate the server specific data against MySQL or user supplied expressions and thresholds to create alert and notifications when a server requires some level of proactive attention. For rule "violations", visual alerts are sent to the Enterprise Dashboard and optionally sent via SMTP to email distribution lists or to SNMP enabled network nodes for escalation through an existing notification or ticketing system.

How Does the Upgrade Advisor Work?

The Upgrade Advisor is similar to the existing MySQL Advisors in that it contains rules with expressions that evaluate data collected for specific MySQL servers. In the case of the Upgrade Advisor rules, MySQL version specific data is compared to security or crash related bugs that are defined in the MySQL bug database and users are notified if they are running a version of MySQL that is affected by a bug that has been fixed in a newer version of the MySQL Community or Enterprise Servers.

The specific rules provided by the Upgrade Advisor can be automated to run and advise on all or defined groups of MySQL servers, replication topologies, etc. and are designed to keep users aware of potentially serious security or crash related bugs so they can proactively plan an upgrade path. Further, when a problem is found, the generated alert and notification includes which minimal version of MySQL to upgrade to, completely taking the guesswork out of the equation.

Like the other MySQL Advisors users are encouraged to use the MySQL provided rules to extend or to create their own rules that best fit their specific needs. To this end, the Monitor provides a straightforward editor that exposes the properties of each rule:

Using the rules editor, users can edit the Upgrade Advisor rule expressions to include additional MySQL or OS specific variables, to set thresholds, to define execution frequencies, to add links to internal documentation, to add specific advise or instructions on how to address the problem, etc. The new or updated rule then becomes available for scheduling as noted above. More information on customizing the Enterprise Monitor for specific use cases can be found here: http://dev.mysql.com/tech-resources/articles/mysql-enterprise-monitoring.html.

In addition, MySQL Enterprise will ship new Upgrade Advisor rule additions as new versions of the Enterprise Server are released so Enterprise Monitor users can continually scan their servers for problems and plan their upgrades to newer, bug-fix relevant releases of the MySQL Community and Enterprise Servers.

Conclusion

Managing change is a huge challenge for today's DBA and development staffs. Whether it is a production server or a key development environment, when servers are stable they are best left alone. DBAs charged with maintaining the performance and availability of these environments need assurance that even beneficial changes are managed so they can proactively plan upgrades. To this end, the true challenge is access to the right information, at the right time, to ensure the right things are done.

The MySQL Enterprise Update and Alert Service is designed to help keep MySQL systems available and running at peak performance by proactively alerting on updates to MySQL and the environment in which it runs. Monthly Updates and Service Packs deliver regular bug fixes to the Enterprise Server ensuring production and development servers have the most up to date, stable version of MySQL running at all times. As a busy DBA, I appreciate that while MySQL Enterprise sends out frequent updates and alerts, I only receive those that are specific to my use of MySQL, but even then it is hard to know when I should consider upgrading my installations. The new Upgrade Advisor rounds out the MySQL Enterprise Software Update service by providing a safeguard for DBAs that simply do not have the bandwidth to comb through new MySQL release information to determine if a Monthly or Quarterly Service pack is meant for them. It helps them proactively plan upgrades to specific version of the Enterprise Server before known bugs can turn into outages.

For more information on MySQL Enterprise and the Enterprise Monitor and Advisors, or to try MySQL Enterprise for yourself, please visit http://www.mysql.com/products/enterprise/.

Thanks, as always, for supporting MySQL!