Solving INFORMATION_SCHEMA slowness

Many of us find INFORMATION_SCHEMA painfully slow to work it when it comes to retrieving table meta data. Many people resort to using file system tools instead to
find for example how much space innodb tables are using and things like it. Besides being just slow accessing information_schema can often impact server performance
dramatically. The cause of majority of this slowness is not opening and closing tables, which can be solved with decent table cache size, and which is very fast for
Innodb but by the fact MySQL by default looks to refresh Innodb statistics each time table is queried from information schema.

The solution is simple, just set innodb_stats_on_metadata=0 which will prevent statistic update when you query information_schema. Most likely
you do not want it anyway. This will not make Innodb to operate without statistics at all as Innodb will still compute statistics for the table first time it opens it.

Here are some numbers from my test box:

As you can see performance gains are huge.
Note enabling this option will not make information_schema to be stale when it comes to important stuff – data_length for example will be correctly returned by information schema as it changes.

Share this post

Comments (36)

  • Sam

    So what are possible drawbacks if any?

    December 23, 2011 at 11:15 am
  • Shlomi Noach

    Peter,
    great info!
    In the past weeks I am working hard with issues in I_S. I find many problems; still need to finalize my finding so as to submit bug reports, but there are very clear memory leaks; even optimized queries consume hundreds of MB; queries on views may leak many GB of memory and are not optimized.
    I fear I’m far from being comfortable with I_S queries, except for the most basic access (e.g. TABLE_SCHEMA, TABLE_NAME, which are indeed well optimized).

    This is really frustrating, since I’ve come to develop very cool ideas so as to gracefully and transparently read I_S data; however even the lightest touch on the wrong row can bring my production servers down.
    If you want an example, I’m sad to say my own “mycheckpoint” project is a “good” example. Try to select INFORMATION_SCHEMA.COLUMNS on one of the HTML views. Such views aggregate data from other views, which in turn aggregate data from other views, which in turn…
    Sit back and watch how GB after GB of memory are drained till the server crashes…

    December 23, 2011 at 11:44 am
  • Peter Zaitsev

    Sam,

    It would sounds strange but I do not see much 🙂 The statistics will not be refreshed when you query information_schema but I’d call it unwanted side effect on the first place.

    December 23, 2011 at 12:10 pm
  • Peter Zaitsev

    Shlomi,

    Thanks. Good to know. I do not use a lot of complicated IS stuff my main needs are to query tables to find how much space table take, how many are MyISAM vs Innodb and similar things. Changing this option changes it from very painful to quite easy 🙂

    December 23, 2011 at 12:12 pm
  • Steve Jackson December 24, 2011 at 5:13 am
  • Roland Bouman

    Hi Peter,

    great tip! Thanks 🙂

    December 25, 2011 at 12:56 am
  • Steve Jackson

    Actually Peter. One of the first things I do with new installs is to turn of innodb_stats_on_metadata, and always advise people to do the same ( at least on db’s which are gonna have a sizable number of rows and/or will be monitored with apps quering the tables table….) Shouldn’t this then actually qualify the behaviour as a bug? If there are no significant drawbacks to turning it off, and indeed it improves performance of said queries many times, then I certainly do think it qualifies.

    December 25, 2011 at 1:49 am
  • Simon Mudd

    Certainly it sounds sesnsible to cache but not refresh each time.
    With the servers I work on some systems store information in MyISAM tables as they record event information and there are 80,000 tables on the server. I_S does not work for me either and generally it’s only being queried for the existence of a table. So I believe that something better is needed and fixing the problem in InnoDB is not enough. Well at least while the disk footprint of a compressed InnoDB table is larger than a compressed MyISAM table.

    December 25, 2011 at 1:54 am
  • icer

    hi Peter

    you said “MySQL by default looks to refresh Innodb statistics each time table is queried from information schema”. What statistics that you refer to ? When the information schema tables only queried by the SELECT statement, then i think nothing should be updated. Can you explain it for me ?

    December 25, 2011 at 4:10 am
  • Raghavendra

    I was able to reproduce the same:

    tl,dr: Number of stages is same but more time is spent in “Opening tables”.

    mysql> select count(*),sum(data_length) from information_schema.tables;
    +———-+——————+
    | count(*) | sum(data_length) |
    +———-+——————+
    | 143 | 159672656 |
    +———-+——————+
    1 row in set (1.86 sec)

    mysql> set global innodb_stats_on_metadata=0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select count(*),sum(data_length) from information_schema.tables;
    +———-+——————+
    | count(*) | sum(data_length) |
    +———-+——————+
    | 143 | 159672656 |
    +———-+——————+
    1 row in set (0.11 sec)

    mysql> pager sort -n -t’|’ -k3
    PAGER set to ‘sort -n -t’|’ -k3′
    mysql> show profile for query 12;
    +———————-+———-+
    +———————-+———-+
    +———————-+———-+
    | Status | Duration |
    | closing tables | 0.000003 |
    | query end | 0.000003 |
    | closing tables | 0.000004 |
    | Opening tables | 0.000004 |
    | removing tmp table | 0.000004 |
    | removing tmp table | 0.000004 |
    | removing tmp table | 0.000004 |
    | removing tmp table | 0.000004 |
    | removing tmp table | 0.000004 |
    | removing tmp table | 0.000004 |
    | removing tmp table | 0.000004 |
    | removing tmp table | 0.000004 |
    | removing tmp table | 0.000004 |
    | Opening tables | 0.000005 |
    | Opening tables | 0.000005 |
    | Opening tables | 0.000005 |
    | Opening tables | 0.000005 |
    | Opening tables | 0.000005 |
    | Opening tables | 0.000005 |
    | Opening tables | 0.000005 |
    | Opening tables | 0.000005 |
    | Opening tables | 0.000005 |
    | Opening tables | 0.000005 |
    | removing tmp table | 0.000005 |
    | removing tmp table | 0.000005 |
    | removing tmp table | 0.000005 |
    | removing tmp table | 0.000005 |
    | removing tmp table | 0.000005 |
    | removing tmp table | 0.000005 |
    | removing tmp table | 0.000005 |
    | removing tmp table | 0.000005 |
    | removing tmp table | 0.000005 |
    | removing tmp table | 0.000005 |
    | Opening tables | 0.000006 |
    | Opening tables | 0.000006 |
    | Opening tables | 0.000006 |
    | Opening tables | 0.000006 |
    | Opening tables | 0.000006 |
    | Opening tables | 0.000006 |
    | Opening tables | 0.000006 |
    | Opening tables | 0.000006 |
    | removing tmp table | 0.000006 |
    | removing tmp table | 0.000006 |
    | removing tmp table | 0.000006 |
    | removing tmp table | 0.000006 |
    | end | 0.000007 |
    | Opening tables | 0.000007 |
    | removing tmp table | 0.000007 |
    | removing tmp table | 0.000007 |
    | removing tmp table | 0.000007 |
    | Opening tables | 0.000008 |
    | Opening tables | 0.000008 |
    | Opening tables | 0.000008 |
    | Opening tables | 0.000008 |
    | Opening tables | 0.000008 |
    | Opening tables | 0.000008 |
    | Opening tables | 0.000008 |
    | Opening tables | 0.000008 |
    | removing tmp table | 0.000008 |
    | Opening tables | 0.000009 |
    | Opening tables | 0.000009 |
    | removing tmp table | 0.000009 |
    | removing tmp table | 0.000009 |
    | removing tmp table | 0.000009 |
    | removing tmp table | 0.000009 |
    | removing tmp table | 0.000009 |
    | Opening tables | 0.000010 |
    | Opening tables | 0.000010 |
    | Opening tables | 0.000010 |
    | Opening tables | 0.000010 |
    | Opening tables | 0.000010 |
    | Opening tables | 0.000010 |
    | removing tmp table | 0.000010 |
    | removing tmp table | 0.000010 |
    | Opening tables | 0.000011 |
    | Opening tables | 0.000011 |
    | Opening tables | 0.000011 |
    | removing tmp table | 0.000011 |
    | removing tmp table | 0.000012 |
    | removing tmp table | 0.000012 |
    | Opening tables | 0.000013 |
    | removing tmp table | 0.000013 |
    | freeing items | 0.000014 |
    | Opening tables | 0.000014 |
    | Opening tables | 0.000014 |
    | Opening tables | 0.000014 |
    | Opening tables | 0.000014 |
    | Opening tables | 0.000014 |
    | Opening tables | 0.000015 |
    | removing tmp table | 0.000015 |
    | removing tmp table | 0.000015 |
    | System lock | 0.000015 |
    | Opening tables | 0.000016 |
    | Opening tables | 0.000016 |
    | optimizing | 0.000016 |
    | preparing | 0.000016 |
    | Opening tables | 0.000018 |
    | Opening tables | 0.000018 |
    | Opening tables | 0.000019 |
    | Opening tables | 0.000019 |
    | Opening tables | 0.000020 |
    | Opening tables | 0.000020 |
    | Opening tables | 0.000020 |
    | Opening tables | 0.000021 |
    | Opening tables | 0.000021 |
    | Opening tables | 0.000021 |
    | Opening tables | 0.000021 |
    | checking permissions | 0.000022 |
    | Opening tables | 0.000022 |
    | checking permissions | 0.000023 |
    | Opening tables | 0.000023 |
    | Opening tables | 0.000023 |
    | Opening tables | 0.000024 |
    | Opening tables | 0.000024 |
    | Opening tables | 0.000025 |
    | Opening tables | 0.000025 |
    | Opening tables | 0.000025 |
    | statistics | 0.000025 |
    | Opening tables | 0.000026 |
    | checking permissions | 0.000027 |
    | Opening tables | 0.000027 |
    | Opening tables | 0.000027 |
    | Opening tables | 0.000027 |
    | Opening tables | 0.000027 |
    | checking permissions | 0.000028 |
    | checking permissions | 0.000029 |
    | checking permissions | 0.000029 |
    | checking permissions | 0.000029 |
    | Opening tables | 0.000029 |
    | Opening tables | 0.000029 |
    | checking permissions | 0.000030 |
    | checking permissions | 0.000030 |
    | Opening tables | 0.000030 |
    | Opening tables | 0.000032 |
    | checking permissions | 0.000034 |
    | Opening tables | 0.000035 |
    | Opening tables | 0.000038 |
    | Opening tables | 0.000039 |
    | removing tmp table | 0.000040 |
    | removing tmp table | 0.000040 |
    | Opening tables | 0.000041 |
    | Opening tables | 0.000041 |
    | removing tmp table | 0.000041 |
    | Opening tables | 0.000042 |
    | Opening tables | 0.000042 |
    | Opening tables | 0.000042 |
    | init | 0.000043 |
    | removing tmp table | 0.000043 |
    | Opening tables | 0.000044 |
    | Opening tables | 0.000044 |
    | Opening tables | 0.000044 |
    | removing tmp table | 0.000044 |
    | removing tmp table | 0.000044 |
    | Opening tables | 0.000045 |
    | Opening tables | 0.000045 |
    | checking permissions | 0.000046 |
    | Opening tables | 0.000046 |
    | removing tmp table | 0.000046 |
    | Opening tables | 0.000047 |
    | checking permissions | 0.000048 |
    | Opening tables | 0.000048 |
    | Opening tables | 0.000048 |
    | Opening tables | 0.000050 |
    | Opening tables | 0.000050 |
    | Opening tables | 0.000050 |
    | removing tmp table | 0.000050 |
    | Opening tables | 0.000060 |
    | Opening tables | 0.000060 |
    | checking permissions | 0.000061 |
    | Opening tables | 0.000061 |
    | Opening tables | 0.000062 |
    | removing tmp table | 0.000062 |
    | Opening tables | 0.000066 |
    | Opening tables | 0.000066 |
    | Opening tables | 0.000066 |
    | Opening tables | 0.000066 |
    | removing tmp table | 0.000067 |
    | Opening tables | 0.000068 |
    | checking permissions | 0.000069 |
    | Opening tables | 0.000070 |
    | Opening tables | 0.000070 |
    | cleaning up | 0.000071 |
    | Opening tables | 0.000071 |
    | Opening tables | 0.000071 |
    | Opening tables | 0.000071 |
    | Opening tables | 0.000072 |
    | Opening tables | 0.000072 |
    | Opening tables | 0.000074 |
    | Opening tables | 0.000074 |
    | Opening tables | 0.000075 |
    | Opening tables | 0.000075 |
    | Opening tables | 0.000076 |
    | Opening tables | 0.000077 |
    | Opening tables | 0.000078 |
    | Opening tables | 0.000078 |
    | Opening tables | 0.000079 |
    | Opening tables | 0.000081 |
    | Opening tables | 0.000081 |
    | Opening tables | 0.000082 |
    | Opening tables | 0.000086 |
    | Opening tables | 0.000091 |
    | Opening tables | 0.000091 |
    | Opening tables | 0.000092 |
    | Opening tables | 0.000093 |
    | Opening tables | 0.000093 |
    | Opening tables | 0.000097 |
    | Opening tables | 0.000098 |
    | Opening tables | 0.000099 |
    | Opening tables | 0.000099 |
    | Opening tables | 0.000101 |
    | Opening tables | 0.000102 |
    | Opening tables | 0.000103 |
    | Opening tables | 0.000104 |
    | starting | 0.000114 |
    | Opening tables | 0.000117 |
    | Opening tables | 0.000118 |
    | Opening tables | 0.000121 |
    | Opening tables | 0.000124 |
    | Opening tables | 0.000129 |
    | Opening tables | 0.000133 |
    | Opening tables | 0.000148 |
    | Opening tables | 0.000149 |
    | Opening tables | 0.000163 |
    | Opening tables | 0.000173 |
    | Opening tables | 0.000210 |
    | Sending data | 0.000256 |
    | Opening tables | 0.000274 |
    | Opening tables | 0.000291 |
    | Opening tables | 0.000299 |
    | Opening tables | 0.000305 |
    | Opening tables | 0.000318 |
    | Opening tables | 0.000337 |
    | Opening tables | 0.000340 |
    | Opening tables | 0.000341 |
    | Opening tables | 0.000349 |
    | Opening tables | 0.000365 |
    | Opening tables | 0.000380 |
    | Opening tables | 0.000383 |
    | Opening tables | 0.000416 |
    | Opening tables | 0.000460 |
    | Opening tables | 0.000496 |
    | Opening tables | 0.000564 |
    | Opening tables | 0.000588 |
    | Opening tables | 0.000600 |
    | Opening tables | 0.000613 |
    | Opening tables | 0.000639 |
    | Opening tables | 0.000654 |
    | Opening tables | 0.001331 |
    | Opening tables | 0.002622 |
    | Opening tables | 0.002978 |
    | Opening tables | 0.005858 |
    | Opening tables | 0.006354 |
    | Opening tables | 0.008876 |
    | Opening tables | 0.009788 |
    | Opening tables | 0.010085 |
    | checking permissions | 0.011430 |
    | Opening tables | 0.012020 |
    | Opening tables | 0.012881 |
    | Opening tables | 0.014028 |
    | checking permissions | 0.014515 |
    | Opening tables | 0.014707 |
    | Opening tables | 0.017983 |
    | Opening tables | 0.018737 |
    | Opening tables | 0.019109 |
    | Opening tables | 0.021337 |
    | Opening tables | 0.026079 |
    | Opening tables | 0.026272 |
    | Opening tables | 0.034298 |
    | checking permissions | 0.047597 |
    | Opening tables | 0.058662 |
    | Opening tables | 0.060606 |
    | executing | 0.135166 |
    | Opening tables | 0.148856 |
    | Opening tables | 0.346616 |
    | Opening tables | 0.358825 |
    | Opening tables | 0.396198 |
    276 rows in set (0.00 sec)

    mysql> show profile for query 14;
    +———————-+———-+
    +———————-+———-+
    +———————-+———-+
    | Status | Duration |
    | removing tmp table | 0.000004 |
    | removing tmp table | 0.000004 |
    | removing tmp table | 0.000004 |
    | removing tmp table | 0.000004 |
    | removing tmp table | 0.000004 |
    | removing tmp table | 0.000004 |
    | Opening tables | 0.000005 |
    | Opening tables | 0.000005 |
    | Opening tables | 0.000005 |
    | Opening tables | 0.000005 |
    | removing tmp table | 0.000005 |
    | removing tmp table | 0.000005 |
    | removing tmp table | 0.000005 |
    | removing tmp table | 0.000005 |
    | removing tmp table | 0.000005 |
    | removing tmp table | 0.000005 |
    | removing tmp table | 0.000005 |
    | Opening tables | 0.000006 |
    | Opening tables | 0.000006 |
    | Opening tables | 0.000006 |
    | removing tmp table | 0.000006 |
    | removing tmp table | 0.000006 |
    | removing tmp table | 0.000006 |
    | checking permissions | 0.000007 |
    | checking permissions | 0.000007 |
    | checking permissions | 0.000007 |
    | checking permissions | 0.000007 |
    | checking permissions | 0.000007 |
    | closing tables | 0.000007 |
    | Opening tables | 0.000007 |
    | Opening tables | 0.000007 |
    | Opening tables | 0.000007 |
    | Opening tables | 0.000007 |
    | Opening tables | 0.000007 |
    | Opening tables | 0.000007 |
    | Opening tables | 0.000007 |
    | Opening tables | 0.000007 |
    | query end | 0.000007 |
    | removing tmp table | 0.000007 |
    | checking permissions | 0.000008 |
    | Opening tables | 0.000008 |
    | Opening tables | 0.000008 |
    | Opening tables | 0.000008 |
    | removing tmp table | 0.000008 |
    | removing tmp table | 0.000008 |
    | removing tmp table | 0.000008 |
    | closing tables | 0.000009 |
    | removing tmp table | 0.000009 |
    | removing tmp table | 0.000009 |
    | removing tmp table | 0.000009 |
    | Opening tables | 0.000010 |
    | Opening tables | 0.000010 |
    | Opening tables | 0.000010 |
    | removing tmp table | 0.000010 |
    | Opening tables | 0.000011 |
    | Opening tables | 0.000011 |
    | Opening tables | 0.000011 |
    | removing tmp table | 0.000011 |
    | checking permissions | 0.000012 |
    | checking permissions | 0.000012 |
    | Opening tables | 0.000012 |
    | Opening tables | 0.000012 |
    | Opening tables | 0.000012 |
    | Opening tables | 0.000012 |
    | removing tmp table | 0.000012 |
    | checking permissions | 0.000013 |
    | optimizing | 0.000013 |
    | removing tmp table | 0.000013 |
    | removing tmp table | 0.000013 |
    | removing tmp table | 0.000013 |
    | Opening tables | 0.000014 |
    | removing tmp table | 0.000014 |
    | Opening tables | 0.000015 |
    | Opening tables | 0.000015 |
    | Opening tables | 0.000015 |
    | Opening tables | 0.000015 |
    | Opening tables | 0.000015 |
    | Opening tables | 0.000015 |
    | Opening tables | 0.000015 |
    | Opening tables | 0.000015 |
    | removing tmp table | 0.000015 |
    | Opening tables | 0.000016 |
    | Opening tables | 0.000016 |
    | Opening tables | 0.000016 |
    | Opening tables | 0.000016 |
    | removing tmp table | 0.000016 |
    | Opening tables | 0.000017 |
    | Opening tables | 0.000017 |
    | removing tmp table | 0.000017 |
    | checking permissions | 0.000018 |
    | Opening tables | 0.000018 |
    | Opening tables | 0.000018 |
    | removing tmp table | 0.000018 |
    | removing tmp table | 0.000018 |
    | Opening tables | 0.000019 |
    | Opening tables | 0.000019 |
    | removing tmp table | 0.000019 |
    | removing tmp table | 0.000019 |
    | end | 0.000020 |
    | Opening tables | 0.000020 |
    | preparing | 0.000020 |
    | removing tmp table | 0.000020 |
    | removing tmp table | 0.000020 |
    | Opening tables | 0.000021 |
    | Opening tables | 0.000021 |
    | Opening tables | 0.000021 |
    | Opening tables | 0.000021 |
    | Opening tables | 0.000021 |
    | Opening tables | 0.000021 |
    | System lock | 0.000021 |
    | checking permissions | 0.000022 |
    | checking permissions | 0.000022 |
    | Opening tables | 0.000022 |
    | Opening tables | 0.000022 |
    | Opening tables | 0.000022 |
    | Opening tables | 0.000022 |
    | Opening tables | 0.000022 |
    | removing tmp table | 0.000022 |
    | Opening tables | 0.000023 |
    | Opening tables | 0.000023 |
    | Opening tables | 0.000024 |
    | Opening tables | 0.000024 |
    | Opening tables | 0.000024 |
    | Opening tables | 0.000024 |
    | statistics | 0.000024 |
    | Opening tables | 0.000025 |
    | Opening tables | 0.000025 |
    | Opening tables | 0.000025 |
    | Opening tables | 0.000026 |
    | Opening tables | 0.000026 |
    | Opening tables | 0.000026 |
    | Opening tables | 0.000026 |
    | Opening tables | 0.000026 |
    | Opening tables | 0.000026 |
    | Opening tables | 0.000027 |
    | Opening tables | 0.000027 |
    | Opening tables | 0.000027 |
    | Opening tables | 0.000027 |
    | Opening tables | 0.000027 |
    | Opening tables | 0.000027 |
    | Opening tables | 0.000028 |
    | Opening tables | 0.000028 |
    | Opening tables | 0.000028 |
    | Opening tables | 0.000028 |
    | Opening tables | 0.000028 |
    | Opening tables | 0.000028 |
    | Opening tables | 0.000028 |
    | Opening tables | 0.000028 |
    | Opening tables | 0.000029 |
    | Opening tables | 0.000029 |
    | Opening tables | 0.000029 |
    | Opening tables | 0.000029 |
    | Opening tables | 0.000029 |
    | Opening tables | 0.000029 |
    | Opening tables | 0.000030 |
    | Opening tables | 0.000030 |
    | Opening tables | 0.000030 |
    | freeing items | 0.000031 |
    | Opening tables | 0.000031 |
    | Opening tables | 0.000031 |
    | Opening tables | 0.000031 |
    | init | 0.000032 |
    | Opening tables | 0.000032 |
    | Opening tables | 0.000032 |
    | Opening tables | 0.000032 |
    | Opening tables | 0.000032 |
    | Opening tables | 0.000032 |
    | Opening tables | 0.000033 |
    | Opening tables | 0.000034 |
    | Opening tables | 0.000034 |
    | Opening tables | 0.000035 |
    | Opening tables | 0.000035 |
    | cleaning up | 0.000037 |
    | Opening tables | 0.000037 |
    | Opening tables | 0.000038 |
    | Opening tables | 0.000038 |
    | Opening tables | 0.000038 |
    | removing tmp table | 0.000041 |
    | Opening tables | 0.000043 |
    | removing tmp table | 0.000044 |
    | Opening tables | 0.000046 |
    | Opening tables | 0.000047 |
    | Opening tables | 0.000047 |
    | Opening tables | 0.000048 |
    | Opening tables | 0.000051 |
    | checking permissions | 0.000052 |
    | checking permissions | 0.000052 |
    | Opening tables | 0.000052 |
    | Opening tables | 0.000052 |
    | Opening tables | 0.000053 |
    | Opening tables | 0.000055 |
    | Opening tables | 0.000055 |
    | Opening tables | 0.000056 |
    | Opening tables | 0.000059 |
    | Opening tables | 0.000059 |
    | Opening tables | 0.000061 |
    | Opening tables | 0.000063 |
    | Opening tables | 0.000067 |
    | Opening tables | 0.000067 |
    | Opening tables | 0.000068 |
    | Opening tables | 0.000069 |
    | Opening tables | 0.000069 |
    | Opening tables | 0.000071 |
    | checking permissions | 0.000072 |
    | Opening tables | 0.000072 |
    | Opening tables | 0.000072 |
    | Opening tables | 0.000072 |
    | Opening tables | 0.000072 |
    | Opening tables | 0.000073 |
    | Opening tables | 0.000073 |
    | Opening tables | 0.000074 |
    | Opening tables | 0.000075 |
    | Opening tables | 0.000076 |
    | Opening tables | 0.000076 |
    | Opening tables | 0.000077 |
    | Opening tables | 0.000079 |
    | Opening tables | 0.000079 |
    | Opening tables | 0.000079 |
    | Opening tables | 0.000080 |
    | Opening tables | 0.000083 |
    | Opening tables | 0.000083 |
    | Opening tables | 0.000085 |
    | Opening tables | 0.000087 |
    | checking permissions | 0.000092 |
    | Opening tables | 0.000094 |
    | Opening tables | 0.000094 |
    | checking permissions | 0.000095 |
    | Opening tables | 0.000098 |
    | Opening tables | 0.000099 |
    | Opening tables | 0.000099 |
    | Opening tables | 0.000101 |
    | Opening tables | 0.000101 |
    | Opening tables | 0.000103 |
    | Opening tables | 0.000104 |
    | Opening tables | 0.000104 |
    | Opening tables | 0.000105 |
    | starting | 0.000107 |
    | removing tmp table | 0.000117 |
    | removing tmp table | 0.000118 |
    | removing tmp table | 0.000119 |
    | Opening tables | 0.000121 |
    | removing tmp table | 0.000123 |
    | removing tmp table | 0.000124 |
    | Opening tables | 0.000125 |
    | Opening tables | 0.000127 |
    | Opening tables | 0.000131 |
    | Opening tables | 0.000132 |
    | removing tmp table | 0.000136 |
    | removing tmp table | 0.000146 |
    | Opening tables | 0.000149 |
    | Opening tables | 0.000153 |
    | removing tmp table | 0.000170 |
    | Opening tables | 0.000174 |
    | Opening tables | 0.000200 |
    | Opening tables | 0.000206 |
    | Opening tables | 0.000210 |
    | Opening tables | 0.000237 |
    | Opening tables | 0.000246 |
    | Opening tables | 0.000263 |
    | Opening tables | 0.000264 |
    | Opening tables | 0.000285 |
    | Opening tables | 0.000307 |
    | Opening tables | 0.000324 |
    | Opening tables | 0.000325 |
    | Opening tables | 0.000397 |
    | Opening tables | 0.000426 |
    | Opening tables | 0.000432 |
    | Opening tables | 0.000461 |
    | Opening tables | 0.000465 |
    | executing | 0.000472 |
    | Sending data | 0.000488 |
    | Opening tables | 0.000576 |
    | Opening tables | 0.009464 |
    | Opening tables | 0.013812 |
    | Opening tables | 0.020566 |
    | Opening tables | 0.054761 |
    276 rows in set (0.00 sec)

    December 25, 2011 at 9:16 am
  • Peter Zaitsev

    Icer,

    I’m speaking about basic statistics such as number of rows per key value. Check out “SHOW INDEX FROM table” to see what statistics MySQL gathers. By default MySQL does update statistics on selects to information schema. This does not make much sense to me too but it is how it is done. Fully thing too SHOW INDEX FROM table also updates statistics so you really can’t see what stats optimizer is using for query as once you read the data it is refreshed an is different already 🙂

    December 27, 2011 at 5:09 pm
  • Steve Jackson

    Hehe, yeah… just try debugging a bad query plan when your stats are flapping between good and bad (random index dives)

    explain blah -> bad plan… hummm show indexes from blah… (looks ok)… explain blah -> good plan… wOOt??

    December 28, 2011 at 4:07 am
  • Bill Karwin

    I see only two very minor drawbacks:

    1. The innodb_stats_on_metadata variable is global scope only, not session. So you have to change it for all clients.

    2. The innodb_stats_on_metadata variable is available since MySQL 5.1.17, and it’s a dynamic variable only since InnoDB Plugin 1.0.2.
    http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-other-changes-innodb_stats_on_metadata.html

    January 6, 2012 at 11:51 am
  • Rick

    Hi Peter: Thanks this is good info….I am going to make this change before I do though…would you also recommend regularly analyzing DBs and tables in addition to setting innodb_stats_on_metadata=0? If so, is there a recommended best practce?

    Script to iterate over all tables performing analyze on each
    ” mysqlcheck –all-databases –analyze ”
    Other (tool kit option)?

    Thanks for your thoughts….

    January 23, 2012 at 1:27 pm
  • zhang

    Hi Peter…

    This variable is useful for all the InnoDB tables in our application. I mean When there are many InnoDB tables and updated tables so frequently, indeed, setting innodb_stats_on_metadata = 0 can solve the issue of query slow of IS.

    But if all tables in our application is MyISAM tables, how shall we deal with the issue when the same question happened?

    Thanks so much

    February 16, 2012 at 12:52 am
  • Sergio Roysen

    I’ve just hit the INFORMATION_SCHEMA slowness today when I began the tests to integrate the pt-online-schema-change tool into our own migration procedures.
    It was taking the tool about one hour just to finish this query:
    “SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE …”
    After setting innodb_stats_on_metadata = 0, that query run almost instantly (btw, Thank you Peter!).
    If I may, I would suggest adding an option to the tool (and this might apply to the rest of the percona tools that use the MySQL INFORMATION_SCHEMA ) allowing it to momentarily set innodb_stats_on_metadata=0 while running (or just before running those kind of queries), and leave the original value for that variable after it finishes.
    Thanks!

    April 20, 2012 at 6:16 am
  • Harry Chen

    This is the most useful post I’ve ever found! I was facing the query slowess issue by using the Federated table. When the Federated table gets queried, MYSQL will issue the SHOW TABLES STATUS which is querying the information_schema table. There are only 1,000,000 records in the table but each query takes 1 second.

    By turning this flag off, it becomes 0.001 second!! Thank you very much!

    September 17, 2012 at 6:26 am
  • Richard Dale

    This also significantly speeds up phpmyadmin on InnoDB databases

    November 18, 2012 at 7:41 pm