A Defragmentation Script to Optimize Fragmented Tables in MySQL

by Phil Dufault on August 7, 2008 · 37 comments

in Featured,Linux,MySQL

I wrote a little script that will defragment fragmented tables. You can supply usernames and passwords, and even run it against remote MySQL databases.

Here’s an example of it’s output:
root@lit [~]# mysqlFragFinder.sh
MySQL fragmentation finder (and fixer) v1.0.0
Written by Phil Dufault (phil@dufault.info, http://www.dufault.info)
Found 2 databases
Checking dufault_blog ... found 6 fragmented tables.
Optimizing wp_commentmeta ... done
Optimizing wp_comments ... done
Optimizing wp_options ... done
Optimizing wp_postmeta ... done
Optimizing wp_term_relationships ... done
Optimizing wp_users ... done
6 tables were fragmented, and were optimized.

I find this a great script — maybe a feature to add later is a way to prevent it from optimizing really large tables.

The source code is now hosted on GitHub!

Here’s a link to it:
https://github.com/pdufault/mysqlfragfinder

 

Any other features you’d like to see in it?   Comment! Patches welcome.

{ 31 comments… read them below or add one }

Rubén Ortiz November 27, 2008 at 12:11 pm

You did a great job, is a very good script :) Thanks!

Phil Dufault November 30, 2008 at 10:48 pm

Thanks for the feedback! Let me know if you see any improvements I can make in the script.

Banned December 18, 2008 at 12:39 pm

Thanks dude, nice job!

tom3k May 1, 2009 at 1:13 am

i use this on a ton of my servers.

cheers!

fsckr October 24, 2009 at 9:18 am

The process of optimizing/fixing fragmented tables has always been kinda painful especially with 100′s of relation tables.

Hundreds of thousands of users thank you…

Dave August 6, 2010 at 6:03 am

Great script! How about a parameter to only operate on a specific database (and it’s tables). I need to work on just one table as I am not responsible for all of the tables on this server.

Ken December 20, 2010 at 9:46 pm

Thanks for this script. Just an FYI, with mysql 5, you can do the following query that can replace the fragmented variable or you can use it to replace other variables that list the tables or databases.

