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.

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

share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

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.

share|improve this answer
add comment

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 !!!

share|improve this answer
    
+1 for an extremely convoluted by equally beautiful answer to the question. Should we retag high availability? ;) –  msanford Sep 19 '12 at 16:21
    
Thanks for the solution I will not be able to do this for now but this is a good solution in the long term. As far as the DBVIP is concerned what tool would you suggest ? heartbeat/pacemaker perhaps ? –  user94437 Sep 19 '12 at 16:36
    
You can either heartbeat or pacemaker. My company uses ucarp ( www.ucarp.org or pureftpd.org/project/ucarp ) –  RolandoMySQLDBA Sep 19 '12 at 16:43
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.