Information_Schema.Tables reports different result in MySQL 5.5 and MySQL 5.7

Question

I have started testing some MySQL 5.7 databases to replace our ageing 5.5 Databases, and there seems to be a significant change in the way the information_schema.tables Table records the data size.

I run the following query:

SELECT  
    table_schema AS table_schema,   
    table_name AS table_name,   
    (data_length + index_length + data_free) AS table_size, 
    ROUND((data_length + index_length + data_free)/POWER(1024,3),2) AS table_sizeGB,    
    t.data_length,  
    t.index_length, 
    t.data_free
FROM information_schema.TABLES t
WHERE table_schema = 'myDb' 
ORDER BY table_size DESC, table_name
LIMIT 10;

This gives me the 10 largest tables. I run this daily to record the growth over time. On MySQL 5.5 it takes apx 5 seconds, on MySQL 5.7 it is instant.

On MySQL 5.5 I get the following result for the largest table:

myDb | myTable | 319383535616 | 297.45 | 319116279808 | 262012928 | 5242880

On MySQL 5.7 I get:

myDB | myTable | 270051115008 | 251.50 | 269824819200 | 226295808 | 0

Both Databases were created a few days apart. The 5.7 Database is a Slave of the 5.5 Database. Neither are active yet (just sitting as a slave of the Active System).

If I look on the server, the tables .ibd file is 302GB on the 5.5 Database, and 292GB on the 5.7 Database.

I expected a small discrepancy (one is using Antelope, on is Barracuda File format - both are innodb). Data is being constantly added to the database, and very little (if any) is being removed.

On the MySQL 5.5 database, I can see the numbers increase each time I run the query, even just seconds apart. But on MySQL 5.7, the numbers haven't changed since I started looking at it, a week ago.

Is this correct behaviour? Is there a better way to monitor this in MySQL 5.7 that I have missed?

-- UDPATED 2018-10-16

After three weeks, the result of the query is still virtually the same on MySQL 5.7:

5.5 -- myDb | myTable | 327890632704 | 305.37 | 327616036864 | 268304384 |6291456
5.7 -- myDb | myTable | 270055309312 | 251.51 | 269824819200 | 226295808 | 4194304

The Physical size on the server is 310GB on MySQL 5.5, and 300GB on MySQL 5.7.

Both Tables are created identically.

Neither database is active yet, so the only queries running (besides my testing) is from replication.

Both databases uses innodb_file_per_table.

The MySQL 5.5 was copied from an active slave using mysqldump.

The 5.7 database was copied from the MySQL 5.5 database using mysqldump + mysql_upgrade.

Optimize table has never been run on these tables.

Answer 1

Short answer: The numbers are close; don't worry.

Long answer:

There are a lot of explanations.

• Well, I guess Barracuda is 15% smaller than Antelope. (This may be the main reason for the difference. Still, many other things can cause differences.)
• Are they a different ROW_FORMAT.
• SELECTs can cause locking that leads to extra fragmentation of inserts/updates/deletes.
• Is innodb_file_per_table the same?
• How was the Slave data cloned from the Master?
• Have you done OPTIMIZE TABLE on either machine. (There is virtually no reason to every do so.)
• 5.7 has a lot of code changes over 5.5, but I can't think of any other issues that might directly show up in those numbers.
• It are not likely that there are any data differences. Percona has a tool that will check.
• Is there a "better way to monitor?" -- Monitor for what?

Also,

• When an InnoDB block shrinks due to updating/deleting, there is some attempt to merge the block with an adjacent block. It may be that 5.7 does a better job than 5.5, hence leading to slower growth of the table over time. (This seemed to be more of an issue in index BTrees.)
• The "Data_free" includes only a fraction of the reusable space. It should not be trusted for much.