How does one reduce the statement_chunk_size to 1000 from 10000?
-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of
[email protected]
Sent: Tuesday, April 16, 2013 8:00 AM
To: [email protected]
Subject: Bucardo-general Digest, Vol 67, Issue 8
Send Bucardo-general mailing list submissions to
[email protected]
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
[email protected]
You can reach the person managing the list at
[email protected]
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 <[email protected]>
To: Jonathan Brinkman <[email protected]>
Cc: [email protected]
Subject: Re: [Bucardo-general] Canceled on identification as a pivot
Message-ID: <[email protected]>
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 [email protected]
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 <[email protected]>
To: Mitchell Perilstein <[email protected]>
Cc: [email protected]
Subject: Re: [Bucardo-general] My locks overfloweth
Message-ID: <[email protected]>
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 [email protected]
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 <[email protected]>
To: Mitchell Perilstein <[email protected]>
Cc: [email protected]
Subject: Re: [Bucardo-general] My locks overfloweth
Message-ID: <[email protected]>
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 [email protected]
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 <[email protected]>
To: Jonathan Brinkman <[email protected]>
Cc: [email protected]
Subject: Re: [Bucardo-general] Canceled on identification as a pivot
Message-ID: <[email protected]>
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 [email protected]
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 <[email protected]>
To: Bucardo General <[email protected]>
Subject: Re: [Bucardo-general] Syncs are aborted if one of the
involved DBs is offline
Message-ID: <[email protected]>
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 [email protected]
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
[email protected]
https://mail.endcrypt.com/mailman/listinfo/bucardo-general
End of Bucardo-general Digest, Vol 67, Issue 8
**********************************************
_______________________________________________
Bucardo-general mailing list
[email protected]
https://mail.endcrypt.com/mailman/listinfo/bucardo-general
