Issue with table size after upgrading to MySQL 8

[Stefan Jankovic]

I have two MySQL servers. One is running on 5.7.24 and another one is running on 8.0.29 version.

In 5.7.24 I have a table with 71M (millions) records and its size is about 6GB. The same table on 8.0.29 version (with same indexes) has 13.5GB.

This table size affects a lot of my queries, both inserts, and selects.

The steps that I made in this process are:

* Dumping table from MySQL 5.7.24
* Importing dump to MySQL 8.0.29

Both MySQL servers are running in a Docker container. MySQL 5.7.24 is running on port 3306 MySQL 8.0.29 is running on port 3307

The important thing to mention is that after the table dump, MySQL 5.7.24 is not running anymore so it doesn't use any hardware resources.

Does anyone know what could be the reason for this behavior?

[Tim Holloway]

There are all sorts of reasons.

Unlike disk files, which generally have realtively fixed storage requirements, databases use storage in more complex and less predictable ways. MySQL in fact, is a generic engine that can drive several different storage backends, historically innodb, ISAM, various other forms and even CSV! So a very important effector of disk space needs is which backend your tables are stored in.

Databases are also designed to deal with large amounts of field-structured data and often with repetitive field values. That means that a backend can get really creative with how it packs information interrnally. Consider Morse Code. The most common letter values (E, T, A, N) are very short. Less common letter values (Y, Q) take more space. ZIP compression utilities do this also, but in their case, they dynamically determine what's most common in a given set of data.

Migrating data can therefore bring a number of effects. First, you might be using a different backend. Secondly. the order in which the data was loaded might impact storage-balancing algorithms. Third, there might be different tuning options set on in the new server.

If space is really critical, consult the MySQL storage tuning literature. For most of us, disk is cheap and tuning expertise is expensive.