Proper use of pg_xlog_location_diff()

On 1/15/15 7:12 PM, Fabio Ugo Venchiarutti wrote:

Greetings

Our company is writing a small ad-hoc implementation of a load balancer for Postgres (`version()` = PostgreSQL 9.2.9 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit).

We're using both streaming and WAL shipping based replication.

Most mainstream solutions seem to implement load balancing with plain round robin over a connection pool. Given that our cloud nodes are diversely capable and subject to noisy neighborhood conditions, we need to factor in instantaneous load profiles (We achieved this by exporting some /sys and /proc paths through custom views and everything works as expected).

We're now adding functionality to temporarily blacklist hot standby clusters based on their WAL records lag and pg_xlog_location_diff() seems to be the key tool for this, but we're perhaps misusing it.

The current draft implementation uses the following queries and compares the output to determine how many bytes a given slave is lagging.
Is there any shortcoming to such approach?

--------------------------------
-- ON MASTER:
--------------------------------
SELECT
pg_xlog_location_diff(pg_current_xlog_location(), '000/00000000')
;
--------------------------------

That's very nonsensical; it will always return the same thing as pg_current_xlog_location.

--------------------------------
-- ON STANDBY:
--------------------------------
SELECT
pg_xlog_location_diff(
COALESCE(
pg_last_xlog_receive_location(),

Note that that is the xlog location that has been *sync'd to disk*. That could potentially lag significantly behind the master's LSN. I think your safest bet would be getting pg_current_xlog_location from the master and subtracting pg_last_xlog_replay_location() from it (but note you could get a negative result).

BTW, http://www.postgresql.org/docs/devel/static/warm-standby.html#STREAMING-REPLICATION says to use pg_last_xlog_receive_location() instead of pg_last_xlog_replay_location() because it tells you what's committed to disk on a standby vs what's visible. But for what you're doing I think you want pg_last_xlog_replay_location().

Also, I don't think you should coalesce. If you get a NULL for any of this then something's almost certainly wrong (like a server is misconfigured). If you were going to coalesce I'd say you should coalesce to 2^63-1.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 16/01/15 14:37, Jim Nasby wrote:

On 1/15/15 7:12 PM, Fabio Ugo Venchiarutti wrote:

Greetings

Our company is writing a small ad-hoc implementation of a load
balancer for Postgres (`version()` = PostgreSQL 9.2.9 on
x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red
Hat 4.4.7-4), 64-bit).

We're using both streaming and WAL shipping based replication.

Most mainstream solutions seem to implement load balancing with plain
round robin over a connection pool. Given that our cloud nodes are
diversely capable and subject to noisy neighborhood conditions, we
need to factor in instantaneous load profiles (We achieved this by
exporting some /sys and /proc paths through custom views and
everything works as expected).

We're now adding functionality to temporarily blacklist hot standby
clusters based on their WAL records lag and pg_xlog_location_diff()
seems to be the key tool for this, but we're perhaps misusing it.

The current draft implementation uses the following queries and
compares the output to determine how many bytes a given slave is lagging.
Is there any shortcoming to such approach?

--------------------------------
-- ON MASTER:
--------------------------------
SELECT
pg_xlog_location_diff(pg_current_xlog_location(), '000/00000000')
;
--------------------------------

That's very nonsensical; it will always return the same thing as
pg_current_xlog_location.

--------------------------------
-- ON STANDBY:
--------------------------------
SELECT
pg_xlog_location_diff(
COALESCE(
pg_last_xlog_receive_location(),

Note that that is the xlog location that has been *sync'd to disk*. That
could potentially lag significantly behind the master's LSN. I think
your safest bet would be getting pg_current_xlog_location from the
master and subtracting pg_last_xlog_replay_location() from it (but note
you could get a negative result).

BTW,
http://www.postgresql.org/docs/devel/static/warm-standby.html#STREAMING-REPLICATION
says to use pg_last_xlog_receive_location() instead of
pg_last_xlog_replay_location() because it tells you what's committed to
disk on a standby vs what's visible. But for what you're doing I think
you want pg_last_xlog_replay_location().

Also, I don't think you should coalesce. If you get a NULL for any of
this then something's almost certainly wrong (like a server is
misconfigured). If you were going to coalesce I'd say you should
coalesce to 2^63-1.

Thank you

--------------------------------
-- ON MASTER:
--------------------------------
SELECT
pg_xlog_location_diff(pg_current_xlog_location(), '000/00000000')
;
--------------------------------

That's very nonsensical; it will always return the same thing as
pg_current_xlog_location.

I know it seems odd but I'm just using the query itself to keep
everything in decimals as my safety threshold is expressed in bytes.

The question this query asks is "how many WAL bytes does this cluster
know about since the hypothetical record 000/00000000"?

Then I do the math.

The actual full form is the same for both master and standbys (already
changed based on your input):

--------------------------------
SELECT
pg_is_in_recovery() AS in_recovery,
pg_xlog_location_diff(
(CASE
WHEN (pg_is_in_recovery()) THEN
pg_last_xlog_replay_location()
ELSE
pg_current_xlog_location()
END),
'000/00000000'
) AS total_wal_offset
--------------------------------

says to use pg_last_xlog_receive_location() instead of
pg_last_xlog_replay_location() because it tells you what's committed to
disk on a standby vs what's visible. But for what you're doing I think
you want pg_last_xlog_replay_location().

You basically framed what my question boils down to.

I'm trying to get my head around
http://www.postgresql.org/docs/9.2/static/functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE

Does it mean that pg_last_xlog_receive_location() returns the last WAL
record that has been successfully "staged for replay" by the stream
replication whereas pg_last_xlog_replay_location() returns the last
successful WAL replay regardless of it coming from streaming or archive
shipping?
As in 2 different stages the first of which is basically irrelevant to
record visibility?

Many thanks

Fabio

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 1/15/15 8:41 PM, Fabio Ugo Venchiarutti wrote:

Does it mean that pg_last_xlog_receive_location() returns the last WAL record that has been successfully "staged for replay" by the stream replication whereas pg_last_xlog_replay_location() returns the last successful WAL replay regardless of it coming from streaming or archive shipping?
As in 2 different stages the first of which is basically irrelevant to record visibility?

(At least in -HEAD) receive is what's been committed to disk; replay is what's actually been replayed and should be visible.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general