The MySQL optimize and analyze commands must be pretty confusing if judged by the CMDBA (Certified MySQL Database Administrator) exams. Hopefully this is because MySQL works wonderfully 'out of the box' and does not need a great deal of maintenance. I am looking at the questions on the exams where candidates are not performing as expected and optimize/analyze is a stumbling point.
There are several table maintenance operations that you need to know about to keep your database healthy. Failure to keep your database healthy can slow down queries and do other nasty things to your quality of life.
CHECK TABLE performs an integrity check on the structure and content of your tables. For MyISAM tables, it will also update the index statistics. If problems appear, proceed to ...
REPAIR TABLE is for MyISAM tables only and will correct corrupted tables. InnoDB tables should be dumped with mysqldump and restored.
ANALYZE TABLE updates a table's information about keys for queries so that the data can be found faster and not having to hunt for it. This works for both MyISAM and InnoDB.
OPTIMIZE TABLE defragments MyISAM tables, sorts indexes, and updates the statistics to speed up searches.
Automatic MyISAM repair can be enabled with the --myisam-recover option, either at startup or in a configuration file. You can direct the server to make a backup of any table before it changes, force recovery, or skip tables when then have no gaps from deletes or updates.
InnoDB will try to fix any repairs at startup as part of normal operations. But it is best to read the documentation on --innodb_force_recovery before you need to use it under pressure.
Please read the documentation on these commands and make sure you understand any locking issues before using them. With careful application, these commands can keep your data healthy.
No comments:
Post a Comment