MySQL – Information_Schema.Tables Reports Different Results in MySQL 5.5 vs 5.7

information-schemaMySQLmysql-5.5mysql-5.7

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.

Best Answer

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.

Related Solutions

Mysql – Adding index to large thesql tables

The activity of altering big tables are done in phases:

Create a new table with required fields and indexes say in test DB (just structure)
Dump the data from the existing table and load the same to the newly created table in test DB
Now announce your downtime :)
Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name; This is an atomic operation taking fraction of a second.
Load the extra records to the newly created table ( the records that came after dumping and then loading). This step can be done before Step: 3 also once to reduce your downtime.
And your system is back

Few notes:

You need not hit information schema directly like this, try using SHOW TABLE STATUS from db like 'table_name'
The speed of alter table is more or less linked with the I/O speed. Last time when we ran a direct alter table(without the above steps), we had 40GB+ table size it took around 4hours. If your 20GB data is taking years, you are working on some outdated machine.
Also drop of the unwanted indexes like index_bslt_ondate, index_base_schedule_line_items_loan_base_schedule as some other indexes has the left most column as the indexed column

Please let me know if you need any clarification on any of these steps.

Edit : A simple python script to automate the process https://github.com/georgecj11/hotswap_mysql_table.git

Mysql – MariaDB 5.5 vs MySQL 5.7 query optimizers

First, I would try an index on (n_file, n_surn, n_type, n_id, n_surname) for this query. You may have to change the GROUP BY clause for the index to be used effectively by all versions of the optimizer:

GROUP BY n_file, n_surn COLLATE 'utf8_unicode_ci'

Second, why the derived table, the group by and the join? I think the query is equivalent to the much simpler:

SELECT SQL_CACHE 
    n2.n_surn, n1.n_surname, n1.n_id
FROM wt_name n1 
WHERE n_file = 4 
  AND n_surn NOT IN ('', '@N.N.')
  AND n_type <> '_MARNM' ;

Best Answer

Related Solutions

Mysql – Adding index to large thesql tables

Mysql – MariaDB 5.5 vs MySQL 5.7 query optimizers

Related Question