occasionally my mysql 4.0.26 server is having slow querry issues which shouldn’t happen (judging by the tuned mem settings and indexes). How can i get more information about the processes that may be locking a particular row - the slow querry log only shows this information:
UPDATE server SET current_kbytes_used = ‘0’, current_kbytes_free = ‘0’, current_load = ‘0.88’ WHERE SRVID=‘1717’;
The server table has about 2000 rows with an index on SRVID and it is INNODB (so the entire table should not be locked). There are multiple reads and infrequent updates and very rare inserts in this table.
Also, please note that in the majority of operation this query is instantaneous. ex:
UPDATE server SET current_kbytes_used = ‘0’, current_kbytes_free = ‘0’, current_load = ‘0.88’ WHERE SRVID=‘1717’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
My only guess is if this could happen when the disk is particularly busy at a certain point in time. Can this be found out through logs?
Peter, thanks for the reply. The strange thing is I am not doing 100 updates/second on those rows. Instead, maybe 1 update every 10 minute for each row. Is this type of extended lock really possible on such low activity? Are there any memory tweaks possible in this situation?
Here is some more info about the drive:
/dev/sda:
Timing buffered disk reads: 184 MB in 3.03 seconds = 60.72 MB/sec
more info - from recent data, it appears that this only happens to my INNODB tables and not the MyISAM. I run a mixture of these table types in my database configuration.
Right. That is why I’m saying it is most likely row level locks.
Something else might be holding the lock which you do not know about.
It also can be short term stall with disk IO. The data you’re showing does not help much as it is important to understand what happens exactly in this 3 seconds query is waiting.