DBBalancer Manual
 
Daniel Varela Santoalla
dvs@arrakis.es

DBBalancer Manual
by Daniel Varela Santoalla
Published $Data: 2001/03/30 $
Revision History
Revision 0.3.0	June 2001		
Alpha release	

Table of Contents
1. Introduction: What is DBBalancer?	000
Features	000
A database connection pool	000
A load balancer	000
A database replicator	000
2. Installation: The first steps	000
Installation from a binary distribution	000
Installation from source	000
Getting and compiling the ACE framework	000
Configuring and compiling DBBalancer	000
3. Configuration and usage: How does it work?	000
The Daemon	000
Runtime flags	000
Configuration file	000
Daemon section	000
Machine sections	000
4. Architectures: How to use it?	000
A connection pool	000
A load balancing connection pool	000
Load balancing connection pool with database replication	000
5. Features and limitations	000
Limitations	000
Connection Pool mode	000
Load Balancer mode	000
Load Balancer and Write Replicator mode	000
6. Implementation details: How was this done?	000
Postgres protocol and pooling	000
Connection recovery	000
Thread and connection load adaptability	000
Another databases	000
7. The future: Where is DBBalancer going?	000

Chapter 1. Introduction: What is DBBalancer?
DBBalancer is some sort of middleware daemon that would sit in between of database clients, like C, C++, TCL, Java JDBC, Perl DBI, etc programs and a database server. Currently the only server supported is PostgreSQL, but the architecture is open to embrace more servers in a future. 
To a client connecting to DBBalancer, the system offers a 
Features
The main feature is an implementation of a database connection pool, but DBBalancer can do different things:
A database connection pool
 As every database connection pool, DBBalancer trades memory for speed, opening all needed connections at startup and thus saving client request processing time. This performance benefit from this kind of arrangement is bigger when the backend database takes a relative long time to init a connection, like PostgreSQL or Oracle. The best case is when the actual processing time (sentence executing and data retrieval) is much smaller than the connection setup time. The worst case is if connections are kept open by the clients during a long time. In this case we could even lose performance by using this (or another pool).
A load balancer
Escalability and fault tolerance have always been two requisites for enterprise systems. The arrival of the web made many other systems avalaible to a ever growing number of users. Many of these systems aren't run by big enterprises so they prefer free and open source software rather than expensive commercial one. The problem is the lack of really escalable and safe hardware and software with those requisites.
The solution that most implement is the clustering of several machines. DBBalancer allows to spread the database load between several backends, thus solving the escalability and fault tolerance problem. But while we have a problem solved, a new one appeared. We have to keep the databases in sync if we want our clients to access any of them randomly and transparently. Here is where replication solutions make their appearance.
One possibility here is using the built-in replication support that some databases, like Postgres, offer now or will offer in a future. But Postgres replication, for example is quite new and limited so we may try the next point: DBBalancer Database Replicator.
A database replicator
Here we follow a simple method: Send a copy of the client input to every backend, and then consolidate all the backends output in just one, that's sent back to the only client. If the data received differs, a sync error is returned to the client. This method should work in most cases, with the drawback of using a different port for writing connections.
It must be noted that I don't consider this replication solution to be a definitive one. A good and efficient replication belongs to the database backend. But as we don't have a working, stable and free solution of this kind at the date of this writing, I offer this possibility as a temporary workaround. That's another reason for implementing a "two daemon" solution, instead of a more integrated one.
All of this functionalities can be combined and used simultaneously, just depending on the way configuration flags and file are used.
And even better is that, unlike some other solutions, all this can be achieved without changing the original code, as DBBalancer accepts the usual (by now just Postgres) clients: libpq, JDBC, libpgtcl, DBI, etc. It only would need some changes to use balancing and replication simultaneously, as this would require clients to use different ports to get "load-balancing" or "replication" connections.

Chapter 2. Installation: The first steps
The installation of DBBalancer must not be very dificult, specially if we start with a precompiled binary. 
Installation from a binary distribution
As every connection pool, DBBalancer pre-allocates several connections, saving the computationaly expensive work of allocating one connection for each execution of some code. This is specially useful in web applications, where usually there's little state persistence between a request and another. Depending on the patterns of use, there's a variable performance gain, being the biggest one for the typical connect-select-disconnect used in most dynamic web pages.
This pool has a variable number of connections and execution threads. The connection number should always be greater or equal than the thread number. The opposite would possible lead to client timeouts. These numbers vary on execution time, depending on the request queue size. Usually with a new thread is created a new db connection, and the reverse happens when a thread is destroyed.
This connection pool also recovers automatically the connections in the case of a database crash, excluding the connections from the pool while they're being recovered.
Installation from source
To successfully build DBBalancer we first should to be sure that the requirements are all checked out. We'll need two libraries with their matching sets of include files. 
Getting and compiling the ACE framework
Directly quoting their homepage, “The ADAPTIVE Communication Environment (ACE) is a freely available, open-source object-oriented (OO) framework that implements many core design patterns for concurrent communication software. ”. Here is used to avoid reinventing the wheel on things like logging format, threads management, etc. I'm sure that this framework could be more extensively used in DBBalancer that it's currently being used, but I'll leave to later versions doing further research on that point. Anyway, all ideas are welcome, so feel free to contribute.
This library (we need version 5.1) is available at two points. 
•	The official ACE (http://www.cs.wustl.edu/~schmidt/ACE.html) site, in the Washington University. Here you can find sources (http://ace.cs.wustl.edu/Download.html), including the latest beta version. Remember to fetch a 5.1 version. Building it is not difficult, as it just needs a good C++ compiler, like, for example, gcc-2.91.66 or better.
•	The commercial support site: Riverace (http://www.riverace.com). They sell packaged precompiled binaries and also sell support.
Configuring and compiling DBBalancer
Once you're finished with the process of getting the libraries you can start configuring the DBBalancer building process. As a configure script is provided, executing it will automatically create the makefiles.
If the libraries and include files are not installed at default locations, it could be needed to use the --with-ACE=dir and --with-pq options. The latter is only needed is some test programs are to be compiled, which are not by default.

Chapter 3. Configuration and usage: How does it work? 
The Daemon
All DBBalancer functionalities are implemented by a single binary that, running as a daemon, services the clients requests. That binary is called DBBalancerDaemon.
Runtime flags
There are only three runtime flags, and their meaning is the following: 
•	-r | -w: This flags allow to configure the daemon in reader or writer mode. In reader mode a client connection is assigned to one backend connection. In writer mode a client connection is assigned to all backends at once, thus providing a replication of the client commands across all the backends. As we'll see later, the -r configuration is used in Connection Pool and Load Balancer modes, and -w in Replicator mode. The default is reader mode.
•	-d: The presence of this flag means debug mode. The daemon will output thru stderr a lot of information about its internal processes. As a consecuence of this, the performance will also suffer somewhat. Right now the amount of debug info isn't configurable, but maybe in a future it will be. It's deactivated by default.
•	-f: This flag tells the daemon to use a particular configuration file. Starting with version 0.3.0, the XML config file is abandoned and substituted by a plain text one. This also removes the Xerces library requirement, easing a lot the installation. The meaning of each configuration item hasn't changed from the meaning they had with the XML file.
Configuration file
The configuration file stores the information that the daemon needs to connect to the databases, to accept clients, and more.
Daemon section
This section encloses some general and behavioural information for the daemon. Lets see

daemon.reader-port=6666
daemon.writer-port=6666
daemon.host=localhost

daemon.writer-transaction-per-request=false
daemon.init-threads=5
daemon.min-threads=5
daemon.max-threads=30
daemon.init-db-connections=5
daemon.min-db-connections=5
daemon.max-db-connections=30
daemon.reaper-delay=1
daemon.user=daniel
daemon.password=daniel
daemon.dbname=testdb
daemon.auth-method=password

•	reader-port: This element defines the port in which the daemon will listen when configured as reader. The users connecting thru the daemon will have to set their options to use this port ("port=6666" included in the connection options).
•	writer-port: This element defines the port in which the daemon will listen when configured as writer. The parameters for reader and writer mode are different to keep the possibility of sharing a config file for both daemons.
•	host: Defines the host name to which the bind is going to be done, while listening. The values "local" or "localhost" will make the daemon to listen on a UNIX socket. Any other value will make it to listen on INET socket, currently to INADDR_ANY.
•	writer-transaction-per-request: This parameter defines if a new trasaction is going to be created each time a connection is assigned to a client. This transaction protects backends synchronization by rollbacking every backend when any of them fails to get and update. This may or may not be a desirable behaviour so it's disabled by default, but can be enabled by assigning this parameter to true..
•	min-threads: The minimum number of threads that the thread pool will handle. When the client requests load is inexistent or very low, the number of threads will gradually move to this number.
•	init-threads: The initial number of threads spawned in the thread pool, just after the daemon startup.
•	min-threads: The minimum number of threads that the thread pool will handle. When the client requests load is inexistent or very low, the number of threads will gradually move to this number.
•	max-threads: As a complement to the previous parameter, this one marks the maximum number of threads that the thread pool will keep spawned simultaneously. This number is likely to be reached when the daemon serves a high number of clients or long running client connections.
•	init-db-connections: This parameter is similar to init-threads, but it controls the initial number of database backend connections that are opened at startup. This number of connections is opened in every backend, so the real connection number initially active is number_of_backends x init-db-connections. The same rule applys to the other two connection parameters.
•	min-db-connections: This parameters marks the minimum number of connections that will be kept open in every backend in the lowest load case.
•	max-db-connections: The maximum number of connections that we'll reach in every backend.
•	reaper-delay: During the normal daemon operation there are two periodic task that should be accomplished: 
•	The first one is to check if the database connections and thread number are enough or too much to handle the current load. For this the daemon checks the request queue size.
•	The second one is to check the database connection status. There's some posibility that a backend fails and thus all the connections allocated there became faulty. This task checks this and tries to recover those connections previously detected as faulty.
These two tasks are performed by a special thread, called system thread. The time (in seconds) that this thread sleeps between invocations of these tasks is defined by this parameters. If the parameter value is 0, the thread is disabled.
•	user: The username that will have to be used to connect to the daemon. This is the username that the clients will have to supply when starting the connection. 
•	password: Password that the daemon will require to connect. If the auth method is trust, then this value is not used for anything.
•	dbname: Database name that the daemon will require to connect. Previosly to defining this parameter the daemon accepted any dbname, always forwarding connections to the only pool. As this could lead to confusions, this parameter will have to be used to stand clear that there is only ONE database in the pool. Maybe in the future will be supported to serve more than one db in the pool.
•	auth-method: This parameter can take two values (trust|password) defining the method the daemon will require the clients to use.
Machine sections
This are, with the Daemon section, the most important sections of the file. They define the backends data, needed to connect to them. There will be one of these elements for each database backend. All the parameters are read sequentially so they are meant to be related to the machine.host which is located before them. A common machine is like this:

machine.host=localhost
machine.dbname=dbbalancer
machine.port=5432
machine.dbuser=daniel
machine.dbpassword=daniel

•	host: The hostname this backend resides. The names "local" or "localhost" will mean that the backend is listening thru UNIX sockets.
•	dbname: The name of the database
•	port: The port in which the postmaster is listening.
•	dbuser: The user needed to connect to the database.
•	dbpassword: The password for this user.

Chapter 4. Architectures: How to use it? 
In this section we'll describe different ways in which DBBalancer could be useful, along with the configurations needed to achieve the desired results.
A connection pool
This is the most easy, and probably most used configuration. A simple connection pool, where several connections to only one host are opened and shared at startup, can be set up with a configuration file like this:
daemon.reader-port=6666
daemon.writer-port=6666
daemon.host=localhost

daemon.init-threads=5
daemon.min-threads=5
daemon.max-threads=30
daemon.init-db-connections=5
daemon.min-db-connections=5
daemon.max-db-connections=30
daemon.reaper-delay=1
daemon.user=daniel
daemon.password=daniel
daemon.auth-method=password

machine.host=localhost
machine.dbname=dbbalancer
machine.port=5432
machine.dbuser=daniel
machine.dbpassword=daniel

The command to execute the daemon with this configuration is ./DBBalancerDaemon -rf test/config_examples/ConnectionPoolExample.conf. We can use the connections we get from the pool to read or write data, and every PostgreSQL feature is available.
A load balancing connection pool
With this configuration we have a number of connections that are distributed across several hosts. This will give us a greater performance, distributing the cpu and i/o load between all the hosts. The problem with it is that we have to keep identical copies of the data on every host, so, while this setup is great for read only data, can't face itself data writes. The configuration would be the following:
daemon.reader-port=6666
daemon.writer-port=6666
daemon.host=localhost

daemon.init-threads=5
daemon.min-threads=5
daemon.max-threads=30
daemon.init-db-connections=5
daemon.min-db-connections=5
daemon.max-db-connections=30
daemon.reaper-delay=1
daemon.user=daniel
daemon.password=daniel
daemon.auth-method=password

machine.host=localhost
machine.dbname=dbbalancer
machine.port=5432
machine.dbuser=daniel
machine.dbpassword=daniel

machine.host=localhost
machine.dbname=dbbalancer
machine.port=5433
machine.dbuser=daniel
machine.dbpassword=daniel

Here we can see, a setup in which the daemon has connections to two database instances running on the same host (we didn't have several hosts available, so this simulates that setup quite acceptable). The command used to start this configuration is ./DBBalancerDaemon -rf test/config_examples/LoadBalancerExample.conf
Load balancing connection pool with database replication
And what if we want to keep the load balanced hosts in sync? We have several options, like to use some external process to do it, to use PostgreSQL builtin replication capabilities (AFAIK, limited to one-way, two hosts), or we can use the database replication option of DBBalancer.
First at all, we should be very conscient that this option is the most fragile feature of DBBalancer, so it shouldn't be used for anything even minimally serious without extensive testing.
With this in mind, I'll explain you the setup. This mode need two daemons running, and also some modifications to clients, basically that they will have to connect to differents ports to do SELECT statements or to do any other statement. The config file would be like this:
daemon.reader-port=6666
daemon.writer-port=6667
daemon.host=localhost

daemon.init-threads=5
daemon.min-threads=5
daemon.max-threads=30
daemon.init-db-connections=5
daemon.min-db-connections=5
daemon.max-db-connections=30
daemon.reaper-delay=1
daemon.user=daniel
daemon.password=daniel
daemon.auth-method=password

machine.host=localhost
machine.dbname=dbbalancer
machine.port=5432
machine.dbuser=daniel
machine.dbpassword=daniel

machine.host=localhost
machine.dbname=dbbalancer
machine.port=5433
machine.dbuser=daniel
machine.dbpassword=daniel

We can see that it's exactly the same that in the last mode (LoadBalancer). In fact, we'll share the config file between the two daemons. We only have to make sure that the ports for reading and writing are not the same (6666 and 6667 in this example). 
This type of configuration consists in two processes. The first process, which will be in charge of SELECTS, should be started with this command ./DBBalancerDaemon -rf test/config_examples/WriteReplicatorExample.conf. The writer process, in charge of modifications, is started with ./DBBalancerDaemon -wf test/config_examples/WriteReplicatorExample.conf. Please note the w flag in the second case, that tells the process to use writer mode.
The drawback of this approach will be that if in the same fragment of client code we need to make read and write operations, we may have to use two connections, one to port 6666 for reads and one to port 6667 for writes. Nothing stops us to issue SELECT sentences to the writer process, it's just that we'll loose some performance, as they will also be replicated to every host. Here we should have to study application by application which is the best configuration. What's absolutely forbidden and would lead to backend lost of synchronicity is to send write (UPDATE, DELETE, INSERT, sequence increasing, ALTER, etc) sentences to a reader DBBalancerProcess that uses more than one host. Nothing within DBBalancer will automatically keep you from doing this, so it's your work to assure the correctness of client code.

Chapter 5. Features and limitations 
Limitations
Connection Pool mode
This mode doesn't require modifications in the client code.
Limitations: 
•	Asynchronous querys aren't supported.
Load Balancer mode
This mode doesn't require modifications in the client code, but it assumes that modifications on the databases are going to be replicated some way.
Limitations: 
•	Asynchronous querys aren't supported.
Load Balancer and Write Replicator mode
This mode requires that the client code use a different connection for reads and for writes. Each of the connections has to be done against a different DBBalancerDaemon, each one listening in a different port and configured with different parameters (one "-r", the other "-w").
Limitations: 
•	Replication support isn't completely finished. While it should somewhat work, errors aren't still very well managed.
•	Asynchronous querys aren't supported
•	OID's returned from a writer daemon shouldn't be relied to be valid when they come after a INSERT or UPDATE operation, as it's very probable that they're different across the different backends. The daemon will always return OID's from the first backend.

Chapter 6. Implementation details: How was this done? 
Postgres protocol and pooling
When a DBBalancerDaemon starts, it inits a number of connections with every one of the backend hosts. These connections are conducted through the authentication procedure and leaved in that state, waiting for connections.
When a client wants a connection, the daemon carries the authentication procedure with the client. If the procedure finishes ok, the rest of the client protocol information is redirected right to one of the backend connections, only taking care of intercept the connection close message. When this message is received, the daemon marks the backend connection as free and then closes the socket connection to the client. As we seen, almost all the protocol data (except connection start and connection close) is transparently sent between the client and the backend server, which is good for performance. 
Connection recovery
Connection recovery, as well as load adaptability is executed by an special Method Object, called DBManagementMO. This object runs continuously, within its own thread, sleeping during configurable periods (reaper-delay parameter).
In each of it invocations it asks the connection pool for a connection for test. That connection has to be free in that moment. Then the connection is asked to check itself, which is done by trying to read data from the socket. If the read returns that there's no data, the connections is thought to be ok. Other values (this could be OS dependent) could mean that it lost its backend connection, so a recovery try would be started.
Thread and connection load adaptability
One of the features of DBBalancer is that it adapts to the load conditions of the moment in two ways. The first one is increasing/decreasing the number of threads available in the thread pool and second one is increasing/decreasing the number of connections available in the connection pool. These two figures have to be similar, because increasing the number of threads without having enough connections to serve them would be a waste of resources.
The algorithm used to determine if we need to shrink/grow is based in the average size of the thread pool requests queue during the last 4 DBManagementMO thread check runs. With this “memory” procedure we slow the grow/shrink response time, which, while being bad to adapt to sudden load increments, keeps the system from identifying spurious peaks as real, more long term increments in the number of requests.
Another databases
If we analyze the class diagram that's part of the documentation set, we can see that there is a dotted line that divides two sections of the diagram. The bigger one is the database independent code, and the smaller (down left) one is the Postgres one. You have to take in consideration that this second part doesn't depend on Postgres libraries, because the small protocol handling parts are directly implemented in the code.
So, what would be needed to implement support for another database? Well, there a virtual C++ class, called DBPooledConnection. We have implemented it with two subclasses DBPostgresPooledConnection and DBPostgresWriterConnection, that are used to serve read-only and write connection requests. Another database support could be done by implementing again this virtual class, giving an alternate resolution to the, Postgres specific, opening and closing of client and backend connections. Then it would be necessary to implement the actual choice of databases, that's not still in place ;-).

Chapter 7. The future: Where is DBBalancer going? 
It depends mostly on users response. If I see that a good number of people is interested in the project further features could be added.
Anyway, I plan to debug and stabilize the current set of features, leading to a 1.0.0 version. Another point of improvement could be portability. Right now it only has been tested on Linux. I plan to test it with Solaris/gcc soon, but most other platforms should have to rely on volunteers to do the work, as I don't have any other machines to test,