Re: [HACKERS] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
Simon Riggs wrote: On Thu, 2007-10-25 at 13:41 -0300, Alvaro Herrera wrote: ... FWIW I disagree with cancelling just any autovac work automatically; in my patch I'm only cancelling if it's analyze, on the grounds that if you have really bad luck you can potentially lose a lot of work that vacuum did. We can relax this restriction when we have cancellable vacuum. That was requested by others, not myself, but I did agree with the conclusions. The other bad luck might be that you don't complete some critical piece of work in the available time window because an automated job kicked in. Yeah, I thought we had agreed that we must cancel all auto vacuum/analyzes, on the ground that foreground operations are usually more important than maintenance tasks. Remember the complaint we already had on hackers just after beta1: auto *vacuum* blocked a schema change, and of course the user complained. Best Regards Michael Paesold ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
Michael Paesold wrote: Simon Riggs wrote: On Thu, 2007-10-25 at 13:41 -0300, Alvaro Herrera wrote: ... FWIW I disagree with cancelling just any autovac work automatically; in my patch I'm only cancelling if it's analyze, on the grounds that if you have really bad luck you can potentially lose a lot of work that vacuum did. We can relax this restriction when we have cancellable vacuum. That was requested by others, not myself, but I did agree with the conclusions. The other bad luck might be that you don't complete some critical piece of work in the available time window because an automated job kicked in. Yeah, I thought we had agreed that we must cancel all auto vacuum/analyzes, on the ground that foreground operations are usually more important than maintenance tasks. What this means is that autovacuum will be starved a lot of the time, and in the end you will only vacuum the tables when you run out of time for Xid wraparound. Remember the complaint we already had on hackers just after beta1: auto *vacuum* blocked a schema change, and of course the user complained. Actually I can't remember it, but I think we should decree that this is a known shortcoming; that we will fix it when we have cancellable vacuum; and that the user is free to cancel the vacuuming on his own if he so decides. -- Alvaro Herrera http://www.flickr.com/photos/alvherre/ The ability to monopolize a planet is insignificant next to the power of the source ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
Alvaro Herrera wrote: Michael Paesold wrote: Simon Riggs wrote: On Thu, 2007-10-25 at 13:41 -0300, Alvaro Herrera wrote: ... FWIW I disagree with cancelling just any autovac work automatically; in my patch I'm only cancelling if it's analyze, on the grounds that if you have really bad luck you can potentially lose a lot of work that vacuum did. We can relax this restriction when we have cancellable vacuum. That was requested by others, not myself, but I did agree with the conclusions. The other bad luck might be that you don't complete some critical piece of work in the available time window because an automated job kicked in. Yeah, I thought we had agreed that we must cancel all auto vacuum/analyzes, on the ground that foreground operations are usually more important than maintenance tasks. What this means is that autovacuum will be starved a lot of the time, and in the end you will only vacuum the tables when you run out of time for Xid wraparound. Well, only if you do a lot of schema changes. In the previous discussion, Simon and me agreed that schema changes should not happen on a regular basis on production systems. Shouldn't we rather support the regular usage pattern instead of the uncommon one? Users doing a lot of schema changes are the ones who should have to work around issues, not those using a DBMS sanely. No? Best Regards Michael Paesold ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
Michael Paesold wrote: In the previous discussion, Simon and me agreed that schema changes should not happen on a regular basis on production systems. Shouldn't we rather support the regular usage pattern instead of the uncommon one? Users doing a lot of schema changes are the ones who should have to work around issues, not those using a DBMS sanely. No? Unfortunately, doing lots of schema changes is a very common phenomenon. It makes me uncomfortable too, but saying that those who do it have to work around issues isn't acceptable IMNSHO - it's far too widely done. cheers andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
Alvaro Herrera [EMAIL PROTECTED] writes: Michael Paesold wrote: Yeah, I thought we had agreed that we must cancel all auto vacuum/analyzes, on the ground that foreground operations are usually more important than maintenance tasks. What this means is that autovacuum will be starved a lot of the time, Not really, because DDL changes aren't *that* common (at least not for non-temp tables). I think the consensus was that we should cancel autovac in these cases unless it is an anti-wraparound vacuum. Isn't that why you were putting in the flag to show it is for wraparound? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
Andrew Dunstan [EMAIL PROTECTED] writes: Michael Paesold wrote: Shouldn't we rather support the regular usage pattern instead of the uncommon one? Users doing a lot of schema changes are the ones who should have to work around issues, not those using a DBMS sanely. No? Unfortunately, doing lots of schema changes is a very common phenomenon. It makes me uncomfortable too, but saying that those who do it have to work around issues isn't acceptable IMNSHO - it's far too widely done. Well, there's going to be pain *somewhere* here, and we already know that users will find the current 8.3 behavior unacceptable. I'd rather have autovacuum not make progress than have users turn it off because it gets in their way too much. Which I think is exactly what will happen if we ship it with the current behavior. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Michael Paesold wrote: Shouldn't we rather support the regular usage pattern instead of the uncommon one? Users doing a lot of schema changes are the ones who should have to work around issues, not those using a DBMS sanely. No? Unfortunately, doing lots of schema changes is a very common phenomenon. It makes me uncomfortable too, but saying that those who do it have to work around issues isn't acceptable IMNSHO - it's far too widely done. Well, there's going to be pain *somewhere* here, and we already know that users will find the current 8.3 behavior unacceptable. I'd rather have autovacuum not make progress than have users turn it off because it gets in their way too much. Which I think is exactly what will happen if we ship it with the current behavior. Agreed. If auto-vacuum is blocking activity, it isn't very 'auto' to me. If vacuum is blocking something, by definition it is a bad time for it to be vacuuming and it should abort and try again later. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
On Thu, 2007-10-25 at 13:51 -0400, Andrew Dunstan wrote: Michael Paesold wrote: In the previous discussion, Simon and me agreed that schema changes should not happen on a regular basis on production systems. Shouldn't we rather support the regular usage pattern instead of the uncommon one? Users doing a lot of schema changes are the ones who should have to work around issues, not those using a DBMS sanely. No? Unfortunately, doing lots of schema changes is a very common phenomenon. It makes me uncomfortable too, but saying that those who do it have to work around issues isn't acceptable IMNSHO - it's far too widely done. We didn't agree that DDL was uncommon, we agreed that running DDL was more important than running an auto VACUUM. DDL runs very quickly, unless blocked, though holds up everybody else. So you must run it at pre-planned windows. VACUUMs can run at any time, so a autoVACUUM shouldn't be allowed to prevent DDL from running. The queuing DDL makes other requests queue behind it, even ones that would normally have been able to execute at same time as the VACUUM. Anyway, we covered all this before. I started off saying we shouldn't do this and Heikki and Michael came up with convincing arguments, for me, so now I think we should allow autovacuums to be cancelled. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
Simon Riggs wrote: On Thu, 2007-10-25 at 13:51 -0400, Andrew Dunstan wrote: Michael Paesold wrote: In the previous discussion, Simon and me agreed that schema changes should not happen on a regular basis on production systems. Shouldn't we rather support the regular usage pattern instead of the uncommon one? Users doing a lot of schema changes are the ones who should have to work around issues, not those using a DBMS sanely. No? Unfortunately, doing lots of schema changes is a very common phenomenon. It makes me uncomfortable too, but saying that those who do it have to work around issues isn't acceptable IMNSHO - it's far too widely done. We didn't agree that DDL was uncommon, we agreed that running DDL was more important than running an auto VACUUM. DDL runs very quickly, unless blocked, though holds up everybody else. So you must run it at pre-planned windows. VACUUMs can run at any time, so a autoVACUUM shouldn't be allowed to prevent DDL from running. The queuing DDL makes other requests queue behind it, even ones that would normally have been able to execute at same time as the VACUUM. Anyway, we covered all this before. I started off saying we shouldn't do this and Heikki and Michael came up with convincing arguments, for me, so now I think we should allow autovacuums to be cancelled. Perhaps I misunderstood, or have been mistunderstood :-) - I am actually agreeing that autovac should not block DDL. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Michael Paesold wrote: Shouldn't we rather support the regular usage pattern instead of the uncommon one? Users doing a lot of schema changes are the ones who should have to work around issues, not those using a DBMS sanely. No? Unfortunately, doing lots of schema changes is a very common phenomenon. It makes me uncomfortable too, but saying that those who do it have to work around issues isn't acceptable IMNSHO - it's far too widely done. Well, there's going to be pain *somewhere* here, and we already know that users will find the current 8.3 behavior unacceptable. I'd rather have autovacuum not make progress than have users turn it off because it gets in their way too much. Which I think is exactly what will happen if we ship it with the current behavior. exactly - 8.3 will be the first release with autovac enabled by default (and concurrent autovacuuming) and we really need to make sure that people wont get surprised by it in the way I (and others) did when playing with Beta1. So my vote would be on cancelling always except in the case of a wraparound vacuum. Stefan ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
On Thu, Oct 25, 2007 at 03:54:28PM -0400, Andrew Dunstan wrote: Simon Riggs wrote: On Thu, 2007-10-25 at 13:51 -0400, Andrew Dunstan wrote: Michael Paesold wrote: In the previous discussion, Simon and me agreed that schema changes should not happen on a regular basis on production systems. Shouldn't we rather support the regular usage pattern instead of the uncommon one? Users doing a lot of schema changes are the ones who should have to work around issues, not those using a DBMS sanely. No? Unfortunately, doing lots of schema changes is a very common phenomenon. It makes me uncomfortable too, but saying that those who do it have to work around issues isn't acceptable IMNSHO - it's far too widely done. We didn't agree that DDL was uncommon, we agreed that running DDL was more important than running an auto VACUUM. DDL runs very quickly, unless blocked, though holds up everybody else. So you must run it at pre-planned windows. VACUUMs can run at any time, so a autoVACUUM shouldn't be allowed to prevent DDL from running. The queuing DDL makes other requests queue behind it, even ones that would normally have been able to execute at same time as the VACUUM. Anyway, we covered all this before. I started off saying we shouldn't do this and Heikki and Michael came up with convincing arguments, for me, so now I think we should allow autovacuums to be cancelled. Perhaps I misunderstood, or have been mistunderstood :-) - I am actually agreeing that autovac should not block DDL. +1 here for having autovacuum not block DDL :) Cheers, David (for what it's worth) -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 6: explain analyze is your friend
