Running a load tests, I found some select statements in an weird "Waiting for table level lock" state. I did an investigation and wrote a conclusion on it.
Issue
Waiting for table level lock | SELECT pad FROM sbtest4 WHERE id=50405|
| 3073 | sb_user | 10.112.17.167:36999 | sbtest | Query | 0 | Waiting for table level lock | SELECT pad FROM sbtest4 WHERE id=50405|
| 3074 | sb_user | 10.112.17.167:36993 | sbtest | Query | 0 | Waiting for table level lock | SELECT pad FROM sbtest4 WHERE id=50376|
| 3075 | sb_user | 10.112.17.167:37000 | sbtest | Sleep | 0 | cleaned up | NULL What it is and when it is used
The session commonly has the "Waiting for global read lock" status with DML statementshttps://mysqldatabaseadministration.blogspot.com/2007/06/innodb-table-locks.html https://bugs.mysql.com/bug.php?id=67941
General thread states https://dev.mysql.com/doc/refman/5.5/en/general-thread-states.html
Waiting for lock_type lock
The server is waiting to acquire a THR_LOCK lock or a lock from the metadata locking subsystem, where lock_type indicates the type of lock. This state indicates a wait for a THR_LOCK:
- Waiting for table level lock
Improve performance by not using the THR_LOCK.c https://dev.mysql.com/worklog/task/?id=6671
way to prevent queries from waiting for table level lock It happens for MyIsam due to the lock typehttps://dba.stackexchange.com/questions/21075/way-to-prevent-queries-from-waiting-for-table-level-lock
innodb_table_locks
Property Value Command-Line Format --innodb-table-locks System Variable innodb_table_locks Scope Global, Session Dynamic Yes SET_VAR Hint Applies No Type boolean Default Value TRUE If autocommit = 0, InnoDB honors LOCK TABLES; MySQL does not return from LOCK TABLES ... WRITE until all other threads have released all their locks to the table. The default value of innodb_table_locks is 1, which means that LOCK TABLES causes InnoDB to lock a table internally if autocommit = 0.
In MySQL 8.0, innodb_table_locks = 0 has no effect for tables locked explicitly with LOCK TABLES ... WRITE. It does have an effect for tables locked for read or write by LOCK TABLES ... WRITE implicitly (for example, through triggers) or by LOCK TABLES ... READ.
How to reproduce it with Sysbench 1.0.15
It was highlighted that the sysbench 0.5 had a bug and the "skip_trx" variable would not work correctly.
export HOST="sebbaphoto.cluster"- Only Point Selects (skip_trx is off) oltp_read_onlysysbench --mysql-host=$HOST --threads=16 --report-interval=10 --time=0 oltp_read_only --table_size=6500000 --tables=60 --delete_inserts=0 --distinct_ranges=0 --index_updates=0 --order_ranges=0 --point_selects=1 --range_selects=off --simple_ranges==0 --sum_ranges=0 run
- Only Point Selects (skip_trx is on) oltp_read_onlysysbench --mysql-host=$HOST --threads=16 --report-interval=10 --time=0 oltp_read_only --table_size=6500000 --tables=60 --delete_inserts=0 --distinct_ranges=0 --index_updates=0 --order_ranges=0 --point_selects=1 --range_selects=off --simple_ranges==0 --sum_ranges=0 --skip_trx=on run
How to monitor the processlist while (sleep 0.1); do mysql -h$HOST -usebbaphoto -p*** -e "select command, time, state, info from information_Schema.processlist where user='sbtest' and state not in ('cleaned up','delayed send ok done');" -sN 2>/dev/null| cut -c-80; done
Query 0 Writing to net SELECT pad FROM sbtest6 WHERE id=49561
Query 0 System lock SELECT pad FROM sbtest4 WHERE id=66471
Query 0 System lock SELECT pad FROM sbtest1 WHERE id=50378
Query 0 System lock SELECT pad FROM sbtest8 WHERE id=54334
Query 0 System lock SELECT pad FROM sbtest5 WHERE id=49695
Query 0 Writing to net SELECT pad FROM sbtest5 WHERE id=49029
Query 0 sending cached result to client SELECT pad FROM sbtest7 WHERE id=50026
Query 0 Writing to net SELECT pad FROM sbtest7 WHERE id=50232
Query 0 Waiting for table level lock SELECT pad FROM sbtest2 WHERE id=50165
Query 0 sending cached result to client SELECT pad FROM sbtest2 WHERE id=55147
Query 0 Waiting for table level lock SELECT pad FROM sbtest5 WHERE id=59354
Query 0 System lock SELECT pad FROM sbtest1 WHERE id=47241
Query 0 Waiting for table level lock SELECT pad FROM sbtest5 WHERE id=49047
Query 0 Waiting for table level lock SELECT pad FROM sbtest8 WHERE id=60993
Query 0 Waiting for table level lock SELECT pad FROM sbtest5 WHERE id=50368
Query 0 Waiting for table level lock SELECT pad FROM sbtest2 WHERE id=50062
Query 0 Waiting for table level lock SELECT pad FROM sbtest1 WHERE id=49599
Query 0 logging slow query SELECT pad FROM sbtest7 WHERE id=63015
Query 0 logging slow query SELECT pad FROM sbtest1 WHERE id=50323
Query 0 Waiting for table level lock SELECT pad FROM sbtest3 WHERE id=39229
Query 0 Waiting for table level lock SELECT pad FROM sbtest7 WHERE id=49873
Query 0 Waiting for table level lock SELECT pad FROM sbtest1 WHERE id=50237Investigation - tx_isolation
Change the tx_isolation from being repeatable read to read committed
https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html REPEATABLE READ This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other.
READ COMMITTED Each consistent read, even within the same transaction, sets and reads its own fresh snapshot.
Changing the tx_isolation value didn't stop the connections being in "Waiting for table level lock" state
Indesiderable effects
Error tx_isolation changed found
|error 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.Investigation - Not open explicitally transaction
--oltp-read-only Read-only mode. No UPDATE, DELETE or INSERT queries will be performed. (default: off)
So, as you correctly stated no DMLs will be executed for this thread, but there is a parameter that was not specified that can impact on your test.
--oltp-test-mode Execution mode. Possible values: simpe (simple), complex (advanced transactional) and nontrx (non-transactional) (default: complex)
From the same documentation, here follows the type of execution defined as Advanced transactional Each thread performs transactions on the test table. If the test table and database support transactions (e.g. InnoDB engine in MySQL), then BEGIN/COMMIT statements will be used to start/stop a transaction.
As check on this thread [2] oltp-test-mode is not working for version 0.5, so it appears that being/commit commands are still being issued when executing the OLTP mode.
So, my first suggestion is to use the latest version and define the oltp-test-mode to simpe or nontrx explicitly and check the locks. If it is still not working could you please send the show variables like '%commit%’; for your session.
It hasn't sorted the issue.
Investigation - Mysql server
As part of the investigation, I've set up a sbtest database on a Mysql server v5.7 on a EC2 instance. And I ran the same command above with 'skip_trx' equals 'on' and I didn't see any waiting lock status
I'm assuming that AWS does something internally that makes the selects waitifng for this table lock.
It would be good to have an insight by the Aurora team and investigate if the issue can be reproduced on a single RDS Mysql instance.
The Mysql server didn't set the state of selects to waiting lock.
Investigation - Query cache disabled
The query cache stores the text of a Select statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client; But this can generate locks on your objects depending of the type of your executions; Query cache is enabled by default in Aurora.
Test to perform
- Disable your query cache (change query_cache_size and query_cache_type to 0 and then restart your Aurora MySQL).
From web.archive.org => link
With query cache enabled query_cache_size Default {DBInstanceClassMemory/24} query_cache_type Default 1
[ 350s ] thds: 24 tps: 21054.36 qps: 21054.36 (r/w/o: 21054.36/0.00/0.00) lat (ms,95%): 2.48 err/s
With query cache disabled query_cache_size = 0 query_cache_type = 0
[ 290s ] thds: 24 tps: 23944.20 qps: 23944.20 (r/w/o: 23944.20/0.00/0.00) lat (ms,95%): 2.26 err/s
Sleep 0 cleaning up NULL Execute 0 statistics SELECT c FROM sbtest15 WHERE id=? Execute 0 statistics SELECT c FROM sbtest60 WHERE id=? Execute 0 statistics SELECT c FROM sbtest31 WHERE id=? Execute 0 Sending data SELECT c FROM sbtest13 WHERE id=? Execute 0 statistics SELECT c FROM sbtest25 WHERE id=?
Query cache is enabled by default in Aurora. Disabling the cache query has allowed having the select statement states set to a different state than waiting for global read lock.