Search
|
Related Items: Search |
|
|
Related Items: Search |
|
|
|
During the process of building the new query analysis feature for MySQL Enterprise Monitor 2.0, we thought the best way to test it at a nascent stage was to use it to tune our own application (since we use MySQL as the backend repository). What we found was actually quite interesting. It also showed that even to seasoned developers, who know that frameworks while helpful, often aren‘t the most direct, concise way to get things done, can often do very strange things that you don‘t quite expect.
For those of you that haven‘t heard about the feature itself, “query analysis“ takes all queries that are being processed by a MySQL server, normalizes them into something similar to a prepared statement form by removing literals, and then keeps track of total, min/max, average execution times, result set sizes, etc. at an aggregate level. It also takes snapshots of the “worst” examples of these queries, the ones with the highest execution time.
When we started using the first implementations of the feature on our own code, we found the following, interesting output:
What stuck out (at least to us), is that there is a lot of time spent toggling auto-commit state. In fact, if you add the "on" and "off" together, it's the second-most time consuming statement in our entire application! We thought we had this licked before we even looked at this query analysis data, because our application uses transactions all of the time, so we told DBCP to always return connections in auto-commit "false" mode. We even looked through what we thought was enough of the DBCP code to make sure this would actually work. So, what was causing these statements to run anyway? Well, the trick was, at this point during implementation, the server-side agent wasn't ready, so we were injecting this query analysis data via statement interceptors in the MySQL JDBC driver. So, we also setup the “worst” query to put in a stack trace in the comment field:
So, it was indeed coming out of some glue code we‘d written to wire DBCP into hibernate for our application (and still use our existing configuration mechanisms). Once the way was pointed, we set some appropriate breakpoints, and low-and-behold, we find this gem:
public void passivateObject(Object obj) throws Exception {
if(obj instanceof Connection) {
Connection conn = (Connection)obj;
if(!conn.getAutoCommit() && !conn.isReadOnly()) {
conn.rollback();
}
conn.clearWarnings();
conn.setAutoCommit(true);
}
if(obj instanceof DelegatingConnection) {
((DelegatingConnection)obj).passivate();
}
}
It makes sense to rollback when a connection is put back in the pool, as the application could‘ve misbehaved and started a transaction but didn‘t call commit() or rollback(). But, then, DBCP, without looking at how we‘ve configured this data source (to always be in auto-commit “false“), goes ahead and sets it to “true”.
So, what to do now? Should we internally fork DBCP, and keep merging this one-liner change every time we update DBCP? Do we file a bug, and wait for a new release of DBCP (we will, eventually). How do we fix it now? Well, once again, MySQL‘s JDBC interception facilities to the rescue. We just implement a very simple ConnectionLifecycleInterceptor that has the following implementation of setAutocommit(), which lets the caller setAutoCommit(false) and have it sent to the server, yet setAutoCommit(true) will never be sent to the server, and the JDBC driver will adjust its idea of autocommit state accordingly.
public boolean setAutoCommit(boolean flag) throws SQLException {
if (!flag) {
return true;
}
return false;
}
Of course, we had to test that nothing bad happened with our application using this trick, and when we determined that it was safe to operate in this manner, we ran query analysis again, and lo-and-behold, one issue solved, other statements to fix:
In my mind, the power of this feature is looking at query performance in aggregation. Seeing the SET … statements popping up in “SHOW PROCESSLIST” (which you‘d be lucky to catch, they‘re very short), or even in the general query log, wouldn‘t have demonstrated the amount of time wasted that we see here in our UI. Using this feature we have iteratively improved performance, watching with each release which queries bubble to the top, and tackling them.
For those of you that would like to see this feature in action on your own systems, if you‘re an existing MySQL Enterprise customer, you can get access to the beta release of MySQL Enterprise Monitor 2.0 at the MySQL Enterprise website.
For those of you that aren‘t yet existing customers, hold tight, soon we‘ll refresh the enterprise trial with this codebase.
In either case, feel free to ask us questions about the new features in our forums at http://forums.mysql.com/list.php?166
For those of you wanting to integrate query analysis with your application at a source-code level like we did with this example, hold tight as well and watch this space. MySQL Enterprise Monitor 2.0 supports REST as a way to populate the repository, and I‘ll be posting an example of how to do this with Connector/J and statement interceptors soon!