Offloading SQL for Amazon RDS using the Heimdall Proxy
AWS Architecture BlogOffloading SQL for Amazon RDS using the Heimdall Proxyby Antony Prasad Thevaraj | on13 OCT 2021| inAmazon Aurora,Amazon CloudWatch,Amazon EC2,Amazon ElastiCache,Amazon RDS,Analytics,Architecture,Containers,Developer Tools,DevOps,Serverless|Permalink|ShareGetting the maximum scale from your database often requires fine-tuning the application. This can increase time and incur cost – effort that could be used towards other strategic initiatives. The Heimdall Proxy was designed to intelligently manage SQL connections to help you get the most out of your database.In this blog post, we demonstrate two SQL offload features offered by this proxy:Automated query cachingRead/Write split with Strong ConsistencyBy leveraging the solution shown in Figure 1, you can save on development costs and accelerate the onboarding of applications into production.Figure 1. Heimdall Proxy distributed, auto-scaling architectureWhy query caching?For ecommerce websites with high read calls and infrequent data changes, query caching can drastically improve yourAmazon Relational Database Sevice (RDS)scale. Retrieving data from cache has a shorter access time, which reduces latency and improves I/O operations.It can take developers considerable effort to create and maintain TTLs for cache subsystems. The proxy technology covered in this article has features that allow for automated results caching and invalidation intoAmazon ElastiCachewithoutcode changes. What makes this solution unique is the distributed, scalable architecture. As your traffic grows, scaling is supported by simply adding proxies. Multiple proxies work together as a cohesive unit for caching and invalidation.Why Read/Write splitting?It can be fairly straightforward toconfigurea primary and read replica instance on theAWS Management Console. But it may be challenging for the developer toimplementsuch a scale-out architecture.Some of the issues they might encounter include:Replication lag.A query read-after-write may result in data inconsistency due to replication lag. Many applications require strong consistency.DNS dependencies.Due to the DNS cache, many connections can be routed to a single replica, creating uneven load distribution across replicas.Network latency.When deploying Amazon RDS globally using theAmazon Aurora Global Database, it’s difficult to modify the application to choose the optimal reader.The Heimdall Proxy streamlines the ability to elastically scale out read-heavy database workloads. The Read/Write splitting supports:ACID compliance.Determines the replication lag and know when it is safe to access a database table, ensuring data consistency.Database load balancing.Tracks the status of each DB instance and evenly distributes connections without relying on DNS.Intelligent routing.Chooses the optimal reader to access based on the lowest latency to create local-like response times. Check out ourAurora Global Databaseblog.Customer use case: TornadoHayden Cacace, Director of Engineering at TornadoTornado is a modern web and mobile brokerage that empowers anyone who aspires to become a better investor.Our engineering team was tasked to upgrade our backend such that it could handle a massive surge in traffic. With a 3-month timeline, we decided to use read replicas to reduce the load on the main database instance.First, we migrated fromAmazon RDS for PostgreSQLtoAurora for Postgressince it provided better data replication speed. But we still faced a problem – the amount of time it would take to update server code to use the read replicas would be significant. We wanted the team to stay focused on user-facing enhancements rather than server refactoring.Enter the Heimdall Proxy: We evaluated a handful of options for a database proxy that could automatically do Read/Write splits for us with no code changes, and it became clear that Heimdall was our best option. It had the Read/Write splitting “out of the box” with zero application changes required. And it also came with database query caching built-in (integrated with Amazon ElastiCache), which promised to take additional load off the database.Before the Tornado launch date, our load testing showed the new system handling several times more load than we were able to previously. We were using a primary Aurora Postgres instance and read replicas behind the Heimdall proxy. When the Tornado launch date arrived, the system performed well, with some background jobs averaging around a 50% hit rate on the Heimdall cache. This has really helped reduce the database load and improve the runtime of those jobs.Using this solution, we now have a data architecture with additional room to scale. This allows us to continue to focus on enhancing the product for all our customers.Download a free trial from theAWS Marketplace.ResourcesBlog:Read/Write Split with ACID ComplianceBlog:Automated Query CachingBlog:Advanced Connection PoolingHeimdall Data technical documentationContact: info@heimdalldata.comHeimdall Data, based in the San Francisco Bay Area, is an AWS Advanced Tier ISV partner. They have Amazon Service Ready designations for Amazon RDS andAmazon Redshift. Heimdall Data offers a database proxy that offloads SQL improving database scale. Deployment does not require code changes. For other proxy options, consider the Amazon RDS Proxy, PgBouncer, PgPool-II, or ProxySQL.TAGS:caching,PgBouncer,ProxyAntony Prasad ThevarajAntony Prasad Thevaraj is a Sr. Partner Solutions Architect in Data and Analytics at AWS. He has over 10 years of experience as a Big Data Engineer, and has worked on building complex ETL and ELT pipelines for various business units. He helps partners build joint solutions using AWS services.
Getting the maximum scale from your database often requires fine-tuning the application. This can increase time and incur cost – effort that could be used towards other strategic initiatives. The Heimdall Proxy was designed to intelligently manage SQL connections to help you get the most out of your database. In this blog post, we demonstrate […]
Antony Prasad Thevaraj
Amazon Aurora Amazon CloudWatch Amazon EC2 Amazon ElastiCache Amazon RDS Analytics Architecture Containers Developer Tools DevOps Serverless