Issue with table size after upgrading to MySQL 8

Question

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

In 5.7.24 I have 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 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 Docker container. MySQL 5.7.24 is running on port 3306 MySQL 8.0.29 is running on port 3307

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

Does anyone knows what could be the reason for this behaviour?

Answer 1

Sounds like the tables were forced to be converted from MyISAM to InnoDB.

See if you can still do SHOW CREATE TABLE on a big table on the old system.

A RoT (Rule of Thumb) is that InnoDB takes 2x-3x the disk space compared to MyISAM. (13.5/6)

The increase in size does not necessarily imply a performance issue. In general InnoDB is faster than MyISAM. If you are having performance issues, let's see some of the slowest queries. Please provide SHOW CREATE TABLE (available in the dump) and EXPLAIN SELECT ... (Preferably from both servers.)