optimize and check table mysql

Question

I have a database used for statistics, with over 2000 tables, each with ~100 millions rows.

I think a few times a week it'd be nice to check the tables to make sure they are healthy, and if not, repair and optimize them.

60% of the tables are updated everyday, and the rest are archives.

My question is about check(repair)/optimize: Is it good to check table health and optimize them a few times a week to make sure the system runs smoothly?

Answer 1

Generally you need to OPTIMIZE table when its datafile is too much fragmented on disk (use tools for your filesystem to check - the script linked by GiantRobot is not calculating fragmentation), when there were many rows updated and changed their size (that would create row fragmentation) or after deleting many records when you won't be adding them again soon. Because free space is used by MySQL for new rows, when the deleted and new records have the same row size, no OPTIMIZE is needed.

CHECK TABLE is used only when you suspect data corruption (which should not occur during normal run). Some linux distributions (Debian for example) have startup scripts which run CHECK TABLE for all tables on MySQL server startup. REPAIR TABLE is then used to repair the corrupted tables.

ANALYZE TABLE can be used to update index cardinality, which is used in determining the query execution plan. Usually it is needed only in special cases.

It is not clear from your question how are your statistics tables used... how many writes, deletes and reads? My statistic tables have writes all the time, and once a day it is read, data consolidated and written to other table, then deleted. In such case, there is no need to run OPTIMIZE as data are not read often and free space is reused for new data. I am using partitioning by day, so instead of deleting the records (which is quite slow) I just DROP PARTITION (which takes 1 sec max.)