innodb_stats_on_metadataと統計情報

MSSQL,Oracle運用時の統計情報更新時にデータベースの処理が遅くなる事があるので、
MYSQLでも統計情報の更新をコントロール出来るか確認。

innodb_stats_on_metadata, has existed since MySQL release 5.1.17 to control whether or not
InnoDB performs statistics gathering when metadata statements are executed.

参考
innodb_stats_on_metadata

innodb_stats_on_metadata / innodb_adaptive_hash_index

Solving INFORMATION_SCHEMA slowness

InnoDB スタートアップオプションとシステム変数

SHOW INDEX を使う時気は付けないと

When Does InnoDB Update Table Statistics?

Beginning with release 1.0.2 of the InnoDB Plugin, it is possible to change the
setting of innodb_stats_on_metadata dynamically at runtime with the command
SET GLOBAL innodb_stats_on_metadata=mode,
where mode is either ON or OFF (or 1 or 0). Changing this setting
requires the SUPER privilege and immediately affects the operation of all connections.
と書いてあるので、ロックかかっているバッチの前とかに動的に統計更新しないように設定して様子を見るという選択肢もありなのかもしれません。

innodb_stats_on_metadataがONの場合に、SHOW TABLE STATUS、SHOW INDEXを実行した場合、
information_schemaのTABLESやSTATISTICSにアクセスした場合にも統計情報が更新される。

When this variable is enabled (which is the default, as before the variable was created),
InnoDB updates statistics during metadata statements such as SHOW TABLE STATUS or SHOW INDEX,
or when accessing the INFORMATION_SCHEMA tables TABLES or STATISTICS.
(These updates are similar to what happens for ANALYZE TABLE.)
When disabled, InnoDB does not update statistics during these operations.
Disabling this variable can improve access speed for schemas that have a large number of tables or indexes.
It can also improve the stability of execution plans for queries that involve InnoDB tables.

5.5.25aではDefaultでONになっているようです。

5.6だとDefault OFFになっていいるようです。

mysql> select @@version;
+--------------+
| @@version    |
+--------------+
| 5.6.7-rc-log |
+--------------+
1 row in set (0.00 sec)

mysql>  show variables like 'innodb_stats%';
+--------------------------------------+-------------+
| Variable_name                        | Value       |
+--------------------------------------+-------------+
| innodb_stats_auto_recalc             | ON          |
| innodb_stats_method                  | nulls_equal |
| innodb_stats_on_metadata             | OFF         |
| innodb_stats_persistent              | ON          |
| innodb_stats_persistent_sample_pages | 20          |
| innodb_stats_sample_pages            | 8           |
| innodb_stats_transient_sample_pages  | 8           |
+--------------------------------------+-------------+
7 rows in set (0.00 sec)

設定変更と確認方法

mysql>  SHOW GLOBAL VARIABLES LIKE 'innodb_stats_on_metadata';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_on_metadata | OFF   |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL innodb_stats_on_metadata = 1;
Query OK, 0 rows affected (0.00 sec)

mysql>  SHOW GLOBAL VARIABLES LIKE 'innodb_stats_on_metadata';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_on_metadata | ON    |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL innodb_stats_on_metadata = 0;
Query OK, 0 rows affected (0.00 sec)

mysql>  SHOW GLOBAL VARIABLES LIKE 'innodb_stats_on_metadata';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_on_metadata | OFF   |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql>

OFFの場合は、自分で統計を更新する為に更新ジョブを別途設定する必要がありますね。

Post Navigation