Innodb buffer pool indexes versus data usage
I have some heartbreaking news for you that will solve your problem.

If you are using MySQL 5.5/5.6, I can assume you are using innodb_file_per_table. There is some disk I/O going on when accessing the INFORMATION_SCHEMA to get information on InnoDB.

What I am about to tell you I have personally eyewitnessed: I worked with a client that had a 2TB MySQL Instance with dozens of database and 10's of thousands of tables. Using MySQL 5.5, I was able to run these queries:

QUERY #1 : Disk Usage by Storage Engine

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;


QUERY #2 : Disk Usage by Storage Engine

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(
FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Total Size" FROM
(SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,SUM(XSize) SXSize,
SUM(TSize) STSize FROM (SELECT table_schema DB,data_length DSize,
index_length XSize,data_length+index_length TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA
GROUP BY DB WITH ROLLUP) AA,(SELECT 3 pw) BB ORDER BY (SDSize+SXSize);


I got responses 10-20 seconds. How? The client uses innodb_file_per_table=0.

I REPEAT : innodb_file_per_table=0

WHAT DO YOU DO NOW ???

Here is why I said "heartbreaking": You would have put all your InnoDB data back inside ibdata1. Would you like to know all the steps to do this ? Here we go...

STEP #1

Add these to /etc/my.cnf

[mysqld]
innodb_file_per_table = 0
innodb_fast_shutdown = 0


STEP #2

service mysql restart


STEP #3

Create a script that converts InnoDB to InnoDB

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -ANe"${SQL}" &gt; RemakeInnoDB.sql
cat RemakeInnoDB.sql


STEP #4

Execute the script

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysql ${MYSQL_CONN} &lt; RemakeInnoDB.sql


EPILOGUE

When everything is done, all the .ibd files will be gone. All the data and index pages will be inside ibdata1. You will find accessing the INFORMATION_SCHEMA against all InnoDB tables shockingly fast.

Here is the InnoDB Architecture



Note that ibdata1 (a.k.a. System Tablespace will house)


Data Pages
Index Pages
Data Dictionary
Double Write Buffer (Needed for crash recovery)
Insert Buffer (changes to secondary indexes)
Rollback Segments
Undo Space


Don't worry about putting 106G inside ibdata1. MySQL was running with innodb_file_per_table=0 before MySQL 5.5. It can still handle it.

Give it a Try !!!