1Z0-908 practice question 17 of 25
Which statement is true about InnoDB persistent index statistics?
Full set: 140 questionsThis 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.
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. ---
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.
Keep practicing
Take the free 1Z0-908 practice test
Ten exam-style questions with answers and explanations, plus the exam facts and study guides.
More questions
Other 1Z0-908 practice questions
- Question 2You must run multiple instances of MySQL Server on a single host. Which three methods are…
- Question 3Which two tools are available to monitor the global status of InnoDB locking? (Choose two…
- Question 4Which two MySQL Server accounts are locked by default? (Choose two.)
- Question 5All MySQL Server instances belonging to InnoDB Cluster have SSL configured and enabled. Y…
- Question 6t is a non-empty InnoDB table. Examine these statements, which are executed in one sessio…
- Question 8Examine this command, which executes successfully: $ mysqlrouter --bootstrap user@hostnam…
