Saturday, February 21, 2009

Happy with myisamchk

We've had problems time to time with head placement issues with disk drives (let's just say I'm no longer a fan of Seagate).  What happens is that a 512-byte sector gets written out to the wrong sector, corrupting the database.  Up to now we've been using innodb exclusively for our tables.  innodb has a nice built-in mechanism that lets you detect such an occurrence --- page level checksums.  As you know, innodb "tablespace" files are divided up into 16K pages.  Each page has a header and a trailer.  The page header contains a checksum computed from the page's content, and the trailer contains a number (log sequence number) that is redundant with the same number in the header.  So when a sector gets written out to the wrong spot in an innodb file, the checksum and/or the header/trailer mismatch will flag the problem.

I wrote a little utility in C that scans over an innodb file checking all the checksums.  I just plagiarized the code from the sources that computes checksums.  I love this utility.  I run it over all our backups after they are freshly made to gain confidence in them.

Recently we created a new "warehouse" db that's using myisam exclusively.  I was wondering if myisamchk could detect the aftermath of a head placement fault.  I made a copy of the .frm, .MYI, and .MYD files for a large table, and wrote a little C program that would go in and copy 512-bytes from one place to another in a file.  I ran "myisamchk --check" over differently corrupted versions of the .MYI and .MYI files, and it successfully detected a problem every time.  I was pleased that "myisamchk --check" was able to do the job rather than having to resort to "myisamchk --extend-check", which is several times slower.  I'll be using myisamchk to verify our backups.

No comments: