Automated Query Caching into Amazon ElastiCache for Amazon RDS, Aurora and Redshift

This is a guest blog post by Roland Lee with Heimdall Data, an AWS Advanced Technology partner. With Data and Analytics Competency, Heimdall Data offers a database proxy on the AWS Marketplace and supports SQL query caching into Amazon ElastiCache for Redis without code changes. Customers will 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 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 utilize the cache. This requires manual application code changes, which is not possible for a third party packaged application (e.g. 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, saving you months of development and management time each year.

Architecture

Heimdall Data software packaging includes:

  • Database Proxy for Amazon RDS: Aurora, Redshift; MySQL, SQL Server 2008+, PostgreSQL
  • JDBC driver: Gives access to Oracle and any other JDBC-compliant database

Figure 1. Heimdall Software Options

For deployment, the only application-level change is to modify the host/port or JDBC URL to route through the Heimdall.

In proxy mode, there are two types of deployment:

  1. Distributed mode: Proxy is installed on every application EC2 instance, for optimal performance (Figure 2)
  2. Centralized mode: Separate EC2 database proxy tier (Figure 3)

Figure 2: Distributed Architecture

Figure 3: Centralized Architecture

The Heimdall proxy provides two levels caching: 1) locally on the application instance and 2) on ElastiCache (akin to an L1/L2 cache). The user has the option to utilize one or both cache store(s). On the Heimdall Central Manager, direct the proxy to the designated cache store and the proxy will automatically cache and invalidate SQL results. You determine storage, and Heimdall provides the caching logic. See Figure 4 below.

Figure 4: Heimdall Two-tier Caching Architecture

Pre-Requisites

This blog covers how Heimdall with Amazon ElastiCache and Aurora for MySQL is deployed with a WordPress application.

Script installation of the Heimdall system

For users installing Heimdall via the one-line installation process, it will download and install both the Heimdall Central Manager and proxy. The default user ID is “admin”. If an Amazon instance is successfully detected, the password will be the instance ID. Otherwise, the default password is “heimdall”.

Getting Started

Once the Heimdall Central Manager is running, access with the server URL and port 8087. For an already configured server, the default tab is the Status tab which displays current server and system status. For a new installation, users are directed to the Wizard tab.

Using the Heimdall-for-AWS Wizard

Heimdall has a configuration wizard designed for AWS. Select AWS Detect, otherwise perform Manual Configuration. The goal is to connect the database system and caching infrastructure.

Add AWS IAM credentials in below window or as an IAM instance role through the AWS interface. Then, select AWS Detect again.

Step 1. Heimdall automatically detects the Amazon Aurora cluster and ElastiCache Redis. Select the appropriate Aurora cluster and ElastiCache cluster from the drop-down list. If you do not have an ElastiCache for Redis instance, leave this field blank, and in the later configuration window, select the “local cache” option. Once the information has been selected, click Next.

Step 2. Specify the database server and connection type. This includes the host name, driver, user name, password, and port:

Step 3. Provide the cache configuration. Amazon Elasticache for Redis is automatically detected. You may use the other cache options. If you have no cache infrastructure, testing can be done using the local cache, but note that invalidation information will not be shared across multiple application nodes:

Step 4. The next two screens provide settings on the use of a proxy and logging. If the database is used as a proxy (e.g. MySQL, PostgreSQL), then Enable Proxy should be checked and a proxy port chosen. The localhost option should also be unselected if using a proxy on another instance other than the application using the proxy is hosted on. For the management server to start the proxy on its own, select the management server proxy option, otherwise install the proxy manually.

Step 5. Summary screen

Step 6. After clicking next, the system provides a summary of instructions. Once Submit is selected, the configuration is updated.

Step 7. The Virtual Databases tab provides connection info for the application. The application accesses via the MySQL proxy on localhost. If using your own instance, make any changes to this information and click Commit to finalize the configuration.

Cache settings can also be changed on the Virtual Database (VDB):

Step 8. The Data Sources tab provides the database connection settings such as connection pooling, load balancing, automated failover, and query routing (read/write split). If using your own instance, make any changes to this information and click Commit to finalize the database configuration.

Step 9. 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. Users can dynamically change rules without restarting the application or database. Information on how the rules are configured are available by clicking on the Help button. If using your own instance, make any changes to this information and click Commit to finalize the rule configuration.

Step 10. To connect the application to the Heimdall Database Proxy, just change the database configuration to match the Heimdall database proxy. The existing MySQL configuration in WordPress was changed to 127.0.0.1:3306. Details on the URL to use for the Heimdall JDBC Driver are in the JDBC section of the Virtual Database; or for the proxy, in the Proxy Configuration section.

Step 11. The dashboard provides information on query traffic and server performance for a WordPress application. Notice: Average query time from cache is 50 microseconds compared to 1000 microseconds from the database. Caching with Heimdall 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.

There were no changes to the application besides the database URL/host+port change; no database system changes were required.

Conclusion

Heimdall Data automates caching for Amazon Elasticache. The configuration is simple, requiring zero disruption to the application or database. Users have seen cache hit rates up to 90% and 20x improvement in response times. Heimdall is available as a free trial on the AWS Marketplace or downloaded at the Heimdall Data website.

Resources

Heimdall Installation videos

Blog: How to use Heimdall Data to Split Reads and Writes for Amazon Aurora and RDS

Contact: info@heimdalldata.com

 

This post was originally published on October 6, 2017 and has been updated as of July 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.