I am curious whether anybody has actually tried this. I am concerned about a few things that can go wrong: 1) even GET request may write to the DB (maybe write session in DB or logging some action) 2) all web2py requests are wrapped in a transaction, can this cause deadlocks? 3) there is a delay in syncing a master with a slave db, can this result in inconsistencies on the user side?
Why do this instead of simply caching read only queries using redis? Massimo On Saturday, 6 January 2018 11:14:33 UTC-6, Anthony wrote: > > Something like that -- it depends on what is easiest. If writing happens > in only a few places, then you can maintain a list of the functions that do > writes rather than those that only do reads. You could also take Dave's > approach and use the HTTP method to determine the connection: > > READ_ONLY_URIS = ['mysql://...1','mysql://...2','mysql://...3'] > > if request.env.request_method and request.env.request_method == 'GET': > db_uri = random.shuffle(READ_ONLY_URIS) > else: > db_uri = 'mysql://...master' > > db = DAL(db_uri, ...) > > Alternatively, you could try changing the connection of the DAL object > within a given controller action. I haven't tried it, but maybe something > like this: > > MASTER_URI = 'mysql://...master' > > def connect_to_master(db, uri=MASTER_URI): > if db._uri != uri: > master = DAL(uri) > db._adapter = master._adapter > > The above function takes a DAL instance and swaps the ._adapter, which is > used to connect to the database. In a given controller, you could then do: > > def myaction(): > connect_to_master(db) > [Do db write operations.] > return dict() > > Anthony > > On Saturday, January 6, 2018 at 8:26:45 AM UTC-5, CDA wrote: >> >> So given the example below from the book, I would add all the controller >> functions that does database read to read_only_actions list? I would have >> to continuously maintain this list as my application grows. Is this the >> right approach? >> >> if request.function in read_only_actions: >> db = DAL(sample(['mysql://...1','mysql://...2','mysql://...3'], 3)) >> elif request.action in read_only_actions: >> db = DAL(shuffle(['mysql://...1','mysql://...2','mysql://...3'])) >> else: >> db = DAL(sample(['mysql://...3','mysql://...4','mysql://...5'], 3)) >> >> >> where 1,2,3 are slaves and 3,4,5 are masters. >> >> On Jan 6, 2018 3:54 AM, "Dave S" <[email protected]> wrote: >> >> >> >> On Friday, January 5, 2018 at 7:50:28 PM UTC-8, CDA wrote: >>> >>> How does web2py select a database to connect to based on the http >>> request content? Is it feasible to connect to different databases depending >>> on whether it is a read request or a write request? Can you provide example >>> on how to do this? >>> >>> >> I would expect that the http request wouldn't directly select a >> database. Instead, your controller would know if a select() was being done >> to satisfy the request (typical for HTTP GET, but often needed for other >> reasons), or an insert or update (often an HTTP POST or HTTP PUT), or a >> delete (such as for an HTTP DELETE). But it's your controller that decides >> what type of DB operation is required. AIUI. >> >> /dps >> >> >> >>> On Fri, Jan 5, 2018 at 6:35 PM, Anthony <[email protected]> wrote: >>> >>>> Do you have doubts about whether it would work? Seems fairly >>>> straightforward. >>>> >>>> >>>> On Friday, January 5, 2018 at 3:59:29 PM UTC-5, CDA wrote: >>>>> >>>>> >>>>> Below is an excerpt about horizontal scaling from Chapter 13 in the >>>>> book. Has any one successfully implemented this using web2py? >>>>> >>>>> It is also possible to connect to different databases depending on the >>>>> requested action or controller. In a master-slave database configuration, >>>>> *some* *action performs only a read* and *some performs both >>>>> read/write*. The former can safely connect to a slave db server, >>>>> while the latter should connect to a master. So you can do: >>>>> >>>>> >>>>> 1 >>>>> 2 >>>>> 3 >>>>> 4 >>>>> 5 >>>>> 6 >>>>> >>>>> if request.function in read_only_actions: >>>>> db = DAL(sample(['mysql://...1','mysql://...2','mysql://...3'], >>>>> 3))elif request.action in read_only_actions: >>>>> db = DAL(shuffle(['mysql://...1','mysql://...2','mysql://...3']))else: >>>>> db = DAL(sample(['mysql://...3','mysql://...4','mysql://...5'], 3)) >>>>> >>>>> >>>>> where 1,2,3 are slaves and 3,4,5 are masters. >>>>> >>>> -- >>>> Resources: >>>> - http://web2py.com >>>> - http://web2py.com/book (Documentation) >>>> - http://github.com/web2py/web2py (Source code) >>>> - https://code.google.com/p/web2py/issues/list (Report Issues) >>>> --- >>>> You received this message because you are subscribed to the Google >>>> Groups "web2py-users" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to [email protected]. >>>> >>>> For more options, visit https://groups.google.com/d/optout. >>>> >>> >>> -- >> Resources: >> - http://web2py.com >> - http://web2py.com/book (Documentation) >> - http://github.com/web2py/web2py (Source code) >> - https://code.google.com/p/web2py/issues/list (Report Issues) >> --- >> You received this message because you are subscribed to the Google Groups >> "web2py-users" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> For more options, visit https://groups.google.com/d/optout. >> >> >> -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
