Select Page

Heimdall Data, a Microsoft technology partner, offers a database proxy that automates caching for Azure Cache for Redis. Users can now implement SQL caching within minutes without any code changes. Users will save months/years of developing and managing cache subsystems

Caching Challenges

Application-database inefficiency (e.g. repeated queries, network latency) is a primary cause of performance bottlenecks. Many use Redis to improve responsiveness. However, developers are challenged to know what to cache, what to invalidate and to ensure data is up to date. Moreover, caching requires manual code changes to the application.

Solution

This blog will walk you through the deployment models and steps to configure automated caching on Azure for Redis. Please note that Heimdall’s proxy also works with other in-memory data grid vendors. Supported vendors include Redis Labs, Pivotal GemFire Hazelcast, GridGain; commercial or open source versions.

Architecture

Heimdall offers two software packages (Figure 1 below):

  • Database Proxy: Postgres, MySQL, MariaDB, SQL Server, Azure Database, Hyperscale (Citus)
  • JDBC Driver: Provides access to any JDBC-compliant database

Figure 1: Software Package Options

Heimdall is deployed in two ways: 

  • Distributed: Caches SQL results at the application tier, removing latency to the database. Heimdall is installed as an agent (i.e. separate process) or JDBC driver on each application instance.

Figure 2: Distributed Architecture

  • Centralized: Installed as a separate proxy tier

Figure 3: Centralized Proxy Tier

For either deployment option, route your application to the Heimdall host/port or JDBC URL. Heimdall provides two levels of caching: 1) locally on the application instance and 2) on Azure Cache for Redis; this is similar to an L1/L2 cache.

 

How it works

As application talks to the database, the proxy intercepts the queries and determines what SQL results to cache and which queries to server out from cache. Additionally, Heimdall routes queries to different servers (for load-balancing and read/write split) to the backend database. Heimdall’s cache algorithms are based on real-time analysis of:

  • Query frequency and variability
  • Relative response time performance of cache vs. database
  • Inventory of objects in cache to avoid cache misses: Our proxy tracks the entries in the Redis cache and will never ask Redis for data not contain therein, eliminating unnecessary round trips.

For Heimdall’s auto-caching feature, SQL results will only be cached if it provides a performance benefit.

How does Heimdall handle cache invalidations? Heimdall sees all changes to the database. Upon a change, the proxy will invalidate the associated cache entries and optionally update the cache entry. Invalidation is automated and synchronized between Heimdall proxies. There is no more complex TTL or expiry configurations for each query cache.

 

Example Overview

The following covers how to configure Heimdall with Azure Cache for Redis in a WordPress, MySQL environment.

Azure Installation and Set up:

Start the Heimdall proxy VM instance from the Azure Marketplace. The installation will include both the proxy and Central Console. For more information, visit our technical documentation.

On the Heimdall Central Console, our configuration wizard takes you step-by-step to successfully connect the Heimdall proxy to your Azure services (i.e. application, database, cache), and configure features (e.g. Query caching, Read/Write splitting, Load balancing). Once installed, on the Heimdall Data Central, click “Wizard”, and then click “Manual Configuration” shown in Figure 2 below.

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

 

The next screen will select Load Balancing. For now, we will bypass this page:

Step 3. Provide the Redis cache configuration.

Step 4.  Configure settings for the proxy. In most cases, the defaults should be accepted. But these defaults can control whether 1) The management server will spawn a copy of the proxy itself, or rely on external tools to spawn the proxy;  2) How the IP should be bound, and 3) What port the proxy should listen to.

Step 5. The next window provides the use of a proxy and log settings. If a database proxy (i.e. not a Heimdall JDBC driver) is used, 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. For the management server to start the proxy on its own, select the management server proxy option, otherwise install the proxy manually.

Step 6. Summary screen, here all the choices can be reviewed and corrected as needed:

Step 7. The system provides a summary of instructions. Once Submit is selected, the configuration is updated.

Step 8. 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 finalizing the configuration.

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

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

Step 10. 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 finalizing the rule configuration.

Step 11. To connect the application to Heimdall, 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 typically 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.

Summary

The below dashboard provides information on query traffic and server performance for a WordPress application. Notice that the average query time from cache is 60 microseconds compared to 900 microseconds from the database. Caching with Heimdall resulted in a performance boost of 15x times!  Other applications have had a much higher improvement, with more complex queries generating more benefits. 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 is a Microsoft technology partner, designed to transparently improve read/write query performance. Our distributed caching offers a simple, low latency solution for users without disruption to the application or database. Download a free trial.

Resources and links: