Background
Horizontally scaling out your SQL database (e.g. Postgres, MySQL, SQL Server) involves separating the write-master from read-only servers. This allows the write server to perform more intensive operations instead of processing read queries as well. However, writing to one node and reading from another can result in inconsistent data due to synchronization delays.
Customer challenge
Application-database response times are important for web applications as users have little patience with slow performing applications. Finding a transparent solution to improve performance while providing up-to-date, accurate information to customers is highly desired. For Azure managed databases, there is a requirement that usernames are in the format of “user@db hostname”. This can cause problems, in particular with Postgres, as @ is not considered a valid character for usernames, and client software may filter or reject the username. Additionally, the same user may be used for both read and write servers when doing manual read/write split, resulting in the improper routing of queries.
Solution
The Heimdall Proxy is a transparent solution that intelligently routes queries to the most optimal data source resulting in SQL offload and improved response times. This includes 1) Routing queries to the read/write instance while keeping data updated, and 2) Automatically caching SQL results.
In this blog, we will show you how to configure the proxy in Azure to transparently scale-out your write master and read replicas while maintain data consistency (aka ACID compliance). Let’s walk you through each feature.
Read/Write Query Separation (aka Read/Write split)
Figure 1: Heimdall Proxy Read/Write Split Architecture Diagram
The Heimdall proxy routes queries to the appropriate write server and read replicas. The proxy determines whether to retrieve the SQL results from cache or the backend database. If the read query is not deemed cachable, the proxy knows which database instance should be accessed, query-by-query, based on what tables were being read.
There are a variety of tools in the market that claim read/write splitting, but they are not replication lag aware. That is, how do you know when the WRITE has been updated across all database instances? Could you be reading stale data? Other solutions may track replication lag but are not intelligent enough to determine WHEN it is safe for a particular query to be routed to the read replica. We are the only proxy solution that supports ACID compliance.
Note: The proxy does not support backend data replication; that is the job of the database. The Heimdall proxy with Azure Cache for Redis, detects when data has completed replication and routes queries to the appropriate database instance.
Each query is parsed to determine what tables are associated with it. The last write time to a table is tracked by the proxy. Next, the replication lag is determined by calculating how long it takes for an update to eventually appear on the read server. With this information, the proxy decides which node is “safe” to read from–either the read nodes or only the write node.
Installation and Configuration in Azure:
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.
In Azure, the Heimdall proxy has an option to enable username pre-processing, to adjust the username based on the desired target server. This option is the “azureDbHost” setting in the connection settings of the data source. When enabled, the application uses a simple username as normal, but when the connection is made to the server, the server hostname is extracted and appended as @hostname. This allows Azure managed databases to properly function with query routing, even if the application is not aware of this behavior.
On the Heimdall Central Console, our configuration Wizard takes you step-by-step to connect the Heimdall proxy to your Azure services (i.e. application, database, cache), and configure features (e.g. Read/Write splits, Caching, Load balancing). Once installed, on the Heimdall Data Central, click “Wizard”, and then click “Manual Configuration” shown in Figure 2 below.
Figure 2. Configuration Wizard
Configuring Read/Write splitting on the Heimdall Central Console:
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:
- Ensure that a read/write master is configured along with at least one read-only server:
Figure 3. Read/Write Splits Configuration in “Data Sources” tab
In Figure 3, since the replication lag was set to be 1000ms, and a table was last written two seconds (2000ms) ago would be deemed safe to use the read replica. If however, the last write was 500ms, the proxy would route the query to the write master node to complete the read operation, guaranteeing that a stale response was not returned. The value of the detected replication lag is found on the status screen:
Figure 4. “Status” tab
For further protection, the Heimdall proxy allows for a configurable fixed lag window value. This allows the replication lag window to spike in the short-term without impacting the freshness of the data being returned. Further control over the read/write split is applied at a rule level based on matching regular expressions, including bypassing the replication lag logic for particular queries to always use the read server.
- Configure the settings (if needed) to enable the azureDbHost setting to “true”, and adjust connection retry and timeout settings, as well as SSL configuration as needed:
Figure 5. “Data Sources” tab
4. In the Rules tab, a Reader Eligible rule should be configured for read queries to be routed to the read server shown in Figure 6.
Figure 6. “Rules” tab
In cases where the replication lag is not a concern, the user can create a read/write split rule for particular queries that should unconditionally be read from the read/write server. This may be a result of a particular user connecting to the database as well, i.e. to prevent a reporting job from putting load on the write master.
5. Real-time analytic charts on the Analytics tab shows read and write queries now splitting:
Figure 7. “Dashboard” tab
6. Specific query source is found in the Log tab:
Figure 8. “Log” tab
Query Caching: The second-way the Heimdall Proxy is used for improving database scale is to offload SQL traffic by caching. In this scenario, Azure for Redis acts as a look-aside, SQL results cache. Heimdall intelligently determines which queries to cache and automatically invalidates when the proxy detects an update to the database. Unique to other caching solutions, Heimdall can cache SQL results in the application tier removing unwanted network latency between the application and database.
Figure 9: Heimdall Distributed Auto-caching Architecture
The best part of this joint caching solution is its transparency, not requiring any code changes. Caching and invalidation are automated. For more information on how to configure the proxy for auto-caching, check our automated caching blog.
Customer Benefit
The Heimdall proxy provides the SQL routing logic, and Azure Redis provides the storage and communication bus to ensure your backend is scalable. By improving scale through read/write spits and SQL caching, queries will be traffic routed for maximum performance. Further, as Heimdall provides a bridge between a normal username and the hostname variation expected by Azure managed databases, it allows your application to run unmodified with Azure databases, and gain the benefit of read/write split.
Resources and links:
- Download in the Azure Marketplace
- Blog: Automated Query Caching on Azure Managed Databases
- Heimdall Data for Azure Overview
- Contact: info@heimdalldata.com
Heimdall Data, a Microsoft Technology partner, offers a database proxy to improve the write-master / read replica scale in the Azure backend without application changes.
