Mysql – Innodb buffer pool indexes versus data usage

innodbMySQLpercona

The server has 128GB of RAM. The innodb_buffer_pool_size is set to 106GB.

mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+--------------+
| Variable_name           | Value        |
+-------------------------+--------------+
| innodb_buffer_pool_size | 113816633344 |
+-------------------------+--------------+

When looking at the information_schema tables, it looks like I'm utilizing ~85GB for indexes.

mysql> SELECT ist.SCHEMA db, ((SUM(data_size) / 1024) / 1024) / 1024 size_in_gb FROM INNODB_BUFFER_POOL_PAGES_INDEX ibppi JOIN INNODB_SYS_INDEXES isi ON(isi.INDEX_ID=ibppi.INDEX_ID) JOIN INNODB_SYS_TABLES ist ON(ist.TABLE_ID=isi.TABLE_ID) GROUP BY ist.SCHEMA ORDER BY size_in_gb DESC;
+-----------+-----------------+
| db        | size_in_gb      |
+-----------+-----------------+
| databaseB | 78.940829364583 |
| databaseA |  5.648017468862 |
+-----------+-----------------+

Is it safe to assume the remaining 20GB's is being used for data?

semi-related-note: information_schema tables are really slow. I've waited on some queries for many hours, only to come back the next day with the query still running. I've even went so far as creating another db, copying the accompanying information_schema tables, changing the table engine to innodb/myisam, and insert into <table> select * but this doesn't give me live results. If anyone has insight into making these information_schema queries faster, that would be appreciated as well.

Best Answer

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}" > 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} < 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

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

Related Question