Innodb MVCC blocking
Hello,
We are regularly seeing entries in the Semaphores section of SHOW ENGINE INNODB STATUS, which are similar to the following. We are trying to understand the impact of the information (below) and see if it’s part of a high CPU issue we are experiencing. We are currently using MySQL 5.5.11 in a windows environment.
Basically, if I have understood this correctly, below, we have four threads, which are all being blocked because a fifth thread (1828) is holding an exclusive lock open on the buffer pool. Albeit, the wait time is low, so this may be a non issue.
The first question we have is; why is there blocking? Ok, thread 1828 is running an update statement and the other four threads are trying to complete select statements, but we thought with MVCC (Multi Version Currency Control) in Innodb, this shouldn’t happen. Maybe this has something to do with forgiven key checks that may be occurring during the update statement? Any advice would be greatly appreciated.
Thanks,
Andy.
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2724943, signal count 4644096
--Thread 2904 has waited at ..\..\..\mysqlcom-pro-5.5.11\storage\innobase\row\row0sel.c line 3801 for 1.00 seconds the semaphore:
S-lock on RW-latch at 00000000805D02C0 created in file ..\..\..\mysqlcom-pro-5.5.11\storage\innobase\buf\buf0buf.c line 898
a writer (thread id 1828) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ..\..\..\mysqlcom-pro-5.5.11\storage\innobase\btr\btr0sea.c line 903
Last time write locked in file ..\..\..\mysqlcom-pro-5.5.11\storage\innobase\row\row0upd.c line 2131
--Thread 4924 has waited at ..\..\..\mysqlcom-pro-5.5.11\storage\innobase\row\row0sel.c line 3801 for 0.00 seconds the semaphore:
S-lock on RW-latch at 00000000805D02C0 created in file ..\..\..\mysqlcom-pro-5.5.11\storage\innobase\buf\buf0buf.c line 898
a writer (thread id 1828) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ..\..\..\mysqlcom-pro-5.5.11\storage\innobase\btr\btr0sea.c line 903
Last time write locked in file ..\..\..\mysqlcom-pro-5.5.11\storage\innobase\row\row0upd.c line 2131
--Thread 5004 has waited at ..\..\..\mysqlcom-pro-5.5.11\storage\innobase\row\row0sel.c line 3801 for 0.00 seconds the semaphore:
S-lock on RW-latch at 00000000805D02C0 created in file ..\..\..\mysqlcom-pro-5.5.11\storage\innobase\buf\buf0buf.c line 898
a writer (thread id 1828) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ..\..\..\mysqlcom-pro-5.5.11\storage\innobase\btr\btr0sea.c line 903
Last time write locked in file ..\..\..\mysqlcom-pro-5.5.11\storage\innobase\row\row0upd.c line 2131
--Thread 4176 has waited at ..\..\..\mysqlcom-pro-5.5.11\storage\innobase\row\row0sel.c line 3801 for 0.00 seconds the semaphore:
S-lock on RW-latch at 00000000805D02C0 created in file ..\..\..\mysqlcom-pro-5.5.11\storage\innobase\buf\buf0buf.c line 898
a writer (thread id 1828) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ..\..\..\mysqlcom-pro-5.5.11\storage\innobase\btr\btr0sea.c line 903
Last time write locked in file ..\..\..\mysqlcom-pro-5.5.11\storage\innobase\row\row0upd.c line 2131
Mutex spin waits 33062341, rounds 152473969, OS waits 2294999
RW-shared spins 1079811, rounds 10201004, OS waits 243212
RW-excl spins 497277, rounds 7597878, OS waits 84652
Spin rounds per wait: 4.61 mutex, 9.45 RW-shared, 15.28 RW-excl