Mysql – Stuck in “query end” status every 5 minutes

MySQLmysql-5.5profiler

I'm sick of seeing data in the slow query log, so I decided to check out what the fuss was all about.

An 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

The table:

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

Best Answer

All uses of that table will run faster (some will run a lot faster) if you jettison id and make name the PRIMARY KEY.

Your UPDATE is currently looking in 2 BTrees, plus updating 2 BTrees. The PK change would change those to 1 and 2. More importantly it would simply the row locking.

How big is the table? How much RAM do you have? What is the value of innodb_buffer_pool_size? innodb_io_read_threads? What the heck, let's see SHOW VARIABLES LIKE 'innodb%';

Related Question