MySQL Connector J and the curse of Autocommit

In my travels I do a lot of analysis of the slow query and the general query logs. In many Java shops I end up seeing the most frequently called command is

set autocommit=1;

Sometimes this statement is called 300-400x more then any other statement. What’s surprising to most of these shops, is that they are leaving autocommit on… so logically they are baffled why the statement is appearing over and over again. You see by default the autocommit statement wraps the calls to the database. So if you trace the calls you will see:

set autocommit=1;
select * from sometable
set autocommit=1;

Yep, even select statements see this behavior. So what, Its a small set statement right? . But that small set statement has to traverse the network, be parsed, be executed, then return something. Lets assume that adds 2ms to your transaction, over the course of a day that’s going to really add up. Over a million queries that means you would waste 30 total minutes of time just doing something that does not need to be done. But 2 ms is probably too low. To help illustrate this I can share a war story with you. I was working with a client that had the curse of the autocommit. They were benchmarking their system and were able to process about 5500 selects per second. We turned off the autocommit and went from executing 5500 selects per second to 7000 selects per second. That’s a nice jump for something soooo small. How do you turn it off?

Well looking though the docs you will find the parameter elideSetAutoCommits. The docs state:

“If using MySQL-4.1 or newer, should the driver only issue ‘set autocommit=n’ queries when the server’s state doesn’t match the requested state by Connection.setAutoCommit(boolean)?”

That seems like a win for most people. So those java fans out their, you may want to check how many sets your database is doing ( show global status like ‘%set%’ ), and you may want to test setting elideSetAutoCommits=true.

This entry was posted in Common Mistakes, jdbc, Matt, mysql, performance. Bookmark the permalink.

3 Responses to MySQL Connector J and the curse of Autocommit

  1. Shlomi Noach says:

    Hi,

    Good post. I’ve seen systems working with Spring-framework / Hibernate which indeed call for more “SET” commands than SELECTs, UPDATEs, INSERTs and DELETEs combined.
    Most are SET AUTOCOMMIT and SET NAMES. The frameworks also issue tons of SHOW VARIABLES to learn about isolation mode (oh, and they set TX_ISOLATION as well…).
    The defaults are really overwhelming, and should be carfully cut down. If the connector and database are properly configured, no reason in calling so many SET and SHOW all the time.

  2. Darren Oldag says:

    This is great advice, but also be aware that connection pool implementations may exacerbate this autocommit issue. See Mark Matthews blog on what he found when he ran Query Analyzer on the Enterprise Monitor itself…

    http://www.jroller.com/mmatthews/entry/huh_i_didn_t_know

  3. Pingback: Big DBA Head! - Database Brain Power! » MySQL Connector J and the curse of show variables