April 19, 2014

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.

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

Comments

  1. Sam says:

    So what are possible drawbacks if any?

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

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

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

  5. Steve Jackson says:
  6. Hi Peter,

    great tip! Thanks :)

  7. Steve Jackson says:

    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.

  8. Simon Mudd says:

    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.

  9. icer says:

    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 ?

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

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

  12. Steve Jackson says:

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

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

  14. Rick says:

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

  15. zhang says:

    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

  16. Sergio Roysen says:

    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!

  17. Harry Chen says:

    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!

  18. This also significantly speeds up phpmyadmin on InnoDB databases

  19. Paul Otto says:

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

  20. Guang says:

    Great tip!

  21. Guang says:

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

  22. Dave005 says:

    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.

  23. 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?

  24. Dave005 says:

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

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

  26. Andrea says:

    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.

  27. jimmy chen says:

    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

  28. 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: http://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

Speak Your Mind

*