mysql myisamchk vs mysqlcheck

Question

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 ?.

Answer 1

According to the reference for myisamchk (emphasis mine):

You can optimize a table in the same way by using the OPTIMIZE TABLE SQL statement. OPTIMIZE TABLE does a table repair and a key analysis, and also sorts the index tree so that key lookups are faster. There is also no possibility of unwanted interaction between a utility and the server, because the server does all the work when you use OPTIMIZE TABLE.

I would run optimize table periodically (and as you suggested, more often than once as month so the operation completes faster) with the server running. Just keep in mind:

Note that MySQL locks the table during the time OPTIMIZE TABLE is running.

Answer 2

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;" > /root/OptimizeAllTables.sql
mysql ${MYSQL_CONN} -AN -e"${SQL}" >> /root/OptimizeAllTables.sql
mysql ${MYSQL_CONN} < /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 !!!