Fuzzy checkpoints.

I have concerns about whether or not 9.2 is as robust in bad situations as 7.x was. My concerns center around the fact that fuzzy operations(updates, inserts, and deletes) are not physically logged To me, this means if I unplug my machine in the middle of a write to disk initiated by a fuzzy operation I am now going to a backup and rolling forward logical logs. Example: Begin work; Insert record; Commit work; ... Cleaner starts writing page to disk; SA unplugs machine before write completes. When machine comes back up there is no Physical log of the before image from the last checkpoint. DBA has to find most recent backup. Considering that many of our machines are continuously doing I/O due to low LRU_MAX_DIRTY values I would imagine that the probability of being in the middle of a write when power was lost would not be low enough for my taste. I do make the assumption that 7.x was able to determine that a page had not been fully written to the physical log. Considering that the last thing in a page is the current timestamp, this seems to be a reasonable assumption. Am I missing something, or misunderstanding the current implementation? Will ------------------------------------------------------------ This e-mail has been sent to you courtesy of OperaMail, as a free service from Opera Software, makers of the award-winning Web Browser, Opera. Visit us at http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free e-mail account is waiting at: http://www.operamail.com/ ------------------------------------------------------------

William Rice wrote: > I have concerns about whether or not 9.2 is as robust in bad situations > as 7.x was. > > My concerns center around the fact that fuzzy operations(updates, inserts, > and deletes) are not physically logged To me, this means if > I unplug my machine in the middle of a write to disk initiated by a > fuzzy operation I am now going to a backup and rolling forward logical > logs. > "Fuzzy checkpoint depends on write-ahead logging for fast recovery to work correctly." Excerpt from the Admin Guide. Even though changes that are a result of fuzzy operations are not logged in the physical log, they are being logged in the logical log. It is from the logical log that Informix will retrieve fuzzy changes in case they are needed during a "Fast recovery" that follows a system crash. Conceptually, fuzzy checkpoints change nothing in terms of recoverability except that "Fast Recovery" will usually take a longer time. Read the chapter "Checkpoints and Fast Recovery" in the Admin Guide. There's good stuff in there on this issue. Rudy

Remember that the Physical Log records are not STRICTLY needed for recovery. They are mostly a paranoid holdover. The physical log slapdown that starts Fast Recovery makes certain that as much as possible the engine has a clean slate to reapply the logical log records to. Remember that any delete or update logical log record set contains a before image of the row and any INSERT or UPDATE logical log record set contains an after image of the row. Using ONLY this information the engine can successfully and fully recover completed transactions and rollback incomplete ones. The only difference after Fast Recovery with PL slapdown and without PL slapdown should be the garbage in the unused slots from which inserted rows were rolled back due to incomplete transactions since the rollback does not know what garbage to put back while the PL slapdown would have previously cleaned the page (and if I'm willing to risk a headache I might think hard enough to say that even that will not be different. Most important keep in mind that in Fuzzy Checkpoints ONLY the data page flushing, and only for built in data types, is skipped. Logical and physical log records are written BEFORE ANY DATA PAGES ARE MODIFIED and only log buffering delays those records getting out to disk between checkpoints. Because of this the engine will absolutely recover to a consistent and known state after a crash, indeed to the same state that a restore would place it, it MAY just take longer and often it will not. In W. Curtis Preston's book "UNIX Backup & Recovery" Curtis spend many hours with me and several other self proclaimed experts in and out of Informix to carefully document the Informix logging process and its safety. While we did not know details of Fuzzy Checkpoints then that chapter is MUST reading for anyone who wants to be comfortable about how safe Informix's data is and how to minimize any risks. I know I did not completely understand the subject until Curtis dragged me up and down the docs and the explanations of others until he had it right. If nothing else the reading will point up how primitive redo and undo logging is and why we think of Informix as the most technically advanced database server on the market. Art S. Kagel Rudy Fernandes wrote: > > William Rice wrote: > > > I have concerns about whether or not 9.2 is as robust in bad situations > > as 7.x was. > > > > My concerns center around the fact that fuzzy operations(updates, inserts, > > and deletes) are not physically logged To me, this means if > > I unplug my machine in the middle of a write to disk initiated by a > > fuzzy operation I am now going to a backup and rolling forward logical > > logs. > > > > "Fuzzy checkpoint depends on write-ahead logging for fast recovery to work correctly." > Excerpt from the Admin Guide. > > Even though changes that are a result of fuzzy operations are not logged in the > physical log, they are being logged in the logical log. It is from the logical log > that Informix will retrieve fuzzy changes in case they are needed during a "Fast > recovery" that follows a system crash. > > Conceptually, fuzzy checkpoints change nothing in terms of recoverability except that > "Fast Recovery" will usually take a longer time. > > Read the chapter "Checkpoints and Fast Recovery" in the Admin Guide. There's good > stuff in there on this issue. > > Rudy

However, restoring the physical log does have one side effect that has never occurred to me before and that is; if any pages were only partially written when the power goes out then restoring the physical log will correct them. If the last page of the physical log was only partially written the check of the trailing timestamp will prevent it from being restored. I believe William's question is; what happens to logical recovery if it encounters a partially written page, which may be possible if the page isn't physically logged? The question being answered is; why do we need or not need the physical log? Note: Mirroring or UPS'es, common on large systems, virtually eliminating this possibility. "Art S. Kagel" wrote: > Remember that the Physical Log records are not STRICTLY needed for recovery. > They are mostly a paranoid holdover. The physical log slapdown that starts > Fast Recovery makes certain that as much as possible the engine has a clean > slate to reapply the logical log records to. Remember that any delete or > update logical log record set contains a before image of the row and any > INSERT or UPDATE logical log record set contains an after image of the row. > Using ONLY this information the engine can successfully and fully recover > completed transactions and rollback incomplete ones. The only difference > after Fast Recovery with PL slapdown and without PL slapdown should be the > garbage in the unused slots from which inserted rows were rolled back due > to incomplete transactions since the rollback does not know what garbage to > put back while the PL slapdown would have previously cleaned the page (and > if I'm willing to risk a headache I might think hard enough to say that > even that will not be different. > > Most important keep in mind that in Fuzzy Checkpoints ONLY the data page > flushing, and only for built in data types, is skipped. Logical and > physical log records are written BEFORE ANY DATA PAGES ARE MODIFIED and > only log buffering delays those records getting out to disk between > checkpoints. Because of this the engine will absolutely recover to a > consistent and known state after a crash, indeed to the same state that a > restore would place it, it MAY just take longer and often it will not. > > In W. Curtis Preston's book "UNIX Backup & Recovery" Curtis spend many hours > with me and several other self proclaimed experts in and out of Informix to > carefully document the Informix logging process and its safety. While we > did not know details of Fuzzy Checkpoints then that chapter is MUST reading > for anyone who wants to be comfortable about how safe Informix's data is > and how to minimize any risks. I know I did not completely understand the > subject until Curtis dragged me up and down the docs and the explanations > of others until he had it right. If nothing else the reading will point up > how primitive redo and undo logging is and why we think of Informix as the > most technically advanced database server on the market. > > Art S. Kagel > > Rudy Fernandes wrote: > > > > William Rice wrote: > > > > > I have concerns about whether or not 9.2 is as robust in bad situations > > > as 7.x was. > > > > > > My concerns center around the fact that fuzzy operations(updates, inserts, > > > and deletes) are not physically logged To me, this means if > > > I unplug my machine in the middle of a write to disk initiated by a > > > fuzzy operation I am now going to a backup and rolling forward logical > > > logs. > > > > > > > "Fuzzy checkpoint depends on write-ahead logging for fast recovery to work correctly." > > Excerpt from the Admin Guide. > > > > Even though changes that are a result of fuzzy operations are not logged in the > > physical log, they are being logged in the logical log. It is from the logical log > > that Informix will retrieve fuzzy changes in case they are needed during a "Fast > > recovery" that follows a system crash. > > > > Conceptually, fuzzy checkpoints change nothing in terms of recoverability except that > > "Fast Recovery" will usually take a longer time. > > > > Read the chapter "Checkpoints and Fast Recovery" in the Admin Guide. There's good > > stuff in there on this issue. > > > > Rudy

It taken me some time :-), but I can finally see where Will is coming from. "Art S. Kagel" wrote: > Remember that the Physical Log records are not STRICTLY needed for recovery. 'Course, they are. And the reasoning is related to the key point that Will has been trying to make all this time - failure causing a PARTIAL write of a page. Let me expand on that. Imagine an update affecting a row on a page. Imagine that the page contains other rows not affected by the update. Now, imagine cleaners kicking in to write dirty pages. Then, while this page is being written, the system crashes corrupting the page. Recovery would require that the before image of the entire page be kept somewhere, because not only has the updated row been lost, but also all other rows in that page, information which does NOT exist in the logical logs (because they never were targeted by the update stmt). Which then raises two points 1. (raised by Will). So are we essentially saying that a partial write to disk is too negligible to worry about? 2. If that is the case, why can't Physical logging, one of the most i/o intensive operations, be junked in entirety? Rudy > > They are mostly a paranoid holdover. The physical log slapdown that starts > Fast Recovery makes certain that as much as possible the engine has a clean > slate to reapply the logical log records to. Remember that any delete or > update logical log record set contains a before image of the row and any > INSERT or UPDATE logical log record set contains an after image of the row. > Using ONLY this information the engine can successfully and fully recover > completed transactions and rollback incomplete ones. The only difference > after Fast Recovery with PL slapdown and without PL slapdown should be the > garbage in the unused slots from which inserted rows were rolled back due > to incomplete transactions since the rollback does not know what garbage to > put back while the PL slapdown would have previously cleaned the page (and > if I'm willing to risk a headache I might think hard enough to say that > even that will not be different. > > Most important keep in mind that in Fuzzy Checkpoints ONLY the data page > flushing, and only for built in data types, is skipped. Logical and > physical log records are written BEFORE ANY DATA PAGES ARE MODIFIED and > only log buffering delays those records getting out to disk between > checkpoints. Because of this the engine will absolutely recover to a > consistent and known state after a crash, indeed to the same state that a > restore would place it, it MAY just take longer and often it will not. > > ... > > Art S. Kagel

Rudy Fernandes wrote: > > It taken me some time :-), but I can finally see where Will is coming from. > > "Art S. Kagel" wrote: > > > Remember that the Physical Log records are not STRICTLY needed for recovery. > > 'Course, they are. And the reasoning is related to the key point that Will has been trying > to make all this time - failure causing a PARTIAL write of a page. I'm getting a headache. OK, so there are two separate issues here. One is Physical logging needed at all, which it appears I brought up and not William. I agree with Rudy's assessment that physical logging is needed to protect against partial write damage. Thanks for finally explaining the need to me. The second issue is whether partial write damage may invalidate Fuzzy Checkpoint results. I think not because of physical logging :-) and the design of the Fuzzy Checkpoint. Partial writes are not an issue in the validity of Fuzzy Checkpointing. Follow my reasoning: If the physical log page was successfully written then the 'unmodified' data page MUST have been written and comitted to disk during a PRIOR full checkpoint or any physical and logical log records modifying the page from its original saved and committed version from prior checkpoints will have been written and committed. There is just no possibility that there exists a partially written page for which a fully written Physical Log page exists which was itself not fully written nor is it possible for a partially written data page to exist for which no Physical Log page was written. This is because in a Fuzzy checkpoint only logical and physical log records are written and in a full checkpoint all logical and physical log records are written before any data pages. (Obviously I am ignoring the added complexity of pages containing UDTs and CLOBS but the same arguments apply log pages are committed before data pages are output.) The above means that partial writes cannot cause uncorrectable page corruption. Does it mean that a system crash and any resultant partial writes could not cause the loss of committed transactions? It depends. Any UNBUFFERED database will flush all logical log records to disk from the current logical log buffer when ANY transaction commits. This includes any records in that buffer from other transactions including those from BUFFERED log databases. So if you have any very active database that is UNBUFFERED the effect is that ALL databases are as safe as UNBUFFERED databases. In this case the ONLY risk is that the system will crash BEFORE the logical log buffer can be safely committed to disk which is a good reason to keep these buffers (there are 3) small. Now IFF the buffer is not flushed before the crash there will still be NO SERVER CORRUPTION but that committed transaction that caused the flush (and any BUFFERED commits written to the next buffer during the flush) will not be complete when Fast recovery reaches the end of the logical logs and will be rolled back. This is minor data loss but a tiny risk which you can minimize by keeping LOGBUFF small and all databases UNBUFFERED. If all databases are BUFFERED then many transactions may be liable to the same risk as the logical log buffer will only flush when it fills. The real danger is that a multiple block logical log record will be partially written causing Fast Recovery to become confused so that tech support will need to log in and truncate the logical log at an earlier point so recovery can complete. Then a bit more data, perhaps another transaction or two, will be lost. To William's statement that he does not trust Fuzzy Checkpoint and so has to restore an archive and roll forward the logical logs, I submit that that will result in the exact same level of data loss as fast recovery OR MORE, but never less. Since you do not trust the output of the engine near the crash I suspect that you said 'no' when the restore asked if you wanted to backup the last logical logs from disk. In this case your restore has only the last backed up logical logs and at least a few records are missing so that more transactions will likely be rolled back than if you just trusted the engine designers. Art S. Kagel > Let me expand on that. > > Imagine an update affecting a row on a page. Imagine that the page contains other rows not > affected by the update. Now, imagine cleaners kicking in to write dirty pages. Then, while > this page is being written, the system crashes corrupting the page. > > Recovery would require that the before image of the entire page be kept somewhere, because > not only has the updated row been lost, but also all other rows in that page, information > which does NOT exist in the logical logs (because they never were targeted by the update > stmt). > > Which then raises two points > 1. (raised by Will). So are we essentially saying that a partial write to disk is too > negligible to worry about? > > 2. If that is the case, why can't Physical logging, one of the most i/o intensive > operations, be junked in entirety? > > Rudy > > > > > They are mostly a paranoid holdover. The physical log slapdown that starts > > Fast Recovery makes certain that as much as possible the engine has a clean > > slate to reapply the logical log records to. Remember that any delete or > > update logical log record set contains a before image of the row and any > > INSERT or UPDATE logical log record set contains an after image of the row. > > Using ONLY this information the engine can successfully and fully recover > > completed transactions and rollback incomplete ones. The only difference > > after Fast Recovery with PL slapdown and without PL slapdown should be the > > garbage in the unused slots from which inserted rows were rolled back due > > to incomplete transactions since the rollback does not know what garbage to > > put back while the PL slapdown would have previously cleaned the page (and > > if I'm willing to risk a headache I might think hard enough to say that > > even that will not be different. > > > > Most important keep in mind that in Fuzzy Checkpoints ONLY the data page > > flushing, and only for built in data types, is skipped. Logical and > > physical log records are written BEFORE ANY DATA PAGES ARE MODIFIED and > > only log buffering delays those records getting out to disk between > > checkpoints. Because of this the engine will absolutely recover to a > > consistent and known state after a crash, indeed to the same state that a > > restore would place it, it MAY just take longer and often it will not. > > > > ... > > > > Art S. Kagel

