Bug #26598 Create variable to allow turning off of statistic gathering on metadata commands
Submitted: 23 Feb 2007 15:56 Modified: 20 Jun 2010 17:19
Reporter: Mark Leith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.1 OS:Any (All)
Assigned to: CPU Architecture:Any
Tags: innodb, metadata, statistics

[23 Feb 2007 15:56] Mark Leith
Description:
Feature request to create a variable - innodb_stats_on_metadata - that will allow you to turn off the statistics gathering (ha_innodb::info) that InnoDB does when running metadata commands such as SHOW TABLE STATUS, SHOW INDEXES or accessing INFORMATION_SCHEMA.TABLES / INFORMATION_SCHEMA.STATISTICS. 

This has 2 side affects:

o Speed of access on large schemas
o Predictability of the optimizer statistics when these commands are used often

The default for the variable is "ON" (the current behaviour). 

How to repeat:
Nothing to repeat
[6 Mar 2007 17:37] 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/21254

ChangeSet@1.2492, 2007-03-06 10:36:15-07:00, tsmith@siva.hindu.god +6 -0
  Bug #26598: Create variable to allow turning off of statistic gathering on metadata commands
  
  Add innodb_stats_on_metadata option, which enables gathering
  index statistics when processing metadata commands such as
  SHOW TABLE STATUS.  Default behavior of the server does not
  change (this option is enabled by default).
[8 Mar 2007 21:16] Timothy Smith
pushed to 5.1.17
[15 Mar 2007 16:44] Paul DuBois
This variable cannot be changed at runtime.
Is that intentional?

mysql> show variables like 'innodb_stats_on_metadata';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_on_metadata | ON    | 
+--------------------------+-------+
1 row in set (0.04 sec)

mysql> set global innodb_stats_on_metadata = off;
ERROR 1193 (HY000): Unknown system variable 'innodb_stats_on_metadata'
[15 Mar 2007 16:55] Mark Leith
Hi Paul,

I did do this intentionally, as:

o I wanted to follow current InnoDB conventions for most variables (although I know some are dynamic)
o I felt it better to allow this to be set by a DBA only, as allowing sessions to change this as well would still lead to "inconsitency" of statistics

If we get enough call to make it dynamic, I can make that change as well.

Cheers,

Mark
[16 Mar 2007 17:50] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.
[5 May 2010 15:23] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 2:54] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 5:57] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:26] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:54] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 23:24] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 12:00] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:41] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:27] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)