mysql myisamchk vs mysqlcheck We have a mysql database running version 5.0. Every month we shutdown the database for maintenance. We run a myisamchk on the tables to fix data fragmentation, and this takes down the system for up to an hour.  

Will it be better to run optimize table daily via a cron so as to eliminate the downtime or is it necessary to do the myisamchk periodically ?.
I have a beautiful idea for you on this one...

If you can setup the following:


Circular Replication

Two DB Servers
Each Server is a Master to the Other
Each Server is a Slave to the Other

Use a DBVIP that will point to one DB Server
Confine all Reads/Writes to the DBVIP


You can perform this OPTIMIZE TABLE maintenance on the DB Server that does not have the DBVIP.

First, construct the script on the Slave (the DB Server that does not have the DBVIP) that will run OPTIMIZE TABLE on all your tables. Then, run it on the Slave:

MYSQL_CONN="-u... -p..."
SQL="SELECT CONCAT('OPTIMIZE TABLE ',table_schema,'.',table_name,';')"
SQL="${SQL} OptimizeTableSQL FROM information_schema.tables WHERE table_schema NOT IN"
SQL="${SQL} ('information_schema','performance_schema','mysql')"
SQL="${SQL} ORDER BY (data_length+index_length)"
echo "SET sql_log_bin = 0;" &gt; /root/OptimizeAllTables.sql
mysql ${MYSQL_CONN} -AN -e"${SQL}" &gt;&gt; /root/OptimizeAllTables.sql
mysql ${MYSQL_CONN} &lt; /root/OptimizeAllTables.sql


The first line of the script is SET sql_log_bin = 0;. This tells the session not to record OPTIMIZE TABLE in its binary logs. This prevents OPTIMIZE TABLE from running on the Master (the DB Server with the DBVIP). Once a month, you can just take down the DBVIP from one DB Server and bring up the DBVIP on the Other DB Server. When you do that, you must also setup the OPTIMIZE TABLE script to on the Other Machine. The key is to run this on the DB Server that does not have the DBVIP.

Give it a Try !!!