LinkedIn
Twitter
Application-database inefficiency (e.g. repeated queries, network latency) is a primary cause of performance bottlenecks. Query caching is used to improve database responsiveness and scale. But caching is not without 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 configure automated query caching. The Heimdall Proxy provides the caching and invalidation logic to the storage of your choice (e.g. Redis). Our solution eliminates the need to 1) Develop a cache subsystem, and 2) Manage your cache over the years.
Architecture
The Heimdall Proxy is a transparent, separate process. The proxy can be installed anywhere between the application and database (client, server or a separate proxy tier). For deployment, simply modify the host/port to route through the Heimdall proxy. Install a TCP load balancer like Cloud Load Balancing (CLB) in front to ensure proxy high availability. Heimdall Proxy deployment requires no application changes.
When downloaded from the Google Cloud Marketplace, the Heimdall is deployed as a proxy tier between the application and database as shown in Figure 1.
Figure 1: Database Proxy 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 database instances (for load-balancing and read/write split).
Heimdall proxy’s cache algorithms are based on real-time analysis of a query’s frequency and variability: If a query result set is repetitive and is not constantly changing, we will cache the result set. If desired, you can also manually add or delete particular cache rules from the Heimdall Central Console. To maximize response times, our caching logic prevents any cache miss, eliminating unnecessary round trips and added latency.
How does Heimdall handle cache invalidations? When there is an update to a database table, the proxy will invalidate the associated cache entries and can optionally update the cache entries (e.g. pre-warm). 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.
Installation and Set up
Step 1: The following example shows you how to configure the Heimdall proxy with Redis in a WordPress, MySQL environment.
Download the Heimdall proxy onto a VM instance from the Google Cloud Marketplace. The installation will include both the proxy and Central Console. For more information, visit our technical documentation.
The Heimdall Central Console manages the configuration and provides SQL performance analytics. Access the Central Console with the server URL and port 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 GCP components (e.g. Application, CloudSQL, Redis), and configure features (e.g. Query caching, Read/Write splitting, Connection pooling).
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 other than change URL/host+port to route through the proxy; 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:
● Heimdall Proxy for GCP Overview
● Technical Documentation
● Contact: info@heimdalldata.com
Heimdall Data, a Google Cloud Partner, offers a database proxy that automates query caching. You can now implement caching to the grid-cache of your choice within minutes without any application changes. Save months/years of developing and managing cache subsystems
