On Wed, 2007-07-18 at 01:01 +0100, Gregory Stark wrote: > "Bruce Momjian" <[EMAIL PROTECTED]> 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 than 32,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 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly