| 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: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.
Description: I've seen this in innodb_plugin in mysql-trunk as well as probably everything back to 5.1.17 when this variable was introduced. ha_innobase::analyze( /*=================*/ THD* thd, /*!< in: connection thread handle */ HA_CHECK_OPT* check_opt) /*!< in: currently ignored */ { /* Simply call ::info() with all the flags */ info(HA_STATUS_TIME | HA_STATUS_CONST | HA_STATUS_VARIABLE); return(0); } versus this code in ::info(): if (flag & HA_STATUS_TIME) { if (innobase_stats_on_metadata) { /* In sql_show we call with this flag: update then statistics so that they are up-to-date */ prebuilt->trx->op_info = "updating table statistics"; dict_update_statistics(ib_table); prebuilt->trx->op_info = "returning various info to MySQL"; } It does not seem like it was the intention of innobase_stats_on_metadata to also disable ANALYZE See also: https://bugs.launchpad.net/percona-xtradb/+bug/655186 (where we are tracking for Drizzle) How to repeat: disable innodb_stats_on_metadata (is on by default), run ANALYZE. Suggested fix: The solution is pretty simple, in ::info() do something like the following: if (! innobase_stats_on_metadata) dict_update_statistics(prebuilt->table);