max_standby_delay considered harmful
in [PgSql]
|
Prev: Further Hot Standby documentation required
Next: [HACKERS] Streaming replication - unable to stop the standby
From: Simon Riggs on 5 May 2010 16:49 On Wed, 2010-05-05 at 16:46 +0300, Heikki Linnakangas wrote: > Simon Riggs wrote: > > The attached patch redefines "standby delay" to be the amount of time > > elapsed from point of receipt to point of application. The "point of > > receipt" is reset every chunk of data when streaming, or every file when > > reading file by file. In all cases this new time is later than the > > latest log time we would have used previously. > > This seems completely wrong to me. If the WAL receiver keeps receiving > stuff, (last receive timestamp) - (current timestamp) would never be > more than a few seconds. Regardless of how much applying the WAL has > fallen behind. I see your point. > To accomplish what you're trying to accomplish, you would need to label > each received WAL record with the timestamp when it was received, and > compare the reception timestamp of the record you're applying against > current timestamp. Yes, OK. Obviously doing it for every record would be silly, so sampling WAL records is the only way. If we save the timestamp every 16MB of WAL that would work for both file and streaming. Of course, if WAL was written regularly none of this would be a problem. Why not have WALSender write a new WAL record with a timestamp every X seconds? -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Simon Riggs on 5 May 2010 16:52 On Wed, 2010-05-05 at 16:58 +0300, Heikki Linnakangas wrote: > Let's have a setting similar to > statement_timeout, that specifies how long a statement is allowed to > run until it becomes subject to killing if it conflicts with recovery > (actually, it would have to be a per-transaction setting, at least in > serializable mode). This would be similar to Tom's proposal, and it > would have the same drawback that it would give no guarantee on how > much the standby can fall behind. However, it would be easier to > understand: > a query gets to run for X seconds, and after that it will be killed if > it gets in the way. If you want this, I have no problem with you getting this (though new feature alert sirens going off, presumably). I only have a problem with the suggestion that this replaces the current max_standby_delay. There is no good case for only a single option. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Simon Riggs on 5 May 2010 16:51 On Wed, 2010-05-05 at 06:23 -0400, Robert Haas wrote: > On Wed, May 5, 2010 at 3:16 AM, Simon Riggs <simon(a)2ndquadrant.com> wrote: > > Expect at least 3 commits from me over next few days. > > I think you need to rethink the way that you decide when it's time to > commit things. There is certainly no consensus on any of the things > you are proposing to commit, nor have they been adequately (or, uh, at > all) reviewed. Saying that your proposal addresses all of Tom's > objections doesn't make it so. I am planning to read that patch and > offer an opinion on it, but I haven't done so yet and I imagine Tom > will weigh in at some point as well. Racing to commit a pile of code > that nobody else has tested is not going to improve anything. Only you have spoken of a race to commit and I have not said I would refuse to listen to you or others. Reading your words, it would be easy to forget we are a team of people whose aim is software development. It's not the OK Corral. Yesterday you berated me for unstable software. Today you oppose my promise to fix that. Why is it, we all wonder, is it that you oppose everything I say and do? No doubt you will oppose other committers in the way you oppose me... -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Josh Berkus on 5 May 2010 18:29 Heikki, all, > There's currently three ways to set max_standby_delay: > > max_standby_delay = -1 # Query wins > max_standby_delay = 0 # Recovery wins > max_standby_delay > X # Query wins until lag > X. > > As Tom points out, the 3rd option has all sorts of problems. I very much > like the behavior that max_standby_delay tries to accomplish, but I have > to agree that it's not very reliable as it is. Wow, thanks for the summary. Based on that, I take back what I said to Greg. Because I think getting 9.0 out *on time* is more important than any of these issues, I'm revising my opinion to be more in line with Greg Smith. So, proposed path forwards. (1) We work on getting the specific bugs Tom reported fixed. (2) max_standby_delay default is 0 (3) documentation covers setting it to an integer, but warns extensively about the required sysadminning and query cancel. As in "for advanced users only". (4) discussion of other synch methods gets shifted to 9.0 Ultimately, I think we'll be going to something lock-based like what Tom suggested. However, I don't think that's doable without delaying 9.0 for 6 months, and I think that would be much worse than any current bug with 9.0. No matter how much we tinker with HS/SR, it's not going to be bulletproof until 9.1. Or, more likely, 9.2. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Bruce Momjian on 5 May 2010 19:18
Heikki Linnakangas wrote: > Tom Lane wrote: > > Comments? > > There's currently three ways to set max_standby_delay: > > max_standby_delay = -1 # Query wins > max_standby_delay = 0 # Recovery wins > max_standby_delay > X # Query wins until lag > X. > > As Tom points out, the 3rd option has all sorts of problems. I very much > like the behavior that max_standby_delay tries to accomplish, but I have > to agree that it's not very reliable as it is. I don't like Tom's > proposal either; the standby can fall behind indefinitely, and queries > get a varying grace period. > > Let's rip out the concept of a delay altogether, and make it a boolean. > If you really want your query to finish, set it to -1 (using the current > max_standby_delay nomenclature). If recovery is important to you, set it > to 0. > > If you have the monitoring in place to sensibly monitor the delay > between primary and standby, and you want a limit on that, you can put > together a script to flip the switch in postgresql.conf if the standby > falls too much behind. > > It would be nice to make that settable per-session, BTW. Though as soon > as you have one session using -1, the standby could fall behind. Still, > it might be useful if you run both kinds of queries on the same standby. +1 for a boolean We are not supposed to be designing the behavior during beta, which is exactly what we are doing, and I don't think we even know what behavior we want, let alone have we implemented it. I think a boolean is very clear and it gives you the chance to optimize _one_ case, which is enough for 9.0. Let's revisit this for 9.1 when we will know a lot more than we do now. Once 9.1 reports slave snapshots back to the master, we might not need anything more than a boolean here anyway. -- Bruce Momjian <bruce(a)momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |