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.