AWS Partner Network (APN) Blog

Improving Application Performance with No Code Changes Using Heimdall’s Database Proxy for Amazon Redshift

By Sudhir Gupta, Sr. Solutions Architect at AWS
By Erik Brandsberg, CTO at Heimdall Data

Heimdall Data-Logo 2.1
Heimdall Data-APN Badge-1
Connect with Heimdall Data-1
Rate Heimdall Data-1

Concurrent singleton DML operations, network latency, and slow repetitive queries are often the cause of slow performance of data warehousing applications.

Whether there’s network latency or data latency, operators are looking for solutions that provide fast response time to end-users.

Heimdall Data is an AWS Partner Network (APN) Advanced Technology Partner with the AWS Data & Analytics Competency that offers a database proxy to transparently improve Amazon Redshift response time.

If you want to be successful in today’s complex IT environment, and remain that way tomorrow and into the future, teaming up with an AWS Competency Partner like Heimdall Data is The Next Smart.

The Heimdall database proxy improves performance of application by two techniques:

  • Batch processing
  • Automated client-side caching

In this post, we’ll demonstrate how to use Heimdall Data to improve the performance of an application that connects to Amazon Redshift, without changing the application code.

Batch Processing

Some applications produce a large number of singleton INSERT statements and execute concurrently to load the data into Amazon Redshift.

Too many concurrent singleton INSERT statements , however, often causes undesirable amounts of commit overhead to Redshift.

Heimdall Data-Amazon Redshift-1

Figure 1 – Heimdall Data high-level diagram.

The Heimdall database proxy improves database write performance by transparently batching single-row data ingestion.

Batch processing results in:

  • Improved application response time due to fewer commits.
  • Improved INSERT throughput performance.

Heimdall Data-Amazon Redshift-2

Figure 2 – Heimdall Data batch processing flow diagram.

The following steps explain how batch processing works, as shown in Figure 2:

  1. DML operations are queued up in the Heimdall database proxy.
  2. Queue is sent to a user-configured batched size (refer to Figure 3 below).
  3. Batch is sent as a single transaction to Redshift.

If there’s a failure during the batching process, Heimdall detects the faulty SQL statement and restarts the batch excluding that statement. That is, exceptions are logged, removed from batch, and the transaction is restarted.

Heimdall Data-Amazon Redshift-3

Figure 3 – Batch processing configuration.

You can see in Figure 3 that the Heimdall Data Central Console’s Rules Tab prompts the user to input:

  • Regex rule.
  • SpoofedResult: Integer value of how many rows were changed when spoofing.
  • HoldUntil: Inserts a condition to hold the thread.
  • Action: Batch processing is under the “Async Execute” feature.
  • Frequency of batching (in seconds).
  • Batch size (number of queries).

Note that Heimdall batch processing is not so ideal when concurrent writes and reads happen against the same table, on the same thread.

Automated Client-Side Caching

Business intelligence (BI), dashboard, and visualization tools often execute repetitive queries. These tools will notice a huge performance boost with automated client-side caching feature of Heimdall Data.

Heimdall Data-Amazon Redshift-4

Figure 4 – SQL client-side caching.

Amazon Redshift provides query result caching, but Heimdall extends the caching capability one step further. Heimdall complement Redshift’s cache as a “cache in front of cache” with the following benefits:

  • Removes network latency by caching data at the client side. The Heimdall database proxy can be distributed and deployed across multiple applications, running on Amazon Elastic Compute Cloud (Amazon EC2) instances. When Heimdall caching is configured in front of Tableau dashboards, for example, customers have seen that response time reduced from 17 seconds to one second.
    .
  • Automating caching and invalidation. Distributed architecture of Heimdall Data allows caching to be scalable, while acting as one cache cluster. Caching can be completely automated and configurable per query.

For more information on how to configure automated SQL caching with Heimdall, read this post on the AWS Database Blog: Heimdall Data SQL Caching for Amazon ElastiCache.

Summary

Rewriting an application code for performance optimization generally requires a significant amount of effort. Also, IT and development groups using third-party applications like Tableau may not have access to the application code.

Heimdall’s database proxy solution offers a flexible and cost-effective alternative to rewriting your application for performance and scale.

Heimdall transparently provides SQL control and visibility to the application owner without (re)writing a single line of code. Heimdall database proxy provides various techniques, based on the application traffic pattern, to improve the response time of application connecting to Amazon Redshift.

To get started with Heimdall Data, download a free trial from AWS Marketplace.

Resources and links:

.


Heimdall Data-Logo 2.1
Connect with Heimdall Data-1

Heimdall Data – APN Partner Spotlight

Heimdall Data is an AWS Competency Partner. It is a SQL database proxy for Amazon Redshift, Amazon RDS, Amazon Aurora, and Amazon ElastiCache. Heimdall is transparently deployed to improve your read and write queries. No code changes are required.

Contact Heimdall Data | Solution Overview | AWS Marketplace

*Already worked with Heimdall Data? Rate this Partner

*To review an APN Partner, you must be an AWS customer that has worked with them directly on a project.