Re: [HACKERS] Measuring replay lag
От | Thomas Munro |
---|---|
Тема | Re: [HACKERS] Measuring replay lag |
Дата | |
Msg-id | CAEepm=2ibk7LKme0TS2bPuajdYQYL9-dR2_vXix5JyBqeN3LrA@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: [HACKERS] Measuring replay lag (Ian Barwick <ian.barwick@2ndquadrant.com>) |
Список | pgsql-hackers |
On Wed, Mar 15, 2017 at 8:15 PM, Ian Barwick <ian.barwick@2ndquadrant.com> wrote: >> 2. Recognise when the last reported write/flush/apply LSN from the >> standby == end of WAL on the sending server, and show lag times of >> 00:00:00 in all three columns. I consider this entirely bogus: it's >> not an actual measurement that was ever made, and on an active system >> it would flip-flop between real measurements and the artificial >> 00:00:00. I do not like this. > > I agree with this; while initially I was expecting to see 00:00:00, > SQL NULL is definitely correct here. Anyone writing tools etc. which need to > report an actual interval can convert this to 00:00:00 easily enough . Right. Another point here is that if someone really wants to see "estimated time until caught up to current end of WAL" where 00:00:00 makes sense when fully replayed, then it is already possible to compute that using information that is published in pg_stat_replication in 9.6. An external tool or a plpgsql function could do something like: observe replay_location twice with a sleep in between to estimate the current rate of replay, then divide the current distance between replay location and end-of-WAL by the replay rate to estimate the time of arrival. I think the result would behave a bit like the infamous Windows file transfer dialogue ("3 seconds, not 7 months, no 4 seconds, no INFINITY, oh wait 0 seconds, you have arrived at your destination!") due to the lumpiness of replay, though perhaps that could be corrected by applying the right kind of smoothing to the rate. I thought about something like that but figured it would be better to stick to measuring facts about the past rather than making predictions about the future. That's on top of the more serious problem for the syncrep delay measurement use case, where I started this journey, that many systems would show zero whenever you query them because they often catch up in between writes, even though sync rep is not free. >> 5. The new proposal: Show only true measured write/flush/apply data, >> as in 1, but with a time limit. To avoid the scenario where we show >> the same times during prolonged periods of idleness, clear the numbers >> like in option 3 after a period of idleness. This way we avoid the >> dreaded flickering/flip-flopping. A natural time to do that is when >> wal_receiver_status_interval expires on idle systems and defaults to >> 10 seconds. >> >> Done using approach 5 in the attached version. Do you think this is a >> good compromise? No bogus numbers, only true measured >> write/flush/apply times, but a time limit on 'stale' lag information. > > This makes sense to me. I'd also add that while on production servers > it's likely there'll be enough activity to keep the columns updated, > on a quiescent test/development systems seeing a stale value looks plain > wrong (and will cause no end of questions from people asking why lag > is still showing when their system isn't doing anything). Cool, and now Simon has agreed to this too. > I suggest the documentation of these columns needs to be extended to mention > that they will be NULL if no lag was measured recently, and to explain > the circumstances in which the numbers are cleared. Done in the v7. Thanks for the feedback! -- Thomas Munro http://www.enterprisedb.com
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Craig RingerДата:
Сообщение: Re: [HACKERS] [PATCH] Transaction traceability - txid_status(bigint)
Следующее
От: Michael PaquierДата:
Сообщение: Re: [HACKERS] bug/oversight in TestLib.pm and PostgresNode.pm