Background
Horizontally scaling your SQL database involves separating the write-master from read-only servers. This allows the write server to perform dedicated write operations while read queries are offloaded to separate replicas. However, this architecture may result in inconsistent data due to synchronization delays. The Heimdall Proxy helps developers, database administrators, and architects achieve optimal scale for their existing database environment without application changes. Customers will save months of development and maintenance at the data access layer.
Solution
In this blog, we will review how to 1) Route queries to read and write instances (aka Read/Write splitting) for strong consistency and 2) Automate query caching.
One of the challenges of splitting queries is data synchronization. There’s a time lag between when data was written and when the read replicas are updated.
The Heimdall proxy is a transparent data access layer that intelligently routes queries to the most optimal data source, resulting in SQL offload and improved response times. The Heimdall proxy leverages a grid cache (e.g. Redis) as a SQL results cache to track SQL queries so they are routed to the appropriate database node for fresh data. We provide the routing and caching logic, and Redis provides the storage medium. This joint solution is particularly useful for Postgres, MySQL, and SQL Server users.
Figure 1 – Heimdall Proxy Architecture
There are a variety of tools in the market that claim Read/Write splitting, but they are not replication lag aware. Hence, they do not truly support strong consistency.
Heimdall proxy tracks the last time a table was written. Next, Heimdall calculates how long it takes for a write to be updated on the read instance. With both pieces of information, the proxy intelligently decides which node is “safe” to read from, either the read nodes or the read/write node.
Configuration and Setup
Once downloaded, it will include both the Proxy and Central Console. For more installation instructions, see our technical documentation.
Figure 2 – Configuration wizard
On the Central Console, the Configuration wizard takes you step-by-step to successfully connect the Heimdall proxy to your application, database, cache, and configure features (read/write splits, caching, load balancing).
Read/Write Split Configuration
After completing the wizard, the configurations should be pre-populated. The Data Sources tab should appear like below, with one read/write instance configured along with at least one read-only instance.
Figure 3 – Data Sources tab on the central console
In Figure 3, since the replication lag window was set to be 1000ms. This value is added to the detected replication lag (2ms) from the cluster, as you can see in Figure 4 below, in order to determine the total effective replication lag, or 1002ms in this case.
If the value of these two combined is higher than the time since a table was last written to, then the primary write node will be used for reads of that table. Otherwise, it will be deemed safe to read from the read-only replica.
Finally, in order to select what queries should be eligible for reading from a read replica, ensure a reader-eligible rule is configured in the Rules tab, as shown in Figure 5.
In cases where the replication lag is not a concern, users can create a Read/Write split rule for particular queries that should unconditionally be read from the read replica using the “lagIgnore” parameter. In scenarios where this may be useful are for reporting users; users can generate reports without impacting the primary write node.
Real-Time Analytics Charts
On the Analytics tab of the central console, the dashboard below shows that read/write splitting is now occurring, re-routing read traffic from write master to the read replicas.
The majority of traffic is now routed to the read replicas. It works!
Additional validation that read/write splitting is working can be seen in the log tab in Figure 7, where the actual source of each query can be found. In this case, the reads are directed to the node “Magento-Demo-source-Reader,” while the update is performed on “Magento-Demo-source-Master.”
Automated Query Caching
The Heimdall proxy provides the query caching logic to improve database scale by offloading SQL traffic. In this scenario, your cache serves as a look-aside, SQL results cache. The proxy intelligently determines which queries to cache and automatically invalidates when there’s an update to the database. Alternatively, users can include or exclude which queries to cache by simply creating policies on the Heimdall Console. For more information, check out our query cache website.
The best part of this proxy caching solution is its transparency, not requiring any code changes. Caching and invalidation are automated. For more information on query caching, check out our blog.
Customer Benefits
To take full advantage of the performance and scalability of your database, application owners must properly interface with these databases. This often requires code changes. The Heimdall database proxy supports optimal SQL offload to maximum Cloud SQL scale. Download a free trial on the GCP Marketplace.
Resources
- Blog: Advanced Connection Pooling
- Blog: Automated query caching
- Technical documentation
- Heimdall Data website
Heimdall Data, a Google Cloud Partner, offers a Cloud SQL proxy that intelligently manages your SQL connections without application changes. Save months/years of developing and maintenance.
