MySQL Tuner OPTIMIZE and Defragmentation

Question

I have just started using mysqltuner to help keep my mysql server fast and healthy. However, I am having a little trouble optimizing my tables. When I execute mysqltuner, it all works fine and returns the results. It states that there are 317 fragmented tables and that I should run 'OPTIMIZE' table to repair them.

So, I wrote a script that executes each week to 'OPTIMIZE' every table in the databases on the local server. The script seems to work fine because of the results that are returned. Despite this, when I run mysqltuner again, it still says there are 317 (actually, to be fair, it has reduced by 4 to 313) fragmented tables and that I should OPTIMIZE them still.

Is there something else I should do? Is my script right?

Here is my OPTIMIZE script:

mysqlcheck -u'admin' -p`cat /etc/psa/.psa.shadow` --auto-repair --optimize --all-databases;

Answer 1

Once you delete some rows from a table its data file could become fragmented and that table becomes unoptimized in terms of MySQL. If you really do frequently delete rows from those tables, you should not worry because this is the expected behavior. You don't need to do anything else.

OPTIMIZE TABLE is usually used to reclaim the unused space and to defragment the data file. Also you should know that MySQL locks the table during the time OPTIMIZE TABLE is running, which means that no one can run queries against that table and has to wait.