Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.8, 10.7.3, 10.7.4, 10.8.2, 10.8.3, 10.9.1
-
None
Description
In MariaDB (as well as in MySQL 8), we have no wait event instrumentation for record locks and for table locks.
As such, table and row lock waits are reported incorrectly as "wait/io/table/sql/handler".
This makes performance troubleshooting impossible for customers.
The expected outcome is to create 2 more instruments: row lock and table lock. These 2 types of lock waits can then be measured inside performance_schema. Being able to separate lock waits from IO waits will aid performance tuning, because reducing them will require difference approaches.
Table and row lock instrumentation maybe too resource intensive. In the meantime, the fix in https://github.com/MariaDB/server/pull/2206 marks this kind of wait as wait/lock/table/sql/handler
Steps to reproduce:
- Add the following line into my.cnf before instance start
performance_schema=ON
- Run the following to set up all consumers and instrumentation
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES';
set global innodb_lock_wait_timeout=7200;
- Set up test table
create database test;
use test
create table t1 (id1 int(11), col1 varchar (200));
insert into t1 values (1, 'aa');
insert into t1 values (2, 'bb');
- Run 3 concurrent sessions to observe the bug
- Session 1
start transaction;
select id1 from test.t1 where id1=1 for update;
select sleep(100000);
- Session 2
start transaction;
select id1 from test.t1 where id1=1 for update;
- Session 3
MariaDB [test]> select * from performance_schema.threads where type = 'foreground'\G
*************************** Only relevant row displayed below ***************************
THREAD_ID: 12
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 4
PROCESSLIST_USER: haidong
PROCESSLIST_HOST: NULL
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 9
PROCESSLIST_STATE: Sending data
PROCESSLIST_INFO: select id1 from test.t1 where id1=1 for update
PARENT_THREAD_ID: 1
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: Socket
THREAD_OS_ID: 22508
Â
MariaDB [test]> select * from performance_schema.events_waits_current where thread_id=12\G
*************************** 1. row ***************************
THREAD_ID: 12
EVENT_ID: 236
END_EVENT_ID: NULL
EVENT_NAME: wait/io/table/sql/handler <<<Incorrect instrument name
SOURCE:
TIMER_START: 767747433709600
TIMER_END: 791300625915200
TIMER_WAIT: 23553192205600
SPINS: NULL
OBJECT_SCHEMA: test
OBJECT_NAME: t1
INDEX_NAME: NULL
OBJECT_TYPE: TABLE
OBJECT_INSTANCE_BEGIN: 139905003640240
NESTING_EVENT_ID: 235
NESTING_EVENT_TYPE: STAGE
OPERATION: fetch
NUMBER_OF_BYTES: 47
FLAGS: NULL
1 row in set (0.001 sec)
- Session 1
I'm working on a fix.