|
|
 | | From: | Simon Riggs | | Subject: | Autotuning Group Commit | | Date: | Fri, 21 Jan 2005 23:52:51 +0000 |
|
|
 | Currently, we have group commit functionality via GUC parameters
commit_delay
and commit_siblings
Group commit is either off or on. Since we do not have a log writer
daemon, there is no way to know whether that is optimal. There is
research to show that setting group commit on when it is not useful
actually causes a performance degradation. Clearly, this means that on a
server that is sometimes busy and sometimes not, you will be unsure of
how to set these parameters.
ISTM that we can autotune the group commit functionality:
Each transaction commit gets the current time(NULL) immediately before
it commits.
If we store that value in shared memory behind XLogInsertLock, then each
time we commit we would be able to tell how long it has been since the
last commit. We could thus make a true/false judgement as to whether it
would have gained us anything to wait for the commit_delay time before
committing. If we store the results of the last 10 commits (various
ways...), then if we have 9+ out of 10 last commits as potentially
beneficial group commits then we have a reasonably probability that
commits are happening on average faster than commit_delay. As a result,
we know to turn on the group commit feature by setting
group_commit_recommendation = true.
Each backend would start with group commit turned off. Each time it
commits it reads the current setting of group_commit_recommendation. If
this is set, it copies the group_commit_recommendation to a local
variable, so that the next time it commits it will wait for CommitDelay.
If CommitDelay is not set, then we would avoid the calculation
altogether and this would remain the default.
With this proposal, group commit will turn on or off according to recent
history reacting within 10*commit_delay milliseconds of a heavy
transaction load starting, turning off again even more quickly. None of
that would require knowledge, or tuning by the administrator. That is
sufficient to react to even small bursts of activity.
We would also be able to remove the commit_siblings GUC. It represents a
simple heuristic only for determining whether commit_delay should be
applied, so is effectively superceded by this proposal. There would be
no additional memory per backend and a minor additional shared memory
overhead, which could easily be optimised with some crafty code.
Overall, the additional minor CPU cost per transaction commit would be
worth the potential saving of 10ms on many transactions where group
commit would not gain performance at all. In any case, the functionality
would be optional and turned off by default.
Any comments, please?
--
Best Regards, Simon Riggs
---------------------------(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
|
|
| | From: | Simon Riggs | | Subject: | Re: Autotuning Group Commit | | Date: | Sat, 22 Jan 2005 08:47:37 +0000 |
|
|
| 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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
|
| | From: | Jim C. Nasby | | Subject: | Re: Autotuning Group Commit | | Date: | Sat, 22 Jan 2005 11:13:04 -0600 |
|
|
| On Sat, Jan 22, 2005 at 08:47:37AM +0000, Simon Riggs wrote:
> 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?
I don't think the frequently executed code would need to differ between
the two options. The remaining analysis would be done by a background
process.
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
|
|
| | From: | Jim C. Nasby | | Subject: | Re: Autotuning Group Commit | | Date: | Sat, 22 Jan 2005 00:18:07 -0600 |
|
|
| 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. :)
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
|
|
| | From: | Tom Lane | | Subject: | Re: Autotuning Group Commit | | Date: | Sat, 22 Jan 2005 16:03:06 -0500 |
|
|
| Simon Riggs writes:
> Each transaction commit gets the current time(NULL) immediately before
> it commits.
time() has 1 second resolution and ergo is utterly useless for this.
gettimeofday() may have sufficient resolution, but the resolution is
not specified anywhere.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
|
|
