2011/11/20 Steve Singer <[email protected]>: > On 11-11-19 03:05 AM, Maxim Boguk wrote: >> On Sat, Nov 19, 2011 at 9:31 AM, Maxim Boguk<[email protected]> wrote: >> >> Now replication lagging for 9 hours with errors every 40-50 minutes: >> >> 2011-11-19 11:01:31 MSKERROR remoteWorkerThread_1: "update >> "_sports".sl_setsync set ssy_seqno = '5016038207', ssy_snapshot = >> '501051321:501051321:', ssy_action_list = '' where ssy_setid in >> (1,9) and ssy_seqno< '5016038207'; " ERROR: could not serialize >> access due to read/write dependencies among transactions >> DETAIL: Reason code: Canceled on identification as a pivot, during write. >> HINT: The transaction might succeed if retried. >> >> again on medium size (500.000) batch update. >> >> It seems 3x-node configuration with slony 2.0.7 + postgresql 9.1 quite >> unstable with batch updates. >> >> Is here everything that I can use to work it more smooth? >> >> > > I wonder out load if the slony remote worker needs to be in a > serializable transaction or if READ COMMITTED is good enough. > > I also wonder if making the remote listener threads explicit read only > transactions would help.
if it is a read only transaction, then it is preferable to set it explicitely. If I well understood Heikki, it should remove 'pivot' situation. See http://wiki.postgresql.org/images/4/4f/SSI-PGConfEU2011.pdf (slide 22 for the immediate suggestions) > > The disorder tests against a 9.1 machine hit many of these read/write > dependencies in a test run. Slony just retries things and it > eventually works but I can see how a 500,000 item batch means that you > might often encounter an issue before finishing a SYNC. > > My unverified guess is that changing this won't break slony but might > effect applications querying the slave that depend on the serializable > behaviour and maybe it should be a configuration parameter. > > There are two places in remote_worker.c where it places the local > connection to serializable. > > > > > _______________________________________________ > Slony1-general mailing list > [email protected] > http://lists.slony.info/mailman/listinfo/slony1-general > -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation _______________________________________________ Slony1-general mailing list [email protected] http://lists.slony.info/mailman/listinfo/slony1-general
