DBIx::DBCluster - Distribute load among mirrored database servers
This document describes version 0.01 of DBIx::DBCluster, released June 17, 2003.
This module is currently being tested in development environment and should be considered beta code.
use DBIx::DBCluster;
my $dbh = DBIx::DBCluster->connect($data_source, $username, $auth, \%attr);
The idea of having multiple database servers that mirror the same database seems fairly simple. Most modern databases provide built-in tools and mechanisms for seamless, virtually instant automatic replication. If you're not trying to just back up your primary database, but actually use all your mirrors in production trying to achieve load balancing -- you will face the challenge of maintaining data integrity. Somewhere, you will need a mechanism that could filter your requests to the database and decide whether it is safe to direct specific request to any available server, or to a specific one.
Since most perl-based applications use DBI module for interacting with databases, a wrapper around DBI's database handler seemed to be the right place to implement the logic. It also requires bare minimum of changes to your existing code.
DBIx::DBCluster creates a database handler object that acts like, and has exactly
the same properties and methods as DBI's database handler. In the background it creates
multiple database connections to mirrored database servers. Acts as an
application level load balancer.
It is assumed that you have a cluster of database servers set up with one-way or two-way replication. Load balancing in two-way replication set-up is comparatively simple since you can both read and write to any server. This module is designed primarily for one-way replication set-up. In the latter case you can write only to the master server and read from any read-only slave server. Since the number of reads usually dominates anyway, there is a real advantage to having multiple read-only servers and deligate most read requests to them.
This database handler object creates two transparent database connections - one designated for modifying statements, the other one for non-modifying statements. Any statement you issue is analyzed and directed to one server or the other. Servers are randomly picked from the list you pre-define.
In order to utilize multiple connection capabilities you need to define a server cluster. Each cluster has (1) a list of servers that can be used for modifying statements, i.e. WRITE_SERVERS, and (2) a list of servers that can be used for non-modifying statements, i.e. READ_SERVERS.
This method takes the same arguments as DBI->connect() method. A special note about
$data_source argument or DSN. In order to utilize load balancing capabilities
your DSN should (1) explicitly specify hostname and (2) be ODBC compliant, i.e be in
one of the following formats:
dbi:DriverName:database_name@hostname:port
dbi:DriverName:database=database_name;host=hostname;port=port
The hostname will be extracted from the DSN and analyzed. If you have a cluster
with the same label configured - your cluster configuration will override actual
hostname when establishing database connection(s). For that matter the hostname
portion of your DSN doesn't even have to be a valid hostname - it can be just a
label of your cluster. If your hostname doesn't correspond with one of the
pre-defined cluster labels though, it will be treated as a real hostname and the
module will try to connect to it. No load balancing will happen in the latter case.
All other methods and attributes are inherited from DBI's database handler. See documentation for DBI package, section "DBI DATABASE HANDLE OBJECTS".
Any of the variables below can be set explicitly in your script or placed
in a configuration file and loaded via require. By default, configuration
data is pulled from DBIx::DBCluster::Config module. Feel free to edit
this file directly if you need to set up universal configuration.
This is a hashref that defines your clusters. This variable must be defined somewhere or you will not have load balancing. Here's the format:
$DBIx::DBCluster::CLUSTERS = {
'cluster_label' => {
'WRITE_HOSTS' => ['db1.mydomain.com'],
'READ_HOSTS' => ['db2.mydomain.com','db3.mydomain.com','db4.mydomain.com'],
},
};
An array of SQL keywords that will denote your statement as modifying or write statement. You probably won't have to modify this, but you can if you need to. The difault is:
@DBIx::DBCluster::WRITE_COMMANDS = qw( ALTER CREATE DELETE DROP INSERT LOCK RENAME REPLACE SET TRUNCATE UNLOCK UPDATE );
When set to true debug infromation is printed to STDERR.
Traditionally you would use DBI in way similar to
use DBI;
my $dbh = DBI->connect('DBI:mysql:test@db1.mydomain.com:3306', 'testuser', 'testpassword');
my $sth = $dbh->prepare('select * from test');
$sth->execute;
while (my $data = $sth->fetchrow_hashref){
## do something with $data
}
In a cluster set-up you would need to replace the top two lines. Instead of
use DBI;
my $dbh = DBI->connect('DBI:mysql:test@db1.mydomain.com:3306', 'testuser', 'testpassword');
you will have
use DBIx::DBCluster;
my $dbh = DBIx::DBCluster->connect('DBI:mysql:test@db1.mydomain.com:3306', 'testuser', 'testpassword');
The rest of your code needs no modifications. It is recommended that you put all your cluster definitions
in DBIx::DBCluster::Config module so that you don't have to define clusters in every script. Alternatively,
you can put your definitions in a central file, say Config.pl and load it up with require:
use DBIx::DBCluster;
require "/path/to/config_file/Config.pl";
my $dbh = DBIx::DBCluster->connect('DBI:mysql:test@db1.mydomain.com:3306', 'testuser', 'testpassword');
Yet another way to define your clusters is to do so explicitly in your script
use DBIx::DBCluster;
$DBIx::DBCluster::CLUSTERS = {
'db1.mydomain.com' => {
'WRITE_HOSTS' => ['db1.mydomain.com'],
'READ_HOSTS' => ['db2.mydomain.com','db3.mydomain.com','db4.mydomain.com'],
},
};
my $dbh = DBIx::DBCluster->connect('DBI:mysql:test@db1.mydomain.com:3306', 'testuser', 'testpassword');
Alex Rak arak@cpan.org
Copyright (c) 2003 Alex Rak. All rights reserved.
This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
The DBI module