MySQL Bugs: #26598: Create variable to allow turning off of statistic gathering on metadata commands

Bug #26598	Create variable to allow turning off of statistic gathering on metadata commands
Submitted:	23 Feb 2007 16:56	Modified:	16 Mar 2007 18:50
Reporter:	Mark Leith
Status:	Closed
Category:	Server: InnoDB	Severity:	S4 (Feature request)
Version:	5.1	OS:	Any (All)
Assigned to:	Timothy Smith	Target Version:
Tags:	innodb, statistics, metadata
Triage:	D5 (Feature request)

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

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).

pushed to 5.1.17

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'

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

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.