Thank you for sharing your experience, Howard. As those are replica servers, I don't care much about losing a second worth of data in case of power failure. I believe the data centre has double independent power sources, and my hardware man assured me if the power goes down at the data centre we'll have bigger issues to worry about. As a result, I can run with innodb_flush_log_at_trx_commit = 2 without worrying too much about it.
The most interesting thing that came out of all this conversation is that everybody seems to agree this is some sort of lock contention on a lock that only get hot under certain conditions, and that doesn't seems part of the usual set of locks monitored by Cacti. I shall start paying beers to the MySQL developers I know again... Thank you very much once more for sharing your experiences. This is invaluable and I hope I can do the same for you in the future. Kind regards, -- Luis Motta Campos
