Upgrade from MySQL 5.7 to MySQL 8 - Do it! Do it now!

Our web hosting application platform Pagebloom has been running on MySQL 5.7 for some time now and it runs brilliantly - it's exceptionally responsive and provides great user experience.

The performance of the underlying MySQL 7 has been great except...

Clustered indexing: The double edged sword

Part of MySQL's great read performance is due to an "under the hood" design feature that most software developers who use it are blissfully unaware of.

In the automobile world it would be the equivalent of fuel injection or a turbo/super charger or in German - a Kompresser! These are features that can be added to the design of an engine to provide a significant boost in performance.

For MySQL this feature is what is known as a "Clustered Index". The main concept of a clustered index is that the core data of each record is actually stored within the primary key index. Yes, you heard correctly - actual record data is embedded into the B-tree frames that make up the index - I kid you not! At first this may seem crazy but what it means is that for data retrieval that involves locating a record via its primary key - arguably the most common type of retrieval in a relational database - once you have located the correct B-tree frame in the index you have the data right there as well. In non clustered RDBMS architectures only a location reference to the actual data is stored in the index frame so retrieval of the record data at that point requires an extra disk read but in MySQL the retrieval can be done with a single disk read.

Schema changes are MySQL 5.7's Achilles heel

While a clustered index provides great read performance its write performance has traditionally suffered in the MySQL 5.x versions.

Where the clustered index has caused very noticeable performance issues is when you need to do schema changes that add a column or two to an existing table with millions of records.

If you google "mysql add column slow" you can read endless accounts of slow schema updates with the 5.x series versions. Some people report waiting, literally, for days for it to complete! This can cause real issues with maintenance/upgrades of live production systems.

MySQL 8 to the rescue!

For one recent update to Pagebloom the required schema changes were to core tables with literally millions of records that hold organizations, people, users etc., Whilst performing the schema update in a test system we saw the schema update took many hours on MySQL 5.7 - this was unacceptable as, even if we performed the update at 2AM in the morning, we still did not wish to take the live system down for multiple hours. We do most updates at that time of morning in under 60 seconds.

We had heard that MySQL 8 had made some inroads to this "slow schema update" issue and so we thought a possible path was to migrate from MySQL 5.7 to 8.0.21 FIRST and then do the schema update once we're live on MySQL 8 - it sounded like a plan so we went with it.

The time to shine is 2AM!

Early one Monday morning we did the fairly quick migrate from 5.7 to 8 and then the following Tuesday morning it was time for the schema update which had taken many hours in our MySQL 5.7 tests. How long would it take on MySQL 8 in production?

OMG! It completed in under 5 minutes!!! I don't know exactly what the MySQL team have done with MySQL 8 but it's like they've added a Kompressor/turbo to their clustered index architecture.

This "many orders of magnitude performance improvement" is very rare in the software world but it is very much appreciated and in fact, is basically mandatory for MySQL to be running real, live, production systems in these days of sophisticated database schemas with highly populated tables.

Conclusion

Never be the first to jump in to a new major version release but after it is stable and many guinea pigs have gone before you and suffered all the necessary "burns" required to get a new major release ready for widespread consumption you really should consider upgrading.

In terms of MySQL 8 it is definitely time to migrate from 5.x releases if you have not already done so. The recommended approach is to upgrade to the latest version of the 5.7.x releases first and then the upgrade to 8.0 should be much smoother. Maybe, like us, you will really appreciate the improvements.