Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.