Killing idle transactions to increase database uptime
Killing long-running idle transactions is a good way to increase the uptime of a MySQL server. This may sound strange, but open transactions will eventually bring the server down, and it is better to hurt a single application than the many that will be hurt when that happens.
Long-running idle transactions are usually caused by a programmer mistake or an unexpected condition that causes an application not to be able to do its work. The potential number of sources for such problems is unlimited, so it’s virtually impossible to prevent long-running transactions. You can find and solve them when they happen, but you can’t ensure that you’ll never get one from an unexpected source (because, by definition, the source is unexpected).
That is why it’s a good idea to run an idle-transaction killer. There are also other types of things you can profitably kill and help your uptime even more, but those are sometimes more complex to identify. A long-running idle transaction is easy to identify and kill.
The pt-kill program from Percona Toolkit is one query-killer program. I think it can be made easier to use and more intelligent. This will make it easier in the future for DBAs to set-and-forget.
What I find interesting about this, is that a lot of the time I find myself killing idle connections trying to hunt idle transactions (there’s not a 1:1 – but the meta data is more easily exposed for connections.)
In 5.5/InnoDB plugin there is SELECT * FROM information_schema.innodb_trx\G – which is useful to find idle transactions. Would it make sense to have a feature in pt-kill to mine this?
(For most applications reconnecting as a sort of “false positive” disconnect usually does not cost that much.)
Morgan Tocker
13 Aug 12 at 10:58 am
Morgan,
If you are using Percona Server you can have it kill idle transactions automatically:
http://www.percona.com/doc/percona-server/5.5/management/innodb_kill_idle_trx.html
Justin Swanhart
13 Aug 12 at 8:20 pm
pt-kill does not have the ability to look at anything other than the processlist, so for my purposes I can’t use it as-is right now. I would like it to be more flexible and consider a broader variety of information about connections and queries.
Xaprb
14 Aug 12 at 11:37 am