Re: Autotuning Group Commit - Mailing list pgsql-hackers

From Simon Riggs
Subject Re: Autotuning Group Commit
Date
Msg-id 1106383657.31592.76.camel@localhost.localdomain
Whole thread Raw
In response to Re: Autotuning Group Commit  ("Jim C. Nasby" <decibel@decibel.org>)
Responses Re: Autotuning Group Commit
List pgsql-hackers
On Sat, 2005-01-22 at 00:18 -0600, Jim C. Nasby wrote:
> 1) I'm in favor of autotuning anything possible.
> 2) In addition to turning group_commit on and off, what about also
> adjusting the commit delay, based on statistics of recent commits? It
> might require a slightly larger sample set (maybe the last 100 commits),
> but it seems it would provide more flexibility (hence more usefulness)
> to the autotuning.
> 
> I belive you'd want to first calculate the elapsed time between each
> commit in the sample set, then look for groupings of elapsed time. If
> you have a set that looks like this:
> 
> Time (ms)  Number
> 2   *
> 4   *
> 6   
> 8   **
> 10  *
> 12  ******
> 14  ****
> 16  **
> 18  
> 20  *
> 
> then you'd want a delay of 16ms. I think this calculation could be done
> fairly quickly by grouping the commits into buckets of different elapsed
> times, then look for the largest elapsed time that has a number of
> commits greater than the mean number of commits for all the buckets. But
> I'm not a statistician, hopefully someone here is. :)

Yes, I considered that, but since we're talking about a frequently
executed piece of code, I was hoping to keep it short and sweet.
What do others think?

The other issue is the likely time granularity for many OS will be 10ms
anyway, so you have a choice of 0, 10, 20, 30ms... Overall, group commit
isn't much use until the log disk is getting very busy. Delays don't
really need to be more than a disk rotation, so even a laptop can manage
11ms between sequential writes. 

I'd suggest hardcoding commit_delay at 10ms, but we still need an on/off
switch, so it seems sensible to keep it. We may be in a better position
to use fine grained settings in the future.

-- 
Best Regards, Simon Riggs



pgsql-hackers by date:

Previous
From: "Jim C. Nasby"
Date:
Subject: Re: Autotuning Group Commit
Next
From: Neil Conway
Date:
Subject: Re: Extending System Views: proposal for 8.1/8.2