On Wed, 2007-07-18 at 01:01 +0100, Gregory Stark wrote:
> "Bruce Momjian" <bruce@momjian.us> writes:
>
> > Where are we on this?
>
> Well Simon just sent the reworked patch yesterday so the answer is we haven't
> started tuning this parameter. (Bruce's message is referring to the discussion
> about what the optimal value of lsns per clog page would be.)
>From discussion, Greg wishes to see a certain parameter higher, though
I'm fairly comfortable with the value now. Thanks to Greg for making me
think this through in more detail. There is some confirmation required,
but no significant tuning effort required.
Here's the thinking:
When we commit, we store an LSN that covers a group of transactions.
When we set hint bits we check the appropriate LSN. If it has been
flushed, then we set the hint bit.
If we're using synch commits then we can always set hint bits.
If we're mixing async commits with sync commits then the clog LSNs will
very often be flushed already, so we'll be able to set most hint bits.
If we are doing *only* simple async commits then only the WAL writer
flushes WAL. In that case when we try to set hint bits we will only be
prevented from setting hint bits for the last "few" transactions. So how
many is that exactly?
We are flushing WAL every W seconds and doing T transactions per second,
then we will flush WAL every T*W transactions. e.g. W = 0.2 (default), T
~ 10,000 => T*W = 2,000 transactions.
We have divided up each clog page up into N groups of transactions, each
with their own LSN, then we will have 32,000/N transactions per group.
The best situation is that we set N such that we never defer the setting
of a hint bit for a transaction that has been flushed.
There is no point setting N any lower than32,000/N = T*W
i.e. N <= W * (32,000/T)
My laptop can do T=1000 with W=0.2 with pgbench, so N <= 6.
By definition, anyone using async commit will have a high transaction
rate (else there is no benefit), so N seems able to be reasonably small.
If the transaction rate drops off momentarily the number of unflushed
committed async transactions doesn't rise, it falls quickly to zero as
the WAL writer flushes WAL up to the last async LSN.
The patch sets a value of 16, which seems likely to be enough to cover
most situations.
Having thought that through, I see two changes I'd like to make:
- I realise that I've arranged the LSNs to be striped rather than
grouped. I'll change this part of the patch - couple of lines in
GetLSNIndex().
- The LSN of sync commits is also used to update the LSN of the clog
page. That is unnecessary and we only need set the clog LSN for async
commits.
I'll wait for other review comments before cutting version 23....
-- Simon Riggs EnterpriseDB http://www.enterprisedb.com
