Re: [HACKERS] VACUUM DELAY
Jan Wieck wrote: On 8/9/2004 7:41 PM, Gaetano Mendola wrote: If I remember well this is the first command that need to change GUC in order to change behaviour, I don't think we wrote: set vacuum_mode = full; set vacuum_verbosity = on; vacuum; You got a point here. However, we don't have SELECT foo FROM bar WHERE baz = 'bumm' NOSEQSCAN; either, and I hope you don't suggest doing that next :-) Good idea indeed :-) This could be the first step to give some hints to the planner, Informix have it for sure and if I remember well Oracle have it, Sybase have it... Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] VACUUM DELAY
Hi all, I have seen the big debat about to have the delay off or on by default. Why not enable it by default and introduce a new parameter to vacuum command itself ? Something like: VACUUM WITH DELAY 100; this will permit to change easilly the delay in the maintainance scripts. Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] VACUUM DELAY
On Mon, 2004-08-09 at 05:19, Gaetano Mendola wrote: Hi all, I have seen the big debat about to have the delay off or on by default. Why not enable it by default and introduce a new parameter to vacuum command itself ? Something like: VACUUM WITH DELAY 100; this will permit to change easilly the delay in the maintainance scripts. The problem, I believe, is that any delay at all results in a VERY slow vacuum run (like 3 to 5 times slower) and for some people, this will be such unexpected behaviour they may believe postgresql is broken, or just want the older, faster vacuum, especially in a development environment. Imagine an increase from 1 to 5 minutes on an otherwise duplicate database from a 7.4 machine. I'll personally be running the delay and autovacuum on any machine I'll be running, and I think once the autovacuum is integrated, it might make sense to have a vacuum command just toss an entry in a que saying vacuum this table next scheduled run and return immediately with a NOTICE: vacuum (on tablex) scheduled. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] VACUUM DELAY
On 8/9/2004 7:19 AM, Gaetano Mendola wrote: Hi all, I have seen the big debat about to have the delay off or on by default. Why not enable it by default and introduce a new parameter to vacuum command itself ? Something like: VACUUM WITH DELAY 100; It's not just one parameter to tune here. It is a set of parameters that all together need to be viewed as a whole. The slowdown will be affected by the other parameters as well, so turning the millisecond knob only is not even half of the story. Setting the delay to zero simply disables the whole feature at runtime. That is why this discussion was using the delay parameter as a synonym for enabling/disabling the feature by default. Jan this will permit to change easilly the delay in the maintainance scripts. Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] VACUUM DELAY
Jan Wieck wrote: On 8/9/2004 7:19 AM, Gaetano Mendola wrote: Hi all, I have seen the big debat about to have the delay off or on by default. Why not enable it by default and introduce a new parameter to vacuum command itself ? Something like: VACUUM WITH DELAY 100; It's not just one parameter to tune here. It is a set of parameters that all together need to be viewed as a whole. The slowdown will be affected by the other parameters as well, so turning the millisecond knob only is not even half of the story. So the other parameter will inserted in the new sintax too, I think is fundamental the ability of override this values during the vacuum call: VACUUM WITH DELAY 100 [ ]; Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] VACUUM DELAY
On Mon, Aug 09, 2004 at 07:19:44PM +0200, Gaetano Mendola wrote: So the other parameter will inserted in the new sintax too, I think is fundamental the ability of override this values during the vacuum call: VACUUM WITH DELAY 100 [ ]; What's wrong with SET vacuum_delat 100; SET whatever_parameter 'value'; VACUUM ...; -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Licensee shall have no right to use the Licensed Software for productive or commercial use. (Licencia de StarOffice 6.0 beta) ---(end of broadcast)--- TIP 3: 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] VACUUM DELAY
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Alvaro Herrera wrote: | On Mon, Aug 09, 2004 at 07:19:44PM +0200, Gaetano Mendola wrote: | | |So the other parameter will inserted in the new sintax too, I think is |fundamental |the ability of override this values during the vacuum call: | |VACUUM WITH DELAY 100 [ ]; | | | What's wrong with | | SET vacuum_delat 100; | SET whatever_parameter 'value'; | VACUUM ...; Noting wrong but: 1) The parameters and new feature will be spotted out better to new users 2) My shell script will become less hugly :-) Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBF8wv7UpzwH2SGd4RAnSHAJ0QI0Uu9ZVJiMFn3NY5jFT6omdkYwCfZ8pU BaVnYczZ9pGGTBXMurNtj30= =hP7Q -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] VACUUM DELAY
On 8/9/2004 1:19 PM, Gaetano Mendola wrote: Jan Wieck wrote: On 8/9/2004 7:19 AM, Gaetano Mendola wrote: Hi all, I have seen the big debat about to have the delay off or on by default. Why not enable it by default and introduce a new parameter to vacuum command itself ? Something like: VACUUM WITH DELAY 100; It's not just one parameter to tune here. It is a set of parameters that all together need to be viewed as a whole. The slowdown will be affected by the other parameters as well, so turning the millisecond knob only is not even half of the story. So the other parameter will inserted in the new sintax too, I think is fundamental the ability of override this values during the vacuum call: VACUUM WITH DELAY 100 [ ]; You can do it right now. set vacuum_cost_delay = 100; vacuum analyze; No need to panic. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 3: 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] VACUUM DELAY
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jan Wieck wrote: | On 8/9/2004 1:19 PM, Gaetano Mendola wrote: | | Jan Wieck wrote: | | On 8/9/2004 7:19 AM, Gaetano Mendola wrote: | | Hi all, | I have seen the big debat about to have the delay | off or on by default. | | Why not enable it by default and introduce a new | parameter to vacuum command itself ? Something like: | | | VACUUM WITH DELAY 100; | | | | It's not just one parameter to tune here. It is a set of parameters | that all together need to be viewed as a whole. The slowdown will be | affected by the other parameters as well, so turning the millisecond | knob only is not even half of the story. | | | So the other parameter will inserted in the new sintax too, I think is | fundamental | the ability of override this values during the vacuum call: | | VACUUM WITH DELAY 100 [ ]; | | | You can do it right now. | | set vacuum_cost_delay = 100; | vacuum analyze; | No need to panic. No need to be smarty pants too. I know that it can be possible, after all 4 years for a dummy like I'm, are enough to understand that is possible to change some GUC for a given connection. :-) However I think is annoying to write: set vacuum_cost_delay = 100; vacuum table big_huge; set vacuum_cost_delay = 0; set whatelse; vacuum table night_table; or even better: psql -c set vacuum_cost_delay = 100; vacuum analyze; and what about the utility vacuumdb ? If I remember well this is the first command that need to change GUC in order to change behaviour, I don't think we wrote: set vacuum_mode = full; set vacuum_verbosity = on; vacuum; Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBGAud7UpzwH2SGd4RAjR0AKDw8XLAI2Lo2uqRauwhWJWwGmwYtgCgmI7u WDZvqwUMzuwXN6Z1qqj91vs= =Wxpz -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] VACUUM DELAY
Gaetano Mendola wrote: However I think is annoying to write: set vacuum_cost_delay = 100; vacuum table big_huge; set vacuum_cost_delay = 0; set whatelse; vacuum table night_table; Well, you are already seting it to zero for night, so why not just set it to non-zero for day? Seems the same to me, or set it to non-zero in postgresql.conf and set it to zero at night. or even better: psql -c set vacuum_cost_delay = 100; vacuum analyze; and what about the utility vacuumdb ? Anyone using the utility command can use PGOPT to set the GUC I think. Maybe we should mention that in the manual page. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] VACUUM DELAY
On 8/9/2004 7:41 PM, Gaetano Mendola wrote: If I remember well this is the first command that need to change GUC in order to change behaviour, I don't think we wrote: set vacuum_mode = full; set vacuum_verbosity = on; vacuum; You got a point here. However, we don't have SELECT foo FROM bar WHERE baz = 'bumm' NOSEQSCAN; either, and I hope you don't suggest doing that next :-) Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Vacuum Delay feature
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (Bruce Momjian) would write: I guess my question is that now that we have the new cache replacement policy, is the vacuum delay worth while. I looked at http://developer.postgresql.org/~wieck/vacuum_cost/ and does seem useful. They satisfy quite separate use cases, so both are surely useful. - The new cache replacement policy allows us to make sure that cache isn't getting blown on worthless things. - Vacuum delay allows us to make sure that we aren't spending all our I/O on vacuuming. There is overlap between their uses, as both should help diminish the use of I/O to fill buffers with data that was discarded, but they surely have separate uses. -- cbbrowne,@,acm.org http://www.ntlug.org/~cbbrowne/lisp.html You know that little indestructible black box that is used on planes---why can't they make the whole plane out of the same substance? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Vacuum Delay feature
Bruce Momjian wrote: Jan Wieck wrote: Attached is a corrected version that solves the query cancel problem by not napping any more and going full speed as soon as any signal is pending. If nobody objects, I'm going to commit this tomorrow. Jan, three questions. First, is this useful now that we have the new cache replacement code, second, do we need this many parameters (can't any of them be autotuned), and third, what about documentation? You mean if stopping to nap is useful when a signal is pending or if napping during vacuum itself is useful at all? I am willing to make it all self tuning and automagic. Just tell me how. Documentation is missing so far. Will work on that. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Vacuum Delay feature
Jan Wieck wrote: Attached is a corrected version that solves the query cancel problem by not napping any more and going full speed as soon as any signal is pending. If nobody objects, I'm going to commit this tomorrow. Jan, three questions. First, is this useful now that we have the new cache replacement code, second, do we need this many parameters (can't any of them be autotuned), and third, what about documentation? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: 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] Vacuum Delay feature
Jan Wieck wrote: Bruce Momjian wrote: Jan Wieck wrote: Attached is a corrected version that solves the query cancel problem by not napping any more and going full speed as soon as any signal is pending. If nobody objects, I'm going to commit this tomorrow. Jan, three questions. First, is this useful now that we have the new cache replacement code, second, do we need this many parameters (can't any of them be autotuned), and third, what about documentation? You mean if stopping to nap is useful when a signal is pending or if napping during vacuum itself is useful at all? I am willing to make it all self tuning and automagic. Just tell me how. I was hoping you would have some ideas. :-) I guess my question is that now that we have the new cache replacement policy, is the vacuum delay worth while. I looked at http://developer.postgresql.org/~wieck/vacuum_cost/ and does seem useful. Documentation is missing so far. Will work on that. Cool. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Vacuum Delay feature
The attached patch applies to CVS tip as of 02/05/2004 and implements the cost based vacuum delay feature. A detailed description with charts of different configuration settings can be found here: http://developer.postgresql.org/~wieck/vacuum_cost/ There is a problem left that seems to be related to Toms observations in the shutdown behaviour of the postmaster. My current guess is that the napping done via select(2) somehow prevents responding to the query abort signal. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # vacuum_cost.75devel.diff.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Vacuum Delay feature
Attached is a corrected version that solves the query cancel problem by not napping any more and going full speed as soon as any signal is pending. If nobody objects, I'm going to commit this tomorrow. Jan Jan Wieck wrote: The attached patch applies to CVS tip as of 02/05/2004 and implements the cost based vacuum delay feature. A detailed description with charts of different configuration settings can be found here: http://developer.postgresql.org/~wieck/vacuum_cost/ There is a problem left that seems to be related to Toms observations in the shutdown behaviour of the postmaster. My current guess is that the napping done via select(2) somehow prevents responding to the query abort signal. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # vacuum_cost.75devel.diff.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] VACUUM delay (was Re: What's planned for 7.5?)
Josh Berkus wrote: People, I don't have the time to make enough different attempts to find the one that pleases all. My argument still is that all this IO throttling and IO optimizing is mainly needed for dedicated servers, because I think that if you still run multiple services on one box you're not really in trouble yet. So in the first round a configurable sync() approach would do. So far nobody even agreed to that. I won't claim expertise on the different sync algorithms. However, I do need to speak up in support of Jan's assertion; the machines most likely to suffer I/O choke are, or should be, dedicated machines. If someone's running 6 major server applications on a server with a 25GB database and a single RAID-5 array, then they've got to expect some serious performance issues. We currently have a lot of users running large databases on devoted servers, though, and they can't vaccuum their databases during working hours because the vacuum ties up the I/O for 10 minutes or more. It's a bad situation and makes us look very bad in comparison to the proprietary databases, which have largely solved this problem. Maybe the sync() approach isn't perfect, but it's certainly better than not doing anything, particularly if it can be turned off at startup time. Thanks for the support Josh, though the sync() issues of the background writer and vacuum might not seem directly related, it all must be done in the same IO bandwidth. So if we are to do this now, this would be my proposal: * GUC parameter vacuum_cost_page_hit=1 is the cost for a page found by vacuum on a buffer cache hit. * GUC parameter vacuum_cost_page_miss=10 is the cost for a page faulted in on behalf of vacuum. * GUC parameter vacuum_cost_page_dirtied=20 is the cost for vacuum marking a formerly clean page dirty. * GUC parameter vacuum_cost_limit=200 is the amount of cost vacuum can produce before napping. * GUC parameter vacuum_cost_naptime=0 (by default the entire mechanism disabled) is the number of milliseconds to nap when the cost limit is reached. * Pages faulted in on behalf of vacuum are placed onto the replacement cache head for immediate eviction. In addition to this, vacuum will yield while the background writer is doing any work. The GUC option bgwriter_sync_method=none (or sync) will control if the background writer will cause a smgr_sync() at the end of a run. Both, vacuum and the background writer, will yield to a checkpoint. With a properly configured background writer, checkpoints do not cause major responsetime spikes any more. Anything forgotten? Tom? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 3: 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] VACUUM delay (was Re: What's planned for 7.5?)
Stephen wrote: The vacuum delay patch is not the ideal solution but it worked like a charm on my servers. I really need the vacuum delay patch or a better solution in 7.5. I'm getting millions of requests a month and running VACUUM without the patch makes PostgreSQL useless for many consecutive hours. Not quite the 24/7 system I was hopping for. :-( Unfortunately, it's rather difficult to patch so many machines as my entire system runs on Redhat RPMs. I'm really hopping to see a solution to this VACUUM problem in 7.5. I've been waiting for this fix for over 3 years and now it's almost there. Will this problem get addressed in the not so official TODO list? Well, I had a few different versions of vacuum delay stuff out as patches, together with ARC and the beginnings of the background writer. Instead of getting some numbers on those, the whole discussion got stuck in differences about how we actually let the background writer tell the kernel do something ... the whole sync(), fsync(), fdatasync(), fadvise() discussion. I don't have the time to make enough different attempts to find the one that pleases all. My argument still is that all this IO throttling and IO optimizing is mainly needed for dedicated servers, because I think that if you still run multiple services on one box you're not really in trouble yet. So in the first round a configurable sync() approach would do. So far nobody even agreed to that. I currently have better to do. We do not have a big IO problem, we have other problems, and I spend my time on solving them. If someone wants to pick up the IO throttle problem, I am allways here to help, but I will not waste my time with making patches nobody even gives a try. Thanks and keep up the good work! Sorry for the venting, but I needed that out. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] VACUUM delay (was Re: What's planned for 7.5?)
People, I don't have the time to make enough different attempts to find the one that pleases all. My argument still is that all this IO throttling and IO optimizing is mainly needed for dedicated servers, because I think that if you still run multiple services on one box you're not really in trouble yet. So in the first round a configurable sync() approach would do. So far nobody even agreed to that. I won't claim expertise on the different sync algorithms. However, I do need to speak up in support of Jan's assertion; the machines most likely to suffer I/O choke are, or should be, dedicated machines. If someone's running 6 major server applications on a server with a 25GB database and a single RAID-5 array, then they've got to expect some serious performance issues. We currently have a lot of users running large databases on devoted servers, though, and they can't vaccuum their databases during working hours because the vacuum ties up the I/O for 10 minutes or more. It's a bad situation and makes us look very bad in comparison to the proprietary databases, which have largely solved this problem. Maybe the sync() approach isn't perfect, but it's certainly better than not doing anything, particularly if it can be turned off at startup time. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] VACUUM delay (was Re: What's planned for 7.5?)
On Mon, 2004-01-19 at 08:37, Jan Wieck wrote: but I will not waste my time with making patches nobody even gives a try. I downloaded and tested your patches. I just didn't get results get results that were put together well enough to present to the group. I hope this doesn't fall by the wayside, it is IMHO, on of the critical problems that needs to be solved. Sorry for the venting, but I needed that out. I understand. I'm sorry there wasn't more feedback as a result of your work. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] VACUUM delay (was Re: What's planned for 7.5?)
The vacuum delay patch is not the ideal solution but it worked like a charm on my servers. I really need the vacuum delay patch or a better solution in 7.5. I'm getting millions of requests a month and running VACUUM without the patch makes PostgreSQL useless for many consecutive hours. Not quite the 24/7 system I was hopping for. :-( Unfortunately, it's rather difficult to patch so many machines as my entire system runs on Redhat RPMs. I'm really hopping to see a solution to this VACUUM problem in 7.5. I've been waiting for this fix for over 3 years and now it's almost there. Will this problem get addressed in the not so official TODO list? Thanks and keep up the good work! Stephen Jan Wieck [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Tom Lane wrote: Christopher Browne [EMAIL PROTECTED] writes: Stephen [EMAIL PROTECTED] writes: Any chance we'll see the VACUUM delay patch (throttle) get into 7.5? The hope, in 7.5, is to have ARC, which is the super-duper-duper version, working. Actually, I'm not sure that ARC should be considered to supersede the usefulness of a per-page delay in VACUUM. ARC should prevent VACUUM from trashing the contents of Postgres' shared buffer arena, but it won't do much of anything to prevent VACUUM from trashing the kernel buffer contents. And it definitely won't do anything to help if the real problem is that you're short of disk bandwidth and VACUUM's extra I/O demand pushes your total load over the knee of the response-time curve. What you need then is a throttle. The original patch I posted was incomplete for a number of reasons, but I think it may still be worth working on. Jan, any comments? I agree that there is considerable value in IO distribution. As such I already have the basics of the Background Writer in there. I left out the vacuum delay since I thought it was good enough to proove that there is low hanging fruit, but that it was far from what I'd call a solution. Ideally Vacuum would coordinate it's IO not only against some GUC variables, but also against the BGWriter+BufStrategy combo. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
