Re: [sqlalchemy] SQLAlchemy dedicated read-server pooling

[mike bayer]

On 12/12/2016 10:43 AM, Matt wrote:
> I am currently writing a Flask web application that uses
> Flask-SQLAlchemy to communicate with a Postgres database. I want to have
> several read-replicas to which all database read queries can be
> directed, while all read+write queries can go to the master database
> instance. In other words, a master-slave setup.
>
> A stackoverflow question from 2012
> <http://stackoverflow.com/questions/8947918/read-slave-read-write-master-setup>
> discusses how this can be done in SQLAlchemy. I have implemented a
> variant of the accepted answer, one that uses the Flask-SQLAlchemy |db|
> object (which holds the session).
>
> In the global scope, I have:
>
>
> |slave_engine =create_engine(app.config['SQLALCHEMY_DATABASE_SLAVE_URI'])
>
> |
>
> Then, as a function decorator for any method that wishes to read from a
> slave instance:
>
>
> |defwith_slave(f):@wraps(f)defdecorated_function(*args,**kwargs):s
> =db.session oldbind =s.bind s.bind =slave_engine
> try:returnf(*args,**kwargs)finally:s.bind =oldbind returndecorated_function

when you have a Session that's in use with normal settings, the ".bind"
variable is not consulted. The Session is in a transaction already and
has procured a Connection from that Engine which is open in a
transaction; you can see this connection via session.connection(). It
can't be "swapped out", as the objects in this Session refer to rows
that are local to that transaction.

When you want to switch between "writer" and "reader", the "reader" must
be fine with using a totally clean transaction and that means totally
new objects and everything inside that Session as well. For a web
application this usually means "reader" has to be selected at the start
of the request, and the whole request consults "reader" only. You can't
switch between two different DB transactions mid-request, one of which
has been writing new rows, and expect to see the same rows between those
two transactions, the transactions will have some degree of isolation
from each other.

Short answer, you can't direct writer/reader at the "query" level,
unless you put the Session into autocommit mode and run COMMIT after
every statement. Normally it has to be at the transaction level.





>
> |
>
> Here's my problem. My AWS RDS dashboard consistently tells me the there
> are no open connections to the read servers (even during heavy use),
> while the master server (which is the default) has a number of open
> connections (probably because of SQLAlchemy's pooling).
>
> The question: am I correct to assume that the |@with_slave| decorated
> does not account for connection pooling? If so, I'm sure this affects
> performance, so the follow up would be: what could be changed to enable
> connection pooling to the read-servers?
>



> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Matt]

On Wednesday, December 14, 2016 at 3:58:19 PM UTC+1, Mike Bayer wrote:

When you want to switch between "writer" and "reader", the "reader" must
be fine with using a totally clean transaction and that means totally
new objects and everything inside that Session as well.  For a web
application this usually means "reader" has to be selected at the start
of the request, and the whole request consults "reader" only.  You can't
switch between two different DB transactions mid-request, one of which
has been writing new rows, and expect to see the same rows between those
two transactions, the transactions will have some degree of isolation
from each other.

Short answer, you can't direct writer/reader at the "query" level,
unless you put the Session into autocommit mode and run COMMIT after
every statement.  Normally it has to be at the transaction level.

It would be acceptable to select the read server at the start of a web request. I am still a little lost on how to get to that behavior, though. Any insights there? How do I bind the read server at the transaction level?

 

[Simon King]

There would be an easy answer if you were creating your own sessions
explicitly on each request; you would simply pass the appropriate
engine when you create the session. Since you're using
Flask-SQLAlchemy, you're slightly at the mercy of whatever it
supports. To complicate things further, Flask-SQLAlchemy uses scoped
sessions, so the actual session creation is buried even deeper.

(Another potential complication from scoped sessions is that the
Session instances themselves may be reused from one connection to the
next, although it looks like Flask-SQLAlchemy calls Session.remove(),
so that may not be a problem in this case)

If you're going to stick with Flask-SQLAlchemy, I think you need to
find a way to customize the session factory that is passed to the
scoped session. Your custom factory would return a session bound to
either the read-only or read-write engine as appropriate.

Here's where Flask-SQLAlchemy creates the scoped session:

https://github.com/mitsuhiko/flask-sqlalchemy/blob/master/flask_sqlalchemy/__init__.py#L747

It looks like you should be able to override the create_session method
(which actually returns a session *factory*) to return your custom
factory instead.

Hope that helps,

Simon

[Simon King]

I just read the StackOverflow post you referenced, and I see now that
replacing the "bind" attribute on a session is a reasonable thing to
do, as long as it is done early enough (Mike's point). Perhaps all you
need to do is move your decorator up the call stack so that it is
wrapping the view function?
http://flask.pocoo.org/docs/0.11/patterns/viewdecorators/ suggests
that that is allowed.

Simon

[Jonathan Vanasco]

I had a similar need under Pyramid and went with a different approach:  My "dbSession" object is actually a proxy container that holds dedicated Read, Write, and Log SqlAlchemy sessions.   I explicitly address "dbSession.reader" or "dbSession.writer" in all code.

Everything that is "public facing" or "reporting" oriented queries the "read".

Everything that is "account" or "admin" oriented queries the "write".