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
for db_table in `mysql -Bse
for db_table in `mysql -Bse "SELECT CONCAT(TABLE_SCHEMA, ':', TABLE_NAME) AS \db_table FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') \
AND Data_free > 0 AND NOT ENGINE='MEMORY' ORDER BY Data_free DESC;"`; do
DB=`echo $db_table | cut -d: -f1`; TB=`echo $db_table | cut -d: -f2`; mysqlcheck -oa "$DB" "$TB"
done
great script, pimped a little
great script, pimped a little for us:
- no mysql-credentials, (using .my.cnf, for cron)
- no stats, just runs
- skips system-db
- skips views
- splitted lines
#!/bin/sh
mysql -NBe "SHOW DATABASES;" | grep -v 'lost+found' \
| while read database ; do
#skip system-db
if [ "$database" = "mysql" ] ; then
continue
fi
mysql -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
#skip views
if [ "$datafree" = "NULL" ] ; then
continue
fi
if [ "$datafree" -gt 0 ] ; then
echo "$database.$name is fragmented"
mysql -NBe "OPTIMIZE TABLE $name;" "$database"
fi
done
done
Hi, The above script gave
Hi,
The above script gave error,
[admin]# ./mysql_optimize./mysql_optimize: line 10: read: ` ': not a valid identifier
./mysql_optimize: line 11: datalength: command not found
./mysql_optimize: line 10: read: ` ': not a valid identifier
./mysql_optimize: line 11: datalength: command not found
./mysql_optimize: line 10: read: ` ': not a valid identifier
./mysql_optimize: line 11: datalength: command not found
./mysql_optimize: line 10: read: ` ': not a valid identifier
./mysql_optimize: line 11: datalength: command not found
Any way to fix this?
Thank you.
I guess it's a copy-paste
I guess it's a copy-paste problem. Lines 10 and 11 end in a backslash (\), but I guess your copy-paste removed that or so.
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