Andrey Khavryuchenko himself

how to use mysql replication in django

October 1, 2007 – 22:19

I’ll start with warning.  Now it’s just a pure thoughts, none of code is tested or ever written and is just a dump of an idea came up at Python Exception.

For those who do not know the issue.  MySQL replication syncs databases one way, from master(s) to slaves.  This allows to keep a lot of read-only slaves and send updates only to the master.  Of course, this gives any benefit only if you have lots of SELECT’s and just handful INSERT’s/UPDATE’s.  Obviously, this requires having two distinct db connections and a handle to switch ORM between slave and master connections.

There are two possible solutions for this:

• statement-level routing, where connection object decides where to route the actual request and
• view-level routing, where decision is done on django view level

First I thought about statement-level routing, but Ivan Sagalaev quickly explained that it will fail as soon as you do any transaction.  Imagine that

1. master and slaves are slightly unsynchronised
2. transaction starts
3. initial selects are done on slave
4. code makes some decisions based on out-of-sync slave data and changes data on master

Ouch.

Thus, the proper decision would be a view-level routing.

It should do three things:

1. On django startup, create two db connections: one to the master and one to the slave.
2. Decide which connection the ORM should use as default (say, slave).
3. The view, that requires non-default access (e.g. master), should be wrapped with the decorator that changes ORM connection to the different one before view code gets executed and restores the value after.

This won’t work for middleware and other non-view stuff.  On the other hand, they are pretty self-contained and usually know well which connection they should use.

Implementation anyone?  My pet project had not yet reached the size, that justifies such setup.