Re: 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
napwhen 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.                                  #
#================================================== JanWieck@Yahoo.com #