Skip to main content

MySQL InnoDB performance | MySQL InnoDB Stats

 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

Labels:
Location: San Francisco, CA, USA

Comments

Post a Comment

Popular posts from this blog

MySQL InnoDB cluster troubleshooting | commands

Cluster Validation: select * from performance_schema.replication_group_members; All members should be online. select instance_name, mysql_server_uuid, addresses from  mysql_innodb_cluster_metadata.instances; All instances should return same value for mysql_server_uuid SELECT @@GTID_EXECUTED; All nodes should return same value Frequently use commands: mysql> SET SQL_LOG_BIN = 0;  mysql> stop group_replication; mysql> set global super_read_only=0; mysql> drop database mysql_innodb_cluster_metadata; mysql> RESET MASTER; mysql> RESET SLAVE ALL; JS > var cluster = dba.getCluster() JS > var cluster = dba.getCluster("<Cluster_name>") JS > var cluster = dba.createCluster('name') JS > cluster.removeInstance('root@<IP_Address>:<Port_No>',{force: true}) JS > cluster.addInstance('root@<IP add>,:<port>') JS > cluster.addInstance('root@ <IP add>,:<port> ') JS > dba.getC
Post a Comment
Read more

MySQL slave Error_code: 1032 | MySQL slave drift | HA_ERR_KEY_NOT_FOUND

MySQL slave Error_code: 1032 | MySQL slave drift: With several MySQL, instance with master slave replication, I have one analytics MySQL, environment which is larger in terabytes, compared to other MySQL instances in the environment. Other MySQL instances with terabytes of data are running fine master, slave replication. But this analytics environment get started generating slave Error_code :1032. mysql> show slave status; Near relay log: Error_code: 1032; Can't find record in '<table_name>', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log <name>-bin.000047, end_log_pos 5255306 Near master section: Could not execute Update_rows event on table <db_name>.<table_name>; Can't find record in '<table_name>', Error_code: 1032; Can't find record in '<table_name>', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log <name>-bin.000047, end_l
1 comment
Read more

Create MySQL database with hyphen

Create MySQL database with hyphen: If you are trying to create MySQL database with hyphen " - " in the name such as test-db and get error  " your MySQL server version for the right syntax to use near '-db' at line" then you might be wondering how to get it done as your business require MySQL database name with hyphen " - "  Here is the fix, use escape character " ` " before and after database name such as `test-db` and you will be able to create database with hyphen. CREATE DATABASE `test-db`; 
Post a Comment
Read more