Front page | perl.dbi.users |
Postings from January 2007
Hello,
Today I've been researching database load balancing solutions to use
with an Apache/mod_perl application and a PostgreSQL database. Right now
I'm using Slony-I for replication, and it seems to work well enough.
The following is the conclusions I've come to from my own research.
Feedback from actual experience is appreciated.
- pgpool
pgpool is one solution to load balancing PostgreSQL connections.
However, I found a number of things not to like about it (again, mostly
from research):
-- says it adds 7 to 15% overhead.
-- Dumb about "SELECT nextval". They all need a space before them.
An accident here could cause havoc, when "SELECT nextval" gets
sent to a read-only db copy.
-- Has a bad failover policy: If the master becomes unavailable,
it starts sending both reads and writes to the read-only slave:
http://pgfoundry.org/pipermail/pgpool-general/2006-May/000378.html
- DBD::Multiplex
There's a fairly recent thread on why not to use this module:
http://www.mail-archive.com/dbi-dev@perl.org/msg04444.html
I found a couple more issues that looked like bugs to me:
Some SELECTs will be falsely classifed as "modifying" statements
http://rt.cpan.org/Ticket/Display.html?id=24220
no support for SELECTs that modify
http://rt.cpan.org/Ticket/Display.html?id=24219
#####
What's beginning to appeal to me know is to solve part load balancing in
the application by using two database handles: a read/write one, and a
read-only one. From there, it's much easier problem to solve: The R/w
handle always talks to the master and the Read-only handle can be load
balanced without being so "smart". Some further details about going
this route:
- I would leave the default handle as "read-write". That means if I
accidently miss converting a handle to "read only" somewhere, it will
still work, it just won't be load-balanced.
- CGI::Application::Plugin::DBH will make adding the second handle
easy. I'll still use $self->dbh() to access the default handle, and
$self->dbh('ro') for the new Read-only handle.
- DBD::Multi looks like it could handle this kind of simple load
balancing. As a bonus, it has some fail-over logic in it, so if the
slave was unvailable for a bit, that would be handled transparently.
Does that seem sound? What's worked for you?
Mark
Thread Next