InnoDB Stats Parameters:
InnoDB stats parameter controls the indexed column’s index statistics
- persistent to disk or
- not persistence to disk
There are two options available to control them,
- Globally and
- locally at table level.
This
parameter needs to be tune according to the nature of the application,
DML activities against the database and tables, for stable execution
plan of the query or for better performance of the query.
There are two options are available to store index statistics persistent to disk or non-persistent to disk.
Following
parameter available for MySQL InnodB stats, starting from MySQL 8. In
older version of MySQL such 5.6 and 5.7 innodb_stats_sample_pages was
used. Starting form MySQL 8.0 it is deprecated and we have to use
innodb_stats_persistent_sample_pages
Ref.: https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_stats_auto_recalc
• innodb_stats_auto_recalc
• innodb_stats_include_delete_marked
• innodb_stats_method
• innodb_stats_on_metadata
• innodb_stats_persistent
• innodb_stats_persistent_sample_pages
• innodb_stats_transient_sample_pages
Persistence statistics parameters:
innodb_stats_auto_recalc:
It
is ON by default, and cause InnoDB to calculate persistence statistics
after the 10% of the rows data of the table changed. It is applied when
innodb_stats_persistent is enabled.
When innodb_stats_peristent is
enabled, it stores statistics data on disk, and make them persistent,
which provides plan stability. Formerly when server restart and some
other operations executed, it clears statistics data. Different estimate
produce different execution plan for the query, and user experience
variation in the performance of the query.
I can be also enabled
when creating table or by altering table using STATS_PERSISTENT=1. How
much stats data produced, controlled by parameter
innodb_stats_transient_sample_pages?
innodb_stats_persistent:
By
default it is ON. Specifies whether index stats persistent to disk or
not. If it is OFF index, statistics calculated frequently, resulting in
variation of the query execution plan. CREATE TABLE and ALTER TABLE can
used to overwrite global setting.
innodb_stats_persistent_sample_pages:
This
parameter controls the no. of index pages to sample, default value is
20 no. of index pages. It provides cardinality and statistics of indexed
column. It defines the no. of pages at global level.
For immediate
up to date statistics, execute analyze table after changing this
parameter. You would be able to see the improvement in performance of
the query.
innodb_stats_persistent_sample_pages only applies when innodb_stats_persistent is enabled for a table;
when innodb_stats_persistent is disabled, innodb_stats_transient_sample_pages applies instead.
Ref.: https://dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html
innodb_stats_method:
Default
is nulls_equal. All NULL index values are considered equal and form a
single value group with a size equal to the number of NULL values. Other
values are nulls_unequal, nulls_ignored.
Non- Persistent statistics parameters
innodb_stats_transient_sample_pages:
Default
value is 8. The no. of pages samples for the index pages of the indexed
column. It samples the number of index pages when estimating
cardinality and other statistics for the indexed column. This happen
while ANALYZING TABLE. This parameter is applicable to Non-Persistent
Optimizer Statistics Parameters. Explore more about it -
https://dev.mysql.com/doc/refman/8.0/en/innodb-statistics-estimation.html
If innodb_stats_persistent=OFF, the number of pages sampled is defined by innodb_stats_transient_sample_pages
innodb_stats_on_metadata:
Default is OFF. This parameter is applicable only when optimizer statistics are configured to be non-persistent.
innodb_stats_include_delete_marked:
Default
is OFF. If this parameter is enabled ANALYZE TABLE considered
delete-marked records when calculating statistics, which could results
in in efficient query execution plan.
IcanExplore Fun - Huto Gaiti Mele
Comments
Post a Comment