servd

Upgrading a Project From MariaDB to MySQL 8

Craft 5 no longer officially supports the MariaDB database which has been our default since Servd first launched. In order to ensure both new and existing projects can run Craft 5 optimally, we've switched out default database to MySQL 8.0 using the Percona XtraDB distribution.

Existing projects can upgrade from MariaDB to MySQL 8.0 using the upgrade button in our dashboard on the Project Settings > Build & Deploy page.

Important Changes in MySQL 8.0 #

• MySQL 8.0 removes the built in 'query cache'. The performance benefits of this feature have always been questionable however Servd has had it enabled by default with a conservative configuration. Its removal in MySQL 8 could therefore lead to changes in performance for some workloads (both positive and negative), however we expect an overall positive performance improvement due to...
• MySQL 8.0 contains a new query optimiser which should improve the speed of queries that include complex or large table joins. Craft itself is also optimised against this query optimiser implementation.
• MySQL 8.0 uses a new, more secure authentication plugin called caching_sha2_password. Any tools used to connect directly to the database will need to support this. All Servd components are already compatible, however you might need to build a new Bundle in order to get the latest PHP base software which allows Craft's Control Panel database dump utility to work.
• MySQL 8.0 supports a lower maximum number of columns in a single table compared to MariaDB. This is due to a long standing bug in MySQL/InnoDB which incorrectly calculates the byte-row length of a set of columns (https://bugs.mysql.com/bug.php...). MySQL 8 therefore only supports a maximum of 196 TEXT/BLOB/VARCHAR columns in a single table, whereas MariaDB supports more. As a result, our database migration process checks for any tables in an existing project's database with greater than 190 columns and aborts if any exist.

The Upgrade Process #

MariaDB and MySQL are not drop-in compatible so a straight switch from one to the other is not possible. The upgrade process therefore uses the following process:

1. Backups of any non-production environments are taken
2. [Optional] The project is switched to an offline mode, responding to all traffic with a 503 error and maintenance page
3. A backup of the production environment is taken
4. The MariaDB database is shut down and filesystem scrubbed
5. The MySQL 8.0 database is started and initialised
6. The production environment backup is imported
7. [Optional] The project is taken out of offline mode
8. Non-production environment backups are imported

How Long Does It Take? #

The process purely depends on the total size of all environment databases, which can be estimated by adding the size of the latest database backup sizes of each of the environments.

It varies per project, but in general if the total size of the latest backups for a project's production and staging environment is less than 100MB, then the migration will likely take less than 10 minutes to complete. A total of around 300MB will likely take about 20 minutes, and 500MB+ will usually take upwards of 30 minutes to complete.

The following can help speed up the migration task:

• Delete as much non-essential data from your database as you can. Wiping staging and development databases and then recreating the data from the production database at a later date can make a considerable difference.
• Perform the migration with Spike Protection activated. Spike Protection servers have considerably more CPU capacity, and have much faster filesystem volumes, so are able to perform the migration faster.

Offline Mode #

When triggering the upgrade you have the option to enable Offline Mode for the production environment's data migration. Offline Mode returns a 503 error for all incoming requests along with a generic, non-branded 'maintenance' page.

This can be useful for three reasons:

• It prevents users from making changes to data during and after the production backup being taken which would then be lost
• It prevents Craft error pages from being shown during the production import phase
• It prevents any upstream caches from caching responses generated half way through the process which might include errors or incorrect data

We recommend enabling Offline Mode for any transactional and commerce projects, or very large databases. However, if your project is using static caching and has a modestly sized database, you can leave Offline Mode disabled and end users are unlikely to notice anything during the process as the static cache will remain functional throughout.