Re: Proper use of pg_xlog_location_diff() - Mailing list pgsql-general

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
onx86_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
ourcloud nodes are diversely capable and subject to noisy neighborhood conditions, we need to factor in instantaneous
loadprofiles (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
givenslave 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'sLSN. 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
ona 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
(likea 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