Overview

MySQL 5.7 has reached EOL in October 2023 forcing users to migrate to MySQL 8.0. Migrating from MySQL 5.7 to MySQL 8.0 offers significant performance improvements and new features, but the removal of the Query Cache in MySQL 8.0 can pose challenges and performance hits. Readyset provides a solution to maintain and improve query performance by acting as an external query cache with automatic cache updates. This white paper outlines a migration strategy that integrates Readyset into the existing ProxySQL and MySQL 5.7 setup, and seamlessly transitions to MySQL 8.0 while leveraging Readyset to mitigate potential performance penalties.

Upgrading to MySQL 8.0 provides numerous benefits, including better indexing, enhanced security, and new functionalities. However, the removal of the Query Cache can lead to performance penalties for applications that heavily rely on this feature in MySQL 5.7. Readyset offers a drop-in replacement by providing an external query cache that can be integrated into the database architecture to maintain high performance. This white paper details a step-by-step migration process using RDS Blue/Green Deployment and Readyset.

Key Differences and Improvements in MySQL 8.0

MySQL 8.0 introduced a significant number of improvements in the 8.0 release series. You can find the full list of improvements here. Here are some of the key differences at glance:

Performance Enhancements

• Improved Indexing: Advanced indexing algorithms, such as descending indexes.
• Optimizer Improvements: Enhanced query execution plans.

Security Improvements

• Default Authentication: Stronger caching_sha2_password authentication.
• Enhanced Encryption: Better data-at-rest encryption.

New Features

• Window Functions and CTEs: Improved query capabilities.
• JSON Enhancements: Better support and indexing.
• Roles: Simplified privilege management.

Removal of Query Cache

MySQL 8.0 has completely removed the Query Cache, which can impact applications that rely on cached query results for performance. Readyset can be integrated to fill this gap, ensuring continued high performance.

Components

• ProxySQL: Acts as a proxy layer to manage and redirect traffic.
• MySQL 5.7: MySQL 5.7 Instance (Blue).
• MySQL 8.0: MySQL 8.0 Instance (Green).
• Readyset: A caching layer or an accelerated database instance to speed up queries.

Migration Plan Overview

Workflow

Pre-Migration Steps

Assessment and Planning

• Compatibility Check: Ensure application compatibility with MySQL 8.0 
• Backup Strategy: Have a working and tested Full backup of MySQL 5.7.

Test Environment Setup

• Readyset Integration Testing: Test the integration of Readyset with MySQL 5.7 and MySQL 8.0.

ProxySQL Setup

• Configure ProxySQL: Install and configure ProxySQL to work with current MySQL 5.7
• Switch Traffic: Switch application traffic to use ProxySQL as entry point.

Migration Steps

RDS MySQL 8.0 Blue/Green Deployment

• Configure Blue/Green Deployment: Deploy a new Blue/Green deployment selecting the green deployment to use RDS MySQL 8.0. More details here.

Data Synchronization Testing.

• Verify Data Integrity: Ensure data consistency between Blue and Green Deployments.

Switch Over to RDS MySQL 8.0

• Switch Over to Green Deployment: Switchover to green deployment.

Integration of Readyset with RDS MySQL 8.0

• Set Up Readyset: Install and configure a new Readyset instance for RDS MySQL 8.0.
• Test: Validate Readyset has completed snapshot required tables.
• Install ProxySQL: Configure ProxySQL to utilize the new Readyset instance.

Configure Cache

• Adjust ProxySQL: Gather slow queries and adjust ProxySQL to redirect them to  Readyset.
• Monitor and Optimize: Continuously monitor the new setup and add more queries to Readyset to optimize performance.

Detailed Migration Steps

Architecture

ProxySQL Configuration

• Main Proxy Layer: ProxySQL will serve as the main entry point for database traffic.
• Hostgroups: Set up two hostgroups:
  • Hostgroup 1: For MySQL 5.7.
  • Hostgroup 2: For Readyset 

Traffic Routing Logic

• Write Traffic: Direct all write traffic to MySQL 5.7 to ensure data consistency.
• Cache Traffic: Redirect most problematic read traffic to Readyset to leverage its caching capabilities.

Pre-Migration Steps

Install ProxySQL

Install ProxySQL (if not part of your stack) by following ProxySQL official documentation.

Configure ProxySQL

Configure your application user in ProxySQL, redirecting all it’s traffic to Hostgroup 1:

INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('application', 'password', 1);
LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;

Configure RDS MySQL 5.7 (Blue) in Hostgroup 1:

INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES (1, 'mysql_blue_endpoint', 3306, 1);
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVER TO DISK;

At this point, you can point your production traffic to ProxySQL and it will flow to current blue server (RDS MySQL 5.7)

RDS MySQL 8.0 Blue/Green Deployment

