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 rather than processing redundant read queries. However, writing to one node and reading from another can 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 any application changes. Customers will save months of development and maintenance at the data access layer.
Solution
In this post, we will cover how to route queries to read and write instances (aka read/write splitting) for strong consistency, and 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 Redis to cache SQL results and 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.
There are a variety of tools in the market that claim read/write splitting, but they are not replication lag aware. Other solutions may track lag but are not intelligent enough to determine when it’s safe for a particular query to be routed. 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.
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.
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. Cases where this may be useful are for reporting users, so they 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. 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 read/write splitting, query caching, and advanced connection pooling to intelligently manage database connections for optimal scale.
Questis, a financial services company, was experiencing database scaling issues. To meet production timelines, modifying their application was not an option. They chose the Heimdall proxy to intelligently offload their SQL traffic, deploying both read/write splitting and query caching. You can read about their customer success story in this APN Blog post.
Resources
