You have a consistent InnoDB backup created with mysqldump, the largest table is 50 GB in size. You start to restore your backup with this command: shell> mysql -u root -P < backup.sql After 30…

The correct answer is A. A. The MySQL server has stopped inserting data to check index consistency. During a large mysqldump restore into InnoDB, secondary index changes are buffered in InnoDB's change buffer rather than immediately applied to the index pages on disk. As the 50 GB table grows, InnoDB must periodically pause new inserts to merge those buffered index changes…

Backup and Recovery

Question

You have a consistent InnoDB backup created with mysqldump, the largest table is 50 GB in size. You start to restore your backup with this command: shell> mysql -u root -P < backup.sql After 30 minutes, you notice that the rate of restore seems to have slowed down. No other processes or external factors are affecting server performance. Which is the most likely explanation for this slowdown?

Options

AA. The MySQL server has stopped inserting data to check index consistency.
BB. InnoDB is doing CRC32 checks over the tablespace data as it grows.
CC. The MySQL server is taking a periodical snapshot of data so it can resume the restore if it is interrupted mid-way.
DD. InnoDB has filled the redo log and now must flush the pages.
EE. Secondary indexes no longer fit into the buffer pool.

How the community answered

(38 responses)

A
84% (32)
B
8% (3)
C
5% (2)
D
3% (1)

Explanation

During a large mysqldump restore into InnoDB, secondary index changes are buffered in InnoDB's change buffer rather than immediately applied to the index pages on disk. As the 50 GB table grows, InnoDB must periodically pause new inserts to merge those buffered index changes back into the actual tablespace - this reconciliation process is what "checking index consistency" refers to, and the pauses grow longer as more data accumulates.

Why the distractors are wrong:

B - CRC32 page checksums happen continuously at the page I/O level, not as a periodic batch operation that would cause a noticeable progressive slowdown.
C - MySQL has no built-in mechanism to snapshot a restore in progress or resume a failed one; this feature simply does not exist.
D - A full redo log triggers checkpointing (background dirty-page flushing), which can slow throughput but does not cause discrete, worsening pauses after 30 minutes of a restore.
E - Buffer pool exhaustion causing secondary indexes to spill to disk is the underlying pressure that makes the change buffer merges in A more expensive, not the root explanation. It would also cause a smooth, continuous degradation curve rather than periodic stalls.

Memory tip: Think of InnoDB as a bank teller who batches secondary paperwork (index updates) into a buffer tray. When the tray overflows, the teller stops taking new customers to file everything - that filing pause is option A, and a 50 GB table fills the tray fast.

Topics

#InnoDB bulk inserts#index consistency#constraint checking#restore performance

Community Discussion

No community discussion yet for this question.

Full 1Z0-888 Practice