[CDBI] using different DSNs for reads/writes
perl at rhesa.com
Wed Dec 19 22:02:59 GMT 2007
Aaron Wolfe wrote:
> I am converting some messy scripts I wrote to use the very nice Class
> DBI interface. I have run into a wall that I can't seem to figure out
> (please forgive me if this is easy and I am just ignorant).
> I have multiple sites running mysql using master -> slave replication.
> This means all changes to the data must happen at the master.
> However, lookups should always use the local copy. The master db is
> slow to query, but writes are very infrequent compared to reads so it
> works ok. In my current code, I just have two db handles and pick the
> correct one to use by hand.
For normal DBI code, DBD::Multiplex would be the default solution.
It breaks down when used with Class::DBI, mostly because an insert() or
update() call triggers a select. This can fail or return stale data if your
replication isn't immediate. This is also a big problem for transactions.
As far as I know, there are two attempts on CPAN to solve this for CDBI:
Class::DBI::Replicated and Class::DBI::Replication. Both look unfinished, and
I have tried neither.
I'd love to get this stuff solved properly too, but for the time being I've
managed to get by using Memcached instead of replicated databases. Next year,
I anticipate our company to grow sufficiently to start needing multiple
database servers, so I may revisit this problem later.
Side note: even the DBIx::Class solution is marked Experimental...
More information about the ClassDBI