FAQ
What solutions are available, if any, to work with a master and multiple
slave database configuration that would be transparent to the application?
That is, send all select requests to the slaves but writes, and explicit
transactions (begin, select for update) go to the master. Database is
Postgresql.

Thanks,


--
Bill Moseley
moseley@hank.org

Search Discussions

  • Jared Still at Jul 7, 2009 at 5:26 am

    On Mon, Jun 29, 2009 at 7:59 PM, Bill Moseley wrote:

    What solutions are available, if any, to work with a master and multiple
    slave database configuration that would be transparent to the application?
    That is, send all select requests to the slaves but writes, and explicit
    transactions (begin, select for update) go to the master. Database is
    Postgresql.
    The obligatory question:

    What are you really trying to accomplish?


    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
  • Bill Moseley at Jul 9, 2009 at 12:25 am
    On Mon, Jul 6, 2009 at 10:26 PM, Jared Still wrote:
    On Mon, Jun 29, 2009 at 7:59 PM, Bill Moseley wrote:

    What solutions are available, if any, to work with a master and multiple
    slave database configuration that would be transparent to the application?
    That is, send all select requests to the slaves but writes, and explicit
    transactions (begin, select for update) go to the master. Database is
    Postgresql.
    The obligatory question:

    What are you really trying to accomplish?

    Sorry if that wasn't clear. The goal is to reduce load on the database.

    Using Slony replication with a single master and multiple slaves we want the
    application to transparently send selects to the pool of slaves, but any
    updates (and transactions) must go to the master. Slony replication is
    asynchronous so after a write all selects go to master for a configured
    amount of time to allow slaves to sync.

    The goal of this email was to see if anyone is doing something similar.




    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist


    --
    Bill Moseley
    moseley@hank.org
  • Greg Sabino Mullane at Jul 10, 2009 at 11:18 pm

    Sorry if that wasn't clear. The goal is to reduce load on the database.

    Using Slony replication with a single master and multiple slaves we want the
    application to transparently send selects to the pool of slaves, but any
    updates (and transactions) must go to the master. Slony replication is
    asynchronous so after a write all selects go to master for a configured
    amount of time to allow slaves to sync.

    The goal of this email was to see if anyone is doing something similar.
    Similar, yes: using Bucardo to create two masters and a handful (~8) of slaves.
    Rather than a middleware to route things around (which can be dangerous
    as a transaction should probably not do writes to the master and select
    from a slave due to the async nature of Slony and Bucardo), the application
    is tasked with the job of knowing which to hit and when. Specifically, there
    are two classes of database handles, "readwrite" and "readonly". The
    application then chooses which one to use depending on the situation, oft
    times switching back and forth as needed (with the general rule of "any
    changes made via the readwrite handle should continue to use that handle,
    even for reads, in the current session").

    The other danger in trying to be too transparent is that you quickly get
    into advanced SQL parsing, and deciding if the SQL statement contains any
    write access to the database - easy enough for IUD, but tricky when a SELECT
    contains function calls, any of which may or may not do database writes.

    - --
    Greg Sabino Mullane greg@turnstep.com
    End Point Corporation
    PGP Key: 0x14964AC8 200907101917
    http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
  • Bill Moseley at Jul 11, 2009 at 12:04 am
    (sorry Greg for the duplicate -- lack of List Reply in Gmail is
    driving me nuts...)

    On Fri, Jul 10, 2009 at 4:17 PM, Greg Sabino Mullanewrote:
    Similar, yes: using Bucardo to create two masters and a handful (~8) of slaves.
    Rather than a middleware to route things around (which can be dangerous
    as a transaction should probably not do writes to the master and select
    from a slave due to the async nature of Slony and Bucardo), the application
    is tasked with the job of knowing which to hit and when. Specifically, there
    are two classes of database handles, "readwrite" and "readonly". The
    application then chooses which one to use depending on the situation, oft
    times switching back and forth as needed (with the general rule of "any
    changes made via the readwrite handle should continue to use that handle,
    even for reads, in the current session").
    Or longer? In a web application it's possible that the "next" request
    happens before the slave is synced.

    In a web application with many application servers (and non-sticky sessions)
    the next request could be handled by any process on any web server.
    It would seem that
    something would need to be in the session or request (url) to indicate
    that a write just happened so that all reads (from that web-session)
    need to go to the master for some amount of time to allow the slave to
    sync. Doesn't sound too pretty.
    The other danger in trying to be too transparent is that you quickly get
    into advanced SQL parsing, and deciding if the SQL statement contains any
    write access to the database - easy enough for IUD, but tricky when a SELECT
    contains function calls, any of which may or may not do database writes.
    True. That's perhaps a bit easier to control since we are the ones
    creating the functions.
    But I do see your point.

    Thanks,





    --
    Bill Moseley
    moseley@hank.org
  • Greg Sabino Mullane at Jul 12, 2009 at 9:27 pm

    Or longer? In a web application it's possible that the "next" request
    happens before the slave is synced.

    In a web application with many application servers (and non-sticky
    sessions) the next request could be handled by any process on any
    web server. It would seem that something would need to be in the
    session or request (url) to indicate that a write just happened
    so that all reads (from that web-session) need to go to the master
    for some amount of time to allow the slave to sync. Doesn't
    sound too pretty.
    Right. We've tackled that situation before, and made a working
    prototype to do such a thing (using session variables), but the
    complexity becomes quickly not worth the cost of just defaulting
    to the readwrite handle on all but the safest and simple queries.
    Which ends up being a lot of them.

    - --
    Greg Sabino Mullane greg@turnstep.com
    End Point Corporation
    PGP Key: 0x14964AC8 200907121727
    http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedJun 30, '09 at 3:00a
activeJul 12, '09 at 9:27p
posts6
users3
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase