EmergencyEMERGENCY? Get 24/7 Help Now!

Solving INFORMATION_SCHEMA slowness

 | December 23, 2011 |  Posted In: Insight for DBAs, MySQL

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
Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

32 Comments

  • 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…

  • 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 🙂

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

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

  • 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 ?

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

  • 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 🙂

  • 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??

  • 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….

  • 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

  • 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!

  • 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!

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

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

  • 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?

  • @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.

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

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

  • 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

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

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

  • 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

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

Leave a Reply

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.