Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
add comment

migrated from stackoverflow.com Jan 26 '12 at 12:26

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer
    
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 '12 at 8:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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