Databases Reference
In-Depth Information
1 ----------
2 SEMAPHORES
3 ----------
4 OS WAIT ARRAY INFO: reservation count 13569, signal count 11421
5 --Thread 1152170336 has waited at ./../include/buf0buf.ic line 630 for 0.00 seconds
the semaphore:
6 Mutex at 0x2a957858b8 created file buf0buf.c line 517, lock var 0
7 waiters flag 0
8 wait is ending
9 --Thread 1147709792 has waited at ./../include/buf0buf.ic line 630 for 0.00 seconds
the semaphore:
10 Mutex at 0x2a957858b8 created file buf0buf.c line 517, lock var 0
11 waiters flag 0
12 wait is ending
13 Mutex spin waits 5672442, rounds 3899888, OS waits 4719
14 RW-shared spins 5920, OS waits 2918; RW-excl spins 3463, OS waits 3163
Line 4 gives information about the operating system wait array, which is an array of
“slots.” InnoDB reserves slots in the array for semaphores, which the operating system
uses to signal threads that they can go ahead and do the work they're waiting to do.
This line shows how many times InnoDB has needed to use operating system waits.
The reservation count indicates how often InnoDB has allocated slots, and the signal
count measures how often threads have been signaled via the array. Operating system
waits are costly relative to spin waits, as we'll see momentarily.
Lines 5 through 12 show the InnoDB threads that are currently waiting for a mutex.
The example shows two waits, each beginning with the text “-- Thread <num> has
waited....” This section should be empty unless your server has a high-concurrency
workload that causes InnoDB to resort to operating system waits. The most useful thing
to look at, unless you're familiar with InnoDB source code, is the filename at which the
thread is waiting. This gives you a hint where the hot spots are inside InnoDB. For
example, if you see many threads waiting at a file called
buf0buf.ic
, you have buffer
pool contention. The output indicates how long the thread has been waiting, and the
“waiters flag” shows how many waiters are waiting for the mutex.
The text “wait is ending” means the mutex is actually free already, but the operating
system hasn't scheduled the thread to run yet.
You might wonder what exactly InnoDB is waiting for. InnoDB uses mutexes and
semaphores to protect critical sections of code by restricting them to only one thread
at a time, or to restrict writers when there are active readers, and so on. There are many
critical sections in InnoDB's code, and under the right conditions any of them could
appear here. Gaining access to a buffer pool page is one you might see commonly.
After the list of waiting threads, lines 13 and 14 show more event counters. Line 13
shows several counters related to mutexes, and line 14 is for read/write shared and
exclusive locks. In each case, you can see how often InnoDB has resorted to an operating
system wait.
