mysqlcheck with cron to optimize automatically

Over a period of time, mysql tables usually get fragmented. It degrades the performance of mysql-server significantly.

WordPress has many plugins to do this but it’s better to defragment tables manually from command-line using mysqlcheck

Using mysqlcheck

Run the following command by replacing USER & PASS values:

mysqlcheck -Aos -u USER -pPASS

It will show you some output e.g. “note : Table does not support optimize, doing recreate + analyze instead” if you have some InnoDB tables. You can ignore these notes. I really think mysqlcheck should show only errors rather than showing notes.

Automating mysql defragmentation using cron

You can run mysqlcheck using cron automatically.

Open your crontab -e and add following line in it:

0         4      *       *       *       mysqlcheck -Aos -u USER -pPASS > /dev/null 2>&1

Above will run mysqlcheck daily at 4AM.

Optimizing MyISAM tables only

As InnoDB tables do not support optimization, you may be interested in optimizing MySQL tables only.

You can do that using following: (source)

for i in `mysql -e 'select concat(table_schema,".",table_name) from information_schema.tables where engine="MyISAM"'`; do mysql -e "optimize table $i"; done

More…

14 responses to “mysqlcheck with cron to optimize automatically”

  1. Thanks so much for this series of tutorials. They have been crucial to getting my VPS up and running, and its performing great. A note on the command above for optimizing MyISAM tables. I ran as root as the linked page indicated but returns error.
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
    Suggestions? Thanks again.

  2. Hi,
    Can you comment please on the InnoDB tables that do not allow this command – So does that mean they are no good since they dont get optimized ever? Should everything be myISAM? Because I think my entire database is made of InnoDB tables.

  3. I do not have in-depth knowledge about differences between MyISAM and InnoDB but they both have their use-cases.

    MyISAM is good for full-text search. MyISAM is good for transactional usage.

    IMHO biggest difference is concurrent writes. MyISAM locks entire table but InnoDB offers row-level locking.

    You can find a lot about their differences at http://www.google.com/search?q=myisam+vs+innodb

    About optimizating InnoDB, as per: http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html

    For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which
    rebuilds the table to update index statistics and free unused space in
    the clustered index.

    May be you have seen following in output:

    Table does not support optimize, doing recreate + analyze instead

  4. Hi,

    Thank you for your reply. I’ve seen that output for every single table I think since I saw no other messages but a few screen fulls of the
    Table does not support optimize, doing recreate + analyze instead

    I’ve read already what google search offers and it would make sense for WordPress to have row locking especially for multi-site I would think since WordPress likes a lot of queries so if 10 users are on someone would get a locked database with MYISAM. Makes sense, but I don’t see any DBA willing to be pinned down to that logic.

    Thank you again,

    Nick

    • WordPress doesn’t specify storage engine explicitly. Whatever set by default for a mysql version gets used. On MySQL 5.5 onwards, default storage engine is InnoDB.

      As far as locking is concerned, it will come into picture only for WRITE operation. So rather than multi-site its more about running multi-author blog. Any table where too many concurrent writes are performed. On multi-site, every blog gets its own set of table so concurrent WRITE operations are generally performed on different tables.

      Anyway, personally I will be changing storage engine for this site from MyISAM to InnoDB soon. This is only site where we have MyISAM for WordPress tables as this was setup years ago on MySQL 5.0.

  6. I use this script, can’t remember its source but I posted on pastebin: http://pastebin.com/sm9x6gK8

    You save it somewhere safe from prying eyes because you save your user/password inside then run it like this from the command line or via cron: php optimize_dbs

    Any difference to what your command does?

  7. The script might be useful for shared hosting users who do not have shell access to server to run above commands.

    Apart from that, the script is inefficient and do extra work which is not required.

  8. Can we optimize and repair tables using MySQL events? Are there any downside of automatic method? I mean data loss or corrupt?