RE: Fast method needed to determine if a table is corrupt
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:[EMAIL PROTECTED] Sent: November 8, 2004 12:43 PM To: Tim Murtaugh Cc: '[EMAIL PROTECTED]' 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 [EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
Re: Fast method needed to determine if a table is corrupt
Hello. I assume that MyISAM tables are checked thorougly, i.e. each record is read and compared to the table definition. How big your table is and what kind of storage it is on? If the time to check is comparable to the time needed to actually read the whole data from the storage - then it's probably the best time. You can ask in [EMAIL PROTECTED] then. Tim Murtaugh [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fast method needed to determine if a table is corrupt
Hi, 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? 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? I want to make this an automated check that I can write in C. Are there third-party tools available to help me? Thanks, Tim
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 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fast method needed to determine if a table is corrupt
Tim Murtaugh wrote: Hi, 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. You need to provide a way to have an orderly shutdown on power loss. You are trying to treat the symptom, not the problem. 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? 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? I want to make this an automated check that I can write in C. Are there third-party tools available to help me? Thanks, Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
