1Z0-908 practice question 17 of 25

Which statement is true about InnoDB persistent index statistics?

Full set: 140 questions
Answer options
A: Updating index statistics is an I/O expensive operation.
B: Index statistics are calculated from pages buffered in the buffer pool for tables with InnoDB storage engine.
C: Setting innodb_stats_auto_recalc=ON causes statistics to be updated automatically when a new index is created.
D: Execution plans based on transient index statistics improve precision when innodb_stats_persistent_sample_pages is increased. Correct answer
E: Increasing innodb_stats_persistent_sample_pages determines higher pages scanning speed, at the cost of increased memory usage.
F: Tables are scanned and index statistics recalculated when an instance is restarted.
Correct Answer:
D. Execution plans based on transient index statistics improve precision when innodb_stats_persistent_sample_pages is increased.
Explanation

This statement, despite a likely typo, correctly identifies a key mechanism of InnoDB persistent statistics. The configuration variable innodbstatspersistentsamplepages directly controls the number of index pages sampled when persistent statistics are calculated via ANALYZE TABLE. Increasing this value leads to a more thorough analysis of the index data, resulting in more accurate statistics (e.g., cardinality estimates). The optimizer uses these more precise statistics to generate more stable and efficient query execution plans. The word "transient" in the option appears to be an error; the variable mentioned only affects persistent statistics, which is the subject of the question.

Why Incorrect

A. While often true, this is a general statement about statistics gathering, not a specific characteristic that distinguishes persistent statistics from other types. The cost is also configurable.
B. This describes the behavior of transient (non-persistent) statistics, which are calculated from a random sample of pages already in the buffer pool, making them volatile.
C. The innodbstatsautorecalc setting triggers a recalculation when approximately 10% of the rows in a table have been modified by DML, not when a DDL statement like CREATE INDEX is run.
E. Increasing the number of sample pages makes the statistics-gathering process more thorough but slower; it decreases the overall scanning speed, it does not increase it.
F. The primary purpose of persistent statistics is to avoid recalculating statistics on an instance restart by storing them durably on disk. This statement describes the behavior with transient statistics. ---

References

1. MySQL 8.0 Reference Manual, Section 15.8.10.1, "Configuring Persistent Optimizer Statistics Parameters": This section details the innodbstatspersistentsamplepages variable. It states, "The number of index pages to sample when estimating cardinality and other statistics... Increasing the value improves the accuracy of index statistics, which can improve query execution plans, at the cost of increased I/O during the execution of ANALYZE TABLE." This directly supports the reasoning for option D being correct (assuming the typo) and why option E is incorrect.

2. MySQL 8.0 Reference Manual, Section 15.8.10.1, "Configuring Persistent Optimizer Statistics Parameters": The same section explains the innodbstatsautorecalc variable: "When enabled, statistics are automatically recalculated when a table is modified by more than 10% of the rows." This confirms that the trigger is DML-based, making option C incorrect.

3. MySQL 8.0 Reference Manual, Section 8.3.7, "InnoDB Persistent Statistics": This section contrasts persistent and non-persistent statistics. It clarifies that non-persistent statistics were calculated based on a "random dive into a few index pages" (implying what's in the buffer pool) and were lost on restart. This supports the reasoning for options B and F being incorrect as they describe the old, non-persistent behavior.

Scroll to Top