INFORMATION_SCHEMA is usually the place to go when
you want to get facts about a system (how many tables do we have?
what are the 10 largest tables? What is data size and index size
for table t?, etc). However it is also quite common that such
queries are very slow and create lots of I/O load. Here is a tip
to avoid theses hassles: set
innodb_stats_on_metadata to OFF.
This is a topic we already talked about, but given the number
of systems suffering from INFORMATION_SCHEMA
slowness, I think it is good to bring
innodb_stats_on_metadata back on the table.
The problem
Let’s look at a system I’ve seen recently: MySQL 5.5, working set fitting in memory but not the whole dataset, around 4000 InnoDB tables.
The I/O load is very light as …
[Read more]