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


Mon, 19 Jan 2004 16:37:01 -0800
Josh Berkus <josh@agliodbs.com>
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 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.                                  #
#================================================== JanWieck@Yahoo.com #


Tue, 20 Jan 2004 11:14:40 -0500
Jan Wieck <JanWieck@Yahoo.com>
Re: VACUUM delay (was Re: What's planned for 7.5?)