When you are using MySQL, you will (likely) have tables that can be fragmented. Fragmented tables in mysql needs to be optimized.
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.
[bash]#!/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
done[/bash]
Result will look something like this:
[bash]search_socialnetwork.proxies is 159% fragmented.
search_socialnetwork.proxies optimize note Table does not support optimize, doing recreate + analyze instead
search_socialnetwork.proxies optimize status OK
search_socialnetwork.site_categories_clone is 263% fragmented.
search_socialnetwork.site_categories_clone optimize note Table does not support optimize, doing recreate + analyze instead
search_socialnetwork.site_categories_clone optimize status OK
search_socialnetwork.site_information is 9% fragmented.
search_socialnetwork.site_information optimize note Table does not support optimize, doing recreate + analyze instead
search_socialnetwork.site_information optimize status OK
search_socialnetwork.site_stats is 263% fragmented.
search_socialnetwork.site_stats optimize note Table does not support optimize, doing recreate + analyze instead
search_socialnetwork.site_stats optimize status OK
younet_search.ex_pages is 25% fragmented.
younet_search.ex_pages optimize note Table does not support optimize, doing recreate + analyze instead
younet_search.ex_pages optimize status OK
younet_search.page_stats is 1% fragmented.[/bash]
