Percona Server moved to https://jira.percona.com/projects/PS

innodb_stats_auto_update and innodb_stats_on_metadata should be both disabled

  1. Series 5.5
  2. Bug #1159802
Bug #1159802 reported by Olivier Doucet
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
Invalid
Undecided
Unassigned
5.1
Won't Fix
Low
Unassigned
5.5
Triaged
Low
Borys Belinsky
5.6
Invalid
Undecided
Unassigned

Bug Description

It seems setting innodb_stats_on_metadata=0 does not change MySQL behaviour on Percona Server (at least on version 5.5.28)
We also need to set innodb_stats_auto_update=0 (variable introduced by percona to do the same job).

It should be stated more clearly in documentation that both variables should be set to 0 to have an effect.
This is also stated here :
http://www.mysqlperformanceblog.com/2011/10/06/when-does-innodb-update-table-statistics-and-when-it-can-bite/

is it possible to change innodb_stats_auto_update as an alias of innodb_stats_on_metadata ? So changing one variable automatically change the other one ?

Tags: doc
Revision history for this message
Raghavendra D Prabhu (raghavendra-prabhu) wrote :

@Olivier,

Yes, currently you need to set both to avoid updating statistics.
The documentation can be updated to reflect.

Ideally, setting innodb_stats_auto_update should be checked in ha_innobase::info_low (in addition to innobase_stats_on_metadata).

Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

These two variables are not exactly identical. For example, innodb_stats_auto_update=1, innodb_stats_on_metadata=0 should cause stats update on sufficient table rows changed, but not on the metadata ops.

Let's document this for now.

tags: added: doc
Revision history for this message
Swany (greenlion) wrote : Re: [Bug 1159802] Re: innodb_stats_auto_update and innodb_stats_on_metadata should be both disabled

That is how we teach it in class. Is this unexpected?

Sent from my iPhone

On Mar 26, 2013, at 7:21 PM, Laurynas Biveinis <email address hidden> wrote:

> These two variables are not exactly identical. For example,
> innodb_stats_auto_update=1, innodb_stats_on_metadata=0 should cause
> stats update on sufficient table rows changed, but not on the metadata
> ops.
>
> Let's document this for now.
>
> ** Also affects: percona-server/5.1
> Importance: Undecided
> Status: New
>
> ** Also affects: percona-server/5.5
> Importance: Undecided
> Status: New
>
> ** Also affects: percona-server/5.6
> Importance: Undecided
> Status: New
>
> ** Changed in: percona-server/5.6
> Status: New => Invalid
>
> ** Changed in: percona-server/5.5
> Status: New => Triaged
>
> ** Changed in: percona-server/5.5
> Importance: Undecided => Low
>
> ** Changed in: percona-server/5.1
> Importance: Undecided => Low
>
> ** Changed in: percona-server/5.1
> Status: New => Triaged
>
> ** Changed in: percona-server/5.1
> Assignee: (unassigned) => Hrvoje Matijakovic (hrvojem)
>
> ** Changed in: percona-server/5.5
> Assignee: (unassigned) => Hrvoje Matijakovic (hrvojem)
>
> ** Tags added: doc
>
> --
> You received this bug notification because you are subscribed to Percona
> Server.
> Matching subscriptions: percona-server-bugs
> https://bugs.launchpad.net/bugs/1159802
>
> Title:
> innodb_stats_auto_update and innodb_stats_on_metadata should be both
> disabled
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/percona-server/+bug/1159802/+subscriptions

Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2923

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.