Read slave, read-write master setup

Question

I have a Flask, SQLAlchemy webapp which uses a single mysql server. I want to expand the database setup to have a read-only slave server such that I can spread the reads between both master and slave while continuing to write to the master db server.

I have looked at few options and I believe I can't do this with plain SQLAlchemy. Instead I'm planning to create 2 database handles in my webapp, one each for master and slave db servers. Then using a simple random value use either the master/slave db handle for "SELECT" operations.

However, I'm not sure if this is the right way to go with using SQLAlchemy. Any suggestion/tips on how to pull this off?

Answer 1

I have an example of how to do this on my blog at http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/ . Basically you can enhance the Session so that it chooses from master or slave on a query-by-query basis. One potential glitch with that approach is that if you have one transaction that calls six queries, you might end up using both slaves in one request....but there we're just trying to imitate Django's feature :)

A slightly less magic approach that also establishes the scope of usage more explicitly I've used is a decorator on view callables (whatever they're called in Flask), like this:

@with_slave def my_view(...):    # ... 

with_slave would do something like this, assuming you have a Session and some engines set up:

master = create_engine("some DB") slave = create_engine("some other DB") Session = scoped_session(sessionmaker(bind=master))  def with_slave(fn):     def go(*arg, **kw):         s = Session(bind=slave)         return fn(*arg, **kw)     return go 

The idea is that calling Session(bind=slave) invokes the registry to get at the actual Session object for the current thread, creating it if it doesn't exist - however since we're passing an argument, scoped_session will assert that the Session we're making here is definitely brand new.

You point it at the "slave" for all subsequent SQL. Then, when the request is over, you'd ensure that your Flask app is calling Session.remove() to clear out the registry for that thread. When the registry is next used on the same thread, it will be a new Session bound back to the "master".

Or a variant, you want to use the "slave" just for that call, this is "safer" in that it restores any existing bind back to the Session:

def with_slave(fn):     def go(*arg, **kw):         s = Session()         oldbind = s.bind         s.bind = slave         try:             return fn(*arg, **kw)         finally:             s.bind = oldbind     return go 

For each of these decorators you can reverse things, have the Session be bound to a "slave" where the decorator puts it on "master" for write operations. If you wanted a random slave in that case, if Flask had some kind of "request begin" event you could set it up at that point.