This page needs a newer browser to run its tools. You can still read everything here.

InnoDB internals interview questions

Interview questions on innoDB internals: short warm-ups, then what each harder question tests, the first step of a good answer and its follow-ups.

9 questions: 3 warm-ups and 6 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

Fresher

Where does a committed row change actually live on disk, immediately after the commit returns?

Answered in the lesson InnoDB architecture: pages, tablespaces, threads
Fresher to mid

What does the redo log in InnoDB do, and why does it make commit return before the data files are updated?

Answered in the lesson Redo log, doublewrite and crash recovery
Fresher to mid

Why does one scan of a table larger than the buffer pool not empty it of the pages queries use most?

Answered in the lesson The buffer pool: LRU, flushing and warm-up

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

Mid to senior

A MySQL 8.4 server's data directory is 400 GB and ibdata1 alone is 320 GB, although two large tables were dropped last month and the disk is nearly full. Explain why nothing shrank, and give a plan that returns the space.
What it tests

Knowing which tablespace holds a table's data, why a shared tablespace file never shrinks, and planning a rebuild that returns disk space without losing data.

First step

Find out what is in that file before planning anything: read innodb_file_per_table, then information_schema.FILES and INNODB_TABLESPACES to see which tables are inside innodb_system and how much of it is the change buffer and undo. Only then choose between moving tables out table by table and rebuilding the instance from a dump.
Follow-ups
- Which parts of ibdata1 can never be moved out of it?
- What does moving a table to its own tablespace cost while it runs?
- How do you return the space when the disk has no room for a rebuild?
- What setting stops the next server growing the same way?
From the lesson InnoDB architecture: pages, tablespaces, threads
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I have a licence key
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 5 minutes
Mid to staff

A developer hands you one orders.ibd file from a server that no longer starts. There is no dump and nobody kept the schema. They want the table's definition, and the rows if that is possible at all. What can you get from the file, and how?
What it tests

Knowing what serialized dictionary information is, which tool reads it from a file, and the prerequisites and limits of importing a tablespace into another server.

First step

Separate the two questions. The definition is inside the file: ibd2sdi prints it as JSON from a file no server is using. The rows need a working server, a table created from that definition, a discarded tablespace and an import, which is supported only for a file-per-table tablespace whose page size matches the destination.
Follow-ups
- Which tablespaces carry no serialized definition at all?
- What has to match between the file and the server you import it into?
- How do you turn that JSON into a CREATE TABLE statement?
- What would you change so this is never needed again?
From the lesson InnoDB architecture: pages, tablespaces, threads
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 5 minutes
Mid to senior

The checkpoint age keeps growing and eventually writes stall. What is happening, and how would you investigate and fix it?
What it tests

Diagnose checkpoint lag, connect it to flush rate, and choose a fix.

First step

Query the checkpoint and current LSN from Innodb_redo_log_checkpoint_lsn and Innodb_redo_log_current_lsn. If the gap approaches innodb_redo_log_capacity, dirty-page flushing is not keeping up.
Follow-ups
- Why might flushing fall behind?
- What settings control flush aggressiveness?
- When is increasing the redo log the right answer?
From the lesson Redo log, doublewrite and crash recovery
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to staff

A service sees slow writes. Someone suggests setting innodb_flush_log_at_trx_commit to 2 and sync_binlog to 0. What do those changes risk, and when might they be acceptable?
What it tests

State the durability cost of each setting and justify when it is acceptable.

First step

innodb_flush_log_at_trx_commit = 2 syncs the redo log once a second, not at every commit, so an OS or power crash can lose up to a second of commits. sync_binlog = 0 leaves the binary log to the operating system, so such a crash can leave committed transactions missing from it.
Follow-ups
- What kind of crash does this survive?
- How does this affect replicas?
- Would you use these settings in production?
From the lesson Redo log, doublewrite and crash recovery
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 4 minutes
Mid to senior

A billing service on MySQL 8.4 has a 24 GiB buffer pool. Every night a reporting job of about 40 minutes reads a year of invoices, and each morning the first hour of checkout is slow until the cache recovers. How do you work out what the job does to the pool, and what do you change?
What it tests

Measuring the pool's misses over time, telling a scan-resistance setting from a working set that does not fit, and choosing between server settings, a different schedule and moving the report elsewhere.

First step

Measure before changing anything: sample Innodb_buffer_pool_reads and Innodb_buffer_pool_read_requests once a minute from before the job until the slow hour ends, and read the job's session settings and the two LRU variables, innodb_old_blocks_time and innodb_old_blocks_pct, on the server itself. Then decide whether the job pushed hot pages out or the hot pages never fitted in the first place.
Follow-ups
- What do the two counters look like if the job is the cause, and what if it is not?
- Why would the one-second rule fail to protect the pool for this job?
- Which settings and which schedule changes would you try, and in what order?
- When is a replica the better place for the report?
From the lesson The buffer pool: LRU, flushing and warm-up
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 6 minutes
Mid to staff

A team moves its primary to a 64 GB host that runs only MySQL 8.4, on NVMe storage rated at 50,000 IOPS. They plan a 60 GB buffer pool and innodb_io_capacity at 50,000; the old server stalled under write bursts. What do you change in the plan, and how do you check it?
What it tests

Leaving memory for everything beside the pool, setting I/O capacity from what flushing needs rather than the disk's rating, and reading dirty pages, waits and checkpoint age to confirm the result.

First step

Separate the two decisions. For memory, add up what the host needs beside the pool before choosing its size. For flushing, start from the 8.4 defaults and measure dirty pages, waits for clean pages and the gap between the redo checkpoint and the current position during a write burst, then raise the capacity only if flushing falls behind.
Follow-ups
- What does 60 GB leave for everything else, and what would you choose instead?
- Why is the drive's rated IOPS a poor value for innodb_io_capacity?
- Which counters show that flushing keeps up during a write burst?
- Where do innodb_io_capacity_max and innodb_flush_sync fit in?
From the lesson The buffer pool: LRU, flushing and warm-up
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 6 minutes

All MySQL & MariaDB in Depth interview questions · The MySQL & MariaDB in Depth track

Warm-up questions

Where does a committed row change actually live on disk, immediately after the commit returns?

What does the redo log in InnoDB do, and why does it make commit return before the data files are updated?

Why does one scan of a table larger than the buffer pool not empty it of the pages queries use most?

Questions with model answers

A MySQL 8.4 server's data directory is 400 GB and ibdata1 alone is 320 GB, although two large tables were dropped last month and the disk is nearly full. Explain why nothing shrank, and give a plan that returns the space.

A developer hands you one orders.ibd file from a server that no longer starts. There is no dump and nobody kept the schema. They want the table's definition, and the rows if that is possible at all. What can you get from the file, and how?

The checkpoint age keeps growing and eventually writes stall. What is happening, and how would you investigate and fix it?

A service sees slow writes. Someone suggests setting innodb_flush_log_at_trx_commit to 2 and sync_binlog to 0. What do those changes risk, and when might they be acceptable?

A billing service on MySQL 8.4 has a 24 GiB buffer pool. Every night a reporting job of about 40 minutes reads a year of invoices, and each morning the first hour of checkout is slow until the cache recovers. How do you work out what the job does to the pool, and what do you change?

A team moves its primary to a 64 GB host that runs only MySQL 8.4, on NVMe storage rated at 50,000 IOPS. They plan a 60 GB buffer pool and innodb_io_capacity at 50,000; the old server stalled under write bursts. What do you change in the plan, and how do you check it?