Simple UPDATE suddenly takes up to 2 seconds

Asked Apr 26 '21 at 21:34

Active Apr 26 '21 at 21:36

Viewed 47 times

I am running a website with about 35k users in a table users. Everytime a user requests a page, the database row for that user is updated with the current timestamp. This query, a simple

UPDATE `users` SET `last_seen`=1619472395 WHERE `id`=12345678

usually took like a microsecond or so.

From one day to the next, this query now takes much longer, sometimes up to two seconds, making the website very slow. Strange thing: Sometimes the query is as fast as you would expect, one second later it takes two seconds.

The user table is tiny, only has 35k rows (MyISAM), ´id´ is the primary key and the total database size is not even 100 MByte.

Any ideas what's going wrong here?

edited Apr 26 '21 at 21:36

asked Apr 26 '21 at 21:34

Armin Hierstetter

1,078
2
12
27

Did you create index in there? – Farrukh Faizy Apr 26 '21 at 21:35
@FarrukhFaizy The question says it's the primary key. – Barmar Apr 26 '21 at 21:37
3

The only guess I have is that something else is locking the table and it's waiting for the lock. If you can switch to InnoDB it uses row-level locking, so this should be less of a problem. – Barmar Apr 26 '21 at 21:38
1

do always use innodb, unless you have clear evidence you would do better with myisam – ysth Apr 26 '21 at 21:44
Can I just switch to INNO like that??? – Armin Hierstetter Apr 26 '21 at 21:44
3

`ALTER TABLE users ENGINE=InnoDB;` this should not take long on such a small table. – Bill Karwin Apr 26 '21 at 21:46
You might like to read [my answer to MyISAM versus InnoDB](https://stackoverflow.com/a/17706717/20860) – Bill Karwin Apr 26 '21 at 21:56
I changed to InnoDB and we are back to normal speed. Still, this does not explain the extremely bad speed on a rather tiny table. 35k rows should be nothing. – Armin Hierstetter Apr 27 '21 at 22:05

Simple UPDATE suddenly takes up to 2 seconds

0 Answers0