Connecting JBDC to MySQL Enterprise Monitor’s Query Analyzer

October 11th, 2010 by Leandro Morgado

With the release of MySQL Enterprise Monitor (MEM) 2.2, there is now the ability to monitor queries using the Query Analyzer (QUAN) without needing the agent proxy to be running. You can use a .NET or JDBC connector plugin to directly gather the query statistics. In the example below, we will use the MySQL Enterprise Plugin for Connector/J.

First, make sure both the Connector/J, the Connector/J plugin and the Apache Commons Logging jars are in the $CLASSPATH. At the time of writing, these are the files needed:

mysql-connector-java-5.1.12-bin.jar
c-java-mysql-enterprise-plugin-1.0.0.42.jar
required/commons-logging-1.1.1.jar

Then, add the plugin to the connection string so that it changes from something like this:

conn =	DriverManager.getConnection("jdbc:mysql://localhost:3306/test?"+"user=root&password=PASSWORD");

to something like this:

conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?"
       +"user=root&password=PASSWORD"
       +"&statementInterceptors=com.mysql.etools.jdbc.StatementPerformanceCounters"
       +"&serviceManagerUrl=http://memserverhost.com:18080/"
       +"&serviceManagerUser=agent&serviceManagerPassword=AGENTPASSWORD");

Here is the full example code:

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 = null;
		conn =	DriverManager.getConnection("jdbc:mysql://127.0.0.1:33306/test?"+"user=root&password=PASSWORD"+"&statementInterceptors=com.mysql.etools.jdbc.StatementPerformanceCounters&serviceManagerUrl=http://memserverhostname.com:18080/&serviceManagerUser=agent&serviceManagerPassword=AGENTPASSWORD");
 
		//Get a Statement object
	      	Statement stmt = conn.createStatement();
    		ResultSet rs = stmt.executeQuery("SELECT SLEEP(10)");
		System.out.println("Closing connection");
		rs.close();
		conn.close();
        } catch (Exception ex) {
            // handle the error
	    ex.printStackTrace();
        }
    }
}

Now, when you Java application runs queries, they will be analyzed by QUAN. In my example code, there is a sleep call for 10 seconds. You can clearly see that this query was caught by QUAN:

Slow queries fed to QUAN using the Connector/J plugin

This now eliminates the need for the monitoring agent to be running it’s own instance of mysql-proxy for QUAN. Java and .NET applications can simply use the interceptor, saving on resources.

Of course, prior to setting this up, you should always check the online documentation.

Leave a Reply

You must be logged in to post a comment.