⚡ MySQL Advisor

Understanding SHOW ENGINE INNODB STATUS

If you just ran SHOW ENGINE INNODB STATUS and you are staring at a wall of numbers with no idea what any of it means, you are in the right place. This field guide walks through what each section actually means, in plain language: what to check first, the query that confirms it, the trap that catches people, and what to do next. Already have a capture open? Paste it into SEIS Advisor and let it do the reading for you.

SHOW ENGINE INNODB STATUS is one of the most useful commands MySQL gives you, and one of the least explained. It is a live snapshot of what InnoDB is doing right now: which transactions are open, whether anything is stuck waiting on a lock or a latch, how the buffer pool and redo log are holding up, and what the most recent deadlock looked like. Nothing in the output tells you what any of that means, and the exact fields shift across MySQL, Percona Server and MariaDB versions, which is exactly why this guide exists.

If you would rather not work through it by hand, paste your own output into SEIS Advisor. It runs through the same checks described below automatically, flags what actually needs your attention, and links back to the section here for the reasoning behind each finding.

Open SEIS Advisor

HEADER & BACKGROUND THREAD

Sampling window • Master thread
#

Start here every time. These first few lines decide how much you can trust everything else in the output: every per-second figure printed below is an average over the window named here, not a live reading.

  1. 1
    Verify first
    • Read the capture timestamp and the window: Per second averages calculated from the last N seconds.
    • Note the background thread state line. In the captures we tested it is state: sleeping on 5.7-era builds and MariaDB 10.4 and 10.6, and state=sleeping on MySQL 8.0, Percona Server 8.0 and PXC 8.4. The exact version boundaries are not confirmed, so verify on your build.
    • Check whether the output ends at END OF INNODB MONITOR OUTPUT. If it does not, InnoDB truncated it and anything below the cut is missing.
  2. 2
    Capture it properly
    SHOW ENGINE INNODB STATUS\G
    -- wait 60 seconds, then run it again and read the SECOND output
    SHOW ENGINE INNODB STATUS\G

    The window is the gap between two runs. Two captures a minute apart make every cumulative counter subtractable; one capture does not.

    • Two captures is a minimum. During an incident take many samples (for example every 15 to 30 seconds for several minutes) and read the trend across all of them. [Standard practice]
    • Pair them with INNODB_TRX sampled every second, the processlist, the error log, the slow log and OS I/O metrics. [Standard practice]
    • Collection can hurt. Several concurrent SEIS sessions waiting on the monitor-file mutex preceded a crash on a busy server, and heavy collectors (stack sampling every minute, very large log dumps) have stalled servers. Use one collector, cap concurrent SEIS sessions and bound the output size. [Standard practice]
    • innodb_status_output makes InnoDB print this output to the error log periodically, and innodb_status_output_locks adds lock detail. InnoDB also prints it on its own in some conditions, such as long semaphore waits or no free blocks. Generating it costs some performance and the error log can grow large, so turn both off after collection. [Official docs] (the two example conditions are not confirmed)
    • You can paste several captures together into SEIS Advisor, or a stretch of the error log that contains them. Each capture is read on its own, and you can switch between them to see what changed.
  3. 3
    The trap: a 2 to 5 second window, which is what two quick interactive runs produce, is short enough that InnoDB will print 0.00 reads/s in the same output as hundreds of thousands of rows read per second, simply because the burst landed outside the window. Treat any rate from a sub-10-second window as noise, not signal.
  4. 4
    Next step
    • If the background thread shows the same non-sleeping state across several captures, that thread is stuck on something. Purge lag is the usual cause, so go to Transactions.

SEMAPHORES

Latch contention • Stalls
#

InnoDB protects its internal structures with mutexes and RW-latches, and this section lists whatever threads happened to be waiting on one the moment you captured the output. An empty section is completely normal. A populated one can mean two very different things, and telling them apart is the whole game.

  1. 1
    Verify first
    • Count the waiters, then group them by latch address, not by source file. Two different file:line entries on the same 0x... address are two call paths to one object, not two problems.
    • Read the longest single wait. Many threads each under a second on one latch is contention, a throughput ceiling with nothing hung. A few threads with a wait running tens of seconds is a stall.
    • Note created in file for the latch, not just where threads are waiting. Threads waiting in row0sel.cc on a latch created in buf0buf.cc usually means a hot page, not a problem in the read path.
    • OS WAIT ARRAY INFO: reservation count: InnoDB prints one of these lines per wait array (innodb_sync_array_size), each followed by the threads waiting in that array. The count goes up every time a thread has to sleep until a latch is released; the single signal count line goes up every time sleeping threads are woken. Both are running totals since startup and, unlike the spin and OS-wait lines below them, are still counted on 8.0-era builds. Compare them across two captures: if neither moves while threads are still waiting, the waiters are stuck, not busy. [InnoDB source]
  2. 2
    Confirm with
    SELECT event_name, count_star,
           sum_timer_wait / 1000000000000 AS total_wait_sec
    FROM performance_schema.events_waits_summary_global_by_event_name
    WHERE event_name LIKE 'wait/synch/%'
    ORDER BY total_wait_sec DESC LIMIT 10;

    Requires synchronisation instrumentation to be enabled, which it is not by default. Timers are in picoseconds, hence the divisor.

  3. 3
    The trap: threads spin on CPU deliberately, to avoid the cost of an OS context switch, so brief waits are normal on a busy server. One capture with 40 zero-second waits and the next with none is a burst, not a structural ceiling. Separately, on MySQL and Percona 8.0-era builds the RW-lock spin and OS-wait counters below the wait blocks are often left unincremented, producing a Spin rounds per wait value that is really just the round count, or a negative number from an overflowed division. That is how the line is produced, not a symptom.
  4. 4
    Next step
    • Capture twice more, 30 to 60 seconds apart. Contention on every capture is structural; contention on one is a burst.
    • A single wait of about 600 seconds makes InnoDB abort the server deliberately. The time to abort can run somewhat longer, because the monitor thread checks periodically. A wait already in the hundreds of seconds is an incident in progress, not a tuning question. [Standard practice]
    • On MariaDB the limit is the system variable innodb_fatal_semaphore_wait_threshold (default 600, not dynamic, so changing it needs a restart) [Official docs]. It is not listed in the MySQL 8.0 or 8.4 variable references [Official docs: 8.0, 8.4]. On MySQL and Percona, treat 600 seconds as a fixed limit that cannot be raised without rebuilding the server (verify for your build).
    • A single wait of about 20 seconds is a reasonable alert level, but many 0-second waits can still mean contention if the counts grow across samples. [Standard practice] (a heuristic, not a documented limit)
    • Count waits by file:line and also by latch address, creation site and holder thread, across all samples. Waits are often a symptom of saturated I/O (for example an aio queue at its slot limit, with a message such as N aio events waiting) rather than a latch problem. Semaphore waits are rarely corruption. [Standard practice]
    • If waits concentrate in btr0sea.cc, the adaptive hash index is the suspect. Turning it off is a recognised test, not a default: measure real throughput on both sides of the change before keeping it.
    innodb_sync_spin_loops
    How long a thread spins before falling back to an OS wait.
    innodb_sync_array_size
    Number of sync wait arrays; not dynamic in MySQL 8.0 [Official docs]. In one measured high-concurrency case, going from 1 to 8 or 16 cut system CPU from about 50% to under 1% and raised read throughput by roughly a third, with no further gain beyond 8 to 16. That is a single reported case, not a general result, so test and measure. [Standard practice]
    innodb_buffer_pool_instances
    Spreads buffer-pool page latch contention across more mutexes.
    innodb_adaptive_hash_index
    On or off, whole-server. A genuine trade-off, not a free win.

LATEST DETECTED DEADLOCK

Lock inversion • One event only
#

InnoDB only remembers the most recent deadlock, no matter how long ago it happened, so the first thing to work out is how old this one actually is.

  1. 1
    Verify first
    • Compare the deadlock's own timestamp against the capture timestamp in the header. Seconds ago is live information. Six days ago is history that happens to still be in the buffer.
    • For each transaction, read the lock it holds against the lock it is waiting for. Block order and which sub-blocks appear vary by version, so read the labels, not the positions.
    • Note whether the waits are on gap locks, foreign key checks, or secondary index entries; they point at different fixes.
  2. 2
    Confirm with
    SET GLOBAL innodb_print_all_deadlocks = ON;
    -- then watch the error log: every deadlock is written there, not just the last one
    SHOW GLOBAL VARIABLES LIKE 'log_error';

    This section shows one event. The error log shows the pattern, which is the thing you actually need.

  3. 3
    The trap: this output records neither frequency nor any deadlock but the latest. A single old deadlock tells you your application already retries them correctly, since otherwise it would have surfaced as an error. It does not tell you whether one is happening every few minutes.
  4. 4
    Next step
    • If deadlocks recur, make the application acquire rows in a deterministic order across every code path that touches them. That is the fix; settings are not.
    • Make sure the application actually retries a deadlocked transaction rather than surfacing it to the user. Retries are normal.
    • Error 1213 can occur with nothing in this section. Metadata-lock deadlocks never appear here, and the capture may come from a different instance than the one that raised the error. [Standard practice]
    • On Galera and PXC, certification conflicts surface as 1213 with brute-force abort messages in the error log. [Standard practice]
    • READ COMMITTED often reduces gap-lock deadlocks. Weigh the trade-offs first: binary logging requirements and phantom reads. [Standard practice] (verify the binary logging requirements for your version)
    • While investigating, use innodb_print_all_deadlocks (on PXC together with log_error_verbosity=3) and turn it off afterwards. Counting RECORD LOCKS lines in the error log does not count deadlocks. [Standard practice]
    innodb_print_all_deadlocks
    Logs every deadlock, not just the last. Enable while investigating.
    innodb_deadlock_detect
    Disabling it falls back to lock wait timeouts. A specific high-concurrency tactic, not a general default.
    innodb_lock_wait_timeout
    How long a session waits for a row lock before erroring out.

LATEST FOREIGN KEY ERROR

Constraint failure • Data issue
#

This section works the same way as the deadlock one above: just the most recent constraint failure, with the statement that triggered it, the constraint involved, and the offending row printed as a raw hex and asc dump.

  1. 1
    Verify first
    • Check the age against the header timestamp, exactly as with deadlocks.
    • Read which side failed: a child row pointing at a missing parent, or a parent delete blocked by children.
  2. 2
    The trap: the record dump contains real row data, which on a production server means real customer data. Redact this section before pasting it into a ticket, a chat, or any tool outside your own environment.
  3. 3
    Next step
    • This is usually an application or data-load ordering problem, not a tuning one. The fix is in the insert and delete order, or in the data itself. [Standard practice]
    • Exception: on some PXC 8.0 and 8.4 builds a stale internal table reference can make this section claim the parent table or its .ibd file does not exist while the parent reads normally. In the reported case rebuilding the tables did not clear it, and dropping and re-adding the foreign key did. Before concluding the parent is missing, read the parent table and run CHECK TABLE on it. [Standard practice] (reported case, verify on your build)

TRANSACTIONS

Purge lag • Long transactions • Lock waits
#

Three things share this section, and they are more connected than they look: the History List Length (undo waiting to be purged), how purge is progressing, and the list of active transactions. One long-running transaction can tie all three together, because it holds a read view open, and that alone stops purge from reclaiming undo for everyone else.

  1. 1
    Verify first
    • History list length: is it stable across captures, or climbing? The trend matters more than the value.
    • Compare it with that instance's own baseline. About 100K to 1M is commonly treated as significant. [Standard practice] (a heuristic, not a documented limit). Also read the gap between Trx id counter and Purge done for trx's n:o: a huge gap means purge is far behind.
    • Scan the active list for ACTIVE n sec with a large n, especially entries holding row locks and undo entries but showing no query. That is a session idle inside an open transaction.
    • Look for ------- TRX HAS BEEN WAITING ... FOR THIS LOCK TO BE GRANTED and find the blocking transaction elsewhere in the same section.
  2. 2
    Confirm with
    SELECT trx_id, trx_state, trx_started,
           TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
           trx_rows_locked, trx_rows_modified, trx_query
    FROM information_schema.INNODB_TRX
    ORDER BY trx_started;

    Works across MySQL, Percona and MariaDB. For the current blocker-and-waiter chain, sys.innodb_lock_waits is the convenient view where the sys schema is installed; on MySQL 8.0 the underlying tables are performance_schema.data_locks and data_lock_waits.

  3. 3
    The trap: a healthy-looking History List Length can sit right beside a transaction that has been open for hours. An uncommitted read-only transaction, a client that ran a SELECT and went idle, shows no lock waits at all while still pinning undo and blocking purge system-wide. Never judge this section on the HLL number alone.
  4. 4
    Next step
    • Find the oldest active read view before touching purge settings. If one transaction is the cause, more purge threads will not help.
    • When no single transaction pins the read view, raising innodb_purge_threads and the purge batch size (innodb_purge_batch_size) can help. I/O saturation alone can also slow purge. [Standard practice]
    • Chase down the application connection holding it: an abandoned transaction, a connection pool that never commits, a forgotten interactive session.
    • Watch HLL across several captures afterwards to confirm purge resumes.
    • This section shows the blocking transaction, not necessarily its statement. Use performance_schema or sys views plus statement history to find the SQL. [Standard practice]
    innodb_purge_threads
    Parallelises purge. Does nothing against one held-open read view.
    innodb_lock_wait_timeout
    Bounds how long a session blocks on a row lock.

FILE I/O

Disk latency • Pending I/O
#

A look at InnoDB's background I/O helper threads and how deep each queue was sitting at the moment you captured this.

  1. 1
    Verify first
    • Pending normal aio reads and aio writes: a standing queue means InnoDB is asking for more I/O than the storage is returning.
    • Pending fsyncs: sustained counts point at storage controller or write-barrier latency rather than throughput.
    • The trailer line differs by version. 5.7-era builds print ibuf aio reads, log I/O's and sync I/O's; 8.0-era builds print a shorter line.
  2. 2
    Confirm with
    SHOW GLOBAL STATUS LIKE 'Innodb_data%';

    Portable across all three engines. Where performance_schema file instrumentation is enabled, events_waits_summary_global_by_event_name filtered on wait/io/file/innodb/% gives per-file latency; AVG_TIMER_WAIT / 1000000000 converts picoseconds to milliseconds.

  3. 3
    The trap: these pending counts are a snapshot, not an average. One capture showing a queue is not evidence of a bottleneck. A queue on every capture is.
  4. 4
    Next step
    • Check the OS at the same moment with iostat -xz 1 or your platform's equivalent. A queue InnoDB reports while the OS shows fast, low-latency completion is a different problem from a saturated device.
    • If InnoDB also shows many semaphore waits at the same time, suspect I/O saturation (for example an aio queue at its slot limit) before suspecting a latch. [Standard practice]
    innodb_io_capacity, innodb_io_capacity_max
    Budget for background I/O. Set from measured device capability, not a round number. Values of 10,000 and above were repeatedly judged too high for the storage in the cases reviewed. [Standard practice]
    innodb_read_io_threads, innodb_write_io_threads
    More threads help only if the storage can serve more concurrent I/O.
    innodb_flush_neighbors
    Leave at 0 on SSD and NVMe; it exists for spinning disks.

INSERT BUFFER AND ADAPTIVE HASH INDEX

Change buffer • Hash lookups
#

Two unrelated subsystems just happen to share this section. The change buffer batches up secondary index changes for pages that are not currently in the pool, and the adaptive hash index turns repeated B-tree lookups into faster hash probes.

  1. 1
    Verify first
    • Change buffer: compare merges against inserts into the buffer. A buffer that fills faster than it merges is deferring work, not avoiding it.
    • AHI: compare hash searches/s against non-hash searches/s. That ratio is the whole value proposition.
  2. 2
    Confirm with
    SHOW GLOBAL VARIABLES LIKE 'innodb_change_buffering';
    SHOW GLOBAL VARIABLES LIKE 'innodb_adaptive_hash_index%';
  3. 3
    The trap: AHI reporting all zeros does not mean something is broken. AHI is on by default before MySQL 8.4 and off by default from MySQL 8.4 and in MariaDB [Official docs], so zeros are expected on 8.4 and later and on MariaDB. On MySQL 8.0 the default does not explain zeros: check whether it was disabled, or whether the workload simply does not use it. Absence here is a configuration fact, not a fault.
  4. 4
    Next step
    • If AHI latch contention is showing up in Semaphores (waits in btr0sea), compare the hash and non-hash search rates together with those waits. If non-hash searches dominate (several times the hash rate) and the waits exist, disabling is a candidate. If most searches use the hash and the waits are few, keep it. [Standard practice]
    • Test on a replica first and measure throughput both ways. Some workloads gain clearly, others lose clearly.
    innodb_change_buffering
    Which operation types get buffered.
    innodb_adaptive_hash_index
    Whole-server on or off.
    innodb_adaptive_hash_index_parts
    Number of AHI partitions, each with its own latch (default 8, maximum 512). The manual suggests more partitions, or disabling AHI, when many threads wait on btr0sea latches. Test and measure. [Official docs]

