Enterprise-Ready Database, Monitoring and Support with MySQL Enterprise

Copyright © 2008, Sun Microsystems Enterprise-Ready Database, Monitoring and Support with MySQL Enterprise Maximize Security, Performance and Availability with MySQL Enterprise Subscription Services August, 2008 Copyright © 2008, Sun Microsystems Page 2 Table of Contents Introduction ................................................................................................................................. 3 MySQL Enterprise – Database. Monitoring. Support............................................................... 3 MySQL Enterprise Server........................................................................................................... 4 MySQL Enterprise Monitor with Query Analyzer ..................................................................... 6 Production Support Services .................................................................................................. 11 MySQL Professional Services ................................................................................................. 14 Customer Success Stories ...................................................................................................... 14 Conclusion ................................................................................................................................ 17 Learn More................................................................................................................................. 17 About Sun’s MySQL Portfolio.................................................................................................. 18 Copyright © 2008, Sun Microsystems Page 3 Introduction Whether you are building and supporting commercial websites, distributing enterprise applications or engineering advanced communications networks, the technologies used to run your organization must be readily adaptable for you to remain competitive. This paper explores how you can confidently deploy MySQL, the “M” in the LAMP stack, as part of an innovative and cost-effective platform ideal for delivering internet-based applications. It examines some of the challenges associated with building and supporting scalable, data-driven online applications using, open source web 2.0 technologies and provides a detailed overview of MySQL Enterprise, which is comprised of database and monitoring software and production support services that are designed to alleviate these challenges. The MySQL Enterprise solution, backed by Enterprise-class service capabilities of Sun Microsystems, is specifically designed to mitigate risk and ensure you meet customer and end-user Service Level Agreements (SLAs). MySQL Enterprise – Database. Monitoring. Support. If you are currently using MySQL, it is a fair bet you started with the Community version. In fact, in many instances MySQL enters an organization via an application development project and makes its way into the data center when the application is promoted for production use. Challenges arise when these applications become vital to business revenues or key business functions. The most common challenges around running MySQL and other open source technologies are revealed by a simple line of questioning: • How will you ensure you are running the most reliable, secure, up-to-date version? • How will you know: o If one of your servers or applications is down? o When you have a replication master/slave synchronization or latency issue? o When something else is affecting the performance of your servers? • Will you know: o If your MySQL servers are configured to naturally scale and expand as your data and customer base grows? o How to set MySQL variables to ensure your systems run at their peak performance? • How will you: o Identify security loopholes in your MySQL servers? o Know when there have been security alterations on your MySQL servers? • How will you: o Optimize your database designs and queries before they are migrated into your production environments? o Ensure your replicated servers are configured correctly for performance and scalability? o Monitor and tune poorly performing user/application SQL code? • Are you willing to spend time to save money or spend money to save time? To help you answer these questions with confidence MySQL provides MySQL Enterprise. MySQL Enterprise is a subscription based offering comprised of database and monitoring software plus production support for organizations delivering highly available, business critical applications and services. MySQL Enterprise includes the following components: MySQL Enterprise Server – is the most reliable, secure and up-to-date version of MySQL that provides a cost-effective solution for: • Scalable Web 2.0 and Enterprise 2.0 on-line applications • Corporate OLTP Applications • Multi-terabyte Business Intelligence and Data Warehousing deployments Copyright © 2008, Sun Microsystems Page 4 • Complex communications networks • And more The MySQL Enterprise Server is configured for high performance and supports both “safe” and ACID (atomic, consistent, isolated, durable) compliant transactions with full commit, rollback, crash recovery and roll level locking capabilities. MySQL Enterprise Monitor and Advisors with Query Analyzer – is a distributed web application that you deploy within the safety of your corporate firewall. The Monitor continually monitors all of your MySQL servers and proactively alerts you to potential problems and tuning opportunities before they become costly outages. It also provides you with MySQL expert advice on the issues it has found so you know where to spend your time in optimizing your MySQL systems. MySQL Production Support Services – MySQL Enterprise includes 24 X 7 X 365 production support for your MySQL servers to help ensure your business critical applications are continuously available and running at their peak. MySQL Production Support Services include: • Online Self-Help Support – The knowledge base is a self-help tool that provides you with access to 2,000+ technical articles on MySQL specific topics that help quickly answer questions and solve problems. • Problem Resolution Support – Allows you to work directly with the MySQL Production Support team via phone, email or an online for quick resolution of technical problems. • Consultative Support – Allows you to work with MySQL Engineers on the proper installation, configuration and deployment of MySQL and its advanced feature set and on best practices around the design and tuning of schemas, queries and application specific code. • Advanced Support for MySQL High Availability and Scalability Solutions – MySQL Enterprise includes full production support for additional advanced MySQL features and third-party solutions to scale the availability and performance of your online applications. The remainder of this paper describes each of these components in detail. It also provides practical examples of how existing MySQL customers leverage their MySQL Enterprise subscriptions to lower their costs and scale their applications in support of their growing business. MySQL Enterprise Server As stated earlier, the MySQL Enterprise Server is the most reliable, secure and up-to-date version of MySQL. That said, any discussion around the MySQL Enterprise Server must include the MySQL Community Server as the two products are identical in features and production readiness. However, MySQL Enterprise provides the added value of the update services wrapped around the MySQL Enterprise server in the form of: • Monthly Rapid Updates • Quarterly Service Packs • Hot Fix Build Program Monthly Rapid Updates Monthly Rapid Update (“MRU”) releases provide you with the most current bug fixes (in source and binary formats) to the MySQL Enterprise server. Released on a regular, predictable schedule, each MRU is an accumulation of the bug fixes included in all predecessor MRUs. Note: While MRU releases are considered stable and production-ready, you are advised to standardize on the latest MRU release only if it includes a needed fix to a production hampering bug. Before Copyright © 2008, Sun Microsystems Page 5 upgrading you are encouraged to review the Release Notes for the MRU release you are considering to ensure it addresses a specific problem in your environment. Quarterly Service Packs Quarterly Service Pack (“QSP”) releases provide you with the most comprehensive set of bug fixes (again, in source and binary formats) to the MySQL Enterprise server. Also released on a regular, predictable schedule, each QSP is an accumulation of the bug fixes included in its predecessor MRU releases plus any addition fixes to bugs that have been reported against those same MRUs. Note: QSPs are recommended for your new MySQL installations or if you are on a regular upgrade maintenance schedule. As with MRU releases you are encouraged to review the Release Notes for the QSP release you are considering to ensure it addresses specific problems you are experiencing. Again, you are encouraged to standardize on a specific QSP and upgrade your production systems only when necessary. While the Community Server eventually receives the same bug fixes, they are made available on a less predictable schedule and are in the form of quarterly source and bi-annual binary releases. Release schedules for the Enterprise and Community Servers are depicted in Figure 1: Figure1: MySQL Enterprise Server vs Community Server Monthly/Quarterly Release Schedule Hot Fix Build Program Under the Hot Fix Build Program MySQL Enterprise subscribers who have a critical production problem or an outage can work with the MySQL Support and Engineering teams on a resolution that provides a bug fix binary outside of the regular MRU and QSP release schedule. All hot fixes are included in the next scheduled MRU and QSP release and are also made available to subscribers who report support cases against bug that has been fixed. As with the MRU and QSP hot fixes are provided in source and binary formats. Indemnification MySQL Enterprise protects your use of the MySQL Enterprise server product with intellectual property infringement indemnification. MySQL understands that organizations often have concerns about intellectual property infringement issues when making the decision to obtain software for use in building business-critical enterprise applications and revenue generating product lines. MySQL considers the integrity of its software code to be a valuable asset. Therefore, the MySQL indemnification program has been created to eliminate these concerns and mitigate any potential risk in the unlikely event of a covered intellectual property infringement claim. Copyright © 2008, Sun Microsystems Page 6 The Enterprise Server database software, Update Services and Indemnification are all designed to save you and your DBA staff time (and money) by identifying which versions of the MySQL server you should be running, keeping you in front of slow response times and costly outages with predictable maintenance releases and safeguarding your deployments from unwarranted IP claims. MySQL Enterprise Monitor with Query Analyzer One of the biggest challenges for those tasked with developing and supporting applications built on MySQL is that over time MySQL servers tend to propagate rapidly, especially when MySQL is deployed as part of a scale-out strategy. A recent survey of MySQL users revealed that the average MySQL DBA is supporting 5-10 physical MySQL servers. The same survey revealed that, on average, those using MySQL Replication to scale high traffic web applications add a new read slave to their topology every 2 months. To help manage more MySQL with less time and effort MySQL Enterprise includes the MySQL Enterprise Monitor. Running completely within your datacenter, the Enterprise Monitor is a “Virtual DBA” assistant that helps your DBA staff keep your MySQL servers running at the highest levels of security, performance and availability. The Monitor proactively monitors your MySQL systems and provides expert advice on how even those new to MySQL can tighten security, tune application code and find and fix problems before they can become serious problems or costly outages. Architecture – Built on Web 2.0 Technology The MySQL Enterprise Monitor is a distributed web-application that is typically installed within the safety of your corporate firewall. It is comprised of three main components: • a lightweight Service Agent which is deployed to each of your monitored MySQL servers to collect MySQL/OS specific metrics and Query activity, • a centralized Service Manager that stores and evaluates the collected data and SQL code and a • a web-enabled Enterprise Dashboard that serves as the portal for the MySQL Enterprise Monitor. The architecture for the MySQL Enterprise Monitor and each of its components is shown in Figure 2: Figure 2: MySQL Enterprise Monitor Architecture Copyright © 2008, Sun Microsystems Page 7 Enterprise Dashboard One of the biggest challenges MySQL DBAs face is managing scalability and performance across an ever-growing number of MySQL servers and databases. Regardless of the size of your MySQL environment, each server requires specific attention when it comes to basic administration, security, performance monitoring and availability. Dynamic, replicated environments must be documented to reflect current Master/Slave relationships as new capacity is added for scale-out to ensure you can monitor for scalability related latency issues. To give you a proactive advantage in all of these areas, the MySQL Enterprise Monitor provides the Enterprise Dashboard. Using the Enterprise Dashboard, you can monitor MySQL and OS specific metrics for single servers or complete application groupings, and can quickly determine where you have real and potential performance issues. The Enterprise Dashboard is designed so you can easily understand the complete security, availability, and performance picture of all your MySQL servers in one place, all from a thin, browser-based console. The Enterprise Dashboard is shown in Figure 3: Figure 3: MySQL Enterprise Dashboard Automated Advisors The Enterprise Monitor differs from traditional monitoring tools in that it provides proactive vs reactive monitoring, enforces MySQL recommended best practices for scalability and performance, and provides you with expert advise and guidance on how to fix problems and tune your MySQL installation and applications for optimum performance and scalability. To this end, the Enterprise Monitor provides a set of smart, automated MySQL Advisors that are used to monitor MySQL and OS specific metrics and alert your key IT staff when things fall outside of MySQL recommended best practices. The Enterprise Monitor ships with the following set of Advisors: Copyright © 2008, Sun Microsystems Page 8 Figure 4: MySQL Enterprise Advisors • Administration Advisor – Monitors and advises on problems relating to general database administration, recoverability and performance configuration settings. • Security Advisor – Monitors and advises on security vulnerabilities in a MySQL server. • Upgrade Advisor – Monitors and advises on specific bugs that can potentially impact versions of MySQL deployed within an environment. Advises on specific upgrade path to a version of MySQL that provides a fix for identified bugs. • Custom Advisor – Allows you to define best practices to fit your organization’s specific needs or support policies. • Replication Advisor – Monitors and advises on issues related to replication set up, synchronization or performance problems. • Memory Usage Advisor – Monitors and advises on the optimal use of memory or cache settings for specific MySQL implementations. • Schema Advisor – Monitors and advises on unexpected changes to database schema and objects. • Performance Advisor – Monitors and advises on MySQL performance variable tuning as user and workloads grow over time. Each of the MySQL Advisors is designed to cover specific DBA “areas of concern” and is comprised of a set of MySQL Advisor Rules that help you proactively find problems and tuning opportunities you may not have the time or expertise to find yourself. The Enterprise Monitor employs 120+ Advisor Rules that monitor 600+ MySQL and OS specific metrics to track and proactively alert and advise you on any problems or tuning opportunities that could negatively impact the scalability and performance of a business-critical, online application. Each of the Advisors and Rules can be configured with customized thresholds, alerts, and SMTP or SNMP-enabled notifications so DBAs can right size the information and alerts they get based on application specific criticality. Right out of the box the MySQL Enterprise Advisor rules save you time! No more writing, testing, maintaining, versioning, deploying and scheduling custom scripts that would cover the same ground. In most cases, the MySQL Advisors can be downloaded, installed, scheduled and monitoring all of the MySQL servers in a datacenter in under 30 minutes (on average for the typical MySQL deployment). If you have advanced monitoring needs, the MySQL Advisors can be extended to include custom Advisor Rules that monitor existing metrics or others that you opt to collect from a monitored MySQL server. You can easily copy and edit the MySQL provided Advisor Rules to better meet your organization’s specific needs or create Rules and evaluated expressions from scratch using the MySQL provided template and the MySQL and OS variables and counters collected by the MySQL Enterprise Monitor. You can also add custom problem descriptions, required notifications, specific actions to be taken and links to your internal, organizational documentation or SOPs. Copyright © 2008, Sun Microsystems Page 9 Advanced Monitoring for Scale-Out MySQL Replication provides a robust solution, enabling Web and Enterprise 2.0 businesses to further leverage MySQL’s scalability. Managing MySQL in such an environment has been a continual challenge, particularly when it comes to documenting slaves added to service growing read loads and understanding or keeping track of the resulting master/slave hierarchical relationships. This is a common DBA pain point and can be the result of several factors: • MySQL Replication topologies are often not documented or existing documentation is not maintained to reflect hierarchy changes and additions. • For scale out, read slaves can easily be added to an existing replication topology to keep up with demand and SLAs. In this scenario, status checking of new slaves is only as good as the last time the documented topology inventory was updated. • Slaves are commonly promoted to master to correct master/slave out of synch conditions. • DBAs new to MySQL or to an existing MySQL Replication topology may not understand the hierarchies they are inheriting and often must spend upfront time learning the relationships that are in place. The Enterprise Dashboard makes scaling applications with MySQL Replication easier by providing industry-leading auto detection, grouping, documenting and monitoring of all MySQL Replication master/slave hierarchical relationships. Changes and additions to existing replication topologies are also auto detected and maintained providing instant visibility into newly implemented updates. This help