Upgrading to MySQL 8 on All SiteGround Servers

A few weeks ago we initiated an upgrade to MySQL 8 across our servers. That came as a result of a long process of monitoring and testing the compatibility of this version with our clients’ websites, until we concluded that MySQL 8 is highly compatible with major applications hosted on our platform and safe to switch to.

MySQL is a fundamental piece of software in our stack, used by the great majority of our clients to power up their databases. Its optimal functioning has a great impact on the overall website performance and availability. That is why the SiteGround team dedicates a lot of resources to optimize and customize our MySQL, always aiming to squeeze out better and better performance. To achieve this goal over the years, we introduced various customizations such as our unique MySQL setup, and we carefully plan and test any new MySQL version upgrades, making sure the benefits outweigh the potential damages such an upgrade could bring. We are now confident that MySQL version 8 is largely compatible and our clients can safely take advantage of the enhanced database performance, scalability, and coding optimizations it brings along.

What is the plan for the MySQL update?

First, we would like to announce that all new websites created on our shared servers and all newly created cloud accounts now come with MySQL 8 by default.

Second, the migration process to MySQL 8 of existing shared sites is now in progress. Once our shared servers are updated, we will start updating the Cloud instances too. The process will continue in the next few months. According to our schedule, we plan to have migrated all our customers to MySQL 8 by the end of June this year.

To make the transition as easy and smooth for you as possible, we are sending you emails at least one week in advance to inform you on what dates exactly we are going to upgrade your website to MySQL 8. The upgrade procedure is performed late at night when the traffic to the server is minimal. A brief service interruption may occur while updating the software. No action is required on your side in relation to the migration.

What is new in MySQL 8?

MySQL 8 introduces a variety of new features and improvements of old functionalities all aiming to improve the overall performance and scalability as well as developer experience. Here are some of the most notable ones.

Better performance and scalability

MySQL 8 offers better performance and scalability. Benchmark tests show better results in terms of both performance and scalability for MySQL 8 compared to MySQL 5.7. The tests show that MySQL 8 is handling read/write operations much better and the number of processed transactions is much higher.

Furthermore, the tests show that MySQL 8 utilizes the hardware much better compared to 5.7.

Here are some of the improvements that make this possible:

• Scaling Read/Write Workloads

MySQL 8 shows better performance on intensive read/write and more than 2 times better performance on high loads is observed compared to MySQL 5.7.

• Better Performance upon High Contention Loads

Performance for high contention workloads has been significantly improved in MySQL 8. A high contention workload occurs when multiple transactions are waiting for a lock on the same row in a table, causing queues of waiting transactions.

In MySQL 8, such bursts are dealt with much better in terms of transactions per second and latency. That leads to better hardware utilization (efficiency), as the system needs less spare resources and can thus run with a higher average load.

• Native partitioning/InnoDB Pool Enhancement

MySQL 8 supports native partitioning for the InnoDB engine. That allows you to split large tables into smaller partitions. This can improve query performance and reduce storage requirements, especially for large tables. The new MySQL version also introduces enhancements of the InnoDB pool. The pool is responsible for caching the data and reducing I/O operations.

Common table expressions (CTEs)

With the latest MySQL 8 version, you can now take advantage of common table expressions as well. A common table expression is a result set which exists only temporarily while executing a statement that can later be used in the same statement.

These CTEs allow for creating more complex queries by using simpler blocks, based on temporary tables. In other words, common table expressions allow the usage of named temporary result sets.

Default character change

The default character in MySQL 8 is changed to utf8mb4.

One of the most notable differences between utf8, utf8mb3, and utf8mb4 is the maximum number of bytes per character. While utf8 and utf8mb3 can only store characters in the BMP (Basic Multilingual Plane) of Unicode, utf8mb4 can store characters in the Supplementary Plane. utf8mb4 supports a wide range of characters, including emojis and other special characters.

Invisible indexes

Another new feature in MySQL 8 are the invisible indexes. Generally, indexes are used by the MySQL query optimizer to improve the speed with which data is retrieved, while invisible indexes are the ones that are hidden from the MySQL query optimizer and make it possible to test removing an index on query performance, without making a destructive change.

For example, if you are working with a very large table with a few indexes, and you want to optimize it further by removing certain indexes and adding new ones. Imagine that you know that you will need these indexes in the future. Removing them in the first place might be dangerous, because building them again later on takes lots of resources. To avoid this, you can now simply use invisible indexes – make some existing ones into invisible ones, test whatever you need with the new ones, and then bring them back, when needed, by making them visible again.

Why is now the right time to migrate?

As MySQL is such a fundamental technology, major updates happen rarely and take a long time to be universally adopted. MySQL 8 was first released in 2018, but it took several years for the majority of the websites and their underlying applications to become compatible with it. We were constantly monitoring the level of compatibility and have chosen the current update time, as we have seen that now the risk of negatively impacting a website with the upgrade has greatly diminished. Since the update started a few weeks ago, we have already seamlessly moved hundreds of thousands of sites to MySQL 8.