[Bucardo-general] statement_chunk_size

Tue Apr 16 13:19:11 UTC 2013

We just used the set command but there's probably other ways like the rc 
file.

    # bucardo set statement_chunk_size=1000
    Set "statement_chunk_size" to "1000"
    # bucardo show statement_chunk_size
    statement_chunk_size = 1000

On 04/16/2013 08:51 AM, Jonathan Brinkman wrote:
> How does one reduce the statement_chunk_size to 1000 from 10000?
>
> -----Original Message-----
> From: bucardo-general-bounces at bucardo.org
> [mailto:bucardo-general-bounces at bucardo.org] On Behalf Of
> bucardo-general-request at bucardo.org
> Sent: Tuesday, April 16, 2013 8:00 AM
> To: bucardo-general at bucardo.org
> Subject: Bucardo-general Digest, Vol 67, Issue 8
>
> Send Bucardo-general mailing list submissions to
> 	bucardo-general at bucardo.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
> 	https://mail.endcrypt.com/mailman/listinfo/bucardo-general
> or, via email, send a message with subject or body 'help' to
> 	bucardo-general-request at bucardo.org
>
> You can reach the person managing the list at
> 	bucardo-general-owner at bucardo.org
>
> When replying, please edit your Subject line so it is more specific than
> "Re: Contents of Bucardo-general digest..."
>
>
> Today's Topics:
>
>     1. Re: Canceled on identification as a pivot (Greg Sabino Mullane)
>     2. Re: My locks overfloweth (Greg Sabino Mullane)
>     3. Re: My locks overfloweth (Greg Sabino Mullane)
>     4. Re: Canceled on identification as a pivot (Greg Sabino Mullane)
>     5. Re: Syncs are aborted if one of the involved DBs is	offline
>        (Greg Sabino Mullane)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Mon, 15 Apr 2013 11:09:36 -0400
> From: Greg Sabino Mullane <greg at endpoint.com>
> To: Jonathan Brinkman <jb at blackskytech.com>
> Cc: bucardo-general at bucardo.org
> Subject: Re: [Bucardo-general] Canceled on identification as a pivot
> Message-ID: <20130415150936.GA2374 at broken.home>
> Content-Type: text/plain; charset="us-ascii"
>
> On Sun, Apr 14, 2013 at 11:46:07AM -0400, Jonathan Brinkman wrote:
>> [Sat Apr 13 18:54:04 2013]  KID Warning! Aborting due to exception for
>> clientdata.security_clearanceevents_loops.loopeventid: 3505855 Error
>> was DBD::Pg::st execute failed: ERROR:  could not serialize access due
>> to read/write dependencies among transactions\nDETAIL:  Reason code:
>> Canceled on identification as a pivot, during write.\nHINT:  The
>> transaction might succeed if retried. at
> /usr/local/share/perl/5.14.2/Bucardo.pm line 5815.
>
> That's a normal serialization error. Can happen a lot on a busy system, as
> Bucardo needs to be at least read committed level. You might try cranking
> your default isolation_level down from serializable to 'read committed'.
> This is done at the sync level, or you can set it globally in the bucardo
> config:
>
> bucardo set isolation_level=read_committed
>
> If it still persists, the next step is to figure out why things are not
> serializing. On the bucardo side, you can try smaller tables-per-sync, and
> running the syncs more often. Otherwise, you have to look at what else is
> accessing the replicated tables and see if it can be more friendly (e.g.
> short transactions help).
>
> --
> Greg Sabino Mullane greg at endpoint.com
> End Point Corporation
> PGP Key: 0x14964AC8
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: signature.asc
> Type: application/pgp-signature
> Size: 163 bytes
> Desc: Digital signature
> URL:
> <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20130415/af
> b47244/attachment-0001.sig>
>
> ------------------------------
>
> Message: 2
> Date: Mon, 15 Apr 2013 11:27:04 -0400
> From: Greg Sabino Mullane <greg at endpoint.com>
> To: Mitchell Perilstein <Mitchell.Perilstein at trueposition.com>
> Cc: bucardo-general at bucardo.org
> Subject: Re: [Bucardo-general] My locks overfloweth
> Message-ID: <20130415152704.GA3013 at broken.home>
> Content-Type: text/plain; charset="us-ascii"
>
> On Sat, Apr 13, 2013 at 11:07:21AM -0400, Mitchell Perilstein wrote:
>> Hi Greg,   We haven't tried that isolation level yet but we were
>> using our preferred 'read uncommitted' for a while (yes we know it's
>> supposed to be the same as 'read committed' in pg) and that didn't
>> seem to affect the lock usage or the memory complaints versus
>> serializable.
> Hmmm... I think I meant "repeatable read". I need to revisit this when my
> brain is not fuzzy. I don't think we should have read_committed or
> read_uncommitted in there at all!
>   
>> But here's a bigger hint: we just discovered last night that reducing
>> statement_chunk_size from 10000 to 1000 does have an impact.  It's
>> been running overnight so far and the lock level is holding in the
>> hundreds, which is fine.  This is with pg back at its defaults for
>> max_pred_locks_per_transaction and max_locks_per_transaction and
>> moderate load from our app hitting the db.
> Interesting, thanks for the report.
>
>
> --
> Greg Sabino Mullane greg at endpoint.com
> End Point Corporation
> PGP Key: 0x14964AC8
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: signature.asc
> Type: application/pgp-signature
> Size: 163 bytes
> Desc: Digital signature
> URL:
> <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20130415/30
> 4c0698/attachment-0001.sig>
>
> ------------------------------
>
> Message: 3
> Date: Mon, 15 Apr 2013 11:28:14 -0400
> From: Greg Sabino Mullane <greg at endpoint.com>
> To: Mitchell Perilstein <Mitchell.Perilstein at trueposition.com>
> Cc: bucardo-general at bucardo.org
> Subject: Re: [Bucardo-general] My locks overfloweth
> Message-ID: <20130415152814.GB3013 at broken.home>
> Content-Type: text/plain; charset="us-ascii"
>
> On Mon, Apr 15, 2013 at 11:27:04AM -0400, Greg Sabino Mullane wrote:
>> On Sat, Apr 13, 2013 at 11:07:21AM -0400, Mitchell Perilstein wrote:
>>> Hi Greg,   We haven't tried that isolation level yet but we were
>>> using our preferred 'read uncommitted' for a while (yes we know it's
>>> supposed to be the same as 'read committed' in pg) and that didn't
>>> seem to affect the lock usage or the memory complaints versus
>>> serializable.
>> Hmmm... I think I meant "repeatable read". I need to revisit this when
>> my brain is not fuzzy. I don't think we should have read_committed or
>> read_uncommitted in there at all!
> Yeah, sorry - it should always be set to repeatable read or serializable.
> Where are you setting it to read uncommitted?
>
>
>
> --
> Greg Sabino Mullane greg at endpoint.com
> End Point Corporation
> PGP Key: 0x14964AC8
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: signature.asc
> Type: application/pgp-signature
> Size: 163 bytes
> Desc: Digital signature
> URL:
> <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20130415/e2
> a22a75/attachment-0001.sig>
>
> ------------------------------
>
> Message: 4
> Date: Mon, 15 Apr 2013 11:29:10 -0400
> From: Greg Sabino Mullane <greg at endpoint.com>
> To: Jonathan Brinkman <jb at blackskytech.com>
> Cc: bucardo-general at bucardo.org
> Subject: Re: [Bucardo-general] Canceled on identification as a pivot
> Message-ID: <20130415152910.GC3013 at broken.home>
> Content-Type: text/plain; charset="us-ascii"
>
> On Mon, Apr 15, 2013 at 11:09:36AM -0400, Greg Sabino Mullane wrote:
>> That's a normal serialization error. Can happen a lot on a busy
>> system, as Bucardo needs to be at least read committed level.
> Oops, that should say "repeatable read"
>
>
> --
> Greg Sabino Mullane greg at endpoint.com
> End Point Corporation
> PGP Key: 0x14964AC8
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: signature.asc
> Type: application/pgp-signature
> Size: 163 bytes
> Desc: Digital signature
> URL:
> <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20130415/74
> 3d48a8/attachment-0001.sig>
>
> ------------------------------
>
> Message: 5
> Date: Mon, 15 Apr 2013 19:41:51 -0400
> From: Greg Sabino Mullane <greg at endpoint.com>
> To: Bucardo General <bucardo-general at bucardo.org>
> Subject: Re: [Bucardo-general] Syncs are aborted if one of the
> 	involved DBs is	offline
> Message-ID: <20130415234151.GE3013 at broken.home>
> Content-Type: text/plain; charset="us-ascii"
>
> On Mon, Apr 08, 2013 at 11:27:37AM -0400, Greg Sabino Mullane wrote:
>>> sync is performed.  All my remote nodes are slave only so it's not
>>> too much of an issue that way for me, however I do have a cluster of
>>> 4 masters (2 east coast, 2 west cost) that should a master fail it
>>> should be placed in read-only on startup whilst the DB is synced
>>> with outstanding changes.  Greg, would this be possible to look at?
>> Well, the case with one or more "targets" being down in a simple
>> single-source-many-targets is fixable, and I will do that once I get
>> some tuits.
> Found some tuits, but the problem is deeper than it first appears. Since we
> track what rows we've already replicated at the dbgroup level, we would have
> to break it into per-db if we allowed "catch up". Which means lots more rows
> in bucardo_track, and a higher quantity and/or more complex queries when
> pulling relevant rows from bucardo_delta. I'll keep playing with it though:
> the tradeoff is probably worth it.
>
> --
> Greg Sabino Mullane greg at endpoint.com
> End Point Corporation
> PGP Key: 0x14964AC8
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: signature.asc
> Type: application/pgp-signature
> Size: 163 bytes
> Desc: Digital signature
> URL:
> <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20130415/0a
> e06dcd/attachment-0001.sig>
>
> ------------------------------
>
> _______________________________________________
> Bucardo-general mailing list
> Bucardo-general at bucardo.org
> https://mail.endcrypt.com/mailman/listinfo/bucardo-general
>
>
> End of Bucardo-general Digest, Vol 67, Issue 8
> **********************************************
>
> _______________________________________________
> Bucardo-general mailing list
> Bucardo-general at bucardo.org
> https://mail.endcrypt.com/mailman/listinfo/bucardo-general
>

Confidentiality Notice: This e-mail (including any attachments) is intended only for the recipients named above. It may contain confidential or privileged information and should not be read, copied or otherwise used by any other person. If you are not a named recipient, please notify the sender of that fact and delete the e-mail from your system.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20130416/f1fce2bf/attachment-0001.html>