SHOW INNODB STATUS walk-through

Many people asked me to publish a walk through SHOW INNODB STATUS output, showing what you can learn from SHOW INNODB STATUS output and how to use this info to improve MySQL Performance.

What is SHOW INNODB STATUS

To start with basics, SHOW INNODB STATUS is a command which prints out a lot of internal Innodb performance counters, statistics, information about transaction processing and all kinds of other things. In MySQL 5 number of Innodb performance counters were exported and now available in SHOW STATUS output. Most of them are the same as you previously could find in SHOW INNODB STATUS, there are however few which were not available before.

In SHOW INNODB STATUS many values are per second. If you’re planning to use these values make sure they are sampled over a decent period of time. In the very start of printout Innodb will print:

Make sure data is sampled for at least 20-30 seconds. If averages are calculated for last 0 or 1 second they are pretty much unusable.
To be honest I do not really like averages Innodb provides as it is hard to get average for the interval you want to have, if you’re writing scripts to look at SHOW INNODB STATUS it is much better to use global counters and get averages manually. They are still however quite helpful if you’re just looking at the output.

Next sections in Semaphores information:

There are two portions in this section. One is a list of current waits. This section will only contain any entries if you’re running in high concurrency environment, so Innodb has to fall back to OS waits frequently. If the wait was resolved via Spinlock it will not be seen in this section.

Looking at this section you can get an idea of what might be a hot spot in your workload. It, however, requires some knowledge of source code – you only get file names and lines (which are different in different versions), you get no information what this object is responsible for. You can well guess from file names – in this case, file is “buf0buf.ic” what means there is some buffer pool contention. However if you want to know more – you need to browse the source.

You also see some details printed about wait. “lock var” is the current value for the mutex object (locked=1/free=0) , “waiters flag” is the current number of waiters, plus you can see wait status information “wait is ending” in this case which means mutex is already free for grabs but os has not yet scheduled thread so it could proceed with execution.

The second piece of information is event counters – “reservation count” and “signal count” show how actively innodb uses internal sync array – how frequently slots are allocated in it and how frequently threads are signaled using sync array. These counters can be used to represent the frequency with which Innodb needs to fall back to OS Wait. There is direct information about OS waits as well – you can see “OS Waits” for mutexes, as well as for read-write locks. For this information both for exclusive locks and for shared locks is displayed. OS Wait is not exactly the same as “reservation” – before falling back to complex wait using sync_array Innodb tries to “yield” to OS hoping when name thread is scheduled next time object will be free already. OS Waits is relatively slow, and if you get tens of thousands of OS waits per second it may be the problem. The other way to look at it is context switch rate in your OS stats.

The other important piece of information is the number of “spin waits” and “spin rounds”. Spin locks are low-cost wait, compared to OS wait, it is, however, active wait which wastes your CPU cycles, so if you see a very large amount of spin waits and spin rounds significant CPU resources may be wasted. It should come to hundreds of thousands of spin rounds per second to start really worry for most CPUs. innodb_sync_spin_loops can be used to balance between wasting CPU time running spin locks and doing unneeded context switches.

The next section is about deadlock errors:

For last deadlock Innodb shows transactions which caused deadlocks, their state during deadlock, what locks they were holding and what they were waiting for, which of transactions Innodb decided to roll back to resolve deadlock. Note – Innodb only prints information about a few of the locks which transaction is holding. Also, only last statement from each transaction is displayed, while locks rows could be locked by one of the previous statements. For complex deadlock investigations, you might need to look at the log files to find truly conflicting statements. For most simple cases information from SHOW INNODB STATUS is good enough.

As for deadlock information we have similar information about the last failed foreign key constraint: