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
