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
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.
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
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.
Looks like you have run mysql command without specifying pasword.
Try again with password and it might work.
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.
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
May be you have seen following in output:
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,
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.
I recently posted about MyISAM to InnoDB conversion process here – http://rtcamp.com/tutorials/wordpress-mysql-myisam-innodb/
Should change mysql -e to mysql -se to supress header information
With -s, results are less readable.
Anyway, you use
Both works. 🙂
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?
Any feedback Rahul?
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.
Can we optimize and repair tables using MySQL events? Are there any downside of automatic method? I mean data loss or corrupt?
I never used MySQL events for anything myself so I am not right person to answer this question. Sorry.
May be http://dba.stackexchange.com/ will be correct place.