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