LOG (REDO)

Checkpoint age • Flush pressure
#

The number that actually matters here is not printed on most builds. You have to derive it yourself: checkpoint age = current LSN minus last checkpoint LSN. Percona Server and PXC are the exception: they print it, and its maximum, directly.

  1. 1
    Verify first
    • Subtract Last checkpoint at from Log sequence number.
    • Express it as a fraction of total redo capacity. The absolute byte figure means nothing without that denominator.
    • Compare Log flushed up to against the current LSN to see how far writes are trailing generation.
    • Around 75% of redo capacity is concerning. High but stable can be acceptable. [Standard practice] (a heuristic, not a documented limit)
    • A main-thread state of checking free log space signals log pressure. [Standard practice]
  2. 2
    Confirm with
    SHOW GLOBAL STATUS LIKE 'Innodb_os_log%';
    SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';
    SHOW GLOBAL VARIABLES LIKE 'innodb_redo_log_capacity';   -- 8.0.30 and later
    SHOW GLOBAL VARIABLES LIKE 'innodb_log_file%';           -- earlier versions

    Innodb_log_waits counting up means the log buffer, not the log files, is undersized for the write rate.

  3. 3
    The trap: a high byte-generation rate is fine as long as flushing keeps pace. It is checkpoint age relative to capacity, not throughput, that predicts stalling. As that ratio climbs, InnoDB moves from background flushing to progressively more aggressive flushing and eventually to synchronous preflushing, which is where write latency falls off a cliff. Treat the commonly quoted percentages as rules of thumb, not a documented constant: what matters is the trend across captures.
  4. 4
    Next step
    • Sustained high checkpoint age under normal load usually means the redo log is too small. Making it bigger is the fix; flushing harder is treating the symptom.
    • Check File I/O and OS disk latency before concluding the log is the problem; slow storage looks identical from here.
    • To size the redo log, measure LSN growth over an interval, scale it to one hour, and size the log to hold about an hour of peak writes. [Standard practice]
    innodb_redo_log_capacity
    Total redo size on 8.0.30 and later. Earlier, use innodb_log_file_size across the log group.
    innodb_log_buffer_size
    In-memory buffer ahead of the log files. Raise it if Innodb_log_waits grows.
    innodb_flush_log_at_trx_commit
    Durability, not just speed. Lowering it trades away crash safety.

BUFFER POOL AND MEMORY

Working set • Eviction pressure
#

