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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | mysql> select count(*),sum(data_length) from information_schema.tables; +----------+------------------+ | count(*) | sum(data_length) | +----------+------------------+ | 130 | 2856365892 | +----------+------------------+ 1 row in set (1.08 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) | +----------+------------------+ | 130 | 2856365892 | +----------+------------------+ 1 row in set (0.00 sec) |
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.
So what are possible drawbacks if any?
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…
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.
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 🙂
Just adding this as a related thread..
http://www.mysqlperformanceblog.com/2011/10/06/when-does-innodb-update-table-statistics-and-when-it-can-bite/
Hi Peter,
great tip! Thanks 🙂
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??
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
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!
This also significantly speeds up phpmyadmin on InnoDB databases
Thanks for this helpful post. This made life much more bearable for me working with my current dataset!
Great tip!
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
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
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.
Many Thanks! It no longer takes 30 seconds to launch phpMyAdmin for the database!
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.
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.
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.