I plan on running 'mysqlcheck --optimize --all-databases' to clear up some fragmentation.
Would it be useful to run -check with this option or does the -optimize already take care of that? I am only concerned because we have large tables (over 100GB) and this is on a production system and we don't want to have unnecessary downtime.
tom.
Well, I can at least save you some time wondering about this. The mysqlcheck utility only does one thing at a time.
sqlbot@dev:~$ mysqlcheck --check --optimize --all-databases --verbose
Error: mysqlcheck doesn't support multiple contradicting commands.
So, if you want to do both, you'll need 2 runs. While --optimize doesn't appear to first go through the identical motions to "check" the tables, it does rebuild them, which would imply that it has to be able to read them successfully.
I've never seen CHECK TABLE find a new problem with an InnoDB table, but if it does, InnoDB will shut down the MySQL Server, according to the docs... and I would think it likely that "shut down" means "force crash" to keep the corruption contained. On the other hand, if you have tables that are corrupt, OPTIMIZE TABLE (in InnoDB) would likely "help you find" that corruption in much the same way.
Arguably, checking the tables before optimizing seems redundant, though I would welcome correction on this point; however, it also seems potentially wise in the face of such a large amount of data that you're about to rebuild, to invest the time running through the less obtrusive "check" process before launching into the "optimize" process.
Under the hood, all mysqlcheck really does is connect to the server as a client and execute the corresponding SQL statements.
