Re: WHY index can corrupted???`

Hi Madison, Unfortunately, this is just too flaky a condition to reproduce with any kind of sample dataset. However, I'll try to lay out a possible scenario at the end of the discussion. I know from experience that when we switched from UNLOGGED to LOGGED databases the incidence of index corruption dropped dramatically. When I switched to UNBUFFERED logging the problem virtually disappeared. Under no logging I was rebuilding several indexes a week on at least one of 8 servers at the time. Now with over 45 servers and unbuffered logging its more like a couple a year following a system crash or improper shutdown (our quarterly powerdown exercise is a likely culprit). One problem with determining cause is that index corruption is often not detected for weeks following a likely causative event. My contentions are the result of intense 'thought experiments' on how corruption could possibly happen in the face of the apparently airtight logging IDS uses (beyond possible bugs in the index maintenance code in the engine itself of course). As I posted, I don't think there is anything that IBM can do to solve the problem altogether. I believe the ultimate problem is caching controllers with write-back cache. The controllers perform their writebacks to disk in a physical ordered fashion ignoring the cronological ordering of the writes' arrival. After all from a filesytem standpoint it does not matter which sectors are missing after a controller failure, archive restoration is the only fix. In our world of databases, though, it makes a BIG difference. It's my contention that either the data or index page gets flushed physically to disk but not both and not the physical and/or logical log pages before the system crashes. There's nothing you can do in the software to prevent it, you can only minimize the window of risk and IB that using UNBUFFERED LOG does just that by forcing the logical log pages out to the controller LONG before the data and/or index pages are every flushed. This increases the probablility that those logical log pages will make it physically to disk before any crash and far more likely that they will be physically written to disk before the partial update data pages and physical log pages are even written to the controller cache. Once the logical log records are safely on disk the only remaining risk is if the next checkpoint record makes it to disk before the physical log pages and the system crashes in a way that prevents further controller cache flushes. So, test case? Set up a large unlogged and/or a buffered log database. Run a large update that affects several index pages in several indexes, commit it, and then pull the SCSI cable out of the controller. If that controller also controls the ROOT chunk the engine will restart, after appropriate re-plugging and perhaps rebooting, with no chunks marked down. Any DBA would issue a loud sigh of relief and watch the fast recovery complete happily. Now oncheck the indexes and you are likely to see the corruption I'm predicting. At least that's what I think was happening. Again it's hard to investigate back to some unknown past event that might have cuased index corruption discovered days or weeks later. Art S. Kagel ----- Original Message ----- From: Madison Pruet <mpruet@comcast.net> At: 8/28 18:17 > 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 d

Hi Art, If you are using cache controllers that only write to their cache when the os writes to them, this will definitely endanger your data and index integrity in a crash even with unbuffered log. Informix software assumes that everything that was written successfully to the os (using system calls like write()) will really be on disk after a crash. Only if the cache hardware can make sure that everything in the cache will be written to disk even in a crash or power loss the problem is avoided. Otherwise write cache cannot be recommended for enterprise critical data. Michael ART KAGEL, BLOOMBERG/ 65E 55TH wrote: > Hi Madison, > > Unfortunately, this is just too flaky a condition to reproduce with any kind of > sample dataset. However, I'll try to lay out a possible scenario at the end of > the discussion. I know from experience that when we switched from UNLOGGED to > LOGGED databases the incidence of index corruption dropped dramatically. When I > switched to UNBUFFERED logging the problem virtually disappeared. Under no > logging I was rebuilding several indexes a week on at least one of 8 servers at > the time. Now with over 45 servers and unbuffered logging its more like a > couple a year following a system crash or improper shutdown (our quarterly > powerdown exercise is a likely culprit). One problem with determining cause is > that index corruption is often not detected for weeks following a likely > causative event. My contentions are the result of intense 'thought experiments' > on how corruption could possibly happen in the face of the apparently airtight > logging IDS uses (beyond possible bugs in the index maintenance code in the > engine itself of course). > > As I posted, I don't think there is anything that IBM can do to solve the > problem altogether. I believe the ultimate problem is caching controllers with > write-back cache. The controllers perform their writebacks to disk in a > physical ordered fashion ignoring the cronological ordering of the writes' > arrival. After all from a filesytem standpoint it does not matter which sectors > are missing after a controller failure, archive restoration is the only fix. > In our world of databases, though, it makes a BIG difference. It's my > contention that either the data or index page gets flushed physically to disk > but not both and not the physical and/or logical log pages before the system > crashes. > > There's nothing you can do in the software to prevent it, you can only minimize > the window of risk and IB that using UNBUFFERED LOG does just that by forcing > the logical log pages out to the controller LONG before the data and/or index > pages are every flushed. This increases the probablility that those logical log > pages will make it physically to disk before any crash and far more likely that > they will be physically written to disk before the partial update data pages > and physical log pages are even written to the controller cache. Once the > logical log records are safely on disk the only remaining risk is if the next > checkpoint record makes it to disk before the physical log pages and the system > crashes in a way that prevents further controller cache flushes. > > So, test case? Set up a large unlogged and/or a buffered log database. Run a > large update that affects several index pages in several indexes, commit it, and > then pull the SCSI cable out of the controller. If that controller also > controls the ROOT chunk the engine will restart, after appropriate re-plugging > and perhaps rebooting, with no chunks marked down. Any DBA would issue a loud > sigh of relief and watch the fast recovery complete happily. Now oncheck the > indexes and you are likely to see the corruption I'm predicting. > > At least that's what I think was happening. Again it's hard to investigate back > to some unknown past event that might have cuased index corruption discovered > days or weeks later. > > Art S. Kagel > > ----- Original Message ----- > From: Madison Pruet <mpruet@comcast.net> > At: 8/28 18:17 > > >>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 syste