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
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.