Solving INFORMATION_SCHEMA slowness

Solving INFORMATION_SCHEMA slowness

PREVIOUS POST
NEXT POST

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.

PREVIOUS POST
NEXT POST

Share this post

Comments (36)

  • Sam Reply

    So what are possible drawbacks if any?

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

    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 Reply

    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 Reply

    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 Reply December 24, 2011 at 5:13 am
  • Roland Bouman Reply

    Hi Peter,

    great tip! Thanks 🙂

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

    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 Reply

    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 Reply

    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 Reply

    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 Reply

    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 Reply

    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 Reply

    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 Reply

    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 Reply

    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 Reply

    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 Reply

    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 Reply

    This also significantly speeds up phpmyadmin on InnoDB databases

    November 18, 2012 at 7:41 pm
  • Paul Otto Reply

    Thanks for this helpful post. This made life much more bearable for me working with my current dataset!

    January 15, 2013 at 2:12 pm
  • Guang Reply

    Great tip!

    March 12, 2013 at 4:25 am
  • Guang Reply

    Peter, do you have any idea how to get how much space innodb tables are using through file system without querying database?

    March 13, 2013 at 11:39 pm
  • Dave005 Reply

    Peter, I have the same slow query issue, however the tables are MyISAM, so was sceptic if this solution would work equally well for MyISAM tables?

    Please suggest.

    May 5, 2013 at 4:29 am
  • Bill Karwin Reply

    Dave005, past versions of MySQL have had many performance complaints about I_S, besides the overhead of InnoDB statistics refresh. You may get some insight by reading the following bug logs:

    http://bugs.mysql.com/bug.php?id=19588 – reported fixed in MySQL 5.1.21 but many people reported I_S was slow.
    http://bugs.mysql.com/bug.php?id=56178 – reported fixed in MySQL 5.5.16, 5.6.3.

    What version are you running? Is it possible you can upgrade to the latest 5.5.x?

    May 5, 2013 at 10:52 am
  • Dave005 Reply

    @Bill, Current version on the server is 5.1.58, and it was recently migrated from 5.1.34.

    And no we cannot upgrade right now.

    May 6, 2013 at 4:43 am
  • Bill Karwin Reply

    Dave005, If your performance issue is caused by bug #56178 then you must run MySQL 5.5 to get te fix. It might be possible, however, to backport that fix to 5.1.58, and run a custom build until you can upgrade sometime in the future. Percona can help, read more here: http://www.mysqlperformanceblog.com/2013/01/28/percona-custom-development-services-how-we-can-help/

    You may also be able to work around the issue, depending on what you’re using I_S for. When I developed the database library for Zend Framework, I needed to write a function to discover the structure of a table, its columns and primary key, etc. Using I_S was too slow, so instead I used the simple “DESCRIBE ” statement (which is a synonym for “SHOW COLUMNS FROM “). That has less information than all of I_S, but it was sufficient for what I was doing at the time, and it didn’t suffer from the performance issues.

    May 6, 2013 at 12:24 pm
  • Andrea Reply

    Very interesting…
    I prepare I small tool to export from MySQL
    So this is my query that cause me trouble:
    SELECt t.table_schema, t.table_name, t.Constraint_type, t.constraint_name, c.column_name, c.ordinal_position from
    information_schema.key_column_usage As c
    INNER JOIN information_schema.table_constraints as t ON T.table_schema =c.table_schema
    AND T.table_name = c.table_name
    AND T.constraint_name= c.constraint_name
    WHERE t.table_Schema = ‘figa’
    AND t.table_name =’aggr1out’
    ORDER BY t.Constraint_type,t.constraint_name, c.ordinal_position
    It needed 1.56 sec. After your article it need just 0.56 sec. It is a really huge improvement but the same, can not help. I need the join because key_column_usage does not contains the type of constraint, that is contained in table_constraints.
    So…. but half second, the same, is not so good. I will link your site on my. Do you know a way to optimize it…. I tryed with parameterized query, but there is no appreciable improvment.

    September 22, 2013 at 8:01 am
  • jimmy chen Reply

    Hi, Peter
    http://www.google.com/recaptcha/api/image?c=03AHJ_VuvkCub7g_16GZJihgRkwd69MunMQWQARu7_1hNqFh9RAudLJ-KDG24EFvXmZqE67_XRsblCgCY0BncoMEdus8knXNAAPl28d9MWiZrjsY-AXOX9JkHunfrf_NGMmOzW48a9v1mHxFNsK164UFNWPv4AuzYMB5FSNgWe7bzt6ER2D21wbTQ
    We have a large DB shared as
    mysql> select count(*),sum(data_length) from information_schema.tables;
    +———-+——————+
    | count(*) | sum(data_length) |
    +———-+——————+
    | 168121 | 30353254531 |
    +———-+——————+
    1 row in set, 26 warnings (38.84 sec)

    No of Information_schema (tables) are indexed although in memory. When liquibase application query against
    Information_schema, they are taking hours to be finished.

    Any Clue ?

    Jimmy

    October 30, 2013 at 1:44 pm
  • Tom Diederich Reply

    Thanks for the post, Jimmy. However, Percona’s MySQL discussion forums are really the best place to ask questions like the one you have. Here’s the url: https://www.percona.com/forums/

    I’m the community manager and I’ll try to make sure you get the info you need once you post there.
    -Tom

    October 30, 2013 at 4:41 pm
  • Johan De Meersman Reply

    Thank you for this. Implemented it as a dirty hack in a munin module that graphs schema sizes 🙂

    June 3, 2014 at 7:34 am
  • Brad Boyle Reply

    I was doing lot of head-scratching about painfully slow queries on INFORMATION_SCHEMA until I came across your post. Problem solved. Thanks for posting, Peter.

    September 2, 2015 at 3:52 pm
  • Scott Reply

    Problem:
    PTOSC is hanging waiting for the following table to open: its been over an hour and this proposed fix is already in effect.

    SELECT table_schema, table_name
    FROM information_schema.key_column_usage
    WHERE referenced_table_schema=’specific_schema’
    AND referenced_table_name=’specific_table’;

    All help appreciated since this big data shop lives and dies by pt-online-schema-change. I have automated the same to implement the same alteration across 13000 schemas, and the number of schemas is growing daily.

    Scott

    Observations:
    Currently trying to pt-online-schema-change a super high volume table that has hundreds of millions or rows while the data is live, in the midst of millions of rows inserted daily.

    The above value set global innodb_stats_on_metadata=0; was already set. The really bad news is I have 11 db application clusters with over 13000 schemas, each schema requiring the exact same alteration and this is only the first alteration. Fortunately most of the application schemas have much less data in same table, therefore, lower volumes.

    Normally on low volume tables in the same mysql instance same PTOSC startup query take about 3 mins to start due to the fact the specific db instance has approximate 2500 db schemas / 30 tables per schema and respective volumes.

    With the above query it has been running an hour still waiting for the tables to open.

    As an aside why is ptosc running the query when the table_schema, table_name are stated in the queries where clause, in otherwords the only data it is asking for is already known. Perhaps a means to determine table is available?

    CPU, memory, and storage are all none issues due to the average db node has 32-40 cpus, 384gb ram, 6.5tb to 22tb ssd’s. My buffer hit ratio average 1000/1000 occassionally 999/1000

    October 26, 2015 at 6:57 pm
  • Makshud Ansari Reply

    Awsome! Grete work!!

    But this solution “set global innodb_stats_on_metadata=0;” only for mysql version 5.1 to 5.5, in mysql version 5.6, default set of innodb_on_metadata = off.

    May 6, 2016 at 3:57 am
  • Jeremy Stanners Reply

    Many Thanks! It no longer takes 30 seconds to launch phpMyAdmin for the database!

    May 15, 2017 at 5:53 pm
  • Sklasing Reply

    Our installation already has set set global innodb_stats_on_metadata=0 and it makes no difference, the main info schema query pt online schema change(ptosc) uses to start up is VERY SLOW, i.e., 15mins.

    SELECT table_schema, table_name
    FROM information_schema.key_column_usage
    WHERE referenced_table_schema=’xxxxx’
    AND referenced_table_name=’xxxx’;

    When processing 11000 schemas to alter same respective table per customer it takes forever to complete, often just because of the slow ptosc startup cost.

    Table open cache is 16384 and same number of tables are open at all time since there are probably 55,000 innodb tables in the percona instance 5.6.28-76.1

    Almost every table, i.e., 55,000 of them are touched often hourly.
    The server is 32cpu, 384GB ram, 12TB SSDs and we pull millions of rows of data from the innodb tables in blazing couple seconds speed, just not information schema queries, which are very slow.

    On a side note I don’t understand why percona issues the above query for ptosc because since the referenced_table_name and …schema_name return identical values. I presume Percona is issuing the query for valid reason so that too would be wonderful to understand better.

    Any and all help appreciated.

    August 25, 2017 at 5:07 pm
  • Sklasing Reply

    ended up editing pt-online-schema-change perl script and removing the call to find_child_tables thus leaving the same return array empty. Our tables have no dependencies, especially foreign keys for performance throughput so bypassing the information_schema sql sky rocketed the performance.

    August 25, 2017 at 5:52 pm
  • Herinean Reply

    Some good years later, this article is still useful.
    Didn’t expect such a radical improvement, thanks for the tip.
    I’ll soon be upgrading to the most recent version of MariaDB though, so things will get better by default.
    Thanks nonetheless.

    December 6, 2018 at 3:41 am

Leave a Reply