List:General Discussion« Previous MessageNext Message »
From:Rick James Date:May 21 2013 11:06pm
Subject:RE: fragmentation in innodb tables
View as plain text  
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_gonzalez@stripped]
> Sent: Tuesday, May 21, 2013 2:03 PM
> To: mysql@stripped
> 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

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