Calculated mysql5 table size different from ibdata

Question

I am trying to calculate the total size of all tables in mysql:

SELECT sum( data_length + index_length ) / 1024 / 1024 "Mb" FROM information_schema.TABLES;

+--------------+
| Mb           |
+--------------+
| 942.02298263 | 
+--------------+

The size of /opt/mysql5/data/ibdata is 3.1 GB. Why is there such a difference between the two numbers?

Answer 1

The ibdata is the busiest file in the InnoDB infrastructure. It houses many different classes of information.

• Table data
• Table indexes
• Table metadata
• MVCC data

You can see a graphic representation here.

Here are several links to discussions on this topic.

• MySQL: reducing ibdata file size for MyISAM tables
• Best way to reduce the size of ibdata
• And of course the docs

What (if any) action you choose will obviously depend on your setup and goals. I would however consider adjusting the inndb_log_file_size in your my.cnf file as these can get very large very quickly.