Need help to understand how information_schema.TABLES get updated

Question

I created large innodb table

mysql> select count(*) from big_data;

+----------+
| count(*) |
+----------+
| 23822336 |
+----------+

checked the stats in informaion_schema.TABLES

mysql> select data_length, index_length, data_free 
       from information_schema.TABLES 
       where TABLE_NAME = 'big_data';

+-------------+--------------+-----------+
| data_length | index_length | data_free |
+-------------+--------------+-----------+
|  4385144832 |            0 |         0 |
+-------------+--------------+-----------+

Then deleted approx 4M rows.

Checked the count

mysql> select count(*) from big_data;

+----------+
| count(*) |
+----------+
| 19978240 |
+----------+

checked the information schema.TABLES

mysql> select data_length, index_length, data_free 
       from information_schema.TABLES 
       where TABLE_NAME = 'big_data';

+-------------+--------------+-----------+
| data_length | index_length | data_free |
+-------------+--------------+-----------+
|  4385144832 |            0 |         0 |
+-------------+--------------+-----------+

There is no change!!

Can somebody please explain to me what happened?

I'm using mysql 5.6.18

Answer 1

InnoDB is telling you that the data pages are the same size.

The DELETE you ran simply makred the rows delete within the associated data pages.

You have to run

OPTIMIZE TABLE big_data;

or

ALTER TABLE big_data ENGINE=InnoDB;

to shrink it.

Since you inserted data into a table with no indexes, that explains 0 for index_length.

The data_free column is really superfluous when innodb_file_per_table is 1 (Thta's the default for MySQL 5.6). The data_free column was used to give you the total space free inside ibdata1 when innodb_file_per_table = 0 and all data and indexes were inside ibdata1. Since innodb_file_per_table = 1 and you loaded data into MySQL from the its beginning, I believe 0 will always appear for data_free for every InnoDB table in INFORMATION_SCHEMA.TABLES.

Here is something else to consider: The INFORMATION_SCHEMA.TABLES does not properly update because of the Storage Engine's ineraction with it.

For example, I noticed you used

select count(*) from big_data;

Why did you not use

select data_length, index_length, data_free, table_rows
from information_schema.TABLES 
where TABLE_NAME = 'big_data';

Using this Pictorial Representation of the InnoDB Architecture

InnoDB Architecture

try imagine what is happening when you delete 4 million rows:

• Drag data pages for the 4M rows into the buffer pool
• Capture those rows in the Rollback Segments
• Mark the rows delete inside those memory-resident data pages
• Write the changes into the Double Write Buffer
• Migrate changed data pages through the Log Buffer to commit the changes back to the .ibd file
• Write Redo Log Info into the Transaction Logs (ib_logfile0, ib_logfile1).
• Clean up the Rollback Segments upon the completion of the DELETE.

In light of this, it would take a considerable amount of time for the INFORMATION_SCHEMA.TABLES to catch up with all these changes.

Please note that you could never reliably extract exact row counts from INFORMATION_SCHEMA.TABLES. The MySQL Query Optimizer always approximates row counts for InnoDB tables by scanning non-leaf BTREE nodes. I have written about this consistently in the DBA StackExchange for almost 3 years.

• Mar 03, 2014 : The most efficient method to obtain the total entries into a mysql table
• Feb 23, 2013 : How does mysql deal with queries that touches myisam and innodb tables? (See ASPECT #3)
• Apr 23, 2012 : Why is a secondary index chosen over a clustered index for SELECT COUNT(*) ...?
• Aug 04, 2011 : Optimizing InnoDB default settings
• Jun 21, 2011 : From where does the MySQL Query Optimizer read index statistics?

Therefore, it is not surprising that other metadata aspects of InnoDB do not update column info in INFORMATION_SCHEMA.TABLES effectively .

What if the table was MyISAM ???

MyISAM does not have to jump through all these hoops like InnoDB. Notwithstanding, even if the big_data table was MyISAM, the .MYD file would remain the same size after the DELETE. You would still have to run OPTIMIZE TABLE big_data; to shrink the .MYD