Re: WHY index can corrupted???`
Madison, I agree we flush the phy log buf. But do we really flush the logical log buf (except when we do no physical logging in some specially optimized cases)? Michael Madison Pruet wrote: > Art, > > Do you have a repro/case involved where this occurred? We are supposed to > be flushing the physical log buffer and the logical log buffers to disk > prior to writing the data page/index page to disk. If there is a case where > we aren't, then we should consider it a bug that needs to be resolved. > > > M.Pruet > > "Art S. Kagel" <kagel@bloomberg.net> wrote in message > news:pan.2003.08.28.15.27.55.322564.10594@bloomberg.net... > >>On Thu, 28 Aug 2003 09:51:41 -0400, Michael Mueller wrote: >> >> >>>Hi Art and all, >> >>There's nothing that can be fixed in IDS to get rid of the risk in > > BUFFERED LOG > >>databases. Here's a typical scenario: >> >>1-Transaction updates a row implying an index page in the buffer cache > > must be > >>updated. >> >>2-The preimage of the page is written to the physical log buffer. >> >>3-Record of the update itself is written to the logical log buffer. >> >>4-Data and index pages are updated in the buffer cache. >> >>5-An LRU containing the index page or the data page but not both is > > flushed to > >>disk because that LRU has reached its LRU_MAX_DIRTY level. >> >>6-System crash or improper shutdown. >> >>7-System/Engine restart - BOOM index corruption! >> >>Cause: The logical and physical log buffers have never been flushed to > > disk > >>(database is BUFFERED LOG right?) but the data or the index page has been >>flushed! So the index reflects a key value that is not reflected in the > > row or > >>indicates a row that's been deleted or that should have been inserted but > > was > >>not or omits a row that's been inserted or that should have been deleted > > but > >>wasn't. On restart fast recovery cannot correct the problem because the >>physical and/or logical log pages needed to undo this part of the partial >>transaction were never flushed! While this is apparently an odd set of >>circumstances, I've seen enough corrupted indexes to know that it does > > indeed > >>happen in the real world. >> >>If this scenario happens in an UNBUFFERED LOG database instance the > > logical log > >>buffers are immediately flushed as soon as ANY transaction on the server >>completes so the window of risk in this case is miniscule even compared to > > the > >>very small window in a BUFFERED LOG environment. >> >>The only way to protect yourself if you use BUFFERED LOG is to use a very > > high > >>value for LRU_MAX_DIRTY and a relatively short checkpoint interval so all > > buffer > >>flushes occur at checkpoint time. Since logical and physical log buffers > > are > >>flushed at the beginning of the checkpoint and so before any dirty > > data/index > >>pages are flushed then the risk is eliminated unless FG writes occur. >> >>Art S. Kagel >> >> >>>I don't doubt that some of you have observed index corruption in logged >>>databases after system crashes. If this was not caused by faulty >> > hardware or > >>>os software that corrupts disk contents directly (index and data pages, >> > log > >>>space, etc.) it should be considered an Informix fast recovery bug and >> > should > >>>be fixed if possible. This should also apply to buffered logging. >>> >>>Michael >>> >>>Art S. Kagel wrote: >>> >>>>On Tue, 12 Aug 2003 22:01:38 -0400, miyaki wrote: >>>> >>>>There are several sources of index corruption generally in a few >>> > classes: > >>>>Caused by bugs: >>>>There was a known BTREE-CLEANER bug that would occassionally corrupt >>> > indexes > >>>>but I no longer remember what release(s) are affected. >>>> >>>>Caused by crashes: >>>>If you use caching controllers and/or an intelligent disk farm there is >>> > a > >>>>real risk that a system crash can leave data in the cache. This may or >>> > may > >>>>not be flushed to disk eventually and you may or may not want it so >>> > flushed. > >>>>Especially prevalent after a controller failure. >>>> >>>>Caused by improper shutdown: >>>>Often I've found corruption similar to what one normally finds after a >>> > system > >>>>crash following a supposedly graceful system shutdown/restart. This is >>>>caused by the rc files sending a shutdown to the database and returning >>>>immediately without waiting for the server to actually shutdown. The >>> > delayed > >>>>shutdown can be caused by a large number of dirty buffers not yet >>> > flushed or > >>>>applications in critical sections for which the server must wait before >>>>starting the checkpoint preparatory to a shutdown. >>>> >>>>Caused by database logging mode: >>>>UNLOGGED databases are at the highest risk for index corruption as the >>> > engine > >>>>has no way to recover a partially written index update after a crash or >>>>improper shutdown. These are most likely to suffer corruption caused >>> > by the > >>>>other issues. >>>> >>>>BUFFERED LOG databases are still at some risk for corruption, >>> > especially if > >>>>there are no active UNBUFFERED LOG databases running in the same >>> > instance. > >>>>This is because a BUFFERED LOG database does not force the logical log >>> > buffer > >>>>to flush when committing a transaction. UNBUFFERED LOG databases >>> > always > >>>>force a logical log buffer flush immediately upon writing a COMMIT >>> > record to > >>>>the logical log buffer and so are rarely found to have a corrupted >>> > index. If > >>>>you have a mixture of BUFFERED and UNBUFFERED databases you may be OK >>> > if the > >>>>UNBUFFERED databases are rather active since the BUFFERED database >>> > records > >>>>will be caught up in the flushes forced by the UNBUFFERED database >>> > activity. > >>>>Note that having databases that are UNBUFFERED LOG databases offers the >>>>additional risk that the engine will not be able to restart after a >>> > crash > >>>>without tech support intervention. This can be caused when a commit >>> > requires > >>>>multiple writes to the logical logs and the commit is partially written >>> > to > >>>>one buffer which fills and is flushed, then the final COMMIT is written >>> > to > >>>>the next logical log buffer which is not immediately flushed before the >>>>system crashes. On restart the engine will hang when it encounters the >>> > end of > >>>>the logical log in the middle of a partially written commit. >>>> >>>>Art S. Kagel >>>> >>>> >>>> >>>> >>>>>Hi all, >>>>> >>>>>OS : Sun Solaris 2.7 >>>>>IDS : 7.31.UD5 >>>>> >>>>>Recently my database engine was crashed due to the index corruption. >>>>@@
OK -- we only flush the logical log if we need to, but the physical log buffers are always flushed synchronously prior to flushing the page buffers. That would mean that recovery should work correctly. "Michael Mueller" <michael.mueller01@kay-mueller.de> wrote in message news:3F4F340E.2000503@kay-mueller.de... > Madison, > > I agree we flush the phy log buf. But do we really flush the logical log > buf (except when we do no physical logging in some specially optimized > cases)? > > Michael > > Madison Pruet wrote: > > Art, > > > > Do you have a repro/case involved where this occurred? We are supposed to > > be flushing the physical log buffer and the logical log buffers to disk > > prior to writing the data page/index page to disk. If there is a case where > > we aren't, then we should consider it a bug that needs to be resolved. > > > > > > M.Pruet > > > > "Art S. Kagel" <kagel@bloomberg.net> wrote in message > > news:pan.2003.08.28.15.27.55.322564.10594@bloomberg.net... > > > >>On Thu, 28 Aug 2003 09:51:41 -0400, Michael Mueller wrote: > >> > >> > >>>Hi Art and all, > >> > >>There's nothing that can be fixed in IDS to get rid of the risk in > > > > BUFFERED LOG > > > >>databases. Here's a typical scenario: > >> > >>1-Transaction updates a row implying an index page in the buffer cache > > > > must be > > > >>updated. > >> > >>2-The preimage of the page is written to the physical log buffer. > >> > >>3-Record of the update itself is written to the logical log buffer. > >> > >>4-Data and index pages are updated in the buffer cache. > >> > >>5-An LRU containing the index page or the data page but not both is > > > > flushed to > > > >>disk because that LRU has reached its LRU_MAX_DIRTY level. > >> > >>6-System crash or improper shutdown. > >> > >>7-System/Engine restart - BOOM index corruption! > >> > >>Cause: The logical and physical log buffers have never been flushed to > > > > disk > > > >>(database is BUFFERED LOG right?) but the data or the index page has been > >>flushed! So the index reflects a key value that is not reflected in the > > > > row or > > > >>indicates a row that's been deleted or that should have been inserted but > > > > was > > > >>not or omits a row that's been inserted or that should have been deleted > > > > but > > > >>wasn't. On restart fast recovery cannot correct the problem because the > >>physical and/or logical log pages needed to undo this part of the partial > >>transaction were never flushed! While this is apparently an odd set of > >>circumstances, I've seen enough corrupted indexes to know that it does > > > > indeed > > > >>happen in the real world. > >> > >>If this scenario happens in an UNBUFFERED LOG database instance the > > > > logical log > > > >>buffers are immediately flushed as soon as ANY transaction on the server > >>completes so the window of risk in this case is miniscule even compared to > > > > the > > > >>very small window in a BUFFERED LOG environment. > >> > >>The only way to protect yourself if you use BUFFERED LOG is to use a very > > > > high > > > >>value for LRU_MAX_DIRTY and a relatively short checkpoint interval so all > > > > buffer > > > >>flushes occur at checkpoint time. Since logical and physical log buffers > > > > are > > > >>flushed at the beginning of the checkpoint and so before any dirty > > > > data/index > > > >>pages are flushed then the risk is eliminated unless FG writes occur. > >> > >>Art S. Kagel > >> > >> > >>>I don't doubt that some of you have observed index corruption in logged > >>>databases after system crashes. If this was not caused by faulty > >> > > hardware or > > > >>>os software that corrupts disk contents directly (index and data pages, > >> > > log > > > >>>space, etc.) it should be considered an Informix fast recovery bug and > >> > > should > > > >>>be fixed if possible. This should also apply to buffered logging. > >>> > >>>Michael > >>> > >>>Art S. Kagel wrote: > >>> > >>>>On Tue, 12 Aug 2003 22:01:38 -0400, miyaki wrote: > >>>> > >>>>There are several sources of index corruption generally in a few > >>> > > classes: > > > >>>>Caused by bugs: > >>>>There was a known BTREE-CLEANER bug that would occassionally corrupt > >>> > > indexes > > > >>>>but I no longer remember what release(s) are affected. > >>>> > >>>>Caused by crashes: > >>>>If you use caching controllers and/or an intelligent disk farm there is > >>> > > a > > > >>>>real risk that a system crash can leave data in the cache. This may or > >>> > > may > > > >>>>not be flushed to disk eventually and you may or may not want it so > >>> > > flushed. > > > >>>>Especially prevalent after a controller failure. > >>>> > >>>>Caused by improper shutdown: > >>>>Often I've found corruption similar to what one normally finds after a > >>> > > system > > > >>>>crash following a supposedly graceful system shutdown/restart. This is > >>>>caused by the rc files sending a shutdown to the database and returning > >>>>immediately without waiting for the server to actually shutdown. The > >>> > > delayed > > > >>>>shutdown can be caused by a large number of dirty buffers not yet > >>> > > flushed or > > > >>>>applications in critical sections for which the server must wait before > >>>>starting the checkpoint preparatory to a shutdown. > >>>> > >>>>Caused by database logging mode: > >>>>UNLOGGED databases are at the highest risk for index corruption as the > >>> > > engine > > > >>>>has no way to recover a partially written index update after a crash or > >>>>improper shutdown. These are most likely to suffer corruption caused > >>> > > by the > > > >>>>other issues. > >>>> > >>>>BUFFERED LOG databases are still at some risk for corruption, > >>> > > especially if > > > >>>>there are no active UNBUFFERED LOG databases running in the same > >>> > > instance. > > > >>>>This is because a BUFFERED LOG database does not force the logical log > >>> > > buffer > > > >>>>to flush when committing a transaction. UNBUFFERED LOG databases > >>> > > always > > > >>>>force a logical log buffer flush immediately upon writing a COMMIT > >>> > > record to > > > >>>>the logical log buffer and so are rarely found to have a corrupted > >>> > > index. If > > > >>>>you have a mixture of BUFFERED and UNBUFFERED databases you may be OK > >>> > > if the > > > >>>>UNBUFFERED databases are rather active since the BUFFERED database > >>> > > records > > > >>>>will be caught up in the flushes forced by the UNBUFFERED database > >>> > > activity. > > > >>>>Note that having databases that are UNBUFFERED LOG databases offers the > >>>>additional risk that the engine will not be able
Michael Mueller wrote: > I agree we flush the phy log buf. But do we really flush the logical log > buf (except when we do no physical logging in some specially optimized > cases)? There's a thing called log write-ahead protocol that, AFAIK, is required to ensure recoverability. That means that the logical log information must be on disk before the other changes are committed. That applies absolutely at checkpoints (unless fuzzy checkpoint did something I'm not aware of - a possibility), and maybe even before pages get written out normally. At the least, that was the version 5 theory. Anybody aware of when the rules changed. (I suspect that a few details above are a bit slipshod - corrections or amendments welcome.) > Madison Pruet wrote: >> Do you have a repro/case involved where this occurred? We are >> supposed to be flushing the physical log buffer and the logical >> log buffers to disk prior to writing the data page/index page to >> disk. If there is a case where we aren't, then we should consider >> it a bug that needs to be resolved. >> >> "Art S. Kagel" <kagel@bloomberg.net> wrote: >>> On Thu, 28 Aug 2003 09:51:41 -0400, Michael Mueller wrote: [...major snippage because the indentation is shot to pieces...] -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/