OK, you found the problem SQL statement that was affecting your server’s performance, now where did it originate?
The new MySQL Enterprise Plugins for Connector/J and Connector/NET send query statistics, including the source location for each query, directly to the MySQL Enterprise Monitor.
Figure 1 is a screenshot of new source location feature.
Figure 1. Source Location
Figure 2 shows the standard query statistics, which are collected in the query analyzer. In both cases, the statistics are gathered by the MySQL Connector and the Plugin, not MySQL proxy.
Figure 2. Query Analyzer
If you’re a MySQL Enterprise customer, you can download the new monitor and plugins from customer.mysql.com. If you’d like to try it out, a 30 day trial is available at
http://www.mysql.com/trials/
Please review the help file and readme for full instructions, but here are a few of the highlights for setting up monitoring for Java applications. (I’ll provide further instructions for .NET in a future post.)
1. Set up the MySQL Enterprise Monitor and Agent. The agent resides on the MySQL production server and the monitor should reside on a non-production server within your environment. Although the Plugin gathers the query statistics, the agent needs to run and connect to the monitor at least once so the Monitor can associate the data with the appropriate server.
2. Download the following files from the customer portal (customer.mysql.com) and add them to your classpath:
- Connector/J version 5.1.12 or newer
- Plugin for Connector/J c-java-mysql-enterprise-plugin-1.0.0.42.jar
3. Change your JDBC url. In my case, I changed it from the standard JDBC url, “jdbc:mysql:127.0.0.1:3306/sakila , to
“jdbc:mysql://127.0.0.1:3306/sakila?statementInterceptors=com.mysql.etools.jdbc.StatementPerformanceCounters& amp;serviceManagerUrl=http://[mymonitorURL.com]&serviceManagerUser=[myagentusername]& serviceManagerPassword=[myagentpassword]l”;
Full instructions are in the readme. Substitute appropriate URL and credentials for the bracketed [ ] items above.
4. Finally, the mysql user account that is used by the application needs to have SELECT privilege to the mysql.inventory table. This table includes a unique identifier which the monitor uses to match the data with the MySQL instance. I executed the following:
mysql> GRANT SELECT ON mysql.inventory TO ‘elgato’@'%’;
I found it helpful to monitor the application log file for error messages. The readme includes additional logging information. In my tomcat test application, the log information was sent to /var/logs/tomcat6/daemon.log file.
I hope this is helpful information. If you have any issues, feel free to post a comment on this blog post or post on the forums:
http://forums.mysql.com/list.php?166
