Problem with locks waits (server unresponsive)

MySQL & MariaDB Other MySQL® Questions
1

A few days ago our server became unresponsive and required a reboot. The problem was eventually traced back to mutex acquisition issues. From the innodb status, this is what we gathered…

"InnoDB: Warning: a long semaphore wait:
–Thread 1267927360 has waited at dict/dict0dict.c line 723 for 241.00 seconds
the semaphore:Mutex at 0x2ab8624c3298 created file dict/dict0dict.c line 683, lock var 1 waiters flag 1

This is traced to this in dict0dict.c file:
mutex_create(&dict_sys->mutex, SYNC_DICT);

InnoDB: Warning: a long semaphore wait:
–Thread 1273518400 has waited at btr/btr0sea.c line 1695 for 241.00 seconds
the semaphore:X-lock (wait_ex) on RW-latch at 0x2ab7ea858bb8 created in file btr/btr0sea.c line 167
a writer (thread id 1273518400) has reserved it in mode wait exclusivenumber of readers 1, waiters flag 0, lock_word: ffffffffffffffff
Last time read locked in file btr/btr0sea.c line 870"

This is traced to this in btr0sea.c file:
rw_lock_create(&btr_search_latch, SYNC_SEARCH_SYS);

I’m not sure what else to look at, but the 241 seconds is higher than the 50 second innodb lock wait timeout we have. The load on the system was not heavy at the time (compared to other days when we experience higher loads).

Any help with above would be appreciated.

2

Hi,

The waits you are seeing, is due to an ongoing operation on innodb data dictionary (dict/dict0dict.c). It is preventing a separate InnoDB thread from making any changes to the data dictionary cache. Another one is due to b-tree search (btr/btr0sea.c). DDL operations place exclusive locks on the data dictionary cache, and it looks like there was a long running DDL operations which caused this.

I would like to know some more information.

  1. What is MySQL version (Percona/native MySQL)?
  2. What was executing on the server at that time? (Show full processlist)
  3. Can you provide full output of InnoDB status?