The "fragmented" message is bogus. It says it to everyone. Almost no one needs to OPTIMIZE their tables.
> -----Original Message----- > From: Miguel González [mailto:miguel_3_gonza...@yahoo.es] > Sent: Tuesday, May 21, 2013 2:03 PM > To: mysql@lists.mysql.com > Subject: fragmentation in innodb tables > > 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 > > > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
