pg_last_xact_replay_timestamp() sometimes reports unlikely, very large delays
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Mar 22, 2017, at 8:06 PM, Toby Corkindale <toby.corkindale@strategicdata.com.au> wrote:
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?
Are you using streaming replication or only WAL archiving? If you are not streaming the archive command does not send the file until it is full (16MB, if I recall correctly). To address this, you can change the archive_timeout setting to ensure the WAL file is sent at some interval even if it is not full.
I use 'archive_timeout = 300' to send it every 5 minutes. If the lag is greater than 15 minutes, the alarm bells start going off.
John DeSoi, Ph.D.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
----- Original Message -----
On Mar 22, 2017, at 8:06 PM, Toby Corkindale
<toby.corkindale@strategicdata.com.au> wrote: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?
Are you using streaming replication or only WAL archiving? If you are not
streaming the archive command does not send the file until it is full (16MB,
if I recall correctly). To address this, you can change the archive_timeout
setting to ensure the WAL file is sent at some interval even if it is not
full.
Apologies, I should have mentioned. We're using streaming replication.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hello everyone
Can these forums be moved to internet ?
All these emails is so 1990s.
So hard to follow, so hard to search for historical answers.
We really need to be able to post via browser.
best regards to everyone
Hi
2017-03-25 5:49 GMT+01:00 Yuri Budilov <yuri.budilov@hotmail.com>:
Hello everyone
Can these forums be moved to internet ?
It is on internet
All these emails is so 1990s.
And it is working well - there is not spam and ballast
So hard to follow, so hard to search for historical answers.
We really need to be able to post via browser.
why?
there is a fulltexted archive
https://www.postgresql.org/list/group/2/
https://www.postgresql.org/list/pgsql-general/
You can use http://stackoverflow.com/ too
Regards
Pavel
Show quoted text
best regards to everyone
On 3/24/2017 9:49 PM, Yuri Budilov wrote:
Hello everyone
Can these forums be moved to internet ?
go ahead and start a forum. good luck.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 3/24/2017 11:27 PM, John R Pierce wrote:
On 3/24/2017 9:49 PM, Yuri Budilov wrote:
Hello everyone
Can these forums be moved to internet ?go ahead and start a forum. good luck.
sorry, that was a little terse. I tried to move a couple thriving
communities I'd created as email lists to forums. the communities died.
--
john r pierce, recycling bits in santa cruz
John R Pierce <pierce@hogranch.com> writes:
On 3/24/2017 9:49 PM, Yuri Budilov wrote:
Can these forums be moved to internet ?
go ahead and start a forum. good luck.
Think it's been done already, multiple times ... nabble and stackoverflow
already provide forums that are loosely linked to the PG mailing lists.
And probably some others that I'm not remembering at the moment.
They are uniformly unfriendly when viewed from this end of the
relationship. nabble for instance reposts stuff into the mailing lists
that is missing critical portions. stackoverflow doesn't seem to think
they have any responsibility to give back at all.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Sat, Mar 25, 2017 at 04:49:33AM +0000, Yuri Budilov wrote:
Can these forums be moved to internet ?
Last I looked, SMTP generally works on the Internet. You mean "the
web", which is a part of but not all of the Internet.
So hard to follow, so hard to search for historical answers.
Why do you find it hard to follow? The list is pretty well-ordered
and tends to thread nicely, so you can use any threaded mail reader
(or reader in threaded mode) and you should have some success. I note
from the headers on your mail that you appear to be using both MS
Exchange and MS Outlook. I haven't looked since the early 2000s, so
things might be better, but I recall Outlook having really bad
threading support at the time. Maybe you need to switch to gmail for
reading the postgres lists. It threads ok, and meets your stated
requirement of being in a browser.
As for historical answers, I'm not exactly sure what you want but you
will find at https://www.postgresql.org/list/ a "search archives"
function and an "advanced" search function at
https://www.postgresql.org/search/?m=1. It's as good as most online
forum search tools I've ever used, though not as good as Google.
Which, I note, works well too.
We really need to be able to post via browser.
Why? What does "post via browser" get you that sending an email
doesn't? I can think of somethign it does _not_ get you, however, and
that is the attention of some of the key contributors to Postgres, who
appear to work mostly in a mode where email makes things easy for them
and logging into a new forum tool makes things harder.
Best regards,
A
--
Andrew Sullivan
ajs@crankycanuck.ca
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Friday, March 24, 2017, Yuri Budilov <yuri.budilov@hotmail.com> wrote:
Hello everyone
Can these forums be moved to internet ?
All these emails is so 1990s.
So hard to follow, so hard to search for historical answers.
We really need to be able to post via browser.
One constraint is that the current email based flow cannot be disrupted.
Adding on a fully integrated forum-like GUI does have some demand. I don't
see it being met if the it needs to be custom written. Therefore, do you
have any suggestions and examples of communities using such a system?
As it stands we have a kind of read-only forum in our archives. It would
be nice if there were a form at the bottom of the page that would let you
post - or even "send email to me so I can respond".
In short, there is always room for usability improvements. You'll get a
better response if you focus on those since and explain why.
David J.
On 25/03/17 16:25, David G. Johnston wrote:
One constraint is that the current email based flow cannot be
disrupted. Adding on a fully integrated forum-like GUI does have some
demand. I don't see it being met if the it needs to be custom
written. Therefore, do you have any suggestions and examples of
communities using such a system?As it stands we have a kind of read-only forum in our archives. It
would be nice if there were a form at the bottom of the page that
would let you post - or even "send email to me so I can respond".In short, there is always room for usability improvements. You'll get
a better response if you focus on those since and explain why.David J.
google groups would support both methods of access imho
I'm not suggesting its a good thing - perfectly happy with just email
myself and searching the list archives if need be.
Tim
Attachments:
On Saturday, March 25, 2017, Tim Clarke <tim.clarke@manifest.co.uk> wrote:
On 25/03/17 16:25, David G. Johnston wrote:
One constraint is that the current email based flow cannot be
disrupted. Adding on a fully integrated forum-like GUI does have some
demand. I don't see it being met if the it needs to be custom
written. Therefore, do you have any suggestions and examples of
communities using such a system?As it stands we have a kind of read-only forum in our archives. It
would be nice if there were a form at the bottom of the page that
would let you post - or even "send email to me so I can respond".In short, there is always room for usability improvements. You'll get
a better response if you focus on those since and explain why.David J.
google groups would support both methods of access imho
I'm not suggesting its a good thing - perfectly happy with just email
myself and searching the list archives if need be.
Becomming reliant on a third-party host is a non-starter. Nothing about
the present system is so deficient that status-quo would be a less
appealing option.
David J.
On Sat, 25 Mar 2017 16:37:00 +0000, Tim Clarke
<tim.clarke@manifest.co.uk> wrote:
On 25/03/17 16:25, David G. Johnston wrote:
One constraint is that the current email based flow cannot be
disrupted. Adding on a fully integrated forum-like GUI does have some
demand. I don't see it being met if the it needs to be custom
written. Therefore, do you have any suggestions and examples of
communities using such a system?As it stands we have a kind of read-only forum in our archives. It
would be nice if there were a form at the bottom of the page that
would let you post - or even "send email to me so I can respond".In short, there is always room for usability improvements. You'll get
a better response if you focus on those since and explain why.David J.
google groups would support both methods of access imho
I'm not suggesting its a good thing - perfectly happy with just email
myself and searching the list archives if need be.
Google Groups rather routinely screws up message attribution so you
don't know to whom a GG user was responding. Also [IME, anyway] GG
users tend to forget to expand and quote the material to which they
are responding because in the web UI they can just look back and see
it.
Trying to follow a group through email, or by way of a list<>news
bridge, it is easy to lose the flow of a discussion when some of its
participants are on Google.
That said ...
I also dislike having my email full of list posts. I prefer to follow
groups and lists through NNTP (net news) whenever possible. Many
(all?) of the Postgresql lists are available via Gmane[*]. For
historical (hysterical?) reasons, I use Forte Agent as my reader on
Windows, but Thunderbird and SeaMonkey both work well for news and are
cross platform.
YMMV,
George
[*] Gmane is one of the list<>news bridges. They currently are in the
midst of rehosting and changing maintainers. The NNTP servers
(news.gmane.org) are working, but the web site (http://gmane.org/) is
offline.
No account is necessary to use Gmane itself, but membership in a list
is needed to post messages. Join with the same email address used by
your NN reader to access Gmane.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 3/25/2017 10:31 PM, George Neuner wrote:
I also dislike having my email full of list posts.
I have a 'postgres' folder in Thunderbird, and all posts "To:" or CC:
pgsql-*@postgresql.org get moved there, that way they are together, and
don't clutter my other email. I have similar folders for the various
other email lists I'm on.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Sun, Mar 26, 2017 at 2:50 PM, John R Pierce <pierce@hogranch.com> wrote:
On 3/25/2017 10:31 PM, George Neuner wrote:
I also dislike having my email full of list posts.
I have a 'postgres' folder in Thunderbird, and all posts "To:" or CC:
pgsql-*@postgresql.org get moved there, that way they are together, and
don't clutter my other email. I have similar folders for the various
other email lists I'm on.
If you have subscribed to more mailing lists than -general, having one
subfolder per list can also help a lot, grouping as well some of those
having a low activity, for example:
- one folder for -hackers and -hackers-cluster.
- one folder for -general.
- one folder for -jdbc and -odbc.
- one for -bugs and -docs.
- one for -jobs and -announce, etc.
Something like that will make your hacking activity way easier to
handle. I would bet that a lot of people around here do that.
--
Michael
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Mon, 27 Mar 2017 11:31:02 +0900
Michael Paquier <michael.paquier@gmail.com> wrote:
If you have subscribed to more mailing lists than -general, having one
subfolder per list can also help a lot, grouping as well some of those
having a low activity, for example:
- one folder for -hackers and -hackers-cluster.
- one folder for -general.
- one folder for -jdbc and -odbc.
- one for -bugs and -docs.
- one for -jobs and -announce, etc.
Something like that will make your hacking activity way easier to
handle. I would bet that a lot of people around here do that.
I sure do. I have a heck of a lot of email in a heck of a lot of
folders, all stored in a nice, easy to drill down hierarchy. That
hierarchy is maintained by the Dovecot IMAP server that runs on my
desktop computer.
On every successful mailing list, somebody inevitably suggests
replacing it with "a forum" or "a facebook page" or some
proprietary website that acts as a middleman (Google, Meetup and
Linkedin are three of the usual suspects). Such suggestions usually go
nowhere, and when they're followed, communication usually ceases and
the the community becomes a ghost town. When it comes to having a
lively group discussion that focuses all minds into a supermind greater
than the sum of the parts, a mailing list is the best tool. Especially
if those who use it trim properly and make sure they're not being
ambiguous.
Another mailing list benefit: Most of these other types of "community
communicators" sooner or later disappear from the Internet, just like
mailing lists. But with mailing lists, individuals can keep their own
archives. I have archives from my first LUG, even though that LUG's
mailing list went defunct in 2002.
Because my email folder hierarchy was designed by me, I can usually
find emails of any age very quickly. Responding is as easy as replying
to an email.
One assertion in the original post was that email communication is "so
1990's". That's neither a compliment nor an insult, and has prompted me
to write an essay, for which I'll provide the URL when it's finished.
Bottom line though, don't mess with success.
SteveT
Steve Litt
March 2017 featured book: Troubleshooting: Why Bother?
http://www.troubleshooters.com/twb
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Mon, Mar 27, 2017 at 4:23 PM, Steve Litt <slitt@troubleshooters.com>
wrote:
On Mon, 27 Mar 2017 11:31:02 +0900
<snip>
On every successful mailing list, somebody inevitably suggests
replacing it with "a forum" or "a facebook page" or some
proprietary website that acts as a middleman (Google, Meetup and
Linkedin are three of the usual suspects). Such suggestions usually go
nowhere, and when they're followed, communication usually ceases and
the the community becomes a ghost town. When it comes to having a
lively group discussion that focuses all minds into a supermind greater
than the sum of the parts, a mailing list is the best tool. Especially
if those who use it trim properly and make sure they're not being
ambiguous.
Very true. The main reason that I like email is that I get an easy to see
notification about a new message. And I only need to go to _one_ place to
find it.
Another mailing list benefit: Most of these other types of "community
communicators" sooner or later disappear from the Internet, just like
mailing lists. But with mailing lists, individuals can keep their own
archives. I have archives from my first LUG, even though that LUG's
mailing list went defunct in 2002.
so, you're saying you're a LUG nut? [ I just couldn't stop myself. It's a
disease! ]
Because my email folder hierarchy was designed by me, I can usually
find emails of any age very quickly. Responding is as easy as replying
to an email.
Another plus.
One assertion in the original post was that email communication is "so
1990's". That's neither a compliment nor an insult, and has prompted me
to write an essay, for which I'll provide the URL when it's finished.
Agree. I guess the OP doesn't every use paper or pen/pencil for anything.
Bottom line though, don't mess with success.
'cause you'll end up a mess!
SteveT
Steve Litt
March 2017 featured book: Troubleshooting: Why Bother?
http://www.troubleshooters.com/twb
--
"Irrigation of the land with seawater desalinated by fusion power is
ancient. It's called 'rain'." -- Michael McClary, in alt.fusion
Maranatha! <><
John McKown
On 2017-03-27 23:23, Steve Litt wrote:
On Mon, 27 Mar 2017 11:31:02 +0900
Michael Paquier <michael.paquier@gmail.com> wrote:If you have subscribed to more mailing lists than -general, having one
subfolder per list can also help a lot, grouping as well some of those
having a low activity, for example:
- one folder for -hackers and -hackers-cluster.
- one folder for -general.
- one folder for -jdbc and -odbc.
- one for -bugs and -docs.
- one for -jobs and -announce, etc.
Something like that will make your hacking activity way easier to
handle. I would bet that a lot of people around here do that.I sure do. I have a heck of a lot of email in a heck of a lot of
folders, all stored in a nice, easy to drill down hierarchy. That
hierarchy is maintained by the Dovecot IMAP server that runs on my
desktop computer.
I'm not against mailinglists at all, but I am for ease of use,
especially for newcomers.
Every time I tell someone about the mailinglists I then have to explain
how they can subscribe, how to create folders, filters etc. And more
often than not
they just say forget it and go to some forum.
When it comes to having a
lively group discussion that focuses all minds into a supermind greater
than the sum of the parts, a mailing list is the best tool.
Well, in the end, it's not the fact that it's a mailinglist that makes
the community great,
it's just the fact that the active members share a methodof
communication that they all like to use.
Getting notifications of new messages is probably the single most
important feature to keep discussions going
and email provides that.
The thing is; mailinglists are far from userfiendly if you are not used
to them.
Even in this thread several people have explained how much work they
have done to get it
into a state where they can easily work with it. Can you expect Joe
Average to do something like that
if they want to get more involved in PgSQL?
Now, I'm not saying the mailinglists should go, I'm saying there should
be an easier way
to access them. It should be possible to register on the site, post a
message and read replies,
without having to subscribe to the list and setup a way of dealing with
the influx of messages
that are, for the most post, simply not interesting to the average user.
I'd love to have an RSS feed that contains only new questions, so I can
just watch the popup
on my screen the way I do with the rest of the world, and not have to
deal with replies to topics that I don't care about anyway.
And yes, I can probably setup my email to do something like that, the
point is that I shouldn't have to.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
vinny schrieb am 04.04.2017 um 12:01:
I'd love to have an RSS feed that contains only new questions, so I can just watch the popup
on my screen the way I do with the rest of the world, and not have to deal with replies to topics that I don't care about anyway.
You can read them as a newsgroup provided by news.gmane.org - I do it that way.
Thunderbird works quite well with that.
Thomas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Greetings,
* vinny (vinny@xs4all.nl) wrote:
The thing is; mailinglists are far from userfiendly if you are not
used to them.
Even in this thread several people have explained how much work they
have done to get it
into a state where they can easily work with it. Can you expect Joe
Average to do something like that
if they want to get more involved in PgSQL?
I don't actually feel that it's really all that unreasonable, no. I've
explained that we use mailing lists to a few different groups at
different colleges I've spoken at and while there's been a bit of
grousing from a few individuals, I don't recall anyone not knowing what
a mailing list is or having all that bad of a reaction.
I'll be speaking later this month again at GMU, so I'll make it a point
to discuss it with the group there.
Now, I'm not saying the mailinglists should go, I'm saying there
should be an easier way
to access them. It should be possible to register on the site, post
a message and read replies,
without having to subscribe to the list and setup a way of dealing
with the influx of messages
that are, for the most post, simply not interesting to the average user.
I don't think there's anyone who is particularly against that idea, but
it's far from trivial to do and to address the possible spam which will
result from that. All of the website code is open-source and
improvements to it would be greatly welcomed, as long as they don't
create a significant increase in the maintenance burden for the pginfra
team.
I'd love to have an RSS feed that contains only new questions, so I
can just watch the popup
on my screen the way I do with the rest of the world, and not have
to deal with replies to topics that I don't care about anyway.
I don't see any reason, off-hand at least, that this couldn't be
provided. We already provide RSS feeds for other things and it's
reasonably straight-forward. Replying to the RSS feed would require
an email client though, at least for now. Perhaps that could be
integrated into the 'whole mbox' download option though or something
along those lines so you can pull the email/thread into your client
easily to reply.
And yes, I can probably setup my email to do something like that,
the point is that I shouldn't have to.
I'm all for improving things and adding automation where it'll help, but
the infrastructure is basically run by volunteers. Making statements
like "I shouldn't have to" isn't the best approach to getting the
changes you'd like to see happen done.
Thanks!
Stephen
