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.

Information about the queries is sent directly to the MySQL Enterprise Service Manager. Once you install the MySQL Enterprise Plugin for Connector/J, query analysis becomes available for your applications without requiring any modification to the application code.

Prerequisites

Installation

Place the JAR file lib/c-java-mysql-enterprise-plugin-version.jar in the application's CLASSPATH where it is visible to the version of MySQL Connector/J in use. Ideally, use the same location as MySQL Connector/J's JAR file, or in a parent classloader to that JAR file's location.

If the application being analyzed does not have Apache Commons Logging in the CLASSPATH, install the file lib/required/commons-logging-1.1.1.jar in the application's CLASSPATH as well. If no other component in your application uses Apache Commons Logging, install it in the same place where the Query Analyzer plugin was installed.

There is static shutdown() method on com.mysql.etools.jdbc.StatementPerformanceCounters, which can be used to cleanly shutdown the query analysis plugin when the application is going to be shutdown.

If the application in question is deployed in a J(2)EE application server, there is an included ContextListener distributed with the plugin which will call this method when the application's context is shutdown (or reloaded). Application Servers which support @WebListener (such as JEE6 and above) do not need to do any extra configuration, but users with older Application Servers need to add the following line to their application's web.xml file:

<listener> 
    <listener-class> 
        com.mysql.etools.jdbc.ContextListener 
    </listener-class> 
</listener> 

The Connector/J plugin prior to version 1.0.81 can use a workaround by placing the plugin in the shared library loader of their Application Server, rather than in the WEB-INF/lib directory.

Configuration

Once the required JAR files are deployed, modify the connection URL for JDBC to use and configure the Query Analyzer plugin. Add the following properties and their corresponding values to the URL that is used by the application you are monitoring.

For more information on Connector/J properties, see Connector/J (JDBC) Reference.

To use SSL to communicate query information to the MySQL Enterprise Service Manager, configure the following properties. Out of the box, the plugin is configured to work with the self-signed certificates that ship with the MySQL Enterprise Monitor. Therefore, it provides confidentiality for the information that is sent by the plugin if the service manager URL starts with https://. However, as configured, the plugin does not prevent man-in-the-middle attacks, nor does it support client-side certificates for authentication. To enable these features, change the values of the following parameters:

For example, to configure a Connector/J to communicate with the MySQL Enterprise Service Manager localhost, on port 18080, using the agent username agent, and password PASSWORD, you would add the following options to your connection string (but define the option on a single line):

statementInterceptors=com.mysql.etools.jdbc.StatementPerformanceCounters
  &serviceManagerUrl=http://localhost:18080/
  &serviceManagerUser=agent
  &serviceManagerPassword=PASSWORD

Remember that you must also add the application-specific options to the JDBC URL. For example, the following fragment connects to the MySQL database test on localhost, using the user and password of mysqltest, while also collecting query data and sending it to the MySQL Enterprise Service Manager on localhost:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

Connection conn = null;
...
try {
    conn =
       DriverManager.getConnection("jdbc:mysql://localhost/test?" +
           "user=mysqltest" + 
           "&password=mysqltest" +
           "&statementInterceptors=com.mysql.etools.jdbc.StatementPerformanceCounters" +
           "&serviceManagerUrl=http://localhost:18080/" + 
           "&serviceManagerUser=agent" + 
           "&serviceManagerPassword=PASSWORD"
       );

    // Do something with the Connection

   ...
} catch (SQLException ex) {
    // handle any errors
}

If an alternate logging system has not been selected for Connector/J, it is recommended that Connector/J's log factory is configured to use something other than the standard logger by adding the following property to the URL or DataSource:

Further configuration of the plugin is done via the Enterprise Service Manager's user interface, using the Query Analyzer tab. From here, the capture of query performance data for a given MySQL instance that the plugin is being with can be enabled or disabled. Further, the level of detail that is captured; summary, examples (with source code locations) and EXPLAIN plans can be configured as well.

The Query Analyzer plugin checks the configuration once each minute (while it is reporting data it has collected), thus configuration changes are not immediate.

Usage

After you set up MySQL Enterprise Plugin for Connector/J, you monitor the performance of your Java applications through the Query Analyzer tab, as described in Section 3.2, “Query Analyzer User Interface”.

Troubleshooting

Be aware of the following while using the MySQL Enterprise Plugin for Connector/J: