RE: WHY index can corrupted???`

Exactly! That is why i asked for KAIO , i am not sure but i suppose that the OS uses an intermediate-volatil cache. And the same happens with Hardware-RAID with cache, but some of these uses batteries. Informix thinks that the data is already written directly to the disk when a ckeckpoint occurs, but if the OS or Raid put the index data in an intermediate cache, but the actual table data is written to the disk, that could be a risk for index corruption, or viceversa. I am not quite an expert in this issue and i may be (or even probably be) wrong, What do the experts on the list think about it? regards -----Mensaje original----- De: Michael Mueller [mailto:michael.mueller01@kay-mueller.de] Enviado el: Viernes, 29 de Agosto de 2003 10:08 a.m. Para: informix-list@iiug.org Asunto: Re: WHY index can corrupted???` 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

This is not a problem of the os or KAIO. The os (and KAIO) tells the database server that the data was successfully written. But really it's stuck in some caching controller's or raid boxes or whatever cache outside the control of the os. Michael Francisco Roldan wrote: > Exactly! > That is why i asked for KAIO , i am not > sure but i suppose that the OS uses an intermediate-volatil > cache. > > And the same happens with Hardware-RAID with cache, but > some of these uses batteries. > > Informix thinks that the data is already written directly to the disk > when a ckeckpoint occurs, but if the OS or Raid put the index data in an > intermediate cache, but the actual table data is written to the disk, > that could be a risk for index corruption, or viceversa. > > I am not quite an expert in this issue and i may be (or even probably be) > wrong, > > What do the experts on the list think about it? > > regards > > -----Mensaje original----- > De: Michael Mueller [mailto:michael.mueller01@kay-mueller.de] > Enviado el: Viernes, 29 de Agosto de 2003 10:08 a.m. > Para: informix-list@iiug.org > Asunto: Re: WHY index can corrupted???` > > > 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@