Keeping MySQL Optimised

Posted:   |  More posts about System Administration

The following is a guide on how to keep databases in MySQL optimised.

Like a hard disk drive running a file-system such as FAT32, MySQL gets a bit fragmented during use. Databases that only have data inserted and read searially do not need optimisation run on the tables often. However, databases that are used in a transactional fashion need table optimisation performed regularly. You can write a script to manually run the MySQL "optimise" command iterating through a list of tables or you can simply use the "mysqlcheck" utility provided with MySQL (at least on Linux anyway).

The best way perform automatic optimisation is to create a Cron job. This one runs 11PM every Sunday and optimises every database on the MySQL server.

0 23 * * 0 mysqlcheck -u[your MySQL user] -o -A -p[your password here]

According to the mysqlcheck man page:

mysqlcheck uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE So as you would guess this utility also has options to automatically check the validity of tables and repair them.
Comments powered by Disqus
Contents © 2013 Daniel Devine - Nikola Powered - Flattr Me! Flattr this Source