Context:
There is a table with 2 columns integer id column - primary key auto increment and a long text column.
Rows are concurrently added, read and removed from this table by many different processes and connections. There are no DDL statements at all. Just Insert, Select and Delete - and every operation happens on at most 1 row. i.e Inserts a single row, Selects a single row by primary key and then deletes a single row also by primary key.
The table is the only table on the mysql instance (this is in a docker container).
The table has few populated rows, the size of the ibd file is 199G.
Problem
Frequently i see semaphore locks like these
--Thread 140441749952256 has waited at btr0cur.cc line 545 for 249.00 seconds the semaphore:
S-lock on RW-latch at 0x4fcb428 created in file dict0dict.cc line 2606
a writer (thread id 140441750218496) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0cur.cc line 545
Sometimes the lock is held for more than 600s and innodb intentionally crashes.
Looking at the code for 5.6.26 - the function in dict0dict is
Adds an index to the dictionary cache.
@return DB_SUCCESS, DB_TOO_BIG_RECORD, or DB_CORRUPTION */
UNIV_INTERN
dberr_t
dict_index_add_to_cache(
And specifically:
rw_lock_create(index_tree_rw_lock_key, &new_index->lock,
dict_index_is_ibuf(index)
? SYNC_IBUF_INDEX_TREE : SYNC_INDEX_TREE);
Question
Whats causing those locks?
Tried so far
- Seeing that it is contention between b-tree cursor and dictionaries and some research, I turned off adaptive hash indexing. Issue still persisted.
- Then increased buffer pool from the default 128M to 1G. Issue still persists.
Since its a busy table I have not wanted to optimize the table, but in case that is the answer - I'd like to know why.