Automating SQL Caching for Amazon ElastiCache and RDS

Heimdall Data, an AWS partner that offers a “DBA assistant” tool in the AWS Marketplace. This tool transparently auto-caches into Amazon ElastiCache for Redis with any relational database. This solution automates the management of a Redis query cache using ElastiCache and Amazon RDS.

Background
Many have used Amazon ElastiCache to improve responsiveness. However, developers are still challenged to know what to cache, what to invalidate and ensure data is up to date. This requires manual application code changes. We can now automate caching and invalidation for Amazon ElastiCache with Heimdall Data.

Architecture
Heimdall Data is an intelligent query routing and caching data access layer that is installed in one or more EC2 instances. Heimdall Data software packaging includes:For deployment, one only needs to modify the host/port or JDBC URL to route the application through Heimdall.

Figure 1: Heimdall Data Distributed Architecture

In proxy mode, there are two types of deployment:

· Distributed mode: A proxy residing on each EC2 application instance, for optimal performance (Figure 1 above)

· Centralized mode: One EC2 database proxy servicing many application servers (Figure 2 below)

Figure 2: Heimdall Data Centralized Architecture

The proxy provides two levels caching: 1) locally on the application server and 2) on ElastiCache (akin to an L1/L2 cache). As SQL is sent from the application to the database, the proxy responds from the cache, and routes requests to different servers (for use in load-balancing and read/write split). All this functionality requires zero code changes.

Pre-Requisites
This article covers how the Heimdall system with Amazon ElastiCache and RDS can be deployed with an existing application. Users testing with their own infrastructure require:

·  Amazon RDS

·  Amazon ElastiCache for Redis

·  Heimdall Data instance available at the Heimdall Data website or AWS Marketplace

Script Installation of the Heimdall System
For users installing Heimdall via the one-line installation process, it will download and install 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”.

Example Overview
This blog uses an Amazon Aurora with a WordPress application.

Getting Started
Once the Heimdall Central Console 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 wizard designed for Amazon. Select AWS Detect, otherwise perform Manual Configuration. The goal is to connect the database system and caching infrastructure.

If you see a screen requesting AWS IAM credentials, credentials can be added in this window or the IAM credential added to the instance as an IAM instance role through the AWS interface. Then, select AWS Detect again.

Step 1. Amazon RDS cluster and ElastiCache Redis will be automatically populated once detected. Select the appropriate RDS cluster and ElastiCache cluster from the drop-down list. If you do not have an ElastiCache for Redis instance available, then this can be left blank, and later, the “local cache” option selected. 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 window provides settings on logging and the use of a proxy. If the database is to be 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 it’s 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. On the demo system, guest users will receive a warning indicating they cannot update the server configuration. Browsing the configuration tabs provides info on how the system works.

Step 7. The Virtual Databases tab provides connection info for the application. In this case, the application will access 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 VDB tab as well:

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 rules configured is to cache all traffic not in transactions, forward selected traffic to a read-only source, and 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. This change is usually straightforward, but is specific to your application. 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.
Performance boost of over 20 times!
90% cache hit rate: The load on the database is reduced significantly 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 coding or database system changes were required.

Conclusion
Heimdall Data automates caching for Amazon Elasticache. Configuration is simple requiring zero disruption to the application or Amazon RDS. Users will experience up to 5x improvement in performance and scale. Heimdall is available as a free trial on the Amazon Marketplace or downloaded at the Heimdall Data website.

Share via:

Note: Please test scripts in Non Prod before trying in Production.