2

We recently started graphing the innodb_row_lock_time status variable in our monitoring system.

I noticed something I can't understand on a graph for a busy server:

Between 04:55PM and 04:57PM, the value of this counter changed from 488,101,000 to 488,504,000. The MySQL documentation says:

innodb_row_lock_time The total time spent in acquiring row locks, in milliseconds.

How is it possible that during those 2 minutes, the server spent 403,000 ms in acquiring row locks? 2 minutes is only 120,000 ms

The only explanation that comes to mind is that this value is a sum of all threads but I cannot find any documentation to confirm this theory.

The server is MySQL 5.1

5
  • 1
    How many processors does your machine have?
    – Vérace
    Apr 28 '18 at 18:53
  • The machine has 2 processors: Intel(R) Xeon(R) CPU E5-2667 v2 @ 3.30GHz Each has 8 cores / 16 threads
    – bodgix
    Apr 28 '18 at 21:44
  • The factor of 2 (processors) * 8 (cores) * 2 (threads) (= 32 in total) could readily explain the apparent inconsistency in your times?
    – Vérace
    Apr 28 '18 at 22:18
  • It could explain if the time is the sum of wait time of all threads but is it? This is what I'm trying to find out. The documentation doesn't say so was wondering if someone can confirm. I guess reading the source code would answer the question but I'm pretty sure I would get lost completely very quickly in MySQL src code.
    – bodgix
    Apr 28 '18 at 22:26
  • 1
    I can't prove it for innodb_row_lock_time in particular, but this article appears to suggest that it is the case! You pointed it out yourself - it's impossible to have more on the clock than elapsed time unless you have simultaneous execution of commands on different threads/cpus.
    – Vérace
    Apr 28 '18 at 23:03
0

This metric is cumulative. You have to take delta to see the change in amount of time. Then you have to divide by elapsed time to normalize it to say time waited per minute for example.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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