Automated SQL Query Caching into Amazon ElastiCache for Amazon RDS and Redshift

 

Automated query caching can significantly improve the performance of your database by reducing the time required to retrieve frequently accessed data. While Amazon ElastiCache is primarily designed as an in-memory data store for caching, it is not directly integrated with Amazon RDS, Amazon Aurora, or Amazon Redshift for automated query caching. However, following specific approaches, you can still implement query caching using AWS ElastiCache alongside these database services.

 

1. Manual Caching with Amazon ElastiCache:

 

You can manually implement query caching by using ElastiCache as a separate caching layer in front of your database services. Here’s a general approach:

• Set up an ElastiCache cluster (using Redis or Memcached) to act as a cache.
• Modify your application code to check the query results’ cache before querying the database.
• If the data is found in the cache, retrieve it directly from ElastiCache and avoid the database query.
• If the data is not found in the cache, execute the query against the database, retrieve the results, and store them in the cache for future use.
• With this approach, you control which queries to cache and how to manage the cache. However, it requires manual implementation and maintenance.

Challenges with Manual Caching with Amazon ElastiCache

 

Many developers have implemented database caching to improve responsiveness. However, their biggest challenge is knowing what to cache, what to invalidate, and how to ensure that data is up-to-date. A race car’s performance is highly dependent on the driver’s skills. Similarly, leveraging Amazon ElastiCache’s performance and scalability requires the developer to know how to use the cache best. This requires manual application code changes, which is impossible for a third-party packaged application (for example, WordPress).

Therefore, you can choose to automate caching and invalidation safely and optimally into Amazon Elasticache (an AWS cache service) without any application changes. The solution is:

 

2. Query Caching Middleware:

 

Another option is to leverage query caching middleware or libraries that provide automated caching capabilities. For example:

• ElastiChache for Amazon RDS and Amazon Aurora: You can use middleware like Heimdall Data , which supports query caching.
• Elasticache for Amazon Redshift: You can explore libraries like Heimdall Data, which offers query result caching.

Middleware solutions sit between your application and the database, intercepting queries and automatically caching the results based on specified rules or configurations. They can simplify the caching implementation and management process.

 

Heimdall Data Technology

 

How does Heimdal Data Technology Make Your Automated Sql Query Caching into Amazon Elasticache Easier and Cost Efficient?

Heimdall Data is an AWS Advanced Technology partner that 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.

 

How does Heimdall AWS Advanced Technology work?

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 configuring automated query caching with the Heimdall proxy in AWS.

 

Database Proxy Tier Architecture

 

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 1.

 

 

Figure 1: Database Proxy Tier Architecture

 

 

How Caching Works:

 

The Heimdall query caching logic supports many cache stores:

The user chooses the data grid (e.g., Amazon ElastiCache for Redis), and the proxy will manage the storage. ElastiCache is used for a SQL look-aside results cache. The proxy takes the query (key) and serves the SQL results (value). This Elasticache benefits applications with many duplicate, repetitive queries. Through the central console, direct the proxy to the desired cache stores. With “one-click,” Heimdall automates caching and invalidation of SQL results.

Based on learning algorithms, queries are only cached if it provides a performance improvement. Caching is automated. However, our rules engine allows users to add or remove particular cache policies or modify the expiry or TTL, giving complete configuration flexibility. For more information, go to our caching architecture technical documentation.

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

 

Getting Started Amazon Elasticache:

 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 the WordPress application’s query traffic and server performance. Notice: average query time from the 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.

 

 

 

Amazon ElastiCache Features and Uses

 

ElastiCache supports two popular open-source in-memory caching engines: Redis and Memcached. These engines are widely used to store and retrieve data in-memory, resulting in significantly lower latency compared to traditional disk-based databases.

• Caching: ElastiCache helps reduce the load on backend databases by caching frequently accessed data. This reduces the response time of applications and improves overall performance.
• Session Storage: It can be used to store session data for web applications. You can achieve fast and scalable session management by storing session data in memory.
• Real-time Analytics: ElastiCache can be used as a high-performance data store for real-time analytics applications. It allows you to process and analyze large datasets in memory, enabling faster insights.
• Pub/Sub Messaging: With Redis, ElastiCache provides support for pub/sub messaging patterns. It allows you to build real-time communication and event-driven architectures.
• Gaming Leaderboards: ElastiCache can be used to implement real-time leaderboards for gaming applications. It enables fast updates and high concurrency for tracking scores and rankings.

 

Amazon ElastiCache Pricing

 

Amazon ElastiCache offers pricing models based on the caching engine (Redis or Memcached) and the chosen instance types. The pricing includes instance size, node type, and data transfer. It’s recommended to refer to the official AWS website or AWS Pricing Calculator for up-to-date and detailed pricing information.

 

 

Conclusion

Building a cache system is complex and resource exhaustive. The Heimdall Proxy removes this operational 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.

 

Frequently Asked Questions:

 

Question: What is the latency introduced by the Heimdall Database Proxy.

Answer: The latency is negligible compared to the improved response time and database scale you get with query caching

Question: Are there additional costs associated with your query caching implementation?

Answer: An EC2 compute instance is required to host the Heimdall proxy software. The Amazon ElastiCache instance is also purchased and deployed separately in your environment. But don’t worry; Amazon Elasticache’s cost is negligible.

Question: Do you support other Amazon DynamoDB or Amazon ElastiCache for Memcached?

Answer: Our result caching solution is only for SQL databases. Hence, NoSQL solutions such as DynamoDB, Cassandra, or MongoDB are not supported. Amazon ElastiCache for Memcache is not supported. But using open-source or commercial Redis, Hazelcast, or GemFire is supported.

 

Resources

 

• • Blog post: Automated Query Splitting with ACID Compliance
  • Blog post: Advanced Connection Pooling
  • Blog post: Heimdall Data Customer Success Story

• • Technical documentation

• • Contact: info@heimdalldata.com