Using the Heimdall Proxy to Split Reads and Writes for MariaDB

To scale the database tier efficiently, database clusters are often separated into a writeable primary and read replicas. This requires the application to route queries to the appropriate database instance. However, you need to be mindful of technical requirements: 

• To implement read/write split requires expensive application code changes. This can take months of development resources when you have better things to do with your time. 
• Writing to one node and reading from another can result in inconsistent data due to replication lag. 

 

Solution 

The Heimdall Proxy 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 Heimdall Proxy to transparently scale-out your write-master and read replicas for improved application response times. Let’s walk you through each feature. 

  

How Read/Write Split Works

Figure 1: Read/Write Architecture

 

The Heimdall proxy routes queries to the appropriate write-master and read replica while accounting for replication lag and transactional state. The proxy determines whether to retrieve the SQL results from cache or the backend database. If read query is not deemed cachable, Heimdall knows which read node should be accessed, query-by-query, based on what tables were being read. 

To ensure data retrieved from the replicas is fresh, our proxy calculates the replication lag. There are a variety of approaches to 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 lag but are not intelligent enough to determine WHEN it is safe for a particular query to be routed to the read-replica. They are not designed to fully offload the developer from implementing read/write split. 

 

Also, you may have read/write spit implemented today. But our proxy makes better utilization of your replicas by evenly distributing the load so that particular instances are not overwhelmed.

 

In Azure, Heimdall 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. 

 

Note: Heimdall does not support data replication; that is the job of the database. Our proxy detects when data has completed replication and routes queries to the appropriate database instance. 

 

 

Replication Lag Detection 

 

Each query is parsed to determine what tables are associated with it.  The last write time to the table is tracked and is synchronized across proxies.  This provides the first piece of information (i.e. when was the last time a particular table was written to).  Next, we calculate how long it takes for the writes to appear on the read instance.  With this information, the proxy intelligent decides on which node is “safe” to read from–either the read nodes, or only the write node. Our proxy ensures ACID compliance when eventual consistency is not acceptable.

 

 

Azure Installation and Configuration: 

 

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. 

 

On the Heimdall Central Console, our configuration wizard takes you step-by-step to successfully connect the Heimdall proxy to your Azure services (i.e. application, database, cache), and configure features (e.g. Read/Write split, 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 on 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: 

 

1. Ensure that a read/write master is configured along with at least one read-only instance: 

Figure 3. Read/Write Split 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, then Heimdall would route the query to the write master node to complete the read operation, guaranteeing that a stale response was not received.  The value of the detected replication lag is found on the status screen (if any): 

Figure 4. “Status” tab 

 

For further protection, Heimdall adds a fixed lag window value, which is configured in a static manner.  This allows the replication lag window to spike on 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, and always using the read server.  

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 due to a particular user connecting to the database as well, i.e. to prevent a reporting job from putting load on the write master. 

   

Real-time analytic charts on the Analytics tab shows read and write queries now splitting:

Figure 7. “Dashboard” tab 

  

 

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 with a look-aside, results cache. Our proxy intelligently determines which queries to cache and automatically invalidates when the proxy detects an update to the database. We provide the cache logic and you choose storage (e.g. local heap, Redis). Unique to other caching solutions, Heimdall can cache SQL results in the application tier removing unwanted network latency between the application and database as shown in Figure 9 below. 

Figure 9: Heimdall Distributed Auto-caching Architecture 

 

The best part of our caching solution is its transparency, not requiring any code changes. Caching and invalidation are automated and fully user-configurable. For more information on how to configure Heimdall for auto-caching, check our automated caching blog. 

 

 

Customer Benefit 

 

By improving scale through read/write splitting and SQL caching, queries will be traffic routed for maximum performance.  Additionally, our proxy provides a bridge between a normal username and the hostname variation expected by Azure managed databases. Your application will operate seamlessly with Azure managed databases and gain the benefit of read/write query separation. Customers will save on operation costs and accelerate their applications into production. 

 

Resources and links: 

• Download in Azure Marketplace 
• Blog: Automated Query Caching without application changes 
• Blog: Heimdall Proxy for Azure Databases Overview 
• Contact: info@heimdalldata.com 

 

Heimdall Data, a Microsoft Technology partner, offers a database proxy to improve write-master / read replica scale for SQL databases (e.g. MySQL, Postgres, SQL Server, Azure SQL Database) without application changes.