Re: 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