It happens on MySQL, as you know. Run an Optimize Table solve the question.
BUT, be careful! During the optimize the table stay locked (writing is not possible).
(Fragmented Table)
So what?
To not cause a lock in every table, the script below shows and runs (if you want to list but not run, comment the line) only for tables that have fragmentation.
It was very useful to me! 🙂
#!/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
The resul will be like:
MySQL username: root MySQL password: ... mysql.db is 12% fragmented. mysql.db optimize status OK mysql.user is 9% fragmented. mysql.db optimize status OK ...
This script is a full copy from this post by Robert de Bock.
Thanks, Robert!
Matheus.