[MDEV-29311] Server Status Innodb_row_lock_time% is reported in seconds Created: 2022-08-16  Updated: 2023-07-11  Resolved: 2023-07-10

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.6.0, 10.6.9, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.6.15, 10.9.8, 10.10.6, 10.11.5, 11.0.3, 11.1.2, 11.2.1

Type: Bug Priority: Critical
Reporter: Oli Sennhauser Assignee: Vladislav Lesin
Resolution: Fixed Votes: 0
Labels: beginner-friendly, innodb, lock, monitoring, regression-10.6
Environment:

Linux, n.a.

Issue Links:
Problem/Incident
is caused by MDEV-24671 Assertion failure in lock_wait_table_... Closed

 Description   

I detected that recent MariaDB versions report the InnoDB_row_lock_time% now in seconds instead of milliseconds.

This is a bug that destroys all monitoring tools and thus should be fixed immediately.

It also happens in 10.7.1. Other releases were not tested.

SQL> start transaction;
 
SQL> update test set data = 'lock' where id = 1001;
 
-- Wait some seconds and do the same trx in a second console
 
SQL> commit;
 
 
 
SQL> show global status like 'innodb_row_lock%';
 
+-------------------------------+-------+
 
| Variable_name                 | Value |
 
+-------------------------------+-------+
 
| Innodb_row_lock_current_waits | 0     |
 
| Innodb_row_lock_time          | 5     |
 
| Innodb_row_lock_time_avg      | 5     |
 
| Innodb_row_lock_time_max      | 5     |
 
| Innodb_row_lock_waits         | 1     |
 
+-------------------------------+-------+

Source: https://mariadb.com/kb/en/innodb-status-variables/#innodb_row_lock_time
"Total time in milliseconds spent getting InnoDB row locks. "

 Comments   
Comment by Oli Sennhauser [ 2022-08-16 ]

In 10.5.12 the bug was not yet there. So it was introduced in 10.6 ff.

Comment by Marko Mäkelä [ 2022-08-16 ]

Before MDEV-24671, the wait time was derived from my_interval_timer() / 1000 (nanoseconds converted to microseconds, and not microseconds to milliseconds like I must have assumed). The lock_sys.wait_time and lock_sys.wait_time_max are already in milliseconds; we should not divide them by 1000.

In MDEV-24738 the millisecond counts lock_sys.wait_time and lock_sys.wait_time_max were changed to a 32-bit type. That would overflow in 49.7 days. We should probably keep using a 64-bit type for those millisecond counters.

Comment by Marko Mäkelä [ 2023-07-10 ]

This fix looks OK to me.

Generated at Thu Feb 08 10:07:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.