MySQL speed boost
I hit a problem the other day with concurrent queries causing deadlocks. Using innodb gives you a lot of protection with respect to transaction support, but it carries a moderate amount of overhead, and unless you’re aware of what’s going on, you may be paying a higher price which can eventually cause performance or deadlock issues.
FWIW, I thought I knew what was going on, and I *sort of* did, but not entirely.
This article at high scalability has some good introductory info, but I’ll cut to the chase as to what made a huge improvement for me.
Instead of standard BEGIN to start a transaction, I set a specific isolation level for just *one* query:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
This took my combined queries from 18 seconds down to 3. In addition to the 18 seconds average time, those 18 seconds were often going to 30-60 depending on what other concurrent queries were going on. The default ‘REPEATABLE_READ’ transaction level in InnoDB does a lot of locking (or waiting to be able to lock) data, and this was the root of my problems.
You need to understand what transaction isolation levels are doing, of course, but changing some queries to READ COMMITTED is still pretty safe for what I was doing there, and made a *HUGE* difference in speed. Of course, your mileage may vary, but definitely something to research if you haven’t yet and are facing performance issues.