"Art S. Kagel" wrote: > I'm getting a headache. Roll out the acetaminophen :-) > OK, so there are two separate issues here. One is Physical logging needed > at all, which it appears I brought up and not William. I agree with > Rudy's assessment that physical logging is needed to protect against partial > write damage. Thanks for finally explaining the need to me. The second issue > is whether partial write damage may invalidate Fuzzy Checkpoint > results. I think not because of physical logging :-) and the design of the > Fuzzy Checkpoint. > > Partial writes are not an issue in the validity of Fuzzy Checkpointing. > Follow my reasoning: If the physical log page was successfully written then What physical log page? Fuzzy operations, by definition, do NOT trigger writes to the physical log. So, if a row in a previously "clean" buffer page is modified by a fuzzy update, the before-image of the page is NOT stored in the physical log. This is the crux of the issue. While the modification is recorded in the logical log, its only for the row that changed. Other rows within that page that weren't changed aren't recorded anywhere. Which now brings us to the crash... that, in our example, occurs just after LRU cleaning has been triggered by, say, MAX_DIRTY having been reached. Specifically, the disk is in the process of writing our "fuzzy" modified page (written the header timestamp but not the footer timestamp) when it is interrupted by the crash, leaving our page corrupt. Where is the server going to restore that page from, during Fast Recovery? Its not in the physical log (fuzzy update). Its not in the logical log (only one row's information exists there). Rudy > > the 'unmodified' data page MUST have been written and comitted to disk > during a PRIOR full checkpoint or any physical and logical log records > modifying the page from its original saved and committed version from prior > checkpoints will have been written and committed. There is just no > possibility that there exists a partially written page for which a fully > written Physical Log page exists which was itself not fully written nor is > it possible for a partially written data page to exist for which no Physical > Log page was written. This is because in a Fuzzy checkpoint only logical > and physical log records are written and in a full checkpoint all logical > and physical log records are written before any data pages. (Obviously > I am ignoring the added complexity of pages containing UDTs and CLOBS but > the same arguments apply log pages are committed before data pages are > output.) > > The above means that partial writes cannot cause uncorrectable page > corruption. Does it mean that a system crash and any resultant partial > writes could not cause the loss of committed transactions? It depends. > >