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: