Proper use of pg_xlog_location_diff()

From: Fabio Ugo Venchiarutti <fabio(at)vuole(dot)me>
To: pgsql-general(at)postgresql(dot)org
Subject: Proper use of pg_xlog_location_diff()
Date: 2015-01-16 01:12:13
Message-ID: 54B8656D.1040006@vuole.me
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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')
;
--------------------------------

--------------------------------
-- ON STANDBY:
--------------------------------
SELECT
pg_xlog_location_diff(
COALESCE(
pg_last_xlog_receive_location(),
pg_last_xlog_replay_location()
),
'000/00000000'
)
;
--------------------------------

Thanks in advance

Fabio Ugo Venchiarutti

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jim Nasby 2015-01-16 01:18:12 Re: Need advice for handling big data in postgres
Previous Message Adrian Klaver 2015-01-16 01:02:06 Re: "Ungroup" data for import into PostgreSQL