List:General Discussion« Previous MessageNext Message »
From:Miguel Gonz├ílez Date:May 21 2013 9:03pm
Subject:fragmentation in innodb tables
View as plain text  
Dear all,

   I'm a newbie in MySQL so bare my questions.

   I have run mysqltuner.pl and It says I have fragmentation in my 
tables. Searching around I found this script which reports the 
fragmentation in my tables:

========================
  #!/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
========================

I have run it and reports that several of my innodb tables are fragmented

I have read several articles and I'm a bit confused. I have enabled 
innodb_file_per_table from the very beginning

# INNODB #

innodb_log_files_in_group      = 2
innodb_log_file_size           = 512M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 2G

I have run either optimize table and alter table mytable engine=INNODB 
and both commands don't end up shrinking the space in the idb files. The 
script above reports the same fragmentation.

Regards,

Miguel









Thread
fragmentation in innodb tablesMiguel Gonz├ílez21 May
  • RE: fragmentation in innodb tablesRick James21 May