Re: How can we tell how far behind the standby is?

Folks,

I'm continuing in my efforts now to document how to deploy and manage
replication on our wiki. One of the things a DBA needs to do is to use
pg_current_xlog_location() (and related functions) to check how far
behind the master the standby is.

However, there's some serious problems with that:

(1) comparing these numbers is quite mathematically complex -- and, for
that matter, undocumented.

(2) pg_rotate_xlog and/or archive_timeout will create a "gap" in the
xlog positions, quite a large one if it happens near the beginning of a
file. There is no way for any monitoring on the standby to tell the
difference between a gap created by forced rotation as opposed to being
most of a file behind, until the next record shows up. Hello, nagios
false alerts!

(3) There is no easy way to relate a difference in log positions to an
amount of time.

I'll work on some tools to make this a bit more palatable, but I
disagree with earlier assertions that we have the replication monitoring
"done". There's still a *lot* of work to do.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On Fri, Nov 5, 2010 at 2:46 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> I'm continuing in my efforts now to document how to deploy and manage
> replication on our wiki.  One of the things a DBA needs to do is to use
> pg_current_xlog_location() (and related functions) to check how far
> behind the master the standby is.
>
> However, there's some serious problems with that:
>
> (1) comparing these numbers is quite mathematically complex -- and, for
> that matter, undocumented.
>
> (2) pg_rotate_xlog and/or archive_timeout will create a "gap" in the
> xlog positions, quite a large one if it happens near the beginning of a
> file.  There is no way for any monitoring on the standby to tell the
> difference between a gap created by forced rotation as opposed to being
> most of a file behind, until the next record shows up.  Hello, nagios
> false alerts!
>
> (3) There is no easy way to relate a difference in log positions to an
> amount of time.
>
> I'll work on some tools to make this a bit more palatable, but I
> disagree with earlier assertions that we have the replication monitoring
> "done".  There's still a *lot* of work to do.

I've heard the same complaint, and I agree with your concerns.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Nov 5, 2010 at 5:39 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Nov 5, 2010 at 2:46 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> I'm continuing in my efforts now to document how to deploy and manage
>> replication on our wiki.  One of the things a DBA needs to do is to use
>> pg_current_xlog_location() (and related functions) to check how far
>> behind the master the standby is.
>>
>> However, there's some serious problems with that:
>>
>> (1) comparing these numbers is quite mathematically complex -- and, for
>> that matter, undocumented.
>>
>> (2) pg_rotate_xlog and/or archive_timeout will create a "gap" in the
>> xlog positions, quite a large one if it happens near the beginning of a
>> file.  There is no way for any monitoring on the standby to tell the
>> difference between a gap created by forced rotation as opposed to being
>> most of a file behind, until the next record shows up.  Hello, nagios
>> false alerts!
>>
>> (3) There is no easy way to relate a difference in log positions to an
>> amount of time.
>>
>> I'll work on some tools to make this a bit more palatable, but I
>> disagree with earlier assertions that we have the replication monitoring
>> "done".  There's still a *lot* of work to do.
>
> I've heard the same complaint, and I agree with your concerns.

"All this has happened before, and all of it will happen again."

At this point pg has the equivalent of MySQL's "show slave status" in
4.0. The output of that change significantly over time:
http://dev.mysql.com/doc/refman/4.1/en/show-slave-status.html
http://dev.mysql.com/doc/refman/5.5/en/show-slave-status.html

Also of interest
http://dev.mysql.com/doc/refman/4.1/en/show-binary-logs.html

--
Rob Wultsch
wultsch(at)gmail(dot)com

On 6 November 2010 05:46, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> I'm continuing in my efforts now to document how to deploy and manage
> replication on our wiki.  One of the things a DBA needs to do is to use
> pg_current_xlog_location() (and related functions) to check how far
> behind the master the standby is.
>
> However, there's some serious problems with that:
>
> (1) comparing these numbers is quite mathematically complex -- and, for
> that matter, undocumented.
>

Our solution to this was to strip the slash out of the numbers and
then feed them to `bc` for comparison. The shell script for our
zabbix item looks something like this:

----
#!/bin/bash
errval=-1
primary=$(psql -At -h $1 -p $2 -c "SELECT
replace(pg_current_xlog_location(), '/', '');" postgres)
standby=$(psql -At -h $3 -p $4 -c "SELECT
replace(pg_last_xlog_receive_location(), '/', '');" postgres)

if [ -n "$primary" -a -n "$standby" ]
then
echo $(echo "ibase=16; obase=10; $primary-$standby" | bc)
else
echo $errval
fi

----
I'm posting this snippet a) in the hopes that it might help others,
and b) by way of agreement with Josh's point. Requiring every user
who wants to monitor replication to set something like this up for
themselves is ... not awesome.

Cheers,
BJ

On Sat, Nov 6, 2010 at 3:46 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> I'm continuing in my efforts now to document how to deploy and manage
> replication on our wiki.  One of the things a DBA needs to do is to use
> pg_current_xlog_location() (and related functions) to check how far
> behind the master the standby is.
>
> However, there's some serious problems with that:
>
> (1) comparing these numbers is quite mathematically complex -- and, for
> that matter, undocumented.
>
> (2) pg_rotate_xlog and/or archive_timeout will create a "gap" in the
> xlog positions, quite a large one if it happens near the beginning of a
> file.  There is no way for any monitoring on the standby to tell the
> difference between a gap created by forced rotation as opposed to being
> most of a file behind, until the next record shows up.  Hello, nagios
> false alerts!
>
> (3) There is no easy way to relate a difference in log positions to an
> amount of time.

The patch which I'm proposing is helpful for you?
http://archives.postgresql.org/pgsql-hackers/2010-11/msg00167.php

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

--On 5. November 2010 11:46:08 -0700 Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> I'll work on some tools to make this a bit more palatable, but I
> disagree with earlier assertions that we have the replication monitoring
> "done". There's still a *lot* of work to do.

While getting familiar with our SR/HS infrastructure i scripted this

<https://github.com/psoo/pg_standby_status/blob/master/pg_standby_status.pl>

Not sure if it does all things right, but it helped me a lot while load
testing SR. AFAIK Magnus has a monitoring script, too, i think this one is
it:

<https://github.com/mhagander/munin-plugins/blob/master/postgres/postgres_streaming_.in>

Maybe that helps, too.

--
Thanks

Bernd

> The patch which I'm proposing is helpful for you?
> http://archives.postgresql.org/pgsql-hackers/2010-11/msg00167.php

Depends. Is that the timestamp on the master (when it was synced), or
the timestamp on the standby (when it was replayed)? It's only helpful
if it's the former.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On Tue, Nov 9, 2010 at 7:57 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
>> The patch which I'm proposing is helpful for you?
>> http://archives.postgresql.org/pgsql-hackers/2010-11/msg00167.php
>
> Depends.  Is that the timestamp on the master (when it was synced), or
> the timestamp on the standby (when it was replayed)?  It's only helpful
> if it's the former.

It's the former. The function which I'm proposing returns the timestamp
of the last replayed commit/abort log record. That timestamp is given
when the commit/abort log record is generated in the master.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

> It's the former. The function which I'm proposing returns the timestamp
> of the last replayed commit/abort log record. That timestamp is given
> when the commit/abort log record is generated in the master.

That would be *extremely* helpful for all kinds of monitoring tools.
Please complete/submit this.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com