[HACKERS] Hot Standby query cancellation and Streaming Replication integration
On Mon, 2010-03-01 at 16:12 -0400, pgsql-hackers-ow...@postgresql.org wrote: . . . However there is a concern with max_standby_age. If you set it to, say, 300s. Then run a 300s query on the slave which causes the slave to fall 299s behind. Now you start a new query on the slave -- it gets a snapshot based on the point in time that the slave is currently at. If it hits a conflict it will only have 1s to finish before the conflict causes the query to be cancelled. In short in the current setup I think there is no safe value of max_standby_age which will prevent query cancellations short of -1. If the slave has a constant stream of queries and always has at least one concurrent query running then it's possible that the slave will run continuously max_standby_age-epsilon behind the master and cancel queries left and right, regardless of how large max_standby_age is. To resolve this I think you would have to introduce some chance for the slave to catch up. Something like refusing to use a snapshot older than max_standby_age/2 and instead wait until the existing queries finish and the slave gets a chance to catch up and see a more recent snapshot. The problem is that this would result in very unpredictable and variable response times from the slave. A single long-lived query could cause replay to pause for a big chunk of max_standby_age and prevent any new query from starting. Does anyone see any way to guarantee that the slave gets a chance to replay and new snapshots will become visible without freezing out new queries for extended periods of time? At the risk of looking foolish, I have a hand-wavy, unlikely-to-be-possible, not-going-to-make-it-for-9.0, and maybe unoriginal idea that I'll share. As Greg has identified, no matter what max_standby_age you select, a sequence of overlapping queries will eventually exceed the max_standby_delay threshold, and tuples that your query depends on would then be modified underneath you. IIUC this is only a problem for WAL from HOT updates and vacuums. If no vacuums or HOT updates have been performed, there is no risk of returning bad data. So WAL that does not contain HOT updates or vacuums could be applied on the standby without risk, even if there are long-running queries in play. This is not a complete solution but may reduce the likelihood of queries having to be cancelled. I guess the approach here would be to check WAL before applying it, and only cancel queries if the WAL contains HOT updates or vacuums. Taking the idea further, if WAL records contained the tid of the latest tuples that were overwritten, even more WAL could be applied without having to cancel queries. To take it further still, if vacuum on the master could be prevented from touching records that are less than max_standby_delay seconds old, it would be safe to apply WAL from the very latest vacuum. I guess HOT could be handled similarly though that may eliminate much of the advantage of HOT updates. Apologies if this has already been covered, some of this discussion went over my head. /action Puts on asbestos underwear __ Marc -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
On 3/2/10 12:47 PM, Marc Munro wrote: To take it further still, if vacuum on the master could be prevented from touching records that are less than max_standby_delay seconds old, it would be safe to apply WAL from the very latest vacuum. I guess HOT could be handled similarly though that may eliminate much of the advantage of HOT updates. Aside from the inability to convert between transcation count and time, isn't this what vacuum_defer_cleanup_age is supposed to do? Or does it not help with HOT updates? --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
On Tue, 2010-03-02 at 12:47 -0800, Marc Munro wrote: IIUC this is only a problem for WAL from HOT updates and vacuums. If no vacuums or HOT updates have been performed, there is no risk of returning bad data. So WAL that does not contain HOT updates or vacuums could be applied on the standby without risk, even if there are long-running queries in play. This is not a complete solution but may reduce the likelihood of queries having to be cancelled. I guess the approach here would be to check WAL before applying it, and only cancel queries if the WAL contains HOT updates or vacuums. That's what we do. Taking the idea further, if WAL records contained the tid of the latest tuples that were overwritten, even more WAL could be applied without having to cancel queries. To take it further still, if vacuum on the master could be prevented from touching records that are less than max_standby_delay seconds old, it would be safe to apply WAL from the very latest vacuum. I guess HOT could be handled similarly though that may eliminate much of the advantage of HOT updates. Thanks for your ideas. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
On Fri, 2010-02-26 at 16:44 -0500, Tom Lane wrote: Greg Stark gsst...@mit.edu writes: On Fri, Feb 26, 2010 at 9:19 PM, Tom Lane t...@sss.pgh.pa.us wrote: There's *definitely* not going to be enough information in the WAL stream coming from a master that doesn't think it has HS slaves. We can't afford to record all that extra stuff in installations for which it's just useless overhead. BTW, has anyone made any attempt to measure the performance hit that the patch in its current form is creating via added WAL entries? What extra entries? Locks, just for starters. I haven't read enough of the code yet to know what else Simon added. In the past it's not been necessary to record any transient information in WAL, but now we'll have to. There is room for technical confusion here, so I'll just add some info. There was/is potential for performance hit because of the volume of additional WAL *and* the processing overhead from that additional WAL. As Heikki points out these turn out to be minimal, though this has been by careful design. There is also potential for a performance hit because incoming cleanup records may conflict with currently executing queries. If we knew for certain that the master was not sending any cleanup records that would effect current standby queries we could avoid that overhead altogether. That's a good reason for integrating a solution. AccessExclusiveLock lock records are nothing at all to do with that. They exist simply to prevent obvious correctness issues such as somebody reading a file while it is being deleted. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
On Fri, 2010-02-26 at 03:33 -0500, Greg Smith wrote: I really hope this discussion can say focused on if and how it's possible to improve this area, with the goal being to deliver a product everyone can be proud of with the full feature set that makes this next release a killer one. The features that have managed to all get into this release already are fantastic, everyone who contributed should be proud of that progress, and it's encouraging that the alpha4 date was nailed. It would be easy to descend into finger-pointing for why exactly this particular problem is only getting more visibility now, or into schedule-oriented commentary suggesting it must be ignored because it's too late to do anything about it. I hope everyone appreciates wandering that way will not help make PostgreSQL 9.0 a better release. This issue is so easy to encounter, and looks so bad when it happens, that I feel it could easily lead to an embarrassing situation for the community if something isn't done about it before release. Thanks Greg. It's a great relief for me to hear someone else say this and to watch a discussion about this important issue unfold. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Dimitri Fontaine wrote: Bruce Momjian br...@momjian.us writes: Doesn't the system already adjust the delay based on the length of slave transactions, e.g. max_standby_delay. It seems there is no need for a user switch --- just max_standby_delay really high. Well that GUC looks like it allows to set a compromise between HA and reporting, not to say do not ever give the priority to the replay while I'm running my reports. At least that's how I understand it. max_standby_delay=-1 does that. The documentation needs to be updated to reflect that, it currently says: There is no wait-forever setting because of the potential for deadlock which that setting would introduce. This parameter can only be set in the postgresql.conf file or on the server command line. but that is false, -1 means wait forever. Simon removed that option at one point, but it was later put back and apparently the documentation was never updated. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Heikki Linnakangas wrote: Dimitri Fontaine wrote: Bruce Momjian br...@momjian.us writes: Doesn't the system already adjust the delay based on the length of slave transactions, e.g. max_standby_delay. It seems there is no need for a user switch --- just max_standby_delay really high. Well that GUC looks like it allows to set a compromise between HA and reporting, not to say do not ever give the priority to the replay while I'm running my reports. At least that's how I understand it. max_standby_delay=-1 does that. The documentation needs to be updated to reflect that, it currently says: There is no wait-forever setting because of the potential for deadlock which that setting would introduce. This parameter can only be set in the postgresql.conf file or on the server command line. but that is false, -1 means wait forever. Simon removed that option at one point, but it was later put back and apparently the documentation was never updated. I've put back the mention of max_standby_delay=-1 option in the docs. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Greg Smith wrote: Joshua D. Drake wrote: On Sat, 27 Feb 2010 00:43:48 +, Greg Stark gsst...@mit.edu wrote: I want my ability to run large batch queries without any performance or reliability impact on the primary server. +1 I can use any number of other technologies for high availability. Remove must be an instant-on failover at the same time from the requirements and you don't even need 9.0 to handle that, this has been a straightforward to solve problem since 8.2. It's the combination of HA and queries that make things hard to do. If you just want batch queries on another system without being concerned about HA at the same time, the first option is to just fork the base backup and WAL segment delivery to another server and run queries there. That is a lot of administrative overhead. It is hard to say it is equivalent to HS. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Greg, If you think of it in those terms, the idea that you need to run PITR backup/archive recovery to not get that behavior isn't an important distinction anymore. If you run SR with the option enabled you could get it, any other setup and you won't. +1. I always expected that we'd get this kind of behavior with synch in 9.1. I can see that there are two desired modes of behaviour depending on what the replication config is: 1) Master full-speed, degraded operation on slaves: this is the current wal_standby_delay approach. It has the advantage of supporting possibly hundreds of slaves, and certainly dozens. 2) Master burdened, full operation on slaves: this is the publish-xmin-back-to-master approach, which IIRC the core team first discussed at pgCon 2008 before Simon started work, and which you and Tom seem to think can be done soon. I can see people wanting to use either mode depending on their use-case. Or, for that matter, using both modes to different slaves. Now that I think about it, the xmin thing really doesn't seem conceptually difficult. If the slave just opens a 2nd, special query connection back to the master and publishes its oldest xmin there, as far as the master is concerned, it's just another query backend. Could it be that easy? Also, I'm serious about what I suggested earlier for delay mode. We should have an option for cancelled queries to be immediately retried, if that's feasible. It would turn something which is now a major application design issue (lots of cancelled queries) into just degrated performance. Overall, though, I'd say that getting 9.0 out the door relatively on-time is more important than getting it perfect. Release early, release often isn't just a mantra; it's a very good idea if you want your project to keep improving and not bog down and fall apart. --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Josh Berkus wrote: Now that I think about it, the xmin thing really doesn't seem conceptually difficult. If the slave just opens a 2nd, special query connection back to the master and publishes its oldest xmin there, as far as the master is concerned, it's just another query backend. Could it be that easy? Something just like that is in fact already suggested as a workaround in the Hot Standby manual: The first option is to connect to the primary server and keep a query active for as long as needed to run queries on the standby. This guarantees that a WAL cleanup record is never generated and query conflicts do not occur, as described above. This could be done using contrib/dblink and pg_sleep(), or via other mechanisms. And the idea of doing it mainly in client land has its attractions. The main reason I wandered toward asking about it in the context of SR is that there's already this open Standby delay on idle system issue with Hot Standby, and the suggested resolution for that problem involves publishing keep-alive data with timestamps over SR. While all these problems and potential solutions have been floating around for a long time, as you pointed out, the little flash of insight I had here was that it's possible to bundle these two problems together with a combined keep-alive timestamp+xmin message that goes in both directions. That removes one serious Hot Standby issue altogether, and adds an additional conflict avoidance mechanism for people who want to enable it, all with something that needs to get done sooner or later anyway for sync rep. The part I still don't have good visibility on is how much of the necessary SR infrastructure needed to support this communications channel is already available in some form. I had though the walsender on the master was already receiving messages sometimes from the walreceiver on the standby, but I'm getting the impression from Heikki's comments that this not the case at all yet. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
The part I still don't have good visibility on is how much of the necessary SR infrastructure needed to support this communications channel is already available in some form. I had though the walsender on the master was already receiving messages sometimes from the walreceiver on the standby, but I'm getting the impression from Heikki's comments that this not the case at all yet. I don't think asking for a 2nd connection back from the standby to the master would be horrible for 9.0. I think it would be quite reasonable, actually; even with 2 connections per slave, you could still have quite a few slaves before the # of connections bogged down the master. --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Thank you for combining a small personal attack with a selfish commentary about how yours is the only valid viewpoint. Saves me a lot of trouble replying to your messages, can just ignore them instead if this is how you're going to act. Hey, take it easy! I read Stark's post as tongue-in-cheek, which I think it was. Though, Stark, if you're going to be flip, I'd suggest using a smiley next time. --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Josh Berkus wrote: Hey, take it easy! I read Stark's post as tongue-in-cheek, which I think it was. Yeah, I didn't get that. We've already exchanged mutual off-list apologies for the misunderstanding in both directions, I stopped just short of sending flowers. I did kick off this discussion with noting a clear preference this not wander into any personal finger-pointing. And I am far too displeased with the technical situation here to have much of a sense of humor left about it either. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Greg Smith wrote: Josh Berkus wrote: Now that I think about it, the xmin thing really doesn't seem conceptually difficult. If the slave just opens a 2nd, special query connection back to the master and publishes its oldest xmin there, as far as the master is concerned, it's just another query backend. Could it be that easy? Something just like that is in fact already suggested as a workaround in the Hot Standby manual: The first option is to connect to the primary server and keep a query active for as long as needed to run queries on the standby. This guarantees that a WAL cleanup record is never generated and query conflicts do not occur, as described above. This could be done using contrib/dblink and pg_sleep(), or via other mechanisms. I am unclear how you would easily advance the snapshot as each query completes on the slave. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Bruce Momjian wrote: The first option is to connect to the primary server and keep a query active for as long as needed to run queries on the standby. This guarantees that a WAL cleanup record is never generated and query conflicts do not occur, as described above. This could be done using contrib/dblink and pg_sleep(), or via other mechanisms. I am unclear how you would easily advance the snapshot as each query completes on the slave. The idea of the workaround is that if you have a single long-running query to execute, and you want to make sure it doesn't get canceled because of a vacuum cleanup, you just have it connect back to the master to keep an open snapshot the whole time. That's basically the same idea that vacuum_defer_cleanup_age implements, except you don't have to calculate a value--you just hold open the snapshot to do it. When that query ended, its snapshot would be removed, and then the master would advance to whatever the next latest one is. Nothing fancier than that. The only similarity is that if you made every query that happened on the standby do that, it would effectively be the same behavior I'm suggesting could be available via the standby-master xmin publication. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Greg Stark wrote: On Sun, Feb 28, 2010 at 5:28 AM, Greg Smith g...@2ndquadrant.com wrote: The idea of the workaround is that if you have a single long-running query to execute, and you want to make sure it doesn't get canceled because of a vacuum cleanup, you just have it connect back to the master to keep an open snapshot the whole time. Also, I'm not sure this actually works. When your client makes this additional connection to the master it's connecting at some transaction in the future from the slave's point of view. The master could have already vacuumed away some record which the snapshot the client gets on the slave will have in view. Right, and there was an additional comment in the docs alluding to some sleep time on the master that intends to try and improve thins. If you knew how long archive_timeout was you could try to sleep longer than it to try and increase your odds of avoiding an ugly spot. But there are race conditions galore possible here, particularly if your archiver or standby catchup is backlogged. Still it's a handy practical trick even if it isn't 100% guaranteed to work. But I don't think it provides the basis for something we can bake in. Agreed on both counts, which is why it's in the current docs as a workaround people can consider, but not what I've been advocating as the right way to proceed. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Hot Standby query cancellation and Streaming Replication integration
I'm happy to see we've crossed the point where the worst of the Hot Standby and Streaming Replication issues are sorted out. A look at the to-do lists: http://wiki.postgresql.org/wiki/Hot_Standby_TODO http://wiki.postgresql.org/wiki/Streaming_Replication show no Must-fix items and 5 Serious Issues for Hot Standby left; there are 9 Streaming Replication items there, which aren't as clearly prioritized yet. Correct me if I'm wrong here, but those read to me like tweaks and polishing rather than major architecture issues at this point, so I believe that code is the same position as HS: some serious issues, but no really terrible parts. The job Simon asked me to take a look at starting last week is which of the listed HS Serious Issues might be promoted into must-fix items after seeing how easy they were to encounter. There are a number of HS tunables that interact with one another, and depending your priorities a few ways you can try to optimize the configuration for what I expect to be common use cases for this feature. I've written a blog entry at http://blog.2ndquadrant.com/en/2010/02/tradeoffs-in-hot-standby-deplo.html that tries to explain all that background clearly, and relate the implementation details to how I expect DBAs will perceive them. That was a bit much to also cover here, and had a broader audience that might appreciate it than just this list. Attached is a tar file with some test case demo scripts that demonstrate the worst of the problems here IMHO. A README in there outlines how to set the problem demo up (presuming you've already gotten a HS pair going). What this does is execute the following sequence continuously on the master: UPDATE pgbench_tellers SET tbalance = tbalance + delta WHERE tid = tid; (several times) VACUUM pgbench_tellers; Meanwhile, on the standby, the following long query runs on a few sessions at once, again looping constantly: SELECT sum(abalance) FROM pgbench_accounts; It took a bit of testing to get the database scale and iteration times here to easily encounter the issue here on my system, I hope this shows up easily enough for others with the values used. (I have a similar work in progress demo that tries to trigger the b-tree deletion problem too, will follow up once the storm of messages about this topic dies down, as I think this is a pre-requisite for it anyway) I'm not sure what you might be expecting from the above combination, but what actually happens is that many of the SELECT statements on the table *that isn't even being updated* are canceled. You see this in the logs: LOG: restored log file 000100A5 from archive ERROR: canceling statement due to conflict with recovery DETAIL: User query might have needed to see row versions that must be removed. STATEMENT: SELECT sum(abalance) FROM pgbench_accounts; Basically, every time a WAL segment appears that wipes out a tuple that SELECT expects should still be visible, because the dead row left behind by the update has been vacuumed away, the query is canceled. This happens all the time the way I've set this up, and I don't feel like this is a contrived demo. Having a long-running query on the standby while things get updated and then periodically autovacuumed on the primary is going to be extremely common in the sorts of production systems I expect want HS the most. Now, as explained on the blog entry and in the documentation, there are all sorts of ways you can work around this issue by tweaking parameters or doing fun tricks with dblink. You can prioritize any two of keeping the standby current, letting long-running queries execute on the standby, and keeping xid advances on the master moving forward as fast as possible. But you can't get all three at once. The choices available are really about the best you can do given a system that's basically the old warm-standby approach, improved with adding just Hot Standby to the mix. Sure, you might make the conflict resolution a bit smarter or make the UI for setting the parameters more friendly, and there's already been plenty of argument and patching over all of that. I don't want to belittle that work because it's been important to make HS a useful standalone feature, but I feel like that's all triage rather than looking for the most robust fix possible. If you're running a system that also is using Streaming Replication, there is a much better approach possible. This idea has been floating around for a while and I am not taking credit for inventing it (too busy tonight to dig into the archives to figure out exactly when this popped up initially and who deserves credit for it). I'm just pointing out that now is the time where it's actually possible to implement. The HS TODO already includes the following action item, to resolve a serious issue you can run into (that itself would be great to eliminate): Requires keep-alives with timestamps
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Greg Smith wrote: Attached is a tar file with some test case demo scripts that demonstrate the worst of the problems here IMHO. Thanks for that! We've been discussing this for ages, so it's nice to have a concrete example. I don't want to belittle that work because it's been important to make HS a useful standalone feature, but I feel like that's all triage rather than looking for the most robust fix possible. Ideally the standby would stash away the old pages or tuples somewhere so that it can still access them even after replaying the WAL records that remove them from the main storage. I realize that's not going to happen any time soon because it's hard to do, but that would really be the most robust fix possible. I don't know how difficult the keepalive feature was expected to be, and there's certainly plenty of potential landmines in this whole xid export idea. One such landmine is that the keepalives need to flow from client to server while the WAL records are flowing from server to client. We'll have to crack that problem for synchronous replication too, but I think that alone is a big enough problem to make this 9.1 material. How to handle situations where the standby goes away for a while, such as a network outage, so that it doesn't block the master from ever cleaning up dead tuples is a concern. Yeah, that's another issue that needs to be dealt with. You'd probably need some kind of a configurable escape valve in the master, to let it ignore a standby's snapshot once it gets too old. But I do know that the current Hot Standby implementation is going to be frustrating to configure correctly for people. Perhaps others who are not as deep into the code as I am will have a better view on this, but I seriously don't think that's such a big issue. I think the max_standby_delay setting is quite intuitive and easy to explain. Sure, it would better if there was no tradeoff between killing queries and stalling recovery, but I don't think it'll be that hard to understand the tradeoff. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
On 26/02/10 08:33, Greg Smith wrote: There are a number of HS tunables that interact with one another, and depending your priorities a few ways you can try to optimize the configuration for what I expect to be common use cases for this feature. I've written a blog entry at http://blog.2ndquadrant.com/en/2010/02/tradeoffs-in-hot-standby-deplo.html that tries to explain all that background clearly, It did too. Thanks for the nice summary people can be pointed at. I'm not sure what you might be expecting from the above combination, but what actually happens is that many of the SELECT statements on the table *that isn't even being updated* are canceled. You see this in the logs: Hmm - this I'd already figured out for myself. It's just occurred to me that this could well be the case between databases too. Database A gets vacuumed, B gets its queries kicked off on the standby. Granted lots of people just have the one main DB, but even so... LOG: restored log file 000100A5 from archive ERROR: canceling statement due to conflict with recovery DETAIL: User query might have needed to see row versions that must be removed. STATEMENT: SELECT sum(abalance) FROM pgbench_accounts; Basically, every time a WAL segment appears that wipes out a tuple that SELECT expects should still be visible, because the dead row left behind by the update has been vacuumed away, the query is canceled. This happens all the time the way I've set this up, and I don't feel like this is a contrived demo. Having a long-running query on the standby while things get updated and then periodically autovacuumed on the primary is going to be extremely common in the sorts of production systems I expect want HS the most. I can pretty much everyone wanting HS+SR. Thousands of small DBs running on VMs for a start. Free mostly-live backup? Got to be a winner. Dumb non-hacker question: why do we cancel all transactions rather than just those with ACCESS SHARE on the vacuumed table in question? Is it the simple fact that we don't know what table this particular section of WAL affects, or is it the complexity of tracking all this info? If you're running a system that also is using Streaming Replication, there is a much better approach possible. Requires keep-alives with timestamps to be added to sync rep feature If those keep-alives flowed in both directions, and included both timestamps *and* xid visibility information, the master could easily be configured to hold open xid snapshots needed for long running queries on the standby when that was necessary. Presumably meaning we need *another* config setting to prevent excessive bloat on a heavily updated table on the master. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
On 26/02/10 14:10, Heikki Linnakangas wrote: Ideally the standby would stash away the old pages or tuples somewhere so that it can still access them even after replaying the WAL records that remove them from the main storage. I realize that's not going to happen any time soon because it's hard to do, but that would really be the most robust fix possible. Something like snapshotting a filesystem, so updates continue while you're still looking at a static version. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Richard Huxton wrote: On 26/02/10 08:33, Greg Smith wrote: I'm not sure what you might be expecting from the above combination, but what actually happens is that many of the SELECT statements on the table *that isn't even being updated* are canceled. You see this in the logs: Hmm - this I'd already figured out for myself. It's just occurred to me that this could well be the case between databases too. Database A gets vacuumed, B gets its queries kicked off on the standby. No, it's per-database already. Only queries in the same database are canceled. Dumb non-hacker question: why do we cancel all transactions rather than just those with ACCESS SHARE on the vacuumed table in question? Is it the simple fact that we don't know what table this particular section of WAL affects, or is it the complexity of tracking all this info? The problem is that even if transaction X doesn't have an (access share) lock on the vacuumed table at the moment, it might take one in the future. Simon proposed mechanisms for storing the information about vacuumed tables in shared memory, so that if X takes the lock later on it will get canceled at that point, but that's 9.1 material. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
On 26/02/10 14:45, Heikki Linnakangas wrote: Richard Huxton wrote: On 26/02/10 08:33, Greg Smith wrote: I'm not sure what you might be expecting from the above combination, but what actually happens is that many of the SELECT statements on the table *that isn't even being updated* are canceled. You see this in the logs: Hmm - this I'd already figured out for myself. It's just occurred to me that this could well be the case between databases too. Database A gets vacuumed, B gets its queries kicked off on the standby. No, it's per-database already. Only queries in the same database are canceled. That's a relief. Dumb non-hacker question: why do we cancel all transactions rather than just those with ACCESS SHARE on the vacuumed table in question? Is it the simple fact that we don't know what table this particular section of WAL affects, or is it the complexity of tracking all this info? The problem is that even if transaction X doesn't have an (access share) lock on the vacuumed table at the moment, it might take one in the future. Simon proposed mechanisms for storing the information about vacuumed tables in shared memory, so that if X takes the lock later on it will get canceled at that point, but that's 9.1 material. I see - we'd need to age the list of vacuumed tables too, so when the oldest transactions complete the correct flags get cleared. Can we not wait to cancel the transaction until *any* new lock is attempted though? That should protect all the single-statement long-running transactions that are already underway. Aggregates etc. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Richard Huxton wrote: Can we not wait to cancel the transaction until *any* new lock is attempted though? That should protect all the single-statement long-running transactions that are already underway. Aggregates etc. Hmm, that's an interesting thought. You'll still need to somehow tell the victim backend you have to fail if you try to acquire any more locks, but a single per-backend flag in the procarray would suffice. You could also clear the flag whenever you free the last snapshot in the transaction (ie. between each query in read committed mode). -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Replying to my own post - first sign of madness... Let's see if I've got the concepts clear here, and hopefully my thinking it through will help others reading the archives. There are two queues: 1. Cleanup on the master 2. Replay on the slave Running write queries on the master adds to both queues. Running (read-only) queries on the slave prevents you removing from both queues. There are two interesting measurements of age/size: 1. Oldest item in / length of queue (knowable) 2. How long will it take to clear the queue (estimable at best) You'd like to know #2 to keep up with your workload. Unfortunately, you can't for certain unless you have control over new incoming queries (on both master and slave). You might want four separate GUCs for the two measurements on the two queues. We currently have two that (sort of) match #1 Oldest item (vacuum_defer_cleanup_age, max_standby_delay). Delaying replay on a slave has no effect on the master. If a slave falls too far behind it's responsible for catch-up (via normal WAL archives). There is no point in delaying cleanup on the master unless it's going to help one or more slaves. In fact, you don't want to start delaying cleanup until you have to, otherwise you're wasting your delay time. This seems to be the case with vacuum_defer_cleanup_age. If I have a heavily-updated table and I defer vacuuming then when any given query starts on the slave it's going to be half used up already. There's also no point in deferring cleanup on the master if the standby is already waiting on a conflict that will cause its queries to be cancelled anyway. Not only won't it help, but it might make things worse since transactions will be cancelled, the conflict will be replayed and (presumably) queries will be re-submitted only to be cancelled again. This is what Greg Smith's discussion of the keep-alives was about. Giving the master enough information to be smarter about cleanup (and making the conflicts more fine-grained). The situation with deferring on one or both ends of process just gets more complicated with multiple slaves. There's all sorts of unpleasant feedback loops I can envisage there. For the case of single slave being used to run long reporting queries the ideal scenario would be the following. Master starts deferring vacuum activity just before the query starts. When that times out, the slave will receive the cleanup info, refuse to replay it and start its delay. This gives you a total available query time of: natural time between vacuums + vacuum delay + WAL transfer time + standby delay I can think of five useful things we should be doing (and might be already - don't know). 1. On the master, deduce whether the slave is already waiting on a query. If so, don't bother delaying cleanup. Clearly you don't want to be signalling hundreds of times a second though. Does the slave pause fetching via streaming replication if replay is blocked on a query? Could we signal half-way to max-age or some such? 2. Perhaps simpler than trying to make the master smarter, just allow SET this_transaction_is_probably_a_long_one=true on the slave. That (a) clears the queue on the slave and (b) sends the signal to the master which then starts deferring vacuum. 3. Do a burst of cleanup activity on the master after blocking. This should concentrate conflicts together when they reach the slave. Perhaps vacuum_defer_cleanup_age should be vacuum_deferred_queue_size and measure the amount of work to do, rather than the max age of the oldest cleanup (if I've understood correctly). 4. Do a burst of replay on the slave after blocking. Perhaps every time it cancels a transaction it should replay at least half the queued WAL before letting new transactions start. Or perhaps it replays any vacuum activity it comes across and then stops. That should sync with #2 assuming the slave doesn't lag the master too much. 5. I've been mixing defer and delay, as do the docs. We should probably settle on one or the other. I think defer conveys the meaning more precisely, but what about non-native English speakers? -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
On Fri, Feb 26, 2010 at 10:21 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Richard Huxton wrote: Can we not wait to cancel the transaction until *any* new lock is attempted though? That should protect all the single-statement long-running transactions that are already underway. Aggregates etc. Hmm, that's an interesting thought. You'll still need to somehow tell the victim backend you have to fail if you try to acquire any more locks, but a single per-backend flag in the procarray would suffice. You could also clear the flag whenever you free the last snapshot in the transaction (ie. between each query in read committed mode). Wow, that seems like it would help a lot. Although I'm not 100% sure I follow all the details of how this works. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Heikki Linnakangas wrote: How to handle situations where the standby goes away for a while, such as a network outage, so that it doesn't block the master from ever cleaning up dead tuples is a concern. Yeah, that's another issue that needs to be dealt with. You'd probably need some kind of a configurable escape valve in the master, to let it ignore a standby's snapshot once it gets too old. But I do know that the current Hot Standby implementation is going to be frustrating to configure correctly for people. Perhaps others who are not as deep into the code as I am will have a better view on this, but I seriously don't think that's such a big issue. I think the max_standby_delay setting is quite intuitive and easy to explain. Sure, it would better if there was no tradeoff between killing queries and stalling recovery, but I don't think it'll be that hard to understand the tradeoff. Let's look at the five documented cases of query conflict (from our manual): 1 Access Exclusive Locks from primary node, including both explicit LOCK commands and various DDL actions 2 Dropping tablespaces on the primary while standby queries are using those tablespaces for temporary work files (work_mem overflow) 3 Dropping databases on the primary while users are connected to that database on the standby. 4 The standby waiting longer than max_standby_delay to acquire a buffer cleanup lock. 5 Early cleanup of data still visible to the current query's snapshot We might have a solution to #1 by only cancelling queries that try to take locks. #2 and #3 seem like rare occurances. #4 can be controlled by max_standby_delay, where a large value only delays playback during crash recovery --- again, a rare occurance. #5 could be handled by using vacuum_defer_cleanup_age on the master. Why is vacuum_defer_cleanup_age not listed in postgresql.conf? In summary, I think passing snapshots to the master is not something possible for 9.0, and ideally we will never need to add that feature. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
On 2/26/10 6:57 AM, Richard Huxton wrote: Can we not wait to cancel the transaction until *any* new lock is attempted though? That should protect all the single-statement long-running transactions that are already underway. Aggregates etc. I like this approach. Is it fragile in some non-obvious way? --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Greg Stark gsst...@mit.edu writes: On Fri, Feb 26, 2010 at 7:16 PM, Tom Lane t...@sss.pgh.pa.us wrote: I don't see a substantial additional burden there. What I would imagine is needed is that the slave transmits a single number back --- its current oldest xmin --- and the walsender process publishes that number as its transaction xmin in its PGPROC entry on the master. And when we want to support cascading slaves? So? Fits right in. The walsender on the first-level slave is advertising an xmin from the second-level one, which will be included in what's passed back up to the master. Or when you want to bring up a new slave and it suddenly starts advertising a new xmin that's older than the current oldestxmin? How's it going to do that, when it has no queries at the instant of startup? But in any case if I were running a reporting database I would want it to just stop replaying logs for a few hours while my big batch report runs, not cause the master to be unable to vacuum any dead records for hours. That defeats much of the purpose of running the queries on the slave. Well, as Heikki said, a stop-and-go WAL management approach could deal with that use-case. What I'm concerned about here is the complexity, reliability, maintainability of trying to interlock WAL application with slave queries in any sort of fine-grained fashion. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Well, as Heikki said, a stop-and-go WAL management approach could deal with that use-case. What I'm concerned about here is the complexity, reliability, maintainability of trying to interlock WAL application with slave queries in any sort of fine-grained fashion. This sounds a bit brute-force, but what about simply having some form of automatic query retry on the slave? --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Greg Stark gsst...@mit.edu writes: Why shouldn't it have any queries at walreceiver startup? It has any xlog segments that were copied from the master and any it can find in the archive, it could easily reach a consistent point long before it needs to connect to the master. If you really want to protect your master from any additional overhead you don't currently need to configure a streaming connection at all, you can just use the file shipping interface. There's *definitely* not going to be enough information in the WAL stream coming from a master that doesn't think it has HS slaves. We can't afford to record all that extra stuff in installations for which it's just useless overhead. BTW, has anyone made any attempt to measure the performance hit that the patch in its current form is creating via added WAL entries? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
bruce wrote: 4 The standby waiting longer than max_standby_delay to acquire a ... #4 can be controlled by max_standby_delay, where a large value only delays playback during crash recovery --- again, a rare occurance. One interesting feature is that max_standby_delay will _only_ delay if it it encounters a conflict when applying WAL, meaning only if there are long-running transactions. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Tom Lane t...@sss.pgh.pa.us writes: Well, as Heikki said, a stop-and-go WAL management approach could deal with that use-case. What I'm concerned about here is the complexity, reliability, maintainability of trying to interlock WAL application with slave queries in any sort of fine-grained fashion. Some admin functions for Hot Standby were removed from the path to ease its integration, there was a pause() and resume() feature. I think that offering this explicit control to the user would allow them to choose between HA setup and reporting setup easily enough: just pause the replay when running the reporting, resume it to get fresh data again. If you don't pause, any query can get killed, replay is the priority. Now as far as the feedback loop is concerned, I guess the pause() function would cause the slave to stop publishing any xmin in the master's procarray so that it's free to vacuum and archive whatever it wants to. Should the slave accumulate too much lag, it will resume from the archive rather than live from the SR link. How much that helps? Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Greg Stark gsst...@mit.edu writes: On Fri, Feb 26, 2010 at 9:19 PM, Tom Lane t...@sss.pgh.pa.us wrote: There's *definitely* not going to be enough information in the WAL stream coming from a master that doesn't think it has HS slaves. We can't afford to record all that extra stuff in installations for which it's just useless overhead. BTW, has anyone made any attempt to measure the performance hit that the patch in its current form is creating via added WAL entries? What extra entries? Locks, just for starters. I haven't read enough of the code yet to know what else Simon added. In the past it's not been necessary to record any transient information in WAL, but now we'll have to. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Dimitri Fontaine wrote: Tom Lane t...@sss.pgh.pa.us writes: Well, as Heikki said, a stop-and-go WAL management approach could deal with that use-case. What I'm concerned about here is the complexity, reliability, maintainability of trying to interlock WAL application with slave queries in any sort of fine-grained fashion. Some admin functions for Hot Standby were removed from the path to ease its integration, there was a pause() and resume() feature. I think that offering this explicit control to the user would allow them to choose between HA setup and reporting setup easily enough: just pause the replay when running the reporting, resume it to get fresh data again. If you don't pause, any query can get killed, replay is the priority. Doesn't the system already adjust the delay based on the length of slave transactions, e.g. max_standby_delay. It seems there is no need for a user switch --- just max_standby_delay really high. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Bruce Momjian br...@momjian.us writes: Doesn't the system already adjust the delay based on the length of slave transactions, e.g. max_standby_delay. It seems there is no need for a user switch --- just max_standby_delay really high. Well that GUC looks like it allows to set a compromise between HA and reporting, not to say do not ever give the priority to the replay while I'm running my reports. At least that's how I understand it. The feedback loop might get expensive on master server when running reporting queries on the slave, unless you can pause it explicitly I think. I don't see how the system will guess that you're running a reporting server rather than a HA node, and max_standby_delay is just a way to tell the standby to please be nice in case of abuse. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Dimitri Fontaine wrote: Bruce Momjian br...@momjian.us writes: Doesn't the system already adjust the delay based on the length of slave transactions, e.g. max_standby_delay. It seems there is no need for a user switch --- just max_standby_delay really high. Well that GUC looks like it allows to set a compromise between HA and reporting, not to say do not ever give the priority to the replay while I'm running my reports. At least that's how I understand it. Well, if you set it high, it effectively is that. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Bruce Momjian wrote: Doesn't the system already adjust the delay based on the length of slave transactions, e.g. max_standby_delay. It seems there is no need for a user switch --- just max_standby_delay really high. The first issue is that you're basically saying I don't care about high availability anymore when you increase max_standby_delay to a high value. Want to offload an 8 hour long batch report every day to the standby? You can do it with max_standby_delay=8 hours. But the day your master crashes 7 hours into that, you're in for a long wait before your standby is available while it replays all the queued up segments. Your 'hot standby' has actually turned into the old form of 'cold standby' just when you need it to be responsive. This is also the reason why the whole pause recovery idea is a fruitless path to wander down. The whole point of this feature is that people have a secondary server available for high-availability, *first and foremost*, but they'd like it to do something more interesting that leave it idle all the time. The idea that you can hold off on applying standby updates for long enough to run seriously long reports is completely at odds with the idea of high-availability. The second major problem is that the day the report actually takes 8.1 hours instead, because somebody else ran another report that slowed you down a little, you're screwed if that's something you depend on being available--it just got canceled only *after* wasting 8 hours of reporting resource time. max_standby_delay is IMHO only useful for allowing non-real-time web-app style uses of HS (think Facebook status updates), where you say I'm OK giving people slightly out of date info sometimes if it lets me split the query load over two systems. Set max_standby_delay to a few seconds or maybe a minute, enough time to service a typical user query, make your app tolerate the occasional failed query and only send big ones to the master, and you've just scaled up all the small ones. Distributed queries with eventual consistency on all nodes is where many of the web app designs are going, and this feature is a reasonable match for that use case. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Bruce Momjian wrote: 5 Early cleanup of data still visible to the current query's snapshot #5 could be handled by using vacuum_defer_cleanup_age on the master. Why is vacuum_defer_cleanup_age not listed in postgresql.conf? I noticed that myself and fired off a corrective patch to Simon yesterday, he said it was intentional but not sure why that is yet. We'll sort that out. You are correct that my suggestion is targeting primarily #5 on this list. There are two problems with the possible solutions using that parameter though: -vacuum_defer_cleanup_age is set in a unit that people cannot be expected to work in--transactions ids. The UI is essentially useless, and there's no obvious way how to make a better one. The best you can do will still be really fragile. -If you increase vacuum_defer_cleanup_age, it's active all the time. You're basically making every single transaction that could be cleaned up pay for the fact that a query *might* be running on the standby it needs to avoid. You can think of the idea of passing an xmin back from the standby as being like an auto-tuning vacuum_defer_cleanup_age. It's 0 when no standby queries are running, but grows in size to match longer ones. And you don't have to have to know anything to set it correctly; just toggle on the proposed feedback xid from the standby feature and you're safe. Expecting that anyone will ever set vacuum_defer_cleanup_age correctly in the field in its current form is pretty unreasonable I think. Since there's no timestamp-based memory of past xid activity, it's difficult to convert it to that form instead, and I think something in terms of time is what people would like to set this in. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Greg Smith wrote: Bruce Momjian wrote: Doesn't the system already adjust the delay based on the length of slave transactions, e.g. max_standby_delay. It seems there is no need for a user switch --- just max_standby_delay really high. The first issue is that you're basically saying I don't care about high availability anymore when you increase max_standby_delay to a high value. Want to offload an 8 hour long batch report every day to the standby? You can do it with max_standby_delay=8 hours. But the day your master crashes 7 hours into that, you're in for a long wait before your standby is available while it replays all the queued up segments. Your 'hot standby' has actually turned into the old form of 'cold standby' just when you need it to be responsive. Well, I think the choice is either you delay vacuum on the master for 8 hours or pile up 8 hours of WAL files on the slave, and delay application, and make recovery much slower. It is not clear to me which option a user would prefer because the bloat on the master might be permanent. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
On Fri, Feb 26, 2010 at 11:56 PM, Greg Smith g...@2ndquadrant.com wrote: This is also the reason why the whole pause recovery idea is a fruitless path to wander down. The whole point of this feature is that people have a secondary server available for high-availability, *first and foremost*, but they'd like it to do something more interesting that leave it idle all the time. The idea that you can hold off on applying standby updates for long enough to run seriously long reports is completely at odds with the idea of high-availability. Well you can go sit in the same corner as Simon with your high availability servers. I want my ability to run large batch queries without any performance or reliability impact on the primary server. You can have one or the other but you can't get both. If you set max_standby_delay low then you get your high availability server, if you set it high you get a useful report server. If you build sync replication which we don't have today and which will open another huge can of usability worms when we haven't even finish bottling the two we've already opened then you lose the lack of impact on the primary. Suddenly the queries you run on the slaves cause your production database to bloat. Plus you have extra network connections which take resources on your master and have to be kept up at all times or you lose your slaves. I think the design constraint of not allowing any upstream data flow is actually very valuable. Eventually we'll have it for sync replication but it's much better that we've built things incrementally and can be sure that nothing really depends on it for basic functionality. This is what allows us to know that the slave imposes no reliability impact on the master. It's what allows us to know that everything will work identically regardless of whether you have a walreceiver running or are running off archived log files. Remember I wanted to entirely abstract away the walreciever and allow multiple wal communication methods. I think it would make more sense to use something like Spread to distribute the logs so the master only has to send them once and as many slaves as you want can pick them up. The current architecture doesn't scale very well if you want to have hundreds of slaves for one master. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Greg Smith wrote: You can think of the idea of passing an xmin back from the standby as being like an auto-tuning vacuum_defer_cleanup_age. It's 0 when no standby queries are running, but grows in size to match longer ones. And you don't have to have to know anything to set it correctly; just toggle on the proposed feedback xid from the standby feature and you're safe. Yes, there is no question there is value in passing the xid back to the master. My point is that it is like your blog entry: http://blog.2ndquadrant.com/en/2010/02/tradeoffs-in-hot-standby-deplo.html you can't have all the options: 1. agressive vacuum on master 2. fast recovery of slave 3. no query cancel on slave Again, pick two. Passing the xid back to the master gives you #2 and #3, and that might be good for some people, but not others. Do we have any idea which options most administrators would want? If we get xid passback to the master, do we keep the other configuration options? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Greg Stark wrote: Well you can go sit in the same corner as Simon with your high availability servers. I want my ability to run large batch queries without any performance or reliability impact on the primary server. Thank you for combining a small personal attack with a selfish commentary about how yours is the only valid viewpoint. Saves me a lot of trouble replying to your messages, can just ignore them instead if this is how you're going to act. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
On Sat, Feb 27, 2010 at 1:53 AM, Greg Smith g...@2ndquadrant.com wrote: Greg Stark wrote: Well you can go sit in the same corner as Simon with your high availability servers. I want my ability to run large batch queries without any performance or reliability impact on the primary server. Thank you for combining a small personal attack with a selfish commentary about how yours is the only valid viewpoint. Saves me a lot of trouble replying to your messages, can just ignore them instead if this is how you're going to act. Eh? That's not what I meant at all. Actually it's kind of the exact opposite of what I meant. What I meant was that your description of the High Availability first and foremost is only one possible use case. Simon in the past expressed the same single-minded focus on that use case. It's a perfectly valid use case and I would probably agree if we had to choose just one it would be the most important. But we don't have to choose just one. There are other valid use cases such as load balancing and isolating your large batch queries from your production systems. I don't want us to throw out all these other use cases because we only consider high availability as the only use case we're interested in. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Bruce Momjian wrote: Well, I think the choice is either you delay vacuum on the master for 8 hours or pile up 8 hours of WAL files on the slave, and delay application, and make recovery much slower. It is not clear to me which option a user would prefer because the bloat on the master might be permanent. But if you're running the 8 hour report on the master right now, aren't you already exposed to a similar pile of bloat issues while it's going? If I have the choice between sometimes queries will get canceled vs. sometimes the master will experience the same long-running transaction bloat issues as in earlier versions even if the query runs on the standby, I feel like leaning toward the latter at least leads to a problem people are used to. This falls into the principle of least astonishment category to me. Testing the final design for how transactions get canceled here led me to some really unexpected situations, and the downside for a mistake is your query is lost. Had I instead discovered that sometimes long-running transactions on the standby can ripple back to cause a maintenance slowdown on the master, that's not great. But it would not have been so surprising, and it won't result in lost query results. I think people will expect that their queries cancel because of things like DDL changes. And the existing knobs allow inserting some slack for things like locks taking a little bit of time to acquire sometimes. What I don't think people will see coming is that a routine update on an unrelated table is going to kill a query they might have been waiting hours for the result of, just because that update crossed an autovacuum threshold for the other table and introduced a dead row cleanup. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Heikki Linnakangas wrote: One such landmine is that the keepalives need to flow from client to server while the WAL records are flowing from server to client. We'll have to crack that problem for synchronous replication too, but I think that alone is a big enough problem to make this 9.1 material. This seems to be the real sticking point then, given that the xmin/PGPROC side on the master seems logically straightforward. For some reason I thought the sync rep feature had the reverse message flow already going, and that some other sort of limitation just made it impractical to merge into the main codebase this early. My hope was that just this particular part could get cherry-picked out of there, and that it might even have been thought about already in that context given the known HS keepalive serious issue. If there was a solution or partial solution in progress to that floating around, my thought was that just piggybacking this extra xid info on top of it would be easy enough. If there's not already a standby to primary communications backchannel implementation available that can be harvested from that work, your suggestion that this may not be feasible at all for 9.0 seems like a more serious concern than I had thought it was going to be. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Greg Smith wrote: Heikki Linnakangas wrote: One such landmine is that the keepalives need to flow from client to server while the WAL records are flowing from server to client. We'll have to crack that problem for synchronous replication too, but I think that alone is a big enough problem to make this 9.1 material. This seems to be the real sticking point then, given that the xmin/PGPROC side on the master seems logically straightforward. For some reason I thought the sync rep feature had the reverse message flow already going, and that some other sort of limitation just made it impractical to merge into the main codebase this early. My hope was that just this particular part could get cherry-picked out of there, and that it might even have been thought about already in that context given the known HS keepalive serious issue. If there was a solution or partial solution in progress to that floating around, my thought was that just piggybacking this extra xid info on top of it would be easy enough. If there's not already a standby to primary communications backchannel implementation available that can be harvested from that work, your suggestion that this may not be feasible at all for 9.0 seems like a more serious concern than I had thought it was going to be. I suspect the master could connect to the slave to pull an xid. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Greg Stark wrote: Eh? That's not what I meant at all. Actually it's kind of the exact opposite of what I meant. Sorry about that--I think we just hit one of those language usage drift bits of confusion. Sit in the corner has a very negative tone to it in US English and I interpreted your message badly as a result. A Google search for images using that phrase will quickly show you what I mean. What I meant was that your description of the High Availability first and foremost is only one possible use case. Simon in the past expressed the same single-minded focus on that use case. It's a perfectly valid use case and I would probably agree if we had to choose just one it would be the most important. Sure, there are certainly others, and as much as possible more flexibility here is a good thing. What I was suggesting is that if the only good way to handle long-running queries has no choice but to sacrifice high-availability, which is is the situation if max_standby_delay is the approach you use, then the most obvious users for this feature are not being well served by that situation. I would guess a large portion of the users looking forward to Hot Standby are in the have an underutilized high-availability standby I'd like to use for offloading long running reports, and if there is no way to serve them well this feature is missing the mark a bit. You really can't do any better without better master/standby integration though, and as pointed out a couple of times here that was considered and just not followed through on yet. I'm increasingly concerned that nothing else will really do though. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
On Sat, Feb 27, 2010 at 2:43 AM, Greg Smith g...@2ndquadrant.com wrote: But if you're running the 8 hour report on the master right now, aren't you already exposed to a similar pile of bloat issues while it's going? If I have the choice between sometimes queries will get canceled vs. sometimes the master will experience the same long-running transaction bloat issues as in earlier versions even if the query runs on the standby, I feel like leaning toward the latter at least leads to a problem people are used to. If they move from running these batch queries on the master to running them on the slave then sure, the situation will be no worse than before. But if they move from having a plain old PITR warm standby to having one they can run queries on they might well assume that the big advantage of having the standby to play with is precisely that they can do things there that they have never been able to do on the master previously without causing damage. I agree that having queries randomly and unpredictably canceled is pretty awful. My argument was that max_standby_delay should default to infinity on the basis that any other value has to be picked based on actual workloads and SLAs. My feeling is that there are probably only two types of configurations that make sense, a HA replica with a low max_standby_delay or a reporting replica with a high max_standby_delay. Any attempt to combine the two on the same system will only work if you know your application well and can make compromises with both. I would also like to be able to handle load balancing read-only queries but that will be really tricky. You want up-to-date data and you want to be able to run moderately complex queries. That kind of workload may well require synchronous replication to really work properly. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
On Sat, 27 Feb 2010 00:43:48 +, Greg Stark gsst...@mit.edu wrote: On Fri, Feb 26, 2010 at 11:56 PM, Greg Smith g...@2ndquadrant.com wrote: This is also the reason why the whole pause recovery idea is a fruitless path to wander down. The whole point of this feature is that people have a secondary server available for high-availability, *first and foremost*, but they'd like it to do something more interesting that leave it idle all the time. The idea that you can hold off on applying standby updates for long enough to run seriously long reports is completely at odds with the idea of high-availability. I want my ability to run large batch queries without any performance or reliability impact on the primary server. +1 I can use any number of other technologies for high availability. Joshua D. Drake -- PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Joshua D. Drake wrote: On Sat, 27 Feb 2010 00:43:48 +, Greg Stark gsst...@mit.edu wrote: I want my ability to run large batch queries without any performance or reliability impact on the primary server. +1 I can use any number of other technologies for high availability. Remove must be an instant-on failover at the same time from the requirements and you don't even need 9.0 to handle that, this has been a straightforward to solve problem since 8.2. It's the combination of HA and queries that make things hard to do. If you just want batch queries on another system without being concerned about HA at the same time, the first option is to just fork the base backup and WAL segment delivery to another server and run queries there. Some simple filesystem snapshot techniques will also suffice to handle it all on the same standby. Stop warm standby recovery, snapshot, trigger the server, run your batch job; once finished, rollback to the snapshot, grab the latest segment files, and resume standby catchup. Even the lame Linux LVM snapshot features can handle that job--one of my coworkers has the whole thing scripted even this is so common. And if you have to go live because there's a failover, you're back to the same cold standby situation a large max_standby_delay puts you at, so it's not even very different from what you're going to get in 9.0 if this is your priority mix. The new version is just lowering the operational complexity involved. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Greg Stark wrote: But if they move from having a plain old PITR warm standby to having one they can run queries on they might well assume that the big advantage of having the standby to play with is precisely that they can do things there that they have never been able to do on the master previously without causing damage. Just not having the actual query running on the master is such a reduction in damage that I think it's delivering the essence of what people are looking for regardless. That it might be possible in some cases to additionally avoid the overhead that comes along with any long-running query is a nice bonus, and it's great the design allows for that possibility. But if that's only possible with risk, heavy tweaking, and possibly some hacks, I'm not sure that's making the right trade-offs for everyone. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
* Greg Smith g...@2ndquadrant.com [100226 23:39]: Just not having the actual query running on the master is such a reduction in damage that I think it's delivering the essence of what people are looking for regardless. That it might be possible in some cases to additionally avoid the overhead that comes along with any long-running query is a nice bonus, and it's great the design allows for that possibility. But if that's only possible with risk, heavy tweaking, and possibly some hacks, I'm not sure that's making the right trade-offs for everyone. Would we (ya, the royal we) be willing to say that if you want the benifit of removing the MVCC overhead of long-running queries you need to run PITR backup/archive recovery, and if you want SR, you get a closed-loop master-follows-save-xmin behaviour? a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Aidan Van Dyk wrote: Would we (ya, the royal we) be willing to say that if you want the benifit of removing the MVCC overhead of long-running queries you need to run PITR backup/archive recovery, and if you want SR, you get a closed-loop master-follows-save-xmin behaviour? To turn that question around a little, I think it's reasonable to say that closed-loop master-follows-slave-xmin behavior is only practical to consider implementing with SR--and even there, it should be optional rather than required until there's more field experience on the whole thing. Whether it's the default or not could take a bit of debate to sort out too. If you think of it in those terms, the idea that you need to run PITR backup/archive recovery to not get that behavior isn't an important distinction anymore. If you run SR with the option enabled you could get it, any other setup and you won't. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
