On 2/12/06, Marc Morin <marc@xxxxxxxxxxxx> wrote: > From your config, a check point will be forced when > > (checkpoint_segments * 16 M) < rows * (8K/N*h + (1-h)*8K) * B > > Where h is the "hitrate" or correlation between the update scan and the > index. Do you have a sense of what this is? I know my checkpoints happen > 30 secs apart, since PG isn't complaining in my log. I have no clue what the correlation is. > In the limits, we have 100% > correlation or 0% correlation. N is the lower cost of putting the > change in the WAL entry, not sure what this is, but small, I am > assuming, say N=100. B is the average number of blocks changed per > updated row (assume B=1.1 for your case, heap,serial index have very > high correlation) > > In the 0% correlation case, each updated row will cause the index update > to read/modify the block. The modified block will be entirely written to > the WAL log. After (30 * 16M) / (8K) / 1.1 ~ 55k rows, a checkpoint > will be forced and all modified blocks in shared buffers will be written > out. > > Increasing checkpoint_segments to 300 and seeing if that makes a > difference. If so, the excessive WAL checkpoints are your issue. If > performance is exactly the same, then I would assume that you have close > to 0% correlation between the rows in the heap and index. Ok, i'll have to give that a try. > Can you increase shared_buffers? With a low correlation index, the only > solution is to hold the working set of blocks in memory. Also, make > sure that the checkpoint segments are big enough for you to modify them > in place, don't want checkpoints occurring.... I'll have to look at my memory usage on this server... with only 2GB and a bunch of other processes running around I'm not sure if I can go up much more without causing swapping. Of course RAM is cheap... > Note that the more updates you do, the larger the tables/index become > and the worse the problem becomes. Vacuuming the table is an "answer" > but unfortunately, it tends to decrease correlation from our > observations. :-( Good to know. > From our observations, dropping index and rebuilding them is not always > practical, depends on your application; table will be exclusively locked > during the transaction due to drop index. Yep. In my case it's not a huge problem right now, but I know it will become a serious one sooner or later. Thanks a lot Marc. Lots of useful info. -- Aaron Turner http://synfin.net/
