17.8.10.2 Configuring Non-Persistent Optimizer Statistics Parameters (original) (raw)

17.8.10.2 Configuring Non-Persistent Optimizer Statistics Parameters

This section describes how to configure non-persistent optimizer statistics. Optimizer statistics are not persisted to disk wheninnodb_stats_persistent=OFF or when individual tables are created or altered withSTATS_PERSISTENT=0. Instead, statistics are stored in memory, and are lost when the server is shut down. Statistics are also updated periodically by certain operations and under certain conditions.

Optimizer statistics are persisted to disk by default, enabled by the innodb_stats_persistent configuration option. For information about persistent optimizer statistics, see Section 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”.

Optimizer Statistics Updates

Non-persistent optimizer statistics are updated when:

• Running ANALYZE TABLE.
• Running SHOW TABLE STATUS,SHOW INDEX, or querying the Information Schema TABLES orSTATISTICS tables with theinnodb_stats_on_metadata option enabled.
  The default setting forinnodb_stats_on_metadata isOFF. Enablinginnodb_stats_on_metadata may reduce access speed for schemas that have a large number of tables or indexes, and reduce stability of execution plans for queries that involve InnoDB tables.innodb_stats_on_metadata is configured globally using aSET statement.

SET GLOBAL innodb_stats_on_metadata=ON  

• Starting a mysql client with the--auto-rehash option enabled, which is the default. Theauto-rehash option causes allInnoDB tables to be opened, and the open table operations cause statistics to be recalculated.
  To improve the start up time of the mysql client and to updating statistics, you can turn offauto-rehash using the--disable-auto-rehash option. The auto-rehash feature enables automatic name completion of database, table, and column names for interactive users.
• A table is first opened.
• InnoDB detects that 1 / 16 of table has been modified since the last time statistics were updated.

Configuring the Number of Sampled Pages

The MySQL query optimizer uses estimatedstatistics about key distributions to choose the indexes for an execution plan, based on the relativeselectivity of the index. When InnoDB updates optimizer statistics, it samples random pages from each index on a table to estimate thecardinality of the index. (This technique is known asrandom dives.)

To give you control over the quality of the statistics estimate (and thus better information for the query optimizer), you can change the number of sampled pages using the parameterinnodb_stats_transient_sample_pages. The default number of sampled pages is 8, which could be insufficient to produce an accurate estimate, leading to poor index choices by the query optimizer. This technique is especially important for large tables and tables used injoins. Unnecessaryfull table scans for such tables can be a substantial performance issue. SeeSection 10.2.1.23, “Avoiding Full Table Scans” for tips on tuning such queries.innodb_stats_transient_sample_pages is a global parameter that can be set at runtime.

The value ofinnodb_stats_transient_sample_pages affects the index sampling for all InnoDB tables and indexes wheninnodb_stats_persistent=0. Be aware of the following potentially significant impacts when you change the index sample size:

• Small values like 1 or 2 can result in inaccurate estimates of cardinality.
• Increasing theinnodb_stats_transient_sample_pages value might require more disk reads. Values much larger than 8 (say, 100), can cause a significant slowdown in the time it takes to open a table or execute SHOW TABLE STATUS.
• The optimizer might choose very different query plans based on different estimates of index selectivity.

Whatever value ofinnodb_stats_transient_sample_pages works best for a system, set the option and leave it at that value. Choose a value that results in reasonably accurate estimates for all tables in your database without requiring excessive I/O. Because the statistics are automatically recalculated at various times other than on execution ofANALYZE TABLE, it does not make sense to increase the index sample size, runANALYZE TABLE, then decrease sample size again.

Smaller tables generally require fewer index samples than larger tables. If your database has many large tables, consider using a higher value forinnodb_stats_transient_sample_pages than if you have mostly smaller tables.