5

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

Improve this question
4
  • Do you use any form of replication? I have seen everything freeze in "query end" in case of Galera master-master when the other node is locked (alter/slow processing etc). Another possibility might be IO - any backup running or something else which would run periodically each 5 minutes and used a lot of HW/IO capacity? (check cron logs, system events etc.)
    – jkavalik
    Aug 22, 2015 at 9:08
  • No, no replication. It does look related to IO as kjournald's IO is spiking when this happens, but I have no idea why. I have disabled all other services and removed all cronjobs, but the spike is still there. I also switched to MariaDB 10, and nothing changed. The only IO according to iotop is mysql and kjournald, but I'm not sure if that means that mysql is the one causing kjournald to spike.
    – Alex
    Aug 22, 2015 at 10:13
  • You may want to have a look at this similar question. Your situation might be somewhat different, but the question has the sorts of details (and links to more) that you'll need to figure out InnoDB write stall problems.
    – James Lupolt
    Aug 23, 2015 at 0:22
  • Could this be coinciding with log flushes?
    – Andrew Brennan
    Oct 13, 2016 at 11:10

2 Answers 2

Reset to default
1

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%';

Improve this answer
-3

Yes, Please check innodb_buffer_pool_size configuration.

Increase your read io_thread and write_thread,which will help you to figure it out.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.