Load Balancing JDBC Pool (lbpool)
The lbpool project provides a load balancing JDBC driver for use with DB connection pools. It wraps a normal JDBC driver providing reconnect semantics in the event of additional hardware availability, partial system failure, or uneven load distribution. It also evenly distributes all new connections among slave DB servers in a given pool. Each time connect() is called it will attempt to use the best server with the least system load.
The biggest scalability issue with large applications that are mostly READ bound is the number of transactions per second that the disks in your cluster can handle. You can generally solve this in two ways.
- Buy bigger and faster disks with expensive RAID controllers.
- Buy CHEAP hardware on CHEAP disks but lots of machines.
We prefer the cheap hardware approach and lbpool allows you to do this.
Even if you *did* manage to use cheap hardware most load balancing hardware is expensive, requires a redundant balancer (if it were to fail), and seldom has native support for MySQL.
The lbpool driver addresses all these needs.
The original solution was designed for use within MySQL replication clusters. This generally involves a master server handling all writes with a series of slaves which handle all reads. In this situation we could have hundreds of slaves and lbpool would load balance queries among the boxes. If you need more read performance just buy more boxes.
If any of them fail it won't hurt your application because lbpool will simply block for a few seconds and move your queries over to a new production server.
While currently designed for MySQL this could easily be updated to support PostgresQL or any other DB that supports replication.
Scalability
In the current mechanism the driver should be able to scale to dozens and hundreds of slave servers. Generally speaking though everything has a limit to which it can scale and lbpool is no exception.
For 99% of deployed applications we should be able to scale very well outside of the box. The NDB cluster package from MySQL seems like the way forward but until MySQL 5.1 is available with disk support this solution will be out of the price range of most customers since it requires an data be held in memory.
Load Balancing Policy
When the client first boots we attempt to connect to the host with the best chance of running our queries efficiently. We use the number of concurrent connections, load, and slave status to accomplish this. We keep using this host until our policy system determines that its no longer beneficial to do so. We use the following tests to determine if a host can is offline and can no longer can be used.
- It physically goes away (hardware issue, or network connectivity issue)
- Replication breaks (from SHOW SLAVE STATUS output)
- Replication falls behind.
- The load on the box is too high.
- Has too many connections.
Most of the time the load on the box will be to high and we'll simply pick another box and start running queries there. After a few minutes the box will probably correct and the system will continue functioning properly.
Features
* Runtime rebalancing. If other JDBC drivers disconnect and leave the cluster unbalanced we attempt to rebalance the cluster. Hosts are also rebalanced if they go offline and then become available again. This helps evenly distribute load across teh cluster.
* Even if ALL the servers in your cluster go offline the driver can handle this and will block until at least ONE machine comes back online. This means your application will just freeze and not break throwing SQLExceptions from broken servers.
* If a machine is flagged offline existing connections will be disconnected from this slave and reconnected to another production machine (even if they are sitting idle on an webserver for hours).
* Since the configuration is dynamic (and done on the master) machines can be added or removed at anytime. This can allow you to take a machine offline, reconfigure mysql, and then put it back online.
* If a slave physically goes offline it won't interrrupt service. All internal exceptions are caught and the query is replayed on another server.
* If replication breaks or falls behind the existing connections will be re-connected to working MySQL boxes without any interruption in service.
* DB servers can be flagged offline and within the polling interval (default of 2 minutes) they will be removed from production. This will allow you to run ALTER TABLE or REPAIR on production machines without affecting clients. Just remove them from production one at a time and your system shouldn't be affected.
* Ability to function offline even when working with fault client application which aren't cloing JDBC resources correctly.
Configuration
1. Create a db.mydomain.com DNS record with IP addresses for for each one of your servers. If you're paranoid use an internal DNS server.
2. Update your JDBC driver URL to use the new syntax:
lbpool:com.mysql.jdbc.Driver:db.tailrank.com:blogindex
You'll probably also need to add holdResultsOpenOverStatementClose in your connections string. This is a feature I introduced as a patch and Mark Matthews re-implemented in the mainline JDBC driver. Since MySQL 4.1 results sets are just byte arrays we can cheat a bit and allow use of result sets even after a JDBC statement has been closed.
3. Grant the MySQL REPLICATION CLIENT permission to the MySQL user the JDBC driver will connect as:
GRANT REPLICATION CLIENT ON *.* TO 'blogindex'@'%.tailrank.com';
Schema
The following schema needs to be setup on the master. This tells lbpool which hosts to use in production and allows you to flag hosts to go offline if necessary. Note that it will take 60 seconds or so for lbpool to stop using this host so its generally a good idea to flag if as offline and then wait until no other lbpool instances are using it.
On your master exec:
CREATE DATABASE lbpool; USE lbpool; CREATE TABLE HOST ( ID INTEGER NOT NULL AUTO_INCREMENT, POOLNAME VARCHAR (40) NOT NULL, HOSTNAME VARCHAR (40) NOT NULL, OFFLINE BIT default 0, PRIMARY KEY(ID), UNIQUE (POOLNAME(40), HOSTNAME(40)), INDEX HOSTNAME (HOSTNAME(40)) ); GRANT SELECT ON lbpool.* TO 'myuser'@'%.mydomain.com' IDENTIFIED BY 'mypasswd' ; FLUSH PRIVILEGES;
Releases
The latest release (1.0.0-beta1) is available here:
http://download.tailrank.com/lbpool-1.0.0-beta1.tar.gz
Future Direction
- It looks like this will be useful with MySQL Cluster as well as MySQL replication:
http://lists.mysql.com/cluster/3726
- It might be possible to use this concept to support multi-master failover. This would prevent a single point of failure within larger clusters.
Contributors
Kevin Burton (burton _AT_ tailrank _DOT_ com)
Similar Projects
[http://sqlrelay.sourceforge.net/]
All Hosted Tailrank Projects
Tailrank host a number of Open Source projects which we use to build our product including:
