Optimize only fragmented tables in MySQL
When you are using MySQL, you will (likely) have tables that can be fragmented. In MySQL terms this is called "OPTIMIZE".
You could simply OPTIMIZE every table in every database, but during an OPTIMIZE, the tables are locked, so writing is not possible.
To minimize the time that MySQL will be locked (and results cannot be written), here is a script that checks fragmentation of every table of every database. Only if a table is fragmented, the table is OPTIMIZED.
#!/bin/sh
echo -n "MySQL username: " ; read username
echo -n "MySQL password: " ; stty -echo ; read password ; stty echo ; echo
mysql -u $username -p"$password" -NBe "SHOW DATABASES;" | grep -v 'lost+found' | while read database ; do
mysql -u $username -p"$password" -NBe "SHOW TABLE STATUS;" $database | while read name engine version rowformat rows avgrowlength datalength maxdatalength indexlength datafree autoincrement createtime updatetime checktime collation checksum createoptions comment ; do
if [ "$datafree" -gt 0 ] ; then
fragmentation=$(($datafree * 100 / $datalength))
echo "$database.$name is $fragmentation% fragmented."
mysql -u "$username" -p"$password" -NBe "OPTIMIZE TABLE $name;" "$database"
fi
done
doneResult will look something like this:
MySQL username: root
MySQL password:
...
database.cache_filter is 19% fragmented.
meinit.cache_filter optimize status OK
database.cache_page is 35% fragmented.
meinit.cache_page optimize status OK
...You may comment out that line with OPTIMIZE TABLE in it, if you are just interested in seeing the fragmentation.
| About | Consultancy | Articles | Contact |
|
|
|
|
|
| References | Red Hat Certified Architect | By Robert de Bock | Robert de Bock |
| Curriculum Vitae | By Fred Clausen | +31 6 14 39 58 72 | |
| By Nelson Manning | robert@meinit.nl |
Comments
I've modified the above to
I've modified the above to run in a shell script without being prompted to enter the MySQL User / Password.
In addition, if you want to ORDER BY Most Fragmented tables, you can with this script.
The main difference here is that MySQL is doing more of the work then the shell (#!/bin/sh). MySQL is calculating the % AND filtering on the % as well as the number of records in each table.
So tables fragmented more than 55% and have more than 300 records are shown. You can edit those variables in the script. :-)
#!/bin/sh
# --- Global Variabls
source global-variables.sh
# --- Local Variables
PERCENT='55'
ROWCOUNT='300'
mysql -u $MYSQLUSR -p"$MYSQLPW" -NBe "SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, DATA_FREE, (DATA_FREE*100/DATA_LENGTH) as PRC FROM INFORMATION_SCHEMA.TABLES WHERE (DATA_FREE*100/DATA_LENGTH) >= $PERCENT AND TABLE_ROWS >= $ROWCOUNT ORDER BY TABLE_SCHEMA, PRC DESC;" | while read TABLE_SCHEMA TABLE_NAME TABLE_ROWS DATA_LENGTH INDEX_LENGTH DATA_FREE PRC; do
echo "$TABLE_SCHEMA.$TABLE_NAME is $PRC% fragmented and has $TABLE_ROWS rows."
done
I've also found I get some tables with 200% + fragmentation. However when I look at the MySQL raw files and get the size of the tables there, it's different than what MySQL is picking up in DATA_LENGTH & INDEX_LENGTH hence the bogus %'s.
Simplified it a little, and I
Simplified it a little,
and I added the line that will automatically optimize the tables for you since I left it out above. :-)
#!/bin/sh
# --- Variabls
MYSQLROOTUSR='root'
MYSQLROOTPW='password'
PERCENT='55'
ROWCOUNT='300'
mysql -u $MYSQLROOTUSR -p"$MYSQLROOTPW" -NBe "SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, DATA_FREE, (DATA_FREE*100/DATA_LENGTH) as PRC FROM INFORMATION_SCHEMA.TABLES WHERE (DATA_FREE*100/DATA_LENGTH) >= $PERCENT AND TABLE_ROWS >= $ROWCOUNT ORDER BY TABLE_SCHEMA, PRC DESC;" | while read TABLE_SCHEMA TABLE_NAME TABLE_ROWS DATA_LENGTH INDEX_LENGTH DATA_FREE PRC; do
echo "$TABLE_SCHEMA.$TABLE_NAME is $PRC% fragmented and has $TABLE_ROWS rows."
mysql -u "$MYSQLROOTUSR" -p"$MYSQLROOTPW" -NBe "OPTIMIZE TABLE $TABLE_NAME;" "$TABLE_SCHEMA"
done
Thanks for the script, simple
Thanks for the script, simple but effective. What scripts are all about!
When running this script on
When running this script on gentoo I am getting this output:
wsojnj.bm_zip_areacode is 22016000% fragmented.
Wow, well, I'd certainly
Wow, well, I'd certainly optimize that bm_zip_areacode then! ;-)
But seriously, add a " -x" after the first line, see what calculation fails. Could be a bug in the script.
Regards, Robert de Bock.
Robert, I think the large
Robert,
I think the large percents are coming from mysql giving unexpected values. I added a debug print and "-x" to see values like:
table=alle rows=0 datafree=10485760 datalength=16384
+ '[' 10485760 -gt 0 ']'
+ fragmentation=64000
table=collection rows=1 datafree=10485760 datalength=16384
+ '[' 10485760 -gt 0 ']'
+ fragmentation=64000
table=cld rows=445 datafree=10485760 datalength=65536
+ '[' 10485760 -gt 0 ']'
+ fragmentation=16000
For my system, datafree is always either 0 or 10485760. I don't know why that is, but that's what I'm seeing.
All tables are Innodb, that may make a difference from Myisam -- I don't know.
None of my tables are overly big, most have < 5K rows and none have over 25K. Since it appears mysql defaults to a data block of 16K or even 64K on disk, this is a large reason for the large percents, although not the only reason.
I ran the script and let it optimize (optimize not echo'd). Running again with echo on (no optimize) to see if it thought it needed to defrag some more, datafree had changed *UP* to 17825792 on all tables, so I used 7MB more per table! That moved the wrong way!
I don't think this is your problem, but a bug in mysql 5.1.46. Why am I using mysql? (rhetorical question)
Kevin
Hi, I am getting following
Hi,
I am getting following error when I run this script on my CentOS 5.5 (64 bit)
mysqldefrag.sh: line 8: indexlength: command not found
How to fix it?
Thanks
Hi, I think your copy-paste
Hi,
I think your copy-paste action chopped off the long lines into new lines.
This is a single line:
mysql -u $username -p"$password" -NBe "SHOW TABLE STATUS;" $database | while read name engine version rowformat rows avgrowlength datalength maxdatalength indexlength datafree autoincrement createtime updatetime checktime collation checksum createoptions comment ; do
The rest of the lines are short enough to fit on my display.
Regards, Robert de Bock.
> echo "database.$name is
> echo "database.$name is $fragmentation% fragmented."
Missing "$" in front of the variable name "database". Correct line should read:
echo "$database.$name is $fragmentation% fragmented."
Best regards!
Modified that $database
Modified that $database error, thanks for helping.