Querious Docs
Account

Server InnoDB Status view — MySQL

The engine's own status dump, in readable sections.

InnoDB has pretty extensive status information, but the result of SHOW ENGINE INNODB STATUS is hundreds of dense lines that usually aren't fun to read!

The InnoDB Status view displays all of that status in clearly labeled sections, pulls the key numbers into a summary bar, and makes it much easier to navigate. You can still access the full original log text as well.

The InnoDB Status view
The InnoDB Status view

Use this view to investigate the last deadlock, check whether the buffer pool is big enough, or find out why writes are stalling.

The Summary Bar #

The summary bar at the top of the view shows four critical metrics, colored by how alarming they are:

Metric What it means
Buffer Hit Rate The share of page reads served from memory rather than disk. Green at 99%+, orange from 95%, red below. A low number on a busy server usually means innodb_buffer_pool_size is too small for the working set.
History List Length The number of old row versions InnoDB keeps because some transaction might need them. Orange above 10,000, red above 100,000. A number that keeps climbing means a long-running transaction is holding back purge.
Checkpoint Lag How far the redo log has run ahead of the last checkpoint. Orange above roughly 50 MB, red above roughly 500 MB. Large, growing lag precedes InnoDB throttling writes to catch up.
Active Transactions How many transactions are open now.

The Sections #

Section Contains
Buffer Pool Hit rate, pool size, free/used/dirty pages, memory allocated, pages read/created/written and their rates. Dirty pages turn orange above 10,000.
Transactions Transaction ID counter, purge position and state, history list length, row lock structs, active count, and an entry per open transaction showing what it's doing.
Log Log sequence number, flush positions, last checkpoint, checkpoint lag, and log I/O rates.
Row Operations Queries executing inside InnoDB and how many are queued behind them, plus rows read/inserted/updated/deleted and rates. Any queued query is orange; over 100 is red.
File I/O Pending reads, writes, and flushes, and I/O thread states.
Insert Buffer and Adaptive Hash Index, Semaphores, Background Thread Shown as the engine wrote them.
Latest Deadlock The last deadlock InnoDB detected.
Latest Foreign Key Error The last foreign key violation the engine rejected.
Full Text Log The complete unparsed output.

Recipes #

To investigate a deadlock

  1. Read the two transactions in Latest Deadlock. Each shows the index and lock mode it held and the one it waited for.

  2. Note the order each transaction took its locks.

    Deadlocks are nearly always two code paths touching the same rows in opposite orders. The fix is to make both take them in the same order.

  3. Check which transaction InnoDB rolled back. That's the one whose application saw the error, so look in your logs for it.

To decide whether the buffer pool is too small

  1. Check Buffer Hit Rate. Below 95% under real load is the clear case.
  2. In Buffer Pool, check Free Pages. A long-full pool with a mediocre hit rate is being thrashed.

  3. Compare Pool Size with the size of the tables you actually query.

    If the working set doesn't fit, raise innodb_buffer_pool_size in Variables. On MySQL 5.7+ it can be resized without a restart.

To find a long-running transaction holding back purge

  1. Watch History List Length. High and rising while the server is otherwise idle means something has an old read view open.
  2. In Transactions, look for one with a large age or sitting in a non-working state.
  3. Match its thread in Processes to find the connection, and Locks for what it holds.

  4. End the connection if it's an idle transaction that never committed.

    That's almost always an application that forgot to commit or roll back. Ending the connection releases it.

To tell "busy" from "stuck"

  1. In Row Operations, check Queries Inside InnoDB and Queries Queued. Work in progress with nothing queued is just busy.
  2. A long queue means threads are waiting to enter the engine. Check innodb_thread_concurrency in Variables.
  3. If neither is high but queries aren't finishing, they're waiting on locks. Go to Locks.

Notes #

  • Use Refresh to take a new sample. Counters are cumulative since server start; per-second rates cover the interval since the previous dump.
  • The view requires the PROCESS privilege.
  • The view only reads.