Database load balancing is a technique that attempts to distribute the workload (database queries) that are handled by databases evenly among the different database servers.
In a basic database client/server arrangement, one or more database clients can make queries via a network to a back-end database server. The database server executes the queries and returns results to the client that requested them. The clients might be, for example, web servers or application servers or a mix of both. Typically all the clients transmit their queries to a common IP address and port number combination on the database server, and the database server transmits its results back to the IP address that originated each query. Usually an authentication process precedes the queries, in which the client provides the database server with authentication credentials (such as a username and password) and the database server establishes a “connection”, identified by a connection ID, according to which the client can make its queries. A database server in this basic arrangement is often a single computer system, though in some implementations it might actually be a cluster of computer systems. Even such a cluster, however, is arranged so as to appear to the client as a single server reached via a single IP address and port number combination.
Many more sophisticated database client/server arrangements support the concept of master-slave replication to distribute or spread the load of read/write queries among a plurality of servers. Database servers configured for master-slave replication are said to form a cluster, and operate in sync with each other ensuring that the data between all servers in a cluster is current. One of the advantages that master-slave replication offers is that of allowing the application administrators to scale out read performance, without spending excessively on hardware by having to buy a single, more powerful server. The master database server handles all queries that write/manipulate data (insert/update) and the remaining read queries (select) are distributed among the master and the slaves.
Any query or command which does not alter the database in any way, and merely retrieves information stored in the database, is considered a read query, and is typically capable of being processed by both the slave and the master servers, examples of such queries are basic “select” queries, which just retrieve a particular value from a table in the database. Any query or command which may alter or change or delete or add data in the database, or other related meta-data like usernames, passwords, configuration etc. is considered a write query, and can only be handled by master servers, examples of such queries are basic “insert”, or “alter” queries which add or modify the data or structure of a table in a database. If a write query were to be sent to a slave server, it will either be ignored, or cause the slave database to have a different state of data than the master, and hence fall out of sync, compromising the data integrity of the cluster.
In a typical Master-Slave database architecture, there are designated master and slave database servers. The master servers are capable of handling both read and write queries, while the slave servers handle the read queries. The most commonly used replication method used in a typical master-slave cluster involves the slave database servers reading the logs of transactions on the master servers that store all SQL statements that manipulated the database and execute them locally to ensure that the master and the slave database servers are in sync. Some replication architectures may also utilize a separate replication manager component which monitors the master servers for changes, and once a change is found, pushes the change to the slave servers, leaving both the master and the slave servers free from the complexity of maintaining sync.
In certain master-slave architectures, the data is stored in a central location (like a SAN), and read from that central location with the aid of a cluster file system. This eliminates the need to perform log based replication, but comes with the additional complexity involved in maintaining a single data store.
Splitting of read-write queries (i.e. directing appropriate queries to appropriate servers in the cluster) that are submitted to master and slave database servers ensures better response time for each of these types of queries. Database servers that handle write queries are optimized for database writes. Similarly database servers that handle read queries are optimized for the same. Database servers that are high on memory (RAM) and processor speed can also be configured to handle both read and write queries.
Client applications accessing the database servers that have a master-slave replication need to handle the job of splitting the read-write queries. In other words these applications need to be cluster-aware. There exists no single point of access in the cluster to which all queries are directed. Processes that need to manipulate the data (insert or update), must be aware of the master database servers and send data manipulation queries to these servers. Processes that need to retrieve data from the database servers must know what are the existing master/slave database servers and query them accordingly. It is the task of the application developer to ensure that the client applications perform splitting of read and write queries. Modifying an application which is not built to support master-slave replication architecture can be expensive, and time consuming, and in some cases may require significant re-engineering of its underlying structure, rendering it incompatible with future updates to the original application.
Closed source applications that do not support code editing or that do not support multiple servers are not even capable of being modified to handle the required splitting of read and write queries. Many of these applications will not be able to scale out.
Applicants have identified a need for a transparent database query routing system, which is inherently aware of the underlying master-slave architecture between the database servers of a cluster, and acts as a transparent layer between the application and the database servers themselves. Such a system would intelligently route database queries to the appropriate database servers, based on whether they are read, or write queries. This ensures better scalability of the application, and more consistent write performance, as it can ensure that stray read queries do not add to the load of the write optimized servers. This will also provide non-cluster aware client applications the opportunity to use server clusters where a read-write query split will be performed transparently, and gain in performance and reliability.