AWS Database Blog

Automated query caching into Amazon ElastiCache for Amazon RDS, Amazon Aurora and Amazon Redshift

This is a guest blog post by Erik Brandsberg, CTO at Heimdall Data, in partnership with AWS Technical Evangelist Darin Briskman. Heimdall Data is an AWS Advanced Technology partner who in their own words “offers a database proxy on the AWS Marketplace that supports SQL query caching into Amazon ElastiCache for Redis without code changes. Customers improve application response times and database scale for Amazon Aurora, Amazon RDS, or Amazon Redshift.”

Background

Many have implemented database caching to improve responsiveness. However, developers are still challenged to know what to cache, what to invalidate and how to ensure that data is up-to-date. For a race car, performance is highly dependent on the skills of the driver. Similarly, leveraging Amazon ElastiCache’s performance and scalability requires the developer to know how to best use the cache. This requires manual application code changes, which is not possible for a third-party packaged application (for example, WordPress). You can now automate caching and invalidation safely and optimally into Amazon ElastiCache without any application changes.

Heimdall Data is a database proxy that provides the caching and invalidation logic into the cache of your choice. How is Heimdall unique? Caching is automated without any complex configuration and performed closer to the application, removing database interaction. This results in reduced network latency and additional AWS cost savings.

This blog post walks you through the steps of configuring automated query caching with the Heimdall proxy in AWS.

Architecture

Heimdall Data software packaging includes a control and data plane:

  • Control plane: Heimdall Central Console
  • Data plane (two options):
    • Proxy: Amazon RDS, Amazon Aurora, Amazon Redshift; MySQL, SQL Server 2008+, PostgreSQL
    • JDBC driver: Supports any JDBC-compliant data source (for example, Oracle, Amazon Athena)

Figure 1. Heimdall Software Options

For deployment, simply modify the host/port or JDBC URL to route through the Heimdall proxy. No application changes are required. From the AWS Marketplace, the Heimdall is deployed as a proxy tier between the application and database as shown in Figure 2.

How Caching Works:

The Heimdall query caching logic supports many cache stores: the user chooses either a local heap, data grid (for example, Amazon ElastiCache), or combination of both. Through the central console, direct the proxy to the desired cache stores. With “one-click”, Heimdall automates caching and invalidation of SQL results.

The proxy uses real-time analytics and statistics to determine which queries to cache. Based on learning algorithms, queries are only cached if it provides a performance improvement. Caching is automated, however, our rules engine also allows the user to add or remove particular query cache policies giving full flexibility. For more information, go to our caching architecture technical documentation.

Automated cache invalidation occurs when the proxy detects a DML (for example, INSERT, UPDATE, DELETE) or copy operation. Tables are invalidated before issuing the DML and remain invalidated until the transaction is completed. During this invalidation window, the proxy does not serve old content or put new objects into cache. For more information, go to our cache invalidation technical documentation.

Getting Started:

Now, we walk through configuring the proxy with Amazon ElastiCache and Aurora for MySQL, for a WordPress application.

Step 1: Download the Heimdall software from the AWS Marketplace. The installation includes both the proxy and central console. For more information, visit our Heimdall for AWS technical documentation.

Step 2: Access the central console with the server URL and port 8087. Our AWS Configuration Wizard takes you step-by-step through connecting the Heimdall proxy to your AWS services (for example, application, database, cache), and configure caching.

Step 3: Once you have completed the AWS Configuration Wizard, review the configuration starting with the Virtual Databases tab, which provides connection information for the application. See below for a screenshot preview.

Step 4: Confirm the database connection settings in the Data Sources tab, which includes connection pooling, load balancing, automated failover, and read/write splitting. See below for another screenshot preview.

Step 5: The Rules tab controls how queries are cached, routed, and transformed. The default configuration is to 1) Cache traffic NOT in transactions, 2) Forward selected traffic to a read-only source, and 3) Log query traffic. You can create custom rules without restarting the application or database. Make any rule configuration changes and click Commit to finalize.

Step 6: Connect the application to the proxy by changing the database configuration to match the proxy’s host and port, as configured in the Virtual DB tab.

Caching Performance Results

 The Dashboard tab provides information on query traffic and server performance for the WordPress application. Notice: average query time from cache is 50 microseconds compared to 1000 microseconds from the database. Caching with Heimdall proxy resulted in a performance boost of over 20 times! With a 90% cache hit rate, the database load was significantly reduced, allowing for more users to be supported on the same database infrastructure.

Conclusion

Building a cache system is complex and resource exhaustive. Heimdall Data removes this operaltional burden by automating the caching of SQL results without any application changes. Users have seen cache hit rates up to 90% and 20x improvement in response times, while saving months of software development and management every year. Download a free trial on the AWS Marketplace.

Resources

 

 

This post was originally published on October 6, 2017 and has been updated as of October 15, 2019.

 

 

About the Author

 

Darin Briskman is a technical evangelist at Amazon Web Services. He works with our customers to provide guidance and technical assistance on database projects, helping them improving the value of their solutions when using AWS.