List:General Discussion« Previous MessageNext Message »
From:Mark Steele Date:November 10 2004 8:34pm
Subject:RE: Fast method needed to determine if a table is corrupt
View as plain text  
Hi All,

InnoDB tables as the solution is incorrect.
I've been running some fairly large InnoDB databases,
and crashes using InnoDB are probably ALOT worse than
with MyIsam tables.

InnoDB tables tend to corrupt very easily on such things
as power outages, with corrupted page data error which means
that mysql doesn't start at all, and the only option is to
start InnoDB in recovery mode, dump and hope for the best.

How big is your database? If it can fit in RAM, I'd suggest
using a ramdisk to store your database with snapshots taken
every X minutes and stored to disk (or using NVRAM to store
the database).

Other than that, there's no quick way to check for corruption
that I know of.

Cheers,

Mark Steele
Implementation Director
CDT Inc.


-----Original Message-----
From: Dan Nelson [mailto:dnelson@stripped] 
Sent: November 8, 2004 12:43 PM
To: Tim Murtaugh
Cc: 'mysql@stripped'
Subject: Re: Fast method needed to determine if a table is corrupt


In the last episode (Nov 08), Tim Murtaugh said:
> I'm using MySQL server version 4.0.15a in an embedded envirionment (as

> a standalone server, I'm not using the embedded server library). I 
> have 128 MB of memory and disk space is tight. I'm using MyISAM 
> tables.
>  
> If my system loses power, some tables are left in a corrupt state. As 
> stated in the MySQL documentation, I think the data tables are OK, its

> just that the tables were not closed properly and are considered 
> corrupt by MySQL.
>  
> I need a FAST way to determine if a table is corrupt. I've tried 
> myisamcheck --fast and --check-only-changed options, and increased the

> buffer sizes (-O key_buffer_size and -O sort_buffer_size), as 
> mentioned in the documentation. The fastest time I can achieve is 
> 6:55.
>  
> I've also tried "CHECK TABLE tablename FAST QUICK" on a table I know 
> is marked as corrupt, and the fastest time I can achieve is 6:58.
>  
> I need to detemine if a table is corrupt within a few SECONDS, not 
> minutes. How can I do this?

Make your tables smaller? :)  You have to check each record to see that
it's okay.  If your tables are big, you have to spend time reading them.
  
> The documentation says there is a flag in myisam tables that indicates

> when a table is corrupt. Is there a way I can quickly check this flag?

If mysql tries to read a record or index and can't, it sets this flag to
keep you from accessing the table until you repair it.

You may be better off using InnoDB tables and taking the
space/performance hit.  InnoDB uses a logfile to allow it to roll back
partially-commited transactions after a crash, so you never have to
check or repair your tables.

-- 
	Dan Nelson
	dnelson@stripped

Attachment: [application/x-pkcs7-signature] smime.p7s
Attachment: [application/x-pkcs7-signature] smime.p7s
Thread
Fast method needed to determine if a table is corruptTim Murtaugh8 Nov
  • Re: Fast method needed to determine if a table is corruptDan Nelson8 Nov
    • RE: Fast method needed to determine if a table is corruptMark Steele10 Nov
  • Re: Fast method needed to determine if a table is corruptgerald_clark8 Nov
  • Re: Fast method needed to determine if a table is corruptGleb Paharenko9 Nov