Using Java to Connect to MySQL Enterprise Monitor with Plugin for Connector/J

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.

From http://dev.mysql.com/doc/mysql-monitor/2.3/en/mem-introduction.html:

“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.

From http://dev.mysql.com/doc/mysql-monitor/2.3/en/mem-qanal-using-cj.html:

“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)


export set CLASSPATH=/Applications/mysql/enterprise/monitor/apache-tomcat/webapps/ROOT/WEB-INF/lib/mysql-connector-java-commercial-5.1.17-bin.jar:/Applications/mysql/enterprise/monitor/apache-tomcat/webapps/ROOT/WEB-INF/lib/c-java-mysql-enterprise-plugin-1.0.0.61.jar:/Applications/mysql/enterprise/monitor/apache-tomcat/webapps/ROOT/WEB-INF/lib/required/commons-logging-1.1.1.jar:

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):


import java.io.IOException;
import java.sql.*;

// Notice, do not import com.mysql.jdbc.*
// or you will have problems!

public class LoadDriver {
   public static void main(String[] args) {

         try {
            // The newInstance() call is a work around for some
            // broken Java implementations

            Class.forName("com.mysql.jdbc.Driver").newInstance();

            Connection conn = DriverManager.getConnection("jdbc:mysql://192.168.1.2/database_name?"+
               "user=root&password=root_password"+
               "&statementInterceptors=com.mysql.etools.jdbc.StatementPerformanceCounters"+
               "&serviceManagerUrl=http://192.168.1.2:18080/"+
               "&serviceManagerUser=agent"+
               "&serviceManagerPassword=agent_password");

            //Get a Statement object
               Statement stmt = conn.createStatement();
               ResultSet rs = stmt.executeQuery("SELECT sleep(120)");
               System.out.println("Closing connection");
      

               conn.close();
               } catch (Exception ex) {
               // handle the error
               ex.printStackTrace();
         }
      }
}

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.)


macserver01:java root# javac LoadDriver.java
macserver01:java root# java LoadDriver
Fri Oct 14 14:08:57 EDT 2011 DEBUG: Using direct result set size methods.
Fri Oct 14 14:08:57 EDT 2011 DEBUG: Using batch-aware prepared sql extractor.
Fri Oct 14 14:08:57 EDT 2011 INFO: MySQL Enterprise Plugin for Connector/J version 1.0.0.61 started for MySQL instance 53e71576-026c-4798-961b-0025efc37db2.
Fri Oct 14 14:08:57 EDT 2011 DEBUG: GET http://192.168.1.2:18080/v2/rest/instance/mysql/StatementAnalysisSupport/53e71576-026c-4798-961b-0025efc37db2
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: application/json
Transfer-Encoding: chunked
Date: Fri, 14 Oct 2011 18:08:57 GMT

{
    "name": "53e71576-026c-4798-961b-0025efc37db2",
    "parent": "/instance/mysql/Server/53e71576-026c-4798-961b-0025efc37db2",
    "values":     
{
        
"auto_explain_min_exec_time_ms": 1,
        "capture_examples": "true",
        "capture_explain": "true",
        "enabled": "true",
        "frequency":
null
}
}
Fri Oct 14 14:08:57 EDT 2011 DEBUG: Configuration is: com.mysql.etools.jdbc.StatementAnalysisConfiguration@a14e84[captureExamples=true,captureExplains=true,enabled=true,explainPlanThresholdMicros=1000,usingDefaults=false]
Fri Oct 14 14:08:57 EDT 2011 DEBUG: GET http://192.168.1.2:18080/v2/rest/instance/mysql/StatementAnalysisSupport/53e71576-026c-4798-961b-0025efc37db2
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: application/json
Transfer-Encoding: chunked
Date: Fri, 14 Oct 2011 18:08:57 GMT

{
    "name": "53e71576-026c-4798-961b-0025efc37db2",
    "parent": "/instance/mysql/Server/53e71576-026c-4798-961b-0025efc37db2",
    "values":
{
        "auto_explain_min_exec_time_ms": 1,
        "capture_examples": "true",
        "capture_explain": "true",
        "enabled": "true",
        "frequency":
null
}
}
Fri Oct 14 14:08:57 EDT 2011 DEBUG: Configuration is: com.mysql.etools.jdbc.StatementAnalysisConfiguration@a14e84[captureExamples=true,captureExplains=true,enabled=true,explainPlanThresholdMicros=1000,usingDefaults=false]

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.
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 38 other followers