SELECT TABLE_NAME,ENGINE,Data_free
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN (‘information_schema’,'mysql’)
AND (ENGINE = ‘MyISAM’ OR ENGINE = ‘InnoDB’)
AND Data_free > 0;

godsyn April 13, 2011 at 11:27 pm

Just a heads up, the line:
mysql -u”$mysqlUser” -p”$mysqlPass” -h”$mysqlHost” -D “$i” –skip-column-names –batch -e “optimize table $table” 2>”$log” >/dev/null

would benefit from escaping of the $table variable.

EG:

mysql -u”$mysqlUser” -p”$mysqlPass” -h”$mysqlHost” -D “$i” –skip-column-names –batch -e “optimize table \`$table\`” 2>”$log” >/dev/null

Tables with “-” in their name will mess things up if you don’t escape the variable.

fl0 April 20, 2011 at 3:08 pm

Pretty cool!

Thank you very much!

Gabriel Rubilar June 22, 2011 at 1:40 pm

Very nice script. Thank you for sharing!

Phil Dufault June 24, 2011 at 1:09 pm

Thanks for letting me know, updated the file.

Arun Shetty July 2, 2011 at 12:22 pm

Hi Phil,

I tried running the script with the following syntax:
./mysqlDefragmenter -u”username”-p”mypassword” -h”localhost”

The script runs and stays there without showing any output besides “MySQL fragmentation finder (and fixer) v0.7.5, written by Phil Dufault ( http://www.dufault.info/ )”.

I’m running this on a machine with CentOS 5.5 against mysql-5.0.77-4.el5_6.6

What am I doing wrong? Please advise.

Thanks,
Arun.

Arun Shetty July 2, 2011 at 12:40 pm

I was typing the wrong password. Got it to work after keying in the right one :-)

Thanks for the awesome script Phil.

Best Regards,
Arun.

Arun Shetty July 2, 2011 at 12:49 pm

The script runs with an error

[root@IRS-LNX-07 scripts]# ./mysqlDefragmenter –user “root” –password “**redacted**” –host “localhost”
MySQL fragmentation finder (and fixer) v0.7.5, written by Phil Dufault ( http://www.dufault.info/ )
Found 4 databases
Checking openmeetings … found 1 fragmented table.
Optimizing sessiondata … An error occured, check /root/scripts/mysql_error_log.txt for more information.

and the following text is logged:

ERROR at line 1: Unknown command ‘\”.

Please advise.

Best Regards,
Arun.

Arun Shetty July 2, 2011 at 1:17 pm

Not to worry…I managed to fix the error. Its time to use it on my production box now :-)

Thanks,
Arun.

Michael July 27, 2011 at 7:41 am

Phil,

godsyn’s suggestion still threw an error on my file. I had to remove the escape characters and run the variable itself without any quote marks. I would make a note to anyone who experiences an error that this is likely the problem.

Reynold August 13, 2011 at 4:06 pm

Thank you Phil:)

Robert Brown August 14, 2011 at 6:32 pm

Phil – the updated file is using the ‘ character in the new script (which shares the key with “), whereas it needs to use a ` (which shares the key with ~). It won’t work as posted and errors out.

Phil Dufault September 29, 2011 at 12:15 am

Thanks, fixed.

Phil Dufault September 29, 2011 at 9:45 am

Thanks for letting me know about the errors Arun. I’ve fixed them.

However, please be more careful with what text you’re pasting to the internet! You showed me your root password.

Tom December 13, 2011 at 1:21 pm

The script seems to run fine, after I figured out how to populate the ~/.my.cnf file.
But… each time I run it, the same 44 tables are found to be fragmented and optimized.
How can I make the optimization stick?
Thanks, Tom

Phil Dufault January 16, 2012 at 4:02 pm

Hi Tom,

I’ll need some more data from you — try running the script in debug mode to find out what actions it’s taking. (put -x at the end of the line at top of the script)

Let me know how you make out.

Paul February 13, 2012 at 9:00 am

Hi Phil,

I seem to have the same “error”, i tried your script on a more or less fresh installed piwik installation, this is the -x output: https://pastee.org/8z7m2
Best regards,
Paul

Phil Dufault February 15, 2012 at 8:56 pm

Hi Paul,

Are these InnoDB tables perchance?

Paul February 23, 2012 at 3:38 am

Hi Phil,

yes, these are InnoDB tables.

Candra Setiawan April 1, 2012 at 3:45 am

Thanks for your scripts

Serhiy Kolesnyk April 12, 2012 at 10:29 pm

Great job, Phil! Thank you so much.

gavin June 28, 2012 at 2:20 pm

How are the results of your script different than running this?:

mysqlcheck -u root -p –auto-repair –check –all-databases;mysqlcheck -u root -p –auto-repair –optimize –all-databases;

Nils September 4, 2012 at 6:49 am

Nice and useful script.
But i had to change some lines regarding the parsing of the file “.my.cnf” to get it working. In my “.my.cnf” the password value is actually “pasword” and not “pass”.
So i had to change your line “43″ from
if grep “pass=” “$HOME/.my.cnf” >/dev/null 2>&1; then
to
if grep “password=” “$HOME/.my.cnf” >/dev/null 2>&1; then
And i had to change the two lines for the parsing of those values into variables from
mysqlUser=$(grep user= < "$HOME/.my.cnf" | awk -F\" '{print $2}');
mysqlPass=$(grep pass= < "$HOME/.my.cnf" | awk -F\" '{print $2}');
to
mysqlUser=$(grep "user=" "$HOME/.my.cnf" | awk -F= '{print $2}');
mysqlPass=$(grep "password=" "$HOME/.my.cnf" | awk -F= '{print $2}');
Now it works on my system :-)

Phil Dufault November 21, 2012 at 3:42 pm

Thanks, I’ve committed this to the GitHub repo.

Phil Dufault November 21, 2012 at 3:44 pm

Having a little more control over what tables it optimizes, ie: not the big ones

Leave a Comment

{ 6 trackbacks }

Previous post:

Next post: