Checkpoint Question - need feedback
Madison Pruet (IBM) polled users about implementing non-blocking checkpoints, which would eliminate fuzzy checkpoints (improving recovery time) but require a physical log roughly 3-4 times larger, with physical logging volume back to v7 levels. Respondents (Obnoxio, Art Kagel, Eric Rowell, Superboer) were essentially unanimous that bigger physical logs are a trivial price for better responsiveness, with one caveat that blocking/triggered checkpoints still help during heavy bulk loads when page cleaners can't keep up. No implementation or decision is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I've got a question for the user community. Checkpoints are a real pain because there is a period of time in which user threads are going to be blocked. I've been playing around with an idea in which I think I could do non-blocking checkpoints. The cost of doing this, however, would be that there would probably need to be a significant increase in the size of the physical log file. I know that implementing the fractional LRU min/max has helped reduce the impact of the checkpoint, but the fractional LRU min/max is not a guarantee - since it is possible that the LRU page writers won't be able to keep up with the current activity. Also - this idea would eliminate fuzzy checkpoints, which is probably a good thing since fuzzy checkpoints do impact the recovery time of the server. So - basic question --- Is the cost of the increased physical log file (maybe 3-4 times larger in some cases) totally outweigh the benefit of non-blocking checkpoints?
Madison Pruet said: > I've got a question for the user community. > > Checkpoints are a real pain because there is a period of time in which > user threads are going to be blocked. > > I've been playing around with an idea in which I think I could do > non-blocking checkpoints. The cost of doing this, however, would be > that there would probably need to be a significant increase in the size > of the physical log file. > > I know that implementing the fractional LRU min/max has helped reduce > the impact of the checkpoint, but the fractional LRU min/max is not a > guarantee - since it is possible that the LRU page writers won't be able > to keep up with the current activity. > > Also - this idea would eliminate fuzzy checkpoints, which is probably a > good thing since fuzzy checkpoints do impact the recovery time of the > server. > > So - basic question --- Is the cost of the increased physical log file > (maybe 3-4 times larger in some cases) totally outweigh the benefit of > non-blocking checkpoints? Madison, In my experience, I can honestly say that making the physical log file 4, 5 or even 10 times bigger is no cost at all. -- Bye now, Obnoxio "... no bill is required as no value was provided." -- Christine Normile
Obnoxio The Clown wrote: > Madison Pruet said: >> I've got a question for the user community. >> >> >> So - basic question --- Is the cost of the increased physical log file >> (maybe 3-4 times larger in some cases) totally outweigh the benefit of >> non-blocking checkpoints? > > Madison, > > In my experience, I can honestly say that making the physical log file 4, > 5 or even 10 times bigger is no cost at all. > Thanks --- I guess I should have mentioned that the amount of physical logging that this would be using would be roughly the same as v7. Fuzzy checkpoints reduced the amount of physical logging.
Madison Pruet said: > Thanks --- I guess I should have mentioned that the amount of physical > logging that this would be using would be roughly the same as v7. Fuzzy > checkpoints reduced the amount of physical logging. I can't see that changing anyone's mind. Except maybe for the Oracle haters who loved to have something they could latch onto and go on and on and on and on and on about... -- Bye now, Obnoxio "... no bill is required as no value was provided." -- Christine Normile
Obnoxio The Clown wrote: > Madison Pruet said: >> Thanks --- I guess I should have mentioned that the amount of physical >> logging that this would be using would be roughly the same as v7. Fuzzy >> checkpoints reduced the amount of physical logging. > > I can't see that changing anyone's mind. Except maybe for the Oracle > haters who loved to have something they could latch onto and go on and on > and on and on and on about... > Don't hate the player. Hate the game.
Hello Madison, sorry have not looked for it yet.... what is the max size for the phys log nowadays; it used to be 2 GB eq the size of a chunk....that is i guess to small. > So - basic question --- Is the cost of the increased physical log file > (maybe 3-4 times larger in some cases) totally outweigh the benefit of > non-blocking checkpoints? Yes. however if one is loading a truck load of data, page cleaners may not keep up at all. In such a case blocking checkpoint may be welcome. or generate our own checkpoint whenever the buffer cache is 75% dirty start a checkpoint which does big buffer writes... i rather run a checkpoint when the buffer cache is 75 % dirty then when the phys log is 75% full; at least resources are used the best way and i have some control over it and most important, it's fast... So do not take them away completely please. See you Superboer.
Madison Pruet wrote: > I've got a question for the user community. > > Checkpoints are a real pain because there is a period of time in which > user threads are going to be blocked. > > I've been playing around with an idea in which I think I could do > non-blocking checkpoints. The cost of doing this, however, would be > that there would probably need to be a significant increase in the size > of the physical log file. > > I know that implementing the fractional LRU min/max has helped reduce > the impact of the checkpoint, but the fractional LRU min/max is not a > guarantee - since it is possible that the LRU page writers won't be able > to keep up with the current activity. > > Also - this idea would eliminate fuzzy checkpoints, which is probably a > good thing since fuzzy checkpoints do impact the recovery time of the > server. > > So - basic question --- Is the cost of the increased physical log file > (maybe 3-4 times larger in some cases) totally outweigh the benefit of > non-blocking checkpoints? I'll trade cheap disk for improved server responsiveness 99 days out of 100! Go for it Madison. Art S. Kagel
>So - basic question --- Is the cost of the increased physical log file >(maybe 3-4 times larger in some cases) totally outweigh the benefit of >non-blocking checkpoints? I would be willing to go this many more times larger then this if needed.