[Dbix-class] Replicated DB - Best Practices

[Rob Kinyon]

On Tue, Mar 24, 2009 at 08:23, Dave Cross <dave at dave.org.uk> wrote:
>
> I'm looking for some advice on best practice for dealing with replicated
> MySQL databases.
>
> My current client has a large database which is split across a RW master and
> several RO slaves (I believe that's how MySQL replication always works).
> They're just toying with replacing a lot of their DB code with DBIx::Class.

MySQL replication can be master/master, but it's usually master/slave(s).

> * How to decide whether to connect to the master or the slave

Generally, you want to connect to the master if your transaction has
any mutations, but to the slave otherwise. You will very often know
that a given request will not require mutations (such as a report).

> * Do we maintain a pool of database handles to both masters and slaves.

You could, or just let Replicated deal with it for you.

> * Whether it ever makes sense to upgrade a slave connection to a master
> connection on the fly

Replicated deals with this.

> * How referential integrity works in a world where a slave can be out of
> date compared to the master (actually, we know the answer to that one - "it
> doesn't")

Referential integrity works just fine and is really only applicable to
the master. So long as the master enforces referential integrity, the
slaves will have referential integrity.

Rob