Master Slave SQL

Http://turbogears.readthedocs.io/en/latest/cookbook/master-slave.html

SQLAlchemy Master Slave Load balancingSince version 2.2 TurboGears have basic support for Master/slave load Balancingand provides a set of utilities to use I T.

TurboGears permits to declare a master server and any number of the slave servers, all thewrites would automatically redirected To the master node and the other calls Willbe dispatched randomly to the slave nodes.

All the queries executed outside of TurboGears controllers would run only on Themaster node, those include the queries perf Ormed by the authentication stack toinitially look up a already logged in user, its groups and permissions.

Enabling Master Slave Balancing

To enable Master Slave load balancing your just need to edit yourmodel/__init__.py making the use of the sessionmaker Turboge Ars Balancedsession:

From tg.configuration.sqla.balanced_session Import balancedsessionmaker = Sessionmaker (Autoflush=true, autocommit= False, Class_=balancedsession, Extension=zopetransactionextension ())

Doing this by itself would suffice to do load balancing work, but stillas there are only the standard database Configurati On the'll just is redirecting all the queries to the only BalancedSession available serve.

Configuring Balanced Nodes

To let the load balancing work we must specify at least a master and slave serverinside our application configuration. The master server can be specifiedusing thesqlalchemy.masterset of options, while any number of Slavescan is conf Igured using thesqlalchemy.slavesoptions:

Sqlalchemy.master.url = mysql://username:[email protected]:p ort/databasenamesqlalchemy.master.pool_recycle = 3600sqlalchemy.slaves.slave1.url = mysql://username:[email protected]:p ort/ Databasenamesqlalchemy.slaves.slave1.pool_recycle = 3600

The master node can be configured also to being a slave, this is usually thecase when we want the master to also handle some Read queries.

Driving the Balancer

TurboGears provides a set of utilities to let you change the default behaviorof the load balancer. Those include the @with_engine (engine_name) Decoratorand the dbsession (). Using_engine (Engine_name) Context.

The With_engine decorator

The with_engine decorator permits to force a controller method is torun on a specific node. It's a great tool for ensuring this someactions take place to the master node, like controllers that editcontent.

From TG import [email protected] (' Myproj.templates.about ') @with_engine (' master ') def on (self): Dbsession.query ( Model. User). All () return dict (page= ' about ')

The previous query would be executed on the master node, if the @with_enginedecorator is removed it'll get Execu Te on any random slave.

The with_engine decorator can also is used to force Turbogearsto use the master node when some parameters is passed by URL:

@expose (' Myproj.templates.index ') @with_engine (master_params=[' m ']) def index (self): Dbsession.query (model. User). All () return dict (page= ' index ')

In this case calling Http://localhost:8080/index would result in queriesperformed to a slave node, while calling C3>http://localhost:8080/index?m=1 Willforce The queries to being executed on the master node.

Pay attention that the m=1 parameter can actually has any value, it justhas to be there. This is especially useful if redirecting after an actionthat just created a new item to a page that have to show the new Item. Usinga parameter specified in master_params we can force TurboGears to fetchthe items from the master node so to Avoid odd results due to data propagationdelay.

Keeping master_params Around

By default parameters specified in with_engine master_params would bepopped from the controller params. This was to avoid messing with Validatorsor controller code, doesn ' t expect the parameter to exist.

If the controller actually needs to access the parameter a dictionary can bepassed to @with_engine instead of a list. The dictionary keys would bethe parameters, while the value would be if to pops it from theparameters or not.

@expose (' Myproj.templates.index ') @with_engine (master_params={' m ': False}) def index (self, M=none): Dbsession.query ( Model. User). All () return dict (page= ' index ', m=m)

Forcing single Queries on a node

Single queries can is forced to execute on a specific node using the using_engine method of the BalancedSession . This methodreturns a context manager, until queries is executed inside Thiscontext they is run on the constrained engine :

With Dbsession (). Using_engine (' master '): Dbsession.query (model. User). All () Dbsession.query (model. Permission). All () Dbsession.query (model. Group). All ()

In the previous example the Users and the Permissions would befetched from the master node while the Groups would be Fetche Dfrom a random slave node.

Debugging balancing

Setting the root logger of your application to DEBUG would letyou see which node have been choose by the to BalancedSession PE Rform a specific query.

This article from the "7464112" blog, reproduced please contact the author!

Master Slave SQL