One thing I learned fairly early in my DBA career was that if I was ever going to excel in managing the databases under my care, I needed to do one thing: smartly automate as much of my job as I could. This became particularly important in my last full-time DBA spot where one other guy and I had responsibility for about 120 Oracle, SQL Server, and DB2 databases. A lot of these databases ran critical applications with lots of users, so we were rarely bored at work. To keep our heads above water, we needed to do something to ensure we always had our finger on the pulse of all our servers, plus we needed to make sure that any performance issue was recognized as early as possible.

I ended up writing an in-house monitoring and capacity planning system that had a Web front end with a lot of moving parts under the covers. It worked pretty well for what we needed, with me even providing an end user dashboard where users could go to check on their applications before ever calling me (probably one of the smartest things I did in the system…) The problem was that as we grew, I ended up spending more and more time enhancing the home-grown monitoring solution, plus I became aware that there were shortcomings in the system that I couldn’t really overcome on my own. This, unfortunately, is the normal life cycle of home-grown help aids.

For many years, MySQL professionals have had to travel down this path or massage limited free offerings to meet the needs of their IT infrastructure. As I’ve traveled and spoken to many MySQL customers, I’ve heard about the pains they have with doing such things, even in very technically-savvy shops. In addition, I’ve fielded the complaint that the MySQL server can sometimes be too much of a “black box” when it comes to knowing if the server is running well or not. Basically, DBAs need to be able to easily tell if MySQL is responsible for a perceived slowdown in overall performance, or if the problem exists somewhere else (e.g. the network, etc.)

The good news is that we’ve taken all this user input and have recently introduced a new service within the MySQL Enterprise offering that can help. The MySQL Network Monitoring and Advisory Service has been designed to eliminate the need for building home-grown MySQL monitoring solutions, plus it helps jump start folks who are new to MySQL and unsure about how best to configure and tune MySQL for optimal performance. Perhaps most importantly, it lets you get ahead of performance issues before they cripple you key applications, and it lets you extend the service with customizations that are needed for your particular environment. Let me show you just a few reasons why you as a DBA will like what you see in the Monitoring and Advisory Service.

What is the Monitoring and Advisory Service?

First, let’s get a quick understanding of what the Monitoring and Advisory Service is and how it works. Provided as part of MySQL Enterprise, the MySQL Network Monitoring and Advisory Service is a “Virtual DBA” assistant helps manage all your MySQL servers with respect to performance tuning and general best practice management. In other words, how best to setup and maintain your MySQL servers for optimal uptime and response times. Running completely within your firewall (see Figure 1), the Monitoring and Advisory Service will monitor your database environment and provide expert advice on how to fix any best practice deviations it finds.

Figure 1 – The Monitoring and Advisory Service Architecture

Briefly, the architecture consists of the following components:

1. The Service Manager, which is the “brains” of the service. It controls the various aspects of what goes on behind the scenes of monitoring servers and notifies DBAs of issues that are found. The Service Manager is written in Java Servlets.
2. The Service Agent, which carries out the actual interrogation of each MySQL server (both the server machine and the MySQL Instance) with respect to up/down information and performance statistics. The Service Agent is written in C.
3. The Repository, which is used to hold current and historical statistics regarding the health of each monitored server. The repository is (obviously…!) a MySQL database located somewhere on your network.
4. The Enterprise Dashboard, which is a web-based interface that allows a DBA to visually see everything that is going on in their MySQL environment and manage various aspects of the service. The Enterprise Dashboard is written in PHP.

Powering the Monitoring and Advisory Service are a set of best practice Advisors, each of which consists of numerous rules that are used to enforce standards – around-the-clock if desired – for security, performance, replication management and more. These rules have been written by the pro’s at MySQL who know what makes the database server tick and how best to secure and tune things so no unpleasant surprises come up. In addition to the out-of-the-box supplied Advisors and rules, the service can be customized through you adding your own rules or tweaking the existing rules so they exactly match what you need.

With a brief understanding of what the Monitoring and Advisory Service is, and how it functions, let me now show you the things I think you’ll like about it from a DBA perspective.