Using ProxySQL to Replace Deprecated MySQL 8.0 Query Cache
Optimising Amazon Aurora/RDS MySQL 8.0 performance by using ProxySQL to replace deprecated Query Cache
Quinn Cheong
Amazon Employee
Published Apr 29, 2024
This Blog was co-authored together with Pengfei Zhang, Senior Solutions Architect, AWS.
You can find the assets here: https://github.com/aws-samples/proxysql-to-replace-mysql-query-cache
MySQL is the world’s most popular open source database and MySQL 5.7 and MySQL 8.0 compatible editions are supported on Amazon RDS and Amazon Aurora. MySQL community 5.7 edition has reached end of life in October 2023. Subsequently, MySQL 5.7 compatible editions on Amazon RDS and Amazon Aurora has reached / will reach end of standard support on February 2024 and October 2024 respectively.
To allow customers to upgrade to a new major version at their own pace, AWS has announced Amazon RDS Extended Support. When your databases are enrolled into Extended Support, AWS will continue providing critical security and bug fixes for your MySQL databases on RDS even after standard support ends.
It is strongly advised to upgrade to MySQL 8.0 as it brings advanced features and continue community support. However, some MySQL architectural changes have also been made in the community edition of MySQL. One such change is the deprecation of the Query Cache, which was an in-built cache for query results. This could result in initial performance dips for workloads that have not made use of new MySQL 8.0 features.
In this post, we explore how you can optimise cost and increase query performance of your Amazon Aurora MySQL Databases by running a highly-available setup of ProxySQL to replace the deprecated Query Cache. We provide a detailed look into its capabilities, setup, and how it can be leveraged to maintain, and even enhance, database performance under these constraints.
Cost and Performance are paramount to databases, and optimising for these 2 factors differ for every database engine. Optimisation decisions usually involve difficult tradeoffs between one another that must account for numerous external factors, while considering potential implications post-implementation.
In MySQL 5.7, the Query Cache has played a crucial role in optimizing database performance by caching query results, thus reducing CPU and memory load upon subsequent requests. However, its removal in MySQL 8.0 has led to increased resource utilization for some workloads/queries, directly impacting those who relied heavily on this feature for performance optimization.
Consequently, this necessitates a shift towards more advanced hardware configurations to handle the additional processing requirements, inadvertently escalating operational costs. This predicament has prompted a search for viable alternatives, highlighting the urgent need for innovative solutions to sustain efficiency and cost-effectiveness amidst evolving database technologies.
To adapt to the deprecation of the Query Cache in MySQL 8.0, several strategies can be employed to ensure continued optimal database performance:
- Query Optimization: Enhance SQL queries and indexes to minimize execution time and resource usage.
- External Caching Solutions: Utilize Amazon Elasticache or Redis as an in-memory data structure store. Serving as both a cache and message broker, these tools can significantly reduce database load by offloading frequently accessed data, thereby improving access speeds and overall application performance.
- Database Performance Tuning: Changing certain MySQL database parameters to make the database more performant. Parameter tuning differs depending on the workloads that the database faces.
However, there are still situations where these strategies fall short. In many scenarios, query optimization and the integration of external caching solutions like Redis/Elasticache is also not feasible — often due to constraints that modifying application code is not possible with legacy or third-party applications. Performance tuning, while effective, involves deep database expertise and tuning parameters can come with high risks. Thus, finding an effective alternative to these strategies becomes imperative.
It's in this context that ProxySQL emerges as a formidable solution, offering a new avenue to replicate the benefits of the now-deprecated Query Cache in MySQL 8.0. ProxySQL stands out for its ability to act as a middle layer between clients and the MySQL servers, effectively managing and optimizing SQL queries without requiring direct changes to the application code.
ProxySQL can be seamlessly integrated into many different workloads just be changing the MySQL host endpoint. Clients can continue interacting with ProxySQL just like how they would interact with their MySQL database, and there would be no observable differences between the 2.
The following diagram shows the proposed solution:
- Client Applications connecting to ProxySQL Servers via the Internal Network Load Balancer (NLB).
- Internal NLB that routes traffic to a HA deployment of ProxySQL Servers
- ProxySQL Servers redirect traffic to Aurora Reader/Writer Endpoints via ProxySQL Read/Write Split.
- Aurora Reader Endpoint forwards requests to the Aurora Reader Instances, while the Aurora Writer Endpoint will forward requests to the Aurora Writer Instance
The architecture also meets the following well-architected considerations:
The solution is Highly Available and avoids a single-point failure by using the NLB to load balance across multiple ProxySQL Servers. Additionally, Amazon Aurora MySQL, is highly available and fault tolerant in a multi-AZ setup.
Compared to Elasticache, we expect about a 33% cost reduction. Additionally, Elasticache’s smallest node size is cache.r7g.large (2vCPU, 13.07GiB RAM). For some workloads, this might be too much memory. This ProxySQL deployment can even use smaller node sizes like r7g.medium (1vCPU, 8GiB RAM) to enable higher cost savings for smaller workloads.
The monthly cost estimation of HA ProxySQL cluster on r7g.large (2 vCPU, 16 GiB RAM) is 236 USD (based on the price in us-east-1 region as date April 2024):
- ProxySQL in HA: 2 x r7g.large = 156.4 USD
- NLB (10TB Data, 1000 TCP Conn / s, 60s/TCP Connection) = 79.84 USD
As a comparision, if we use ElastiCache for Redis with the similiar HA configuration, the monthly cost estimation is 2 x cache.r7g.large (2 vCPU, 13.07 GiB RAM) = 320 USD. Cost Estimates were made using AWS Pricing Calculator
The solution achieves better performance compared to the benchmark solution with MySQL 5.7 with Query Cache feature. We’ve conducted a performance analysis and our results are shown in the next section. To achieve even better performance on your own workload, you can tweak the performance by tuning the right ProxySQL parameters, e.g. mysql-query_cache_size_mb, etc.
There were some steps that we took before running the benchmarking test.
- We deployed the setup in our Github Repo and used the Sysbench Instance to conduct the benchmark
- We ensure that NLB sticky session were turned on to ensure maximum cache hits on 1 ProxySQL cache.
In our benchmarking report, we ran 2 separate Sysbench tests (oltp_read_only, oltp_point_select). In both tests, we observe a drop in performance on MySQL 8.0 compared to MySQL 5.7. In many cases, MySQL 8.0 was not able to support the high number of concurrent connections. ProxySQL caches were emptied after every test, and all tests were conducted at least 5 minutes apart.
Using ProxySQL with MySQL 8.0 allowed MySQL 8.0 performance to match MySQL 5.7 in cases where the cache hit ratio was not high due to the nature of oltp_read_only tests having an extremely high number of unique queries. This ProxySQL deployment could not only match MySQL 5.7 performance, but also ensure that the connections are not dropped.
The oltp_point_select test showed that ProxySQL with MySQL 8.0 achieved 4.5x more Queries Per Second (QPS) than MySQL 5.7 and a much lower average latency. This was due to very high cache hit ratio for ProxySQL.
The table below shows an overview of the settings that we used for the databases, Sysbench instances, and ProxySQL instances:
We used the following command for the oltp_read_only test. Threads selected maximised QPS while minimising latency:
We used the following command for the oltp_point_select test. Threads selected maximised QPS while minimising latency, and a higher number of threads was used compared to oltp_read_only because this test is less demanding on the database:
ProxySQL not only comes with query caching abilities, but it has many other native features like ProxySQL Multiplexing, ProxySQL Read Write Split. Not only does it help to increase query performance, but also drastically decrease downtime during maintenance events like minor version upgrades.
Aside from ProxySQL features, here are some tips to help make your ProxySQL deployment more performant:
- If you test ProxySQL and find that ProxySQL daemon CPU Utilisation is >200%, try increasing the threads to match how many threads your instance can support.
- Set ProxySQL cache size to a safe memory amount and tune it according to your workload. You can start with about 50%-75% of the EC2 instance and decrease/increase depending on how much memory other processes consume on your EC2 instance. You are strongly advised to leave some extra memory room on the CPU instance to be safe.
- ProxySQL is best used for workloads with the following use cases:
- No requirement of strong consistency between cache and database
- High number of similar queries
- For maximum availability, but increased cost, you can run multiple ProxySQL instances in an autoscaling group, set to scale out horizontally.
Here are considerations to take note off before running ProxySQL.
- Ensure that you test ProxySQL in a non-production environment with equivalent production workload and simulated failure injections to ensure HA capabilities are set up correctly.
- ProxySQL can only invalidate caches with TTL.
- Prepared Statements are not cached.
- In this solution, NLB Sticky Sessions are used to use only 1 ProxySQL Server for maximum cache hit ratio. The tradeoff is that in the event of a server failure, all the traffic will be routed over to the other server. For most workload, if the instance is right-sized, this will not cause much issue because of ProxySQL multiplexing. Slightly higher latency will be observed initially after failover due to the cold cache becoming populated with query data.
The AWS CloudFormation template to deploy the solution is located at this Github Link.
Take note that the deployment will incur some cost as it uses resources outside of the Free Tier.
When you’re finished, save the benchmark results and delete your resources. If you are using the CloudFormation template provided, you can navigate to CloudFormation in the AWS Console, choose the stack (you will have named it when you deployed it), and choose Delete.
In this post, we presented a solution on how to use ProxySQL as an alternative for the deprecated Query Cache feature in MySQL. We explore how ProxySQL can be employed as a highly available and cost-effective substitute, demonstrating that it can match or even surpass the performance of the original MySQL 5.7 Query Cache.
We hope this post is helpful for anyone looking to optimize their database query performance for less-frequently changed data when updating to MySQL 8.0 with the constraints not being able to modify application code to utilize other intermediate caching solutions like Redis.
Pengfei Zhang - Senior Solutions Architect, AWS
Pengfei Zhang is a Senior Solutions Architect in Singapore Startups, providing architectural consultations to diverse early and scale-up AWS customers. He has recently guided priority customers through successful migrations to MySQL 8.0, specifically addressing and mitigating performance issues related to the absence of the Query Cache feature. He is specialized in Serverless and AIML technology domains.
Pengfei Zhang is a Senior Solutions Architect in Singapore Startups, providing architectural consultations to diverse early and scale-up AWS customers. He has recently guided priority customers through successful migrations to MySQL 8.0, specifically addressing and mitigating performance issues related to the absence of the Query Cache feature. He is specialized in Serverless and AIML technology domains.
Quinn Cheong - Associate Solutions Architect, AWS
Quinn is an Associate Solutions Architect specialising in Databases. He works with APJ customers on Database related subjects such as Query Optimisation and Database Migrations, while helping them fine-tune their Data Strategy on AWS. He works mainly on RDS and Aurora Postgres related challenges.
Quinn is an Associate Solutions Architect specialising in Databases. He works with APJ customers on Database related subjects such as Query Optimisation and Database Migrations, while helping them fine-tune their Data Strategy on AWS. He works mainly on RDS and Aurora Postgres related challenges.
Any opinions in this post are those of the individual author and may not reflect the opinions of AWS.