Note: Ensure your DB parameter group for green databases has the following configuration set:

• Binlog_format = ROW
• Log_slave_updates = ON

Data Synchronization Testing.

Query your database and validate all your data is present. Also check if new data has been inserted into the RDS MySQL 8.0 database via the replication workflow.

Switch Over to RDS MySQL 8.0

Go to your RDS Blue/Green DB Identifier and select Actions > Switch over:

Wait for the switch over to complete. ProxySQL will automatically identify the new server under hostgroup ID 1 as they use the same endpoint as previous blue server (RDS MySQL 5.7).

Integration of Readyset with RDS MySQL 8.0

Set Up Readyset

Install Readyset by following the official documentation. To configure it to run against green deployment, adjust /etc/readyset/readyset.conf :

UPSTREAM_DB_URL="mysql://app_user:password@blue_endpoint:port/dbname"
LISTEN_ADDRESS=0.0.0.0:3306
CDC_DB_URL="mysql://replication:password@blue_endpoint:port/dbname"

Once the configuration is applied. Start the service:

systemctl start readyset

Adjust ProxySQL

Add Readyset to ProxySQL. Configure it to use hostgroup 2

INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES (2, 'readyset_endpoint', 3306, 1);
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVER TO DISK;

Test

Connect to Readyset and check Snapshot Status and replicated tables:

readyset> SHOW READYSET STATUS;
+----------------------------+-------------------------------------------+
| Variable_name              | Value                                     |
+----------------------------+-------------------------------------------+
| Database Connection        | Connected                                 |
| Connection Count           | 1                                         |
| Snapshot Status            | Completed                                 |
| Maximum Replication Offset | mysql-bin-changelog.000008:1295           |
| Minimum Replication Offset | mysql-bin-changelog.000008:1295           |
| Last started Controller    | 2024-08-06 18:49:35 UTC                   |
| Last completed snapshot    | 2024-08-06 18:49:35 UTC                   |
| Last started replication   | 2024-08-06 18:49:35 UTC                   |
+----------------------------+-------------------------------------------+
8 rows in set (0.00 sec)

readyset> SHOW READYSET TABLES;
+------------------------+----------------+-------------+
| table                  | status         | description |
+------------------------+----------------+-------------+
| `test`.`my_table`      | Snapshotted    |             |
+------------------------+----------------+-------------+
1 row in set (0.00 sec)

Configure Cache

Query ProxySQL stats_mysql_query_digest to get problematic queries:

proxysql> select digest, digest_text, min_time, max_time from stats_mysql_query_digest order by min_time desc limit 1;
+--------------------+----------------------------------------+----------+----------+
| digest             | digest_text                            | min_time | max_time |
+--------------------+----------------------------------------+----------+----------+
| 0x7721D69250CB40   | SELECT * FROM my_table WHERE ID = ?    | 117541   | 117541   |
+--------------------+----------------------------------------+----------+----------+

Attempt to create a cache in Readyset. If the command succeeds, it means Readyset support this query and a cache will be created for this query:

readyset> CREATE CACHE FROM SELECT * FROM my_table WHERE ID = ?;
Query OK, 0 rows affected (0.00 sec)

readyset> SHOW CACHES;
+--------------------+--------------------+------------------------------------------------------------------------------------------+-------------------+-------+
| query id           | cache name         | query text                                                                               | fallback behavior | count |
+--------------------+--------------------+------------------------------------------------------------------------------------------+-------------------+-------+
| q_5d27f83c7392ebc5 | q_5d27f83c7392ebc5 | SELECT `test`.`my_table`.`ID` FROM `test`.`my_table` WHERE (`test`.`my_table`.`ID` = $1) | fallback allowed  | 0     |
+--------------------+--------------------+------------------------------------------------------------------------------------------+-------------------+-------+
1 row in set (0.00 sec)

Warmup Readyset cache by mirroring the query to Readyset:

proxysql> INSERT INTO mysql_query_rules (rule_id, digest, destination_hostgroup, mirror_hostgroup) VALUES (1, '0x7721D69250CB40', 1, 2);
proxysql> LOAD MYSQL QUERY RULES TO RUNTIME;
proxysql> SAVE MYSQL QUERY RULES TO DISK;

At this point, RDS MySQL 8.0 is still serving the query, but a copy of the query is been redirected to Readyset in order to warmup the cache. Once some time has passed. Adjust the rule to redirect the query to Readyset

proxysql> UPDATE mysql_query_rules SET destination_hostgroup = 2, mirror_hostgroup = NULL WHERE rule_id = 1;
proxysql> LOAD MYSQL QUERY RULES TO RUNTIME;
proxysql> SAVE MYSQL QUERY RULES TO DISK;

Repeat this process to find more slow queries to cache.