Bug #57252 | disabling innobase_stats_on_metadata disables ANALYZE | ||
---|---|---|---|
Submitted: | 5 Oct 2010 15:20 | Modified: | 13 Jan 2011 18:51 |
Reporter: | Stewart Smith | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
Version: | 5.1.17+ | OS: | Any |
Assigned to: | Vasil Dimov | CPU Architecture: | Any |
Tags: | Contribution |
[5 Oct 2010 15:20]
Stewart Smith
[5 Oct 2010 15:46]
Valeriy Kravchuk
Verified with the following test case on current 5.1.52 from bzr: macbook-pro:5.1 openxs$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.1.52-debug Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like 'innodb_stats%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_stats_on_metadata | OFF | +--------------------------+-------+ 1 row in set (0.00 sec) mysql> create table tinno(c1 int auto_increment primary key, c2 int, key(c2)) engine=InnoDB; Query OK, 0 rows affected (0.08 sec) mysql> insert into tinno(c2) values (rand()*1000); Query OK, 1 row affected (0.00 sec) mysql> insert into tinno(c2) select rand()*1000 from tinno; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into tinno(c2) select rand()*1000 from tinno; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into tinno(c2) select rand()*1000 from tinno; Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into tinno(c2) select rand()*1000 from tinno; Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> insert into tinno(c2) select rand()*1000 from tinno; Query OK, 16 rows affected (0.01 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql> insert into tinno(c2) select rand()*1000 from tinno; Query OK, 32 rows affected (0.01 sec) Records: 32 Duplicates: 0 Warnings: 0 mysql> show table status like 'tinno'\G *************************** 1. row *************************** Name: tinno Engine: InnoDB Version: 10 Row_format: Compact Rows: 64 Avg_row_length: 256 Data_length: 16384 Max_data_length: 0 Index_length: 16384 Data_free: 17825792 Auto_increment: 122 Create_time: 2010-10-05 18:40:39 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.01 sec) mysql> show indexes from tinno; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tinno | 0 | PRIMARY | 1 | c1 | A | 64 | NULL | NULL | | BTREE | | | tinno | 1 | c2 | 1 | c2 | A | 64 | NULL | NULL | YES | BTREE | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec) mysql> update tinno set c2=0; Query OK, 63 rows affected (0.01 sec) Rows matched: 64 Changed: 63 Warnings: 0 mysql> select count(distinct c2) from tinno; +--------------------+ | count(distinct c2) | +--------------------+ | 1 | +--------------------+ 1 row in set (0.00 sec) mysql> analyze table tinno; +------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------+---------+----------+----------+ | test.tinno | analyze | status | OK | +------------+---------+----------+----------+ 1 row in set (0.00 sec) mysql> show indexes from tinno; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tinno | 0 | PRIMARY | 1 | c1 | A | 64 | NULL | NULL | | BTREE | | | tinno | 1 | c2 | 1 | c2 | A | 64 | NULL | NULL | YES | BTREE | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec) mysql> set global innodb_stats_on_metadata=1; Query OK, 0 rows affected (0.00 sec) mysql> analyze table tinno; +------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------+---------+----------+----------+ | test.tinno | analyze | status | OK | +------------+---------+----------+----------+ 1 row in set (0.00 sec) mysql> show indexes from tinno; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tinno | 0 | PRIMARY | 1 | c1 | A | 64 | NULL | NULL | | BTREE | | | tinno | 1 | c2 | 1 | c2 | A | 2 | NULL | NULL | YES | BTREE | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec)
[14 Oct 2010 14:42]
Vasil Dimov
This variable was introduced in Bug#26598 Create variable to allow turning off of statistic gathering on metadata commands The original patch is here: http://lists.mysql.com/commits/21254
[14 Oct 2010 14:44]
Vasil Dimov
Looks like the intent really was not to disable ANALYZE TABLE.
[18 Oct 2010 10:51]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/120942 3636 Vasil Dimov 2010-10-18 Fix Bug#57252 disabling innobase_stats_on_metadata disables ANALYZE In order to fix this bug we need to distinguish whether ha_innobase::info() has been called from ::analyze() or not. Rename ::info() to ::info_low() and add a boolean parameter that tells whether the call is from ::analyze() or not. Create a new simple ::info() that just calls ::info_low(false => not called from analyze). From ::analyze() instead of ::info() call ::info_low(true => called from analyze). Approved by: Jimmy (rb://487)
[18 Oct 2010 11:21]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/120949 3637 Vasil Dimov 2010-10-18 Fix Bug#57252 disabling innobase_stats_on_metadata disables ANALYZE This is a merge from 5.1/builtin to 5.1/plugin of: -------------- revision-id: vasil.dimov@oracle.com-20101018104811-nwqhg9vav17kl5s1 committer: Vasil Dimov <vasil.dimov@oracle.com> timestamp: Mon 2010-10-18 13:48:11 +0300 message: Fix Bug#57252 disabling innobase_stats_on_metadata disables ANALYZE In order to fix this bug we need to distinguish whether ha_innobase::info() has been called from ::analyze() or not. Rename ::info() to ::info_low() and add a boolean parameter that tells whether the call is from ::analyze() or not. Create a new simple ::info() that just calls ::info_low(false => not called from analyze). From ::analyze() instead of ::info() call ::info_low(true => called from analyze). Approved by: Jimmy (rb://487) --------------
[18 Oct 2010 14:02]
James Day
Workaround: SET GLOBAL innodb_stats_on_metadata = 1; ANALYZE TABLE <table name> SET GLOBAL innodb_stats_on_metadata = 0;
[13 Nov 2010 16:10]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:42]
Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)
[18 Nov 2010 15:56]
Bugs System
Pushed into mysql-5.1 5.1.54 (revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (version source revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (merge vers: 5.1.54) (pib:21)
[14 Dec 2010 19:34]
John Russell
Added to changelog: The innodb_stats_on_metadata option could prevent the ANALYZE TABLE statement from running.
[16 Dec 2010 22:35]
Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)
[23 Dec 2010 11:36]
James Day
John, Please change the release note from: The innodb_stats_on_metadata option could prevent the ANALYZE TABLE statement from running. to: Setting innodb_stats_on_metadata to 0/off prevent the ANALYZE TABLE statement from updating the cardinality statistics of InnoDB tables. The purpose is to be more specific about which setting had the problem and to describe the symptom better: the statement worked without error messages but did nothing. At http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_stats_on_metad... we should be more explicit in saying that "we recommend setting innodb_stats_on_metadata to 0/OFF and running ANALYZE TABLE periodically".
[12 Jan 2011 21:56]
John Russell
Amended the changelog wording as follows: Turning off the innodb_stats_on_metadata option could prevent the ANALYZE TABLE statement from updating the cardinality statistics of InnoDB tables.