RE: WHY index can corrupted???`

Great Explanation Art, What if we add to this scenario KAIO? Would it be a very serious risk of getting data corruption or even lost data ? -----Mensaje original----- De: Art S. Kagel [mailto:kagel@bloomberg.net] Enviado el: Jueves, 28 de Agosto de 2003 01:28 p.m. Para: informix-list@iiug.org Asunto: Re: WHY index can corrupted???` 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. >>>The engine was back up after repairing the index by using the oncheck >>>command. I'm wondering why the index can be corrupted? In fact, I >>>just performed the database re-org by using the dbexport-import at 3 >>>months ago. >>> >>>Anybody knows the reason that may cause the index corruption??? >>> >>>Appreciate for the comments. >>> >>>TIA, >>>MIyaki >>> >>>__________________________________ >>>Do you Yahoo!? >>>Yahoo! SiteBuilder - Free, easy-to-use web site design software >>>http://sitebuilder.yahoo.com sending to informix-list >> >> > -- -- sending to informix-list

On Thu, 28 Aug 2003 17:03:26 -0400, Francisco Roldan wrote: > Great Explanation Art, > > What if we add to this scenario KAIO? No effect. > Would it be a very serious risk > of getting data corruption or even lost data ? Data loss in the sense, as after any crash, that a transaction will be rolled back. That's not the problem so much as the index corruption. Art S. Kagel > -----Mensaje original----- > De: Art S. Kagel [mailto:kagel@bloomberg.net] Enviado el: Jueves, 28 de Agosto > de 2003 01:28 p.m. Para: informix-list@iiug.org Asunto: Re: WHY index can > corrupted???` > > > 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. The >>>>engine was back up after repairing the index by using the oncheck command. >>>>I'm wondering why the index can be corrupted? In fact, I just performed the >>>>database re-org by using the dbexport-import at 3 months ago. >>>> >>>>Anybody knows the reason that may cause the index corruption??? >>>> >>>>Appreciate for the comments. >>>> >>>>TIA, >>>>MIyaki >>>> >>>>__________________________________ >>>>Do you Yahoo!? >>>>Yahoo! SiteBuilder - Free, easy-to-use web site design software >>>>http://sitebuilder.yahoo.com sending to informix-list >>> >>> >> -- -- > sending to informix-list