Table of contents of the article:
When it comes to choosing a database to run your application, MySQL remains one of the most popular choices. As the most popular open source database in the DB-Engines index for over a decade, MySQL provides a reliable platform. However, on October 31, 2023, version 5.7 reached End of Life (EOL) status.
The End of Life (EoL) of a MySQL DBMS refers to the end of official support provided by the vendor for a specific version of the database. This means that security updates, bug patches, or enhancements will no longer be released, making the system more vulnerable to security risks and less compatible with new technologies. For companies, the EoL means upgrading to newer versions of MySQL to ensure the security, efficiency, and compatibility of their database. Failure to migrate can expose company data to security risks, data loss, and performance issues. Furthermore, new features and optimizations in updated versions of MySQL can be crucial for improving the overall performance and efficiency of the systems using the database.
According to telemetry data from the open source database management tool PMM, more than half of MySQL users are currently running version 5.7. This means that many instances will need to be upgraded or face potential issues.
However, many developers are reluctant to modify their database installations once they're working well. While other elements of the application stack receive regular updates and changes, databases require specific skills and a thorough understanding of how to get the most out of them in areas like queries, indexes, and performance. A complete upgrade like this can inspire trepidation, but planning the next steps before the EOL date is essential.
What does this update mean?
First of all, it is important to look at what the migration offers to improve the execution of your application and increase your productivity. For starters, MySQL 8.0 supports a number of updates to SQL (Structured Query Language) that make it easier to write and support queries. Efficient queries are at the heart of how you use data in your application and business, so anything that improves this should have a direct impact on your work.
A good example of this is subqueries. Creating them can be difficult if you don't work with SQL every day, so taking advantage of options like lateral derived joins and Common Table Expressions (CTE) can help. CTEs help compose and maintain complex queries by breaking them into smaller units that you can reuse over time. By keeping things simple and readable, this helps build the queries you need in your application. There's also a new intersection clause to help with sets, helping you find common data points in two or more datasets without having to write very complex queries.
MySQL 8.0 includes many new commands that can significantly improve your productivity. One example is EXPLAIN ANALYZE, which can help you tune your queries. EXPLAIN provides an estimate of how your query should behave, based on server analysis. However, this is only an estimate and could be incorrect. Using ANALYZE in conjunction with EXPLAIN executes the query, giving you a more precise understanding of how that query actually performs. This makes it much easier to find potential improvements to that query, rather than guessing. Additionally, INVISIBLE INDEX helps you test the efficiency of an index without risking a rebuild that can lead to serious problems.
In addition to these syntax and command changes, MySQL 8.0 now supports more international characters as the default character set has been updated to UTF8MB4, providing support for Unicode version 9.0. This is especially useful for companies with global operations.
Leaving the MySQL Query Cache
MySQL's Query Cache was once a key element for maximizing database performance. Designed in an era when computing systems were based on single-core, single-server architectures, this feature was optimized for such environments, where it could provide fast and efficient results. The Query Cache worked by storing the results of frequently used queries, allowing the database to retrieve this data quickly, without the need to recalculate the same query each time.
However, technological evolution has brought significant changes to computer system architecture. With the emergence and popularity of multicore processors and multithreaded architectures, the technological landscape has undergone a radical transformation. These changes have introduced new challenges to MySQL query cache management. The need to synchronize the cache across multiple cores and manage concurrent access from different threads has significantly complicated its implementation. This growing complexity has led to an increase in synchronization and cache management issues, resulting in slower database operations rather than faster ones.
Faced with these obstacles, MySQL developers made the decision to deprecate the Query Cache in version 5.7 and then completely eliminate it in version 8. This decision was also influenced by the end-of-life of MySQL 5.7 in October 2023, which meant that subsequent versions of MySQL would no longer include this feature. Although the Query Cache has been removed, its concept remains valuable, especially in scenarios where read requests dominate over write operations. In such contexts, the ability to quickly store and retrieve query results can still offer significant efficiency and performance benefits.
The elimination of the Query Cache in MySQL 8 represents a substantial change, with potentially critical implications , especially for websites with high read volumes, such as WordPress blogs or similar platforms. These sites often rely on fast read operations to provide a smooth and responsive user experience. The Query Cache, in these contexts, played a vital role in reducing database response times by storing the results of frequently used queries and thus reducing the load on the database itself.
Removing this feature in MySQL 8 could therefore lead to significant performance issues for such sites. Without the Query Cache, each request will require the full database query to be processed, potentially increasing response times and server load. This scenario poses a significant challenge for system administrators and website developers, who must carefully weigh the benefits and risks associated with upgrading to MySQL 8.
In some cases, it may be advisable to stay with MySQL 5.7 to maintain the benefits of Query Cache, despite reaching the End Of Life of this version. However, this choice can come with security risks and lack of support, making it a temporary and less than ideal long-term solution.
An interesting alternative is to migrate to MariaDB. Unlike MySQL and Percona Server, MariaDB has chosen to retain the Query Cache, thus preserving its advantages (and disadvantages) in contexts where this feature is particularly useful. This choice makes MariaDB an attractive option for sites that rely heavily on read operations and wish to preserve the performance offered by the Query Cache.
However, choosing MariaDB to preserve the benefits of Query Cache also means accepting a trade-off in terms of overall database management system (DBMS) performance. It's important to note that, according to current measurements, MySQL 8 performs significantly better than MariaDB, a difference that shouldn't be overlooked when choosing a DBMS.
Estimates indicate that MySQL 8's performance may be approximately double that of MariaDB. This difference is attributable to several factors, including more advanced optimizations in MySQL 8, improvements in the use of modern hardware, such as multicore processors, and the implementation of new features that increase overall system efficiency.
For example, MySQL 8 introduced improvements in query optimization, memory management, and I/O operations, thus contributing to a dramatic increase in performance. Additionally, MySQL 8 supports advanced features such as atomic DDL (Data Definition Language), which improves the management and security of data definition operations.
Plan the upgrade
Migrating from MySQL 5.7 to 8.0 is a straightforward move, but there are some specific and significant changes that can affect your applications. Checking your application and database ahead of time will signal whether this process will be simple or whether you will need to make some more complex changes as part of the migration process.
The MySQL Shell utility util.checkForServerUpgrade() can help with this. It runs 21 different tests to check for potential issues that could affect your migration. These checks include checking for table names that conflict with new reserved keywords and for any system variables that have been removed or changed to new default values in MySQL 8.0. A complete list of added, deprecated, and removed variables is available here. In addition to these potential issues, this utility checks for other issues, such as partitioned tables using non-native partitioning engines, circular references in tablespace data file paths, and any use of functions that were removed in MySQL 8.0.
Once you've run this utility and figured out how much work is involved in the move, you now have a choice. Should you do that direct migration from 5.7 to 8.0, or should you explore other options? If you have to put in significant work to prepare your application and database for the upgrade, should you put that effort into migrating to another platform or database instead? Likewise, you might decide to explore a different way to manage your infrastructure in the future—for example, you might currently have your application hosted on-premises, but could you move those servers to the cloud or a hosted service instead so you don't have to worry about managing the infrastructure in the future?
Each of these options has advantages and potential disadvantages, so it's important to evaluate them in context. For example, cloud-based services can make things much easier to get up and running, but you also have to spend more on the costs of managing those services. Additionally, you should review the data ingestion and extraction process for any cloud service you might use. This helps avoid large data outage costs if you ever need to change your approach. Finally, you should also consider whether the service you might use is open source. While many providers claim to be "compatible" with open source database options like MySQL, they may offer add-ons specific to their service or version.