Page counts and hit rate for the window you captured, plus, when INDIVIDUAL BUFFER POOL INFO is present, the same numbers broken out per instance. On a server with a lot of instances that block can be most of the output by line count. Scan it for outliers, do not try to read every line.

  1. 1
    Verify first
    • Free buffers: a small free list is normal, because the page cleaner works to keep only a limited number of pages free per instance (innodb_lru_scan_depth governs how far it scans; verify the exact behavior on your version). A sustained hard zero means the page cleaner is behind demand.
    • If the error log around the capture shows Difficult to find free blocks in the buffer pool (N search iterations), a thread needed an empty page and still had none after more than 20 tries at scanning the LRU list and flushing a page. From the third try on it waits 10 ms each time, so N iterations is roughly N × 10 ms of waiting. InnoDB also switches the monitor on when it logs this, which is why monitor output then shows up in the error log by itself. [InnoDB source]
    • Hit rate, read against the window length from the header before drawing any conclusion from it. 999 to 1000 per 1000 is efficient; about 994 per 1000 suggests an undersized pool or scans. [Standard practice] (a heuristic, not a documented limit)
    • Pages evicted without access and read-ahead figures: high eviction without access is cache pollution, typically from scans.
    • Per instance, look for one instance whose free pages or hit rate is an outlier against the others.
  2. 2
    Confirm with
    SELECT POOL_ID, POOL_SIZE, FREE_BUFFERS, DATABASE_PAGES,
           MODIFIED_DATABASE_PAGES, HIT_RATE
    FROM information_schema.INNODB_BUFFER_POOL_STATS;
  3. 3
    The trap: a hit rate of 999 or 1000 per 1000 does not prove the pool is correctly sized. Over a 2-second window it can hide a physical read spike that happened just outside the window, and at high throughput even a 0.1% miss rate can be enough to saturate the disk. Rows read per second has no useful universal threshold either: healthy busy servers range from a few hundred to several million.
  4. 4
    Next step
    • Correlate physical reads with expensive queries in the slow log before resizing anything. A single unindexed query can look like an undersized pool.
    • When free buffers sit at 0 with LRU flushing pending, innodb_lru_scan_depth is a lever to test. When dirty pages are large and fsyncs or aio writes are pending, look at innodb_max_dirty_pages_pct_lwm and innodb_write_io_threads. Test and measure each. [Standard practice]
    innodb_buffer_pool_size
    The highest-leverage InnoDB setting on most servers.
    innodb_buffer_pool_instances
    Reduces per-instance mutex contention on large pools.
    innodb_page_cleaners
    Parallelises dirty page flushing across instances.

ROW OPERATIONS

Throughput • Concurrency queue
#

How many rows InnoDB is actually reading, inserting, updating and deleting, and how many queries are inside the engine right now.

  1. 1
    Verify first
    • N queries inside InnoDB, N queries in queue. A persistently non-zero queue means threads are being held at the door.
    • Read, insert, update and delete rates against your own baseline. There is no universal healthy value.
  2. 2
    Confirm with
    SHOW GLOBAL STATUS LIKE 'Innodb_rows%';
    SHOW GLOBAL VARIABLES LIKE 'innodb_thread_concurrency';
  3. 3
    The trap: row operations are not query counts. One unindexed query reading ten million rows will dominate this section while the server's actual query rate stays flat. A spike here points at a query plan, not necessarily at load.
  4. 4
    Next step
    • If queries are queueing, check innodb_thread_concurrency. A value of 0 means unlimited, the concurrency check is switched off entirely, and that is the default. Some high-concurrency setups set it near the CPU core count, but advice varies, and under a thread pool it is usually unnecessary. [Standard practice] A non-zero value there with a standing queue means that cap, not the workload, is the ceiling.
    • If the rates look wrong rather than merely high, go to the slow query log and look for a plan change.

COMMON MISREADINGS

Traps • Before you conclude
#

Mistakes that come up again and again when this output is read in a hurry. [Standard practice]

  • Expecting every error 1213 to appear here. Metadata-lock deadlocks never do, and the capture may come from a different instance. See Deadlocks. [Standard practice]
  • Expecting the blocker's SQL text. This output names the blocking transaction, not necessarily its statement. See Transactions. [Standard practice]
  • Treating semaphore waits as corruption. They are rarely that, and are often a symptom of saturated I/O. See Semaphores. [Standard practice]
  • Judging purge from History list length alone. A long-open read view can sit beside a healthy-looking number. See Transactions. [Standard practice]
  • Relying on a single capture. Rates need a window, and a burst can land outside it. See Header and background. [Standard practice]

READING THEM TOGETHER

Cross-section correlation
#

No single section tells the whole story. Make a habit of checking these pairings before you draw a conclusion from just one of them.

If you seeAlso check
Waiters concentrated on one latch Buffer pool for a hot page, and where the latch was created, not only where threads wait.
History list length climbing Transactions for the oldest read view, before touching purge settings.
Checkpoint age rising File I/O and dirty page counts. Slow storage and an undersized pool both surface here as slow log flushing.
Many semaphore waiters File I/O for a pending aio queue. Waits are often a symptom of saturated I/O rather than a latch problem.
A deadlock Whether it is the only one or the latest of many, via innodb_print_all_deadlocks and the error log.
Any rate that looks implausible The window it was averaged over.

And remember, this command only sees InnoDB, not the OS underneath it. A stall that lines up with high iostat await times is really a storage problem wearing an InnoDB costume. For the rest of the picture, pair this output with SHOW GLOBAL STATUS and SHOW GLOBAL VARIABLES in the configuration analyzer.

FURTHER READING

Primary sources
#
  • MySQL Reference Manual, the InnoDB Monitors chapter, for the official field-by-field description of this output.
  • MariaDB Knowledge Base, SHOW ENGINE INNODB STATUS, for where MariaDB's output diverges from upstream.
  • Percona's engineering blog has published section-by-section walkthroughs of this output over the years, useful for historical context on how a given section's format evolved.
  • On this site: the variable reference for every innodb_* setting named above, and the SEIS Advisor to run these checks against your own capture automatically.

FREQUENTLY ASKED QUESTIONS

Quick answers
#
What is SHOW ENGINE INNODB STATUS?

It is a MySQL command that prints a point-in-time snapshot of InnoDB's internal state: transactions, locks, the most recent deadlock, buffer pool contents, the redo log, and pending I/O. It carries no version string, and its exact format differs by engine and version, which is what this guide walks through.

Why does SHOW ENGINE INNODB STATUS look different on MySQL 8.0/8.4, Percona, PXC and MariaDB?

Each engine and major version prints InnoDB's internals a little differently, and a few sections change shape completely. The main things you will notice:

  • MySQL 8.0 and 8.4, Percona Server 8.x, PXC 8.x: in the captures we tested the main thread line reads state=sleeping instead of state: sleeping (exact version boundaries not confirmed, verify on your build), the LOG section prints many more LSN lines, the FILE I/O trailer is shorter, and the semaphore spin and OS-wait counters are often left at zero, so zero there does not mean zero contention. MySQL 8.4 also defaults change buffering to none (earlier all) [Official docs], so expect little or no change-buffer activity, and it turns the adaptive hash index off by default (on before 8.4) [Official docs].
  • Percona Server and PXC: print Max checkpoint age, and the Percona Server 8.0.33 and PXC 8.4.8 captures we tested also print Checkpoint age directly, so you can read redo pressure without doing the subtraction yourself (verify for other builds).
  • MariaDB 10.6 and later: still follows the older 5.7-style layout, with a short LOG section. Its change buffer defaults to none from 10.6.7 (also 10.7.3 and 10.8.2), is deprecated and ignored from 10.9, and was removed in 11.0 [Official docs]. innodb_log_file_size is dynamic from 10.9 [Official docs]. The adaptive hash index is off by default [Official docs].
  • MariaDB lock waits: TRX HAS BEEN WAITING ... us is printed in microseconds, where MySQL prints seconds. [Standard practice] (verify on your build)
  • Aurora MySQL: reader instances show no write transactions in their output. A reader's read view can block purge on the writer, so compare the oldest read view IDs across the writer and the readers. [Standard practice]
  • MySQL 9.7 LTS: no 9.7 capture has been part of our test set yet, so treat any 9.7-specific differences as unconfirmed.

SEIS Advisor detects the engine and version family from the output itself and adjusts what it rates to match, so you do not have to work this out by hand.

Can a single SHOW ENGINE INNODB STATUS capture tell you the root cause of a slowdown?

Rarely by itself. It is one sample at one instant, several of its own numbers are rates that need a second capture to mean anything, and correlating it with buffer pool status, OS-level I/O metrics, and repeat captures over a few minutes turns a single snapshot into actual evidence. Two captures is a minimum: sample every 15 to 30 seconds for several minutes and read the trend. [Standard practice]

Is there a tool that reads SHOW ENGINE INNODB STATUS for you?

Yes. SEIS Advisor, part of MySQL Advisor, takes your own SHOW ENGINE INNODB STATUS output and runs the checks described in this guide against it automatically, flags what actually needs attention, and links back to the reasoning behind each finding.