[Dbix-class] DBD::Multiplex - do NOT use

[Tim Bunce]

On Fri, Sep 08, 2006 at 02:24:52PM -0400, Rob Kinyon wrote:
> mst asked me to write up a justification as to why DBD::Multiplex
> should be completely disregarded when developing the master/slave
> round-robin code.
> 
> I was asked to look at added transactional capabilities to
> DBD::Multiplex when dealing with master-slave situations. When I dove
> in, I found a complete mess. Basically, the structure is as follows:
> 
> 1) You create a set of $dbh's and designate one as the master.
> 2) It kinda does the right thing when dealing with prepare().
> 3) The multiplexing code is identical regardless of whether you're
> multiplexing over a drh, dbh, or sth.
> 4) There is no round-robining - it's just broadcast, broadcast to
> masters, or broadcast to slaves.
> 5) There are no tests. I started writing some tests and found bugs
> everywhere I looked.
> 6) There are no comments.
> 
> #3 is the biggest issue - $dbh's and $sth's are treated identically
> when they cannot be.

In the typical use-cases for DBD::Multiplex the $dbh would have multiple
underlying handles, but the prepare would return an $sth that only had one.
The prepare() picks which underlying dbh(s) should be used, and typically
it's just one - the multiplexing code isn't relevant in that situation.

> Frankly, it would be faster to just create a new DBD::Multiplex from
> scratch, taking lessons learned, than to attempt to clean up the
> current DBD::Multiplex.

The DBD::Multiplex on cpan has changed very little since 2004.

I have a version that's rather more flexible in many ways but, sadly,
the project it was being developed for got cancelled and the existing
work wasn't polished/documented/tested enough to release.

You can find it under http://svn.perl.org/modules/dbi/trunk/lib/DBD
It needs more work.

On Fri, Sep 08, 2006 at 02:44:35PM -0400, Drew Taylor wrote:
> 
> Crap. Not good. Interesting though that Tim Bunce mentioned it in
> OSCON talks this summer, so I assumed it was a goodo bet. :-(

I think it said it was in need of some love and asked for volunteers :)

> I'm in the process of adding replication to a mysql installation (RT
> 3.4.x) and was planning to use DBD::Multiplex. Do you have any
> suggestions for alternatives in the meantime? The application is setup
> such that I think it would be a royal PITA to hack the functionality
> into RT.

I believe DBIxHA is one of the more advanced high-availability modules
for the DBI. (I think it's the only one that uses swap_internal_handle,
for example.) It's also designed to be very fast, but it looks like it's
not designed to separate reads and writes.

There's also DBD::Multi by Casey West and Dan Wright which says it has
some "EXPERIMENTAL" support for read/write but the docs are sparse:
http://search.cpan.org/~dwright/DBD-Multi/lib/DBD/Multi.pm


On Fri, Sep 08, 2006 at 02:54:16PM -0400, Rob Kinyon wrote:
> 
> I don't have any good suggestions in terms of packaged solutions.
> 
> One thing to keep in mind is that you usually have a really good idea
> of when you're doing a mutate and when you're not. So, you could just
> defer your DBH creation until you know whether you're mutate-free or not.
> 
> Now, hacking that into RT will be .... interesting. And, some
> applications ALWAYS do a mutate (for logging or auditing purposes), so
> this sort of round-robin architecture will never work. All the
> applications I write tend to fall into that category. I don't know if RT does.

Seems like there are two basic choices: either some "smart" driver
tries to hide the fact there are separate read and write dbh's,
or the application (or ORM-type-layer) explicitly maintains separate
handles.

Smart drivers add overhead and can't always tell if a statement mutates
data (ie stored procedures) - but they can be very useful in places.

ORMs are in the fortunate position of knowing if they're issuing a
mutating statement (in general) and so can pick which handle to use.


On Fri, Sep 08, 2006 at 11:56:38AM -0700, J. Shirley wrote:
> Right now I'm also looking into setting up multiplexing in DBIC.  I
> think that given the problems with DBD::Multiplex it would be prudent
> to start from scratch.  I'll chip in on this as much as I can,
> although others may have more time to dedicate towards it so I can
> either write some test cases and docs, or help out on the less
> core-specific features.

On Fri, Sep 08, 2006 at 08:47:47PM +0100, Matt S Trout wrote:
> 
> Maybe it'd be worthwhile writing some simpler, more lightweight DBDs along 
> this line - maybe a DBD::HA (failover) and a DBD::LoadBalance (for round robin 
> etc.) that Storage could then use?

Writing a 'transparent' DBD layered over another is non-trivial - see
assorted comments in DBD::Multiplex.

Unless there's a very good reason not to I'd suggest that DBIx::Class
add the concept of a read-only db connection that defaults to the main
db connection. Then use that read-only db connection for all read-only
db actions.

(You need to give consideration to how that works with transactions -
but I'm pretty sure those issues will be much easier to address at the ORM
level than they would be if you tried to put the smarts into the DBD layer.)

Tim.