I'm sick of seeing data in the slow query log, so I decided to check out what the fuss was all about.
UPDATE query that is running up to tens of times per second is sometimes really slow. Here is the query:
UPDATE `user` SET `last_active` = NOW(), `last_ip` = '<ip>' WHERE `name` = '<name>' LIMIT 1
CREATE TABLE IF NOT EXISTS `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(16) NOT NULL, `last_ip` varchar(50) NOT NULL DEFAULT 'none', `last_active` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), KEY `last_active` (`last_active`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=34003;
Usually the query takes ~0.2 ms, but every 5 minutes (accurately every 5 minutes, 10:00:12, 10:05:12, 10:10:12...) it takes over 250 ms, with all time spent with the status "query end".
Any idea what may be causing this? Why would one query run slow every 5 minutes? What is happening every 5 minutes that is affecting "query end"? No other processes are running.
MySQL version 5.5.44