If you use MySQL for any number of databases, and if you are not using the MySQL Enterprise Monitor (MEM) to manage your databases, then you are missing out on a great DBA tool.
“MySQL Enterprise Monitor is a companion product for MySQL Server that monitors your MySQL instances, notifies you of potential issues and problems, and advises you how to fix the issues. MySQL Enterprise Monitor can monitor all kinds of configurations, from a single MySQL instance that is important to your business, all the way up to a huge farm of database server machines powering a busy web site. “
The query analyzer function of the Enterprise Monitor helps you to analyze/tune your queries. It can tell you which queries are being run the most often, which ones are taking the longest to run, it can even help you find queries that are returning a zero result set, and other query “violators”.
But the caveat is that in order to use the query analyzer function, you have to redirect your MySQL traffic through a proxy server (configured during the MEM installation). Using the proxy server is an additional step that might be acceptible for testing on development servers, but it might not be a great idea in a production environment.
However, if you are using Java for your database connections, you can bypass the proxy server and send your query information directly to the MEM Service Manager. This is accomplished with the MySQL Enterprise Plugin for Connector/J.
“The MySQL Enterprise Plugin for Connector/J lets you use the Query Analyzer to monitor MySQL queries from any application using the Connector/J JDBC driver. As described in Section 3.2, “Query Analyzer User Interface“, the Query Analyzer can help you locate queries that are inefficient or slow. Tuning such queries helps to shorten load times for web pages, and improves overall system responsiveness and scalability.”
As stated, the information about the queries is sent directly to the MySQL Enterprise Service Manager. Once you install the MySQL Enterprise Plugin for Connector/J and tweak your Java database connection string, query analysis becomes available for your Java applications. And, the Enterprise Monitor can show you the location of the “offensive” Java code. Here is an example, with the code location highlighted in blue:
There are a few things that you need to do before you can use this plugin. The prerequisites are:
- MySQL Connector/J version 5.1.12 or later.
- JDK-1.5.0 or later.
- MySQL Enterprise Service Manager version 2.2 or later.
- The MySQL instance that the application uses must be monitored by a MySQL Enterprise monitoring agent. The mysql.inventory table must be accessible to the configured user for the Connector/J connection to get the UUID of the monitored server.
- Apache Commons logging in the CLASSPATH of the application being analyzed. If you are not already using Commons Logging, modify the application’s CLASSPATH as described in the following section to point to the JAR file bundled with the MySQL Enterprise Monitor product.
I don’t write Java code, but I wanted to be able to test this functionality. I found an example script on MySQL’s web site, and so I was able to modify it to send a test query to my installation of MEM.
I did run into one problem – I wasn’t setting my CLASSPATH variable correctly. To solve this problem, I edited my .profile file for the root user, and added the following JAR file locations to my CLASSPATH: (the colors are added for clarity)
I am running MySQL on a Mac Pro Server, so you will need to modify the file paths in your CLASSPATH to match your system.
After you have completed the prerequisites, you are ready to see if you can connect to the Service Manager with a query via Java. Here is a sample script that you can use (you will need to modify it your system parameters):
You will need to compile the script before running it. Here are the results from compiling and then executing the script: (since you are doing a SELECT SLEEP(120), the script will “sleep” for 120 seconds before finishing.)
And here are the results as show in the Query Analyzer tab of MySQL Enterprise Monitor:
By clicking on the SELECT sleep(?) link, I can then take a look at the query information:
For more complex queries, this tool really provides a wealth of information to help you tune your queries and increase the performance of your MySQL databases. MySQL Enterprise Monitor has a wealth of great features. For more information, see http://dev.mysql.com/doc/mysql-monitor/2.3/en/index.html.
|Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.|