Re: WHY index can corrupted???
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
Hi Art and all,
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
>
-- --
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
>>
>>
> -- --
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.
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
> >>
> >>
> > -- --