max_standby_delay considered harmful
in [PgSql]
|
Prev: Further Hot Standby documentation required
Next: [HACKERS] Streaming replication - unable to stop the standby
From: Robert Haas on 5 May 2010 20:23 On Wed, May 5, 2010 at 7:18 PM, Bruce Momjian <bruce(a)momjian.us> wrote: > 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. The existing behavior is probably not optimal, but I'm not seeing what benefit we get out of neutering it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 20:29 Robert Haas wrote: > >> 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. > > The existing behavior is probably not optimal, but I'm not seeing what > benefit we get out of neutering it. We get to design it right, or maybe not need it at all in 9.1. -- 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
From: Tom Lane on 5 May 2010 20:36 Bruce Momjian <bruce(a)momjian.us> writes: > Robert Haas wrote: >> The existing behavior is probably not optimal, but I'm not seeing what >> benefit we get out of neutering it. > We get to design it right, or maybe not need it at all in 9.1. Yeah. The good thing about a boolean is that it covers the two noncontroversial cases (no-wait and wait forever), and doesn't lock us into supporting cases that we don't really know how to do well yet. regards, tom lane -- 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: Greg Smith on 5 May 2010 21:17 Heikki Linnakangas wrote: > 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. > So the only user options would be "allow long-running queries to block WAL application forever" and "always cancel queries on conflict?" That would be taking away the behavior I was going to suggest as the default to many customers I work with. I expect a non-trivial subset of people using this feature will set max_standby_delay to is some small number of minutes, similarly to how archive_timeout is sized now. Enough time to get reasonably sized queries executed, not so long as to allow something that might try to run for hours on the standby to increase failover catchup time very much. The way the behavior works is admittedly limited, and certainly some people are going to want to set it to either 0 or -1. But taking it away altogether is going to cripple one category of potential Hot Standby use in the field. Consider this for a second: do you really think that Simon would have waded into this coding mess, or that I would have spent as much energy as I have highlighting issues with its use, if there wasn't demand for it? If it wouldn't hurt the usefulness of PostgreSQL 9.0 significantly to cut it, I'd have suggested that myself two months ago and saved everyone (especially myself) a lot of trouble. > 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. > There's a couple of things you should do in order for max_standby_delay to working as well as it can. Watching clock sync and forcing periodic activity are two of them that always come up. Those are both trivial to script for, and something I wouldn't expect any admin to object to. If you need a script that involves changing a server setting to do something, that translates into "you can't do that" for a typical DBA. The idea of a program regularly changing a server configuration setting on a production system is one you just can't sell. That makes this idea incredibly more difficult to use in the field than any of the workarounds that cope with the known max_standby_delay issues. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg(a)2ndQuadrant.com www.2ndQuadrant.us -- 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: Tom Lane on 5 May 2010 21:36
Greg Smith <greg(a)2ndquadrant.com> writes: > Heikki Linnakangas wrote: >> Let's rip out the concept of a delay altogether, and make it a boolean. > So the only user options would be "allow long-running queries to block > WAL application forever" and "always cancel queries on conflict? Got it in one. Obviously, this is something that would be high priority to improve in some fashion in 9.1. That doesn't mean that it's reasonable to drop in a half-baked redesign now, nor to put in the amount of work that would be required to have a really well-designed implementation, and most certainly not to uncritically ship what we've got. We have a ton of other work that has to be done to get 9.0 out the door, and this feature is something that IMO we can live without for this release. One reason I believe this isn't so critical as all that is that it only matters for cases where the operation on the master took an exclusive lock. In high-performance production scenarios that's something you try hard to avoid anyway. When you succeed, the standby behavior is moot. Even if you can't avoid exclusive locks entirely, you may be able to confine them to maintenance windows where performance doesn't matter so much ... and then that goes for the standby performance as well. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |