From: John Scalia <jayknowsunix@gmail.com>
To: David F. Skoll <dfs@roaringpenguin.com>
To: pgsql-admin@postgresql.org
Subject: Re: Weird spikes in delay for async streaming replication on 9.1
Date: Thu, 26 Feb 2015 11:06:50 -0500
Message-ID: <54EF449A.9030407@gmail.com> (raw)
In-Reply-To: <20150226110007.09bb1551@hydrogen.roaringpenguin.com>
References: <20150214110050.2f032f05@ollie.roaringpenguin.com>
<20150226094428.34531376@hydrogen.roaringpenguin.com>
<54EF3E6F.60403@gmail.com>
<20150226104433.1b0df1db@hydrogen.roaringpenguin.com>
<54EF411B.80202@gmail.com>
<20150226110007.09bb1551@hydrogen.roaringpenguin.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
On 2/26/2015 11:00 AM, David F. Skoll wrote:
> On Thu, 26 Feb 2015 10:51:55 -0500
> John Scalia <jayknowsunix@gmail.com> wrote:
>
>> OK, if it's asynchronous, is your script checking that primary isn't
>> holding up closing out and transmitting the latest WAL segment during
>> these times?
> No. The checking script is dead simple. It's a perl script that does
> more-or-less the equivalent of this. $master_dbi is a DBI handle
> pointing to the master database server and $standby_dbi is one pointing
> to the standby. Pseudocode is shown below (the real code actually measures
> the duration in much finer increments than 1s, but you get the idea...)
>
> my $time = time();
>
> # There's one row in the table with key column 'Timestamp'
> $master_dbi->do("UPDATE table SET value = ? WHERE key = 'Timestamp'", undef, $time);
>
> my $start = time();
> while(..) {
> $row = $standby_dbi->fetchrow_arrayref("SELECT * FROM table WHERE key = 'Timestamp'");
> # Check that we got back the same $time we put in and if so,
> # break out of the while loop; otherwise pause for a bit.
> }
> # See how long that took
> my $duration = time() - $start;
>
>> And if the Standby really needs to be up to date, why
>> not try synchronous replication?
> It doesn't need to be totally up-to-date. It can lag by up to 30s without
> impacting our application. And we don't use synchronous replication because
> then a network problem would stall all the write transactions on the primary
> and that would be fatal.
Possibly, but that's why WE use a pair of standby servers, not a single one, so that all transactions get committed in a timely manner. The odds of both standbys failing at the
same time are really small. Maybe your script should check which is latest WAL segment on each system first? That might show that you have a timedelay with getting the info to the
standby.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jayknowsunix@gmail.com, dfs@roaringpenguin.com
Subject: Re: Weird spikes in delay for async streaming replication on 9.1
In-Reply-To: <54EF449A.9030407@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox