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 more than a decade, MySQL provides a reliable platform. However, last 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 manufacturer for a specific version of the database. This means that no more security updates, bug patches or improvements will be released, making the system more vulnerable to security risks and less compatible with new technologies. For businesses, EoL means they need to upgrade 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. Additionally, new features and optimizations found in updated versions of MySQL can be crucial to improving the overall performance and efficiency of systems that use the database.
According to telemetry data from the open source database management tool PMM, more than half of MySQL users currently use version 5.7. This means there are many instances that will need to be updated or risk potential problems.
However, many developers are reluctant to modify their database installations once they are working well. While other elements of the application stack receive regular updates and changes, databases require specific skills and in-depth understanding to make the most of them in areas such as queries, indexes and performance. A comprehensive update like this can inspire feelings of trepidation, but planning your next steps is essential before your EOL date.
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. An example is EXPLAIN ANALYZE, which can help you tune queries. EXPLAIN provides an estimate of how your query should behave, based on server analysis. However, this is only an estimate and may be incorrect. Using ANALYZE together with EXPLAIN runs the query, so you can get a more precise view of how that query behaves in reality. This makes it much easier to find potential improvements in that query, rather than guessing. Furthermore, INVISIBLE INDEX helps you test the efficiency of an index without risking a reconstruction that can lead to serious problems.
In addition to these syntax and command changes, MySQL 8.0 can now support more international characters as the default character set has been updated to UTF8MB4, which provides support for Unicode version 9.0. This is especially useful for companies with worldwide operations.
Leaving the MySQL Query Cache
MySQL Query Cache was once a key element in 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 was able to deliver fast and efficient results. The Query Cache worked by storing the results of frequent 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 in the architecture of computer systems. With the emergence and popularity of multicore processors and multithreaded architectures, the technology landscape has undergone a radical transformation. These changes have introduced new challenges in managing the MySQL Query Cache. The need to synchronize the cache across multiple cores and handle concurrent access from different threads significantly complicated its implementation. This growing complexity has led to an increase in synchronization and cache management problems, resulting in database operations slowing down rather than speeding them up.
Faced with these obstacles, MySQL developers made the decision to deprecate the Query Cache in version 5.7 and subsequently eliminate it completely in version 8. This choice was also influenced by MySQL 5.7 reaching the End Of Life 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 provide significant efficiency and performance benefits.
The elimination of Query Cache in MySQL 8 represents a substantial change, with potentially critical implications, especially for websites with a high volume of reading, 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, storing the results of the most frequent queries and thus reducing the load on the database itself.
Removing this feature in MySQL 8 can therefore lead to significant performance issues for such sites. Without Query Cache, each request will require full database query processing, potentially increasing response times and server load. This scenario poses a major challenge for system administrators and website developers, who must carefully evaluate 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 represented by the migration to MariaDB. Unlike MySQL and Percona Server, MariaDB has chosen to maintain the Query Cache, thus preserving its advantages (and disadvantages) in contexts where this functionality 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 advantages of the Query Cache also involves accepting a compromise in terms of overall performance of the database management system (DBMS). It is important to underline that, according to current evaluations, MySQL 8 presents significantly superior performance compared to MariaDB, a difference that cannot be overlooked when choosing the DBMS.
Estimates indicate that the performance of MySQL 8 can be approximately double that of MariaDB. This difference is attributable to various 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 usefulness MySQL Shell's util.checkForServerUpgrade() can help with this. It runs 21 different tests to check for potential issues that could affect your migration. Checks include searching for tables with 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. The complete list of added, deprecated and removed variables is available here. In addition to these potential issues, this utility looks for other issues such as partitioned tables using non-native partitioning engines, circular references in tablespace data file paths, and any usage of functions that were removed in MySQL 8.0.
Once you've run this utility and found out how much work is involved in the step, you now have a choice. Should you make that direct migration from 5.7 to 8.0, or should you explore other options? If you have to put significant work into preparing your application and database for the upgrade, then should you put that effort into emigrating to another platform or database instead? Likewise, you might decide to look at a different way of managing your infrastructure in the future – for example, you might currently have your application hosted on-premise, but you might 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 will have advantages and potential disadvantages, so it is important to evaluate them in context. For example, cloud-based services can make it much easier to get things running, but you also have to spend more on the costs of running those services. Additionally, you should check the process of getting data in and out of any cloud service you may be using. This helps avoid large data egress costs if you ever need to change your approach. Finally, you should also look into whether the service you may be using is open source or not. While many vendors claim to be “compatible” with open source database options like MySQL, they may offer add-ons specific to their service or version.
