MySQL 5.7 - some .IBD files 15% smaller on master than on slaves

MySQL & MariaDB
1

Hello,

I have an interesting situation where some .IBD files on a master are smaller than on all slaves replicating from it. Normally we expect that this would happen in the reverse order where the master has larger .IBD files. All the slaves have very close sizes.

I’m looking for any input on why this may be.

Thanks.

2

Hello @ChrisDK,
Check how much free space is in the tablespace (.ibd file) between the two servers. It’s possible that on the source the table was rebuilt and free space was reclaimed.

3

Hi Matthew,

Thanks for your input.

I know for fact that the table was not rebuilt on the master.

Any other ideas? It’s a little alarming to see this happening.

Thanks,
Chris

4

Did you check free space as I suggested?

5

Free space does not appear to be a factor. I checked on 1 table where the difference is many GBs. Please note I have masked the results.

Query:
SELECT table_name,
→ sum( data_length + index_length ) / 1024 / 1024 “DB size in MB”,
→ sum( data_free )/ 1024 / 1024 “free/reclaimable space in MB”
→ FROM information_schema.TABLES
→ where table_name = ‘tabletesting’;

Table on master:
----------------±----------------±-----------------------------+
| table_name | DB size in MB | free/reclaimable space in MB |
±---------------±----------------±-----------------------------+
| tabletesting | 909942.71875000 | 20.00000000 |
±---------------±----------------±-----------------------------+

Table on slave:
±---------------±----------------±-----------------------------+
| table_name | DB size in MB | free/reclaimable space in MB |
±---------------±----------------±-----------------------------+
| tabletesting | 776099.38281250 | 10.00000000 |
±---------------±----------------±-----------------------------+

Some more information. The specific table I am looking at is partitioned (hash). The table is compressed by mysql. I am running mysql on AWS EC2. It is mysql 5.7.

I have had my devops team compare EC2 instances and all factors are same (no compression differences on EBS, etc.).

We are also going back and forth with AWS support, but need to also continue exploring whether mysql itself is the culprit.

Thanks.

6

Just some more info. Looking at the file level we see the sizes considerably larger on the slave.

master
-rw-r----- 1 mysql mysql 589G May 31 14:39 tabletesting#P#p5.ibd
-rw-r----- 1 mysql mysql 480G Apr 6 12:36 tabletesting#P#p3.ibd
-rw-r----- 1 mysql mysql 465G May 3 15:18 tabletesting#P#p4.ibd
-rw-r----- 1 mysql mysql 356G Apr 6 12:36 tabletesting#P#p2.ibd

slave
-rw-r–r-- 1 mysql mysql 695G May 31 14:40 tabletesting#P#p5.ibd
-rw-r–r-- 1 mysql mysql 564G Apr 6 12:33 tabletesting#P#p3.ibd
-rw-r–r-- 1 mysql mysql 548G May 3 15:14 tabletesting#P#p4.ibd
-rw-r–r-- 1 mysql mysql 418G Apr 6 12:33 tabletesting#P#p2.ibd

7

Have a look at this recent post and Yves’s answer.

8

Thanks.

I’ve added a few comments in the thread you provided.

One interesting thing that I’ve noted based on Yves’s answer is that we have 2 slaves both replicating from the same master. The slaves have the exact same data sizes (smaller than master) and yet they are used in very different ways. One is a failover node and has no traffic at all. The other is a read replica and so gets all sorts of queries many of which are long running.