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:
None 
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
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);
[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.