Autotuning Group Commit - Mailing list pgsql-hackers

From Simon Riggs
Subject Autotuning Group Commit
Date
Msg-id 1106351571.31592.58.camel@localhost.localdomain
Whole thread Raw
Responses Re: Autotuning Group Commit
Re: Autotuning Group Commit
Re: Autotuning Group Commit
List pgsql-hackers
Currently, we have group commit functionality via GUC parameterscommit_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



pgsql-hackers by date:

Previous
From: "Joshua D. Drake"
Date:
Subject: Re: ARC patent
Next
From: Greg Stark
Date:
Subject: Re: ARC patent