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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
