Search Postgresql Archives

pg_last_xact_replay_timestamp() sometimes reports unlikely, very large delays

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi all,
I wondered if any experts can help me out?

I currently monitor Postgresql's replication status by looking at two metrics.
First I check to see if the current slave xlog replay is equal to the master 
-- if so, it's up to date.
If it's not equal, then I look at pg_last_xact_replay_timestamp().

I can compare this with now() to get a duration, which I believe should 
indicate how far behind the slave is tracking.

Most of the time this works quite well -- the slave might fall behind by some 
seconds under heavy load, but that's fine.

However, occasionally this replay timestamp will report times many hours or 
days behind! This goes on for a few minutes, then suddenly recovers.


My best guess for what is going on is:
 - There has been no activity for hours or days, and so the oldest replayed 
transaction on the slave is genuinely quite old.
 - Something has happened on the master that causes its 
pg_current_xlog_location() to be updated, but not in a way that is sent to the 
slave until the end of a long-running transaction.


Could anyone suggest how to do this in a manner that avoids the problem?


It's annoying because when it happens, because for 5-10 minutes monitoring 
alerts get fired off about catastrophic amounts of lag on the read-only slave!

Cheers
Toby


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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux