[Bucardo-general] statement_chunk_size

Tue Apr 16 12:51:32 UTC 2013

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
**********************************************