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 AdvisorHEADER & BACKGROUND THREAD
Sampling window • Master threadStart 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
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: sleepingon 5.7-era builds and MariaDB 10.4 and 10.6, andstate=sleepingon 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.
- Read the capture timestamp and the window:
-
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\GThe 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_TRXsampled 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_outputmakes InnoDB print this output to the error log periodically, andinnodb_status_output_locksadds 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
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/sin 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
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 • StallsInnoDB 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
Verify first
- Count the waiters, then group them by latch address, not by source
file. Two different
file:lineentries on the same0x...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 filefor the latch, not just where threads are waiting. Threads waiting inrow0sel.ccon a latch created inbuf0buf.ccusually 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 singlesignal countline 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]
- Count the waiters, then group them by latch address, not by source
file. Two different
-
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
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 waitvalue 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
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:lineand 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 asN 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 onlyInnoDB 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
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
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
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
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 COMMITTEDoften 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 withlog_error_verbosity=3) and turn it off afterwards. CountingRECORD LOCKSlines 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 issueThis 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
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
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
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
.ibdfile 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 runCHECK TABLEon it. [Standard practice] (reported case, verify on your build)
TRANSACTIONS
Purge lag • Long transactions • Lock waitsThree 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
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 counterandPurge done for trx's n:o: a huge gap means purge is far behind. - Scan the active list for
ACTIVE n secwith a largen, 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 GRANTEDand find the blocking transaction elsewhere in the same section.
-
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_waitsis the convenient view where the sys schema is installed; on MySQL 8.0 the underlying tables areperformance_schema.data_locksanddata_lock_waits. -
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
SELECTand 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
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_threadsand 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_schemaorsysviews 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/OA look at InnoDB's background I/O helper threads and how deep each queue was sitting at the moment you captured this.
-
1
Verify first
Pending normal aio readsandaio 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
Confirm with
SHOW GLOBAL STATUS LIKE 'Innodb_data%';Portable across all three engines. Where
performance_schemafile instrumentation is enabled,events_waits_summary_global_by_event_namefiltered onwait/io/file/innodb/%gives per-file latency;AVG_TIMER_WAIT / 1000000000converts picoseconds to milliseconds. -
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
Next step
- Check the OS at the same moment with
iostat -xz 1or 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.
- Check the OS at the same moment with
INSERT BUFFER AND ADAPTIVE HASH INDEX
Change buffer • Hash lookupsTwo 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
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/sagainstnon-hash searches/s. That ratio is the whole value proposition.
-
2
Confirm with
SHOW GLOBAL VARIABLES LIKE 'innodb_change_buffering'; SHOW GLOBAL VARIABLES LIKE 'innodb_adaptive_hash_index%'; -
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
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
btr0sealatches. Test and measure. [Official docs]
- If AHI latch contention is showing up in Semaphores (waits in
LOG (REDO)
Checkpoint age • Flush pressureThe 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
Verify first
- Subtract
Last checkpoint atfromLog sequence number. - Express it as a fraction of total redo capacity. The absolute byte figure means nothing without that denominator.
- Compare
Log flushed up toagainst 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 spacesignals log pressure. [Standard practice]
- Subtract
-
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 versionsInnodb_log_waitscounting up means the log buffer, not the log files, is undersized for the write rate. -
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
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_waitsgrows. - innodb_flush_log_at_trx_commit
- Durability, not just speed. Lowering it trades away crash safety.
BUFFER POOL AND MEMORY
Working set • Eviction pressurePage 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
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_depthgoverns 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 accessand 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
Confirm with
SELECT POOL_ID, POOL_SIZE, FREE_BUFFERS, DATABASE_PAGES, MODIFIED_DATABASE_PAGES, HIT_RATE FROM information_schema.INNODB_BUFFER_POOL_STATS; -
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
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_depthis a lever to test. When dirty pages are large and fsyncs or aio writes are pending, look atinnodb_max_dirty_pages_pct_lwmandinnodb_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 queueHow many rows InnoDB is actually reading, inserting, updating and deleting, and how many queries are inside the engine right now.
-
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
Confirm with
SHOW GLOBAL STATUS LIKE 'Innodb_rows%'; SHOW GLOBAL VARIABLES LIKE 'innodb_thread_concurrency'; -
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
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.
- If queries are queueing, check
COMMON MISREADINGS
Traps • Before you concludeMistakes 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 correlationNo 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 see | Also 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 answersWhat 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=sleepinginstead ofstate: 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 tonone(earlierall) [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 printCheckpoint agedirectly, 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
nonefrom 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_sizeis dynamic from 10.9 [Official docs]. The adaptive hash index is off by default [Official docs]. - MariaDB lock waits:
TRX HAS BEEN WAITING ... usis 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.