[Dbix-class] master slave databases.

[John Napiorkowski]

--- On Thu, 6/5/08, Ian Docherty <dbix-class at iandocherty.com> wrote:

> From: Ian Docherty <dbix-class at iandocherty.com>
> Subject: Re: [Dbix-class] master slave databases.
> To: "Class user and developer list" <dbix-class at lists.scsys.co.uk>
> Date: Thursday, June 5, 2008, 9:21 AM
> Matt S Trout wrote:
> > On Tue, Jun 03, 2008 at 06:49:59AM -0700, John
> Napiorkowski wrote:
> >   
> >>
> >> --- On Tue, 6/3/08, Ian Docherty
> <dbix-class at iandocherty.com> wrote:
> >>
> >>     
> >>> From: Ian Docherty
> <dbix-class at iandocherty.com>
> >>> Subject: [Dbix-class] master slave databases.
> >>> To: "Class user and developer list"
> <dbix-class at lists.scsys.co.uk>
> >>> Date: Tuesday, June 3, 2008, 8:31 AM
> >>> Hi
> >>> How would one go about configuring DBIC to
> direct all
> >>> writes to a Master 
> >>> database and all reads from a Slave database?
> >>>
> >>> I am aware of 
> DBIx::Class::Storage::DBI::Replication but
> >>> is it used by 
> >>> anyone in a production environment such that
> it would not
> >>> warrant the 
> >>> 'experimental' status?
> >>>
> >>> Regards
> >>> Ian
> >>>       
> >> There's a branch called
> "replication_redux" which is following trunk very
> closely and is a rewrite of the replication implementation. 
> We are using this (as of last Friday) on our production
> website and everything appears to be working correctly
> using mysql native replication.  Our site gets a lot of
> daily hits, so we feel it's shaping up solidly.
> >>
> >> I'm writing up a more detailed overview for
> the mailing list, but wanted to get a few days in prod.  
> But if you need something like this I would enjoy
> collaboration and review.  Let us know more about what you
> are planning/needing.
> >>     
> >
> > How long do you need before this is ready now?
> >
> > The previous vcersion of the code was already in
> production so there
> > shouldn't need to be -that- much rewriting to make
> it work, and 08100
> > should have shipped already and is being delayed for
> you.
> >   
> I thought that things were quiet on the list. I seem to
> have missed 
> several emails from the list so if anyone has asked me a
> question that I 
> have not answered then I apologise now.
> 
> What should I understand by this response by mst about the
> current 
> version of DBIC since I think I am reading the reply out of
> context.
> 
> Regards
> Ian_______________________________________________


You're context looks fine to me.  I am going to try to get with Matt and the original replication author to resolve differences, but I'd hate to make you wait while that is happening, since I have no idea what kind of deadline you are working under.

The latests Dev release on CPAN contains some bug fixes to the original replication storage driver.  This version is based on DBD::Multi and is a straight up attempt to split read/write traffic using that underlying DBD.  

Although we could get this to work for the test cases and I did clean up some bugs, there was a lot of trouble integrating it with our deployment.  It didn't work correctly with Versioning and our Partitioning setup.  There were a considerable number of other problems.  For example, since the CPAN DBIC version will identify the database type as 'DBD::Multi' and not your true underlying storage type, you might have trouble with database specific stuff like properly decode datetime columns.  Basically, any of the driver specific stuff we do to even out DB differences get lost.  Also, we found that DBD::Multi would generate errors when it failed to find a row in a select query.  For us this was a problem, since on our site having a select return zero results is perfectly valid.  Your results may vary.

Basically we were finding we needed to drop little hints all over the code to make it 'replication aware', which is really bad practice, since components like Versioning should not care if the schema is replicated or not.

The branched version of replication is a rewrite which removes the DBD::Multi dependency and brings replication under DBIC::Storage.  Additionally that branch also contains a number of minor bug fixes to the test schema so that I could run the replication test against a real replicating database, instead of trying to fake it with SQLite.  Lastly, that branch has some minor changes to other parts of DBIC, such as adding method stubs for checking replication lag to DBIC::Storage and dealing with some things that really need to always read from the master, such as DBIC::PK->discard_changes.

The branched version also adds a few useful bits, such as a method to force execute queries against the master (you might need this if you have realtime query needs), some hints to DBIC_TRACE so you can see which queries are sent to the master versus the slaves, support for dynamically adding and dropping slaves from the pool, etc.  I'm working up a full feature list, but most of it is already in the POD.  I wanted to let it bake in prod a bit as well.  As I said, we have a pretty large DBIC setup, almost 200 tables with result classes and resultsets, versioning, partitioned tables, etc., so I felt we could beat on it a bit and shake out the gotchas.

If you can wait, I'd would see how we resolve merging the outstanding branch.  from Matt's post I would imagine he'd be hot to resolve this one way or another.

Since I got your attention, could you please let us know what underlying database and replication system you are planning to use?  

Thanks!
John

> List:
> http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.rawmode.org