2

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;

1 Answer 1

0

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.

1
  • Thank you for your reply. I am aware of what OPTIMIZE TABLE does, but I am not sure if this script is doing it. That is what I would like to know? The locking of the tables is not a problem because of the time of day that I run the script and given that all of my sites are targeted towards one timezone, this is unlikely to be a problem.
    – Ben Carey
    Jan 26, 2012 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.