Application-database inefficiency (e.g. repeated queries, network latency) is a primary cause of performance bottlenecks. Query caching is used to improve responsiveness and scale. But caching has its challenges: knowing what to cache and when to invalidate. Moreover, caching requires manual code changes to the application. This blog walks you through the steps to configuring and automating query caching to the grid-cache of your choice (e.g. Azure for Redis, Hazelcast).
Architecture
Heimdall offers two software packages (Figure 1 below):
- Database Proxy: MariaDB, MySQL, Postgres, SQL Server, Azure Database, Hyperscale (Citus)
- JDBC Driver: Provides access to any JDBC-compliant database
Figure 1: Software Package Options
The Heimdall Proxy can be deployed in two ways:
- JDBC driver: Heimdall driver is inserted between your application and current vendor JDBC driver.
- Agent: Proxy is installed as a separate process on the client, server or a separate proxy tier.
For deployment, simply modify the host/port or JDBC URL to route through the Heimdall proxy. No application changes are required. When downloaded from the Heimdall is deployed as a proxy tier between the application and database as shown in Figure 2.
Figure 2: Database Proxy Tier Architecture
How it works
As your application talks to the database, the proxy intercepts the queries and determines what SQL results to cache. Additionally, Heimdall routes queries to different servers (for load-balancing and read/write split) to your backend database. Heimdall’s cache algorithms are based on real-time analysis of:
- Query frequency and variability
- Comparison of response times between cache vs. database
We track the entries in the cache and will never ask for data not contain therein, eliminating unnecessary round trips; there is never a cache miss. Query results will only be cached if it provides a performance benefit.
How does Heimdall handle cache invalidations? When our proxy detects a change to the database, the proxy will invalidate the associated cache entries and optionally update the cache entry. Invalidation is automated and synchronized between Heimdall proxies. If required, you can always manually configure the TTL or expiry for each query cache. However, we have taken that burden away from you.
Azure Installation and Set up
Step 1: The following example shows you how to configure the Heimdall proxy with Azure Cache for Redis in a WordPress, MySQL environment.
Download the Heimdall proxy onto a VM instance from the Azure Marketplace. The installation will include both the proxy and Central Console. For more information, visit our technical documentation.
Step 2: Access the Heimdall Central Console with the server URL and post 8087. On the left panel, click “Wizard”, and then click “Manual Configuration” shown below. Our configuration wizard takes you step-by-step to successfully connect the Heimdall proxy to your Azure components (i.e. application, database, cache), and configure features (e.g. Query caching, Read/Write splitting, Load balancing).
Step 3: Once you have completed the 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.
Summary
The below dashboard provides information on query traffic and server performance for a WordPress application. Notice that the average query time from cache was 50 microseconds compared to1000 microseconds from the database. Query caching resulted in a performance boost of over 20x 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.
We made no changes to the application besides the database URL/host+port change; no database system changes were required.
Conclusion
The Heimdall proxy transparently improves read/write query performance. Our distributed caching offers a simple, low latency solution for users without disruption to the application or database. Customers will not only improve database scale but also save on operational and licensing costs up to 50%. Download a free trial.
Resources and links:
- Azure Marketplace
- Blog: Heimdall Proxy Overview for Azure
- Blog: Using the Heimdall Proxy to Split Reads and Writes for Azure Databases
- Contact: info@heimdalldata.com
Heimdall Data, a Microsoft technology partner, offers a database proxy that helps developers and DBAs improve SQL performance and scale without application changes. Features include caching, read/write splitting, connection pooling, and query routing. Save months/years of development and maintenance.