… notice you can see poor sequential scan performance even if table is not logically fragmented and Innodb is reading data in… both fragmentation issues is the same – OPTIMIZE TABLE tbl – this command recreates the table by writing the new .ibd file (if you… bad however it requires table to be locked while it is being rebuilt and also it really only defragments clustered key but…
Comment: Learning about MySQL Table Fragmentation
May 18, 2012 By 20 Comments
… / ( 1024 * 1024 ), 2), ‘MB’)FREE from information_schema.TABLES where TABLE_SCHEMA=’xyz’ and table_name=’abc’ and Data_free > 0 limit 10… / ( 1024 * 1024 ), 2), ‘MB’)FREE from information_schema.TABLES where TABLE_SCHEMA=’xyz’ and table_name=’abc’ and Data_free > 0 limit 10… free space remains the same. that means that table could not be defragmented. is there any other way to defragment innodb tables? thanks Kuldeep
Comment: ANALYZE: MyISAM vs Innodb
September 4, 2008 By 9 Comments
… your tests that ANALYZE TABLE on InnoDB tables is useless. We’d better only use OPTIMIZE TABLE to defragment the tables (and this will also update index statistics as OPTIMIZE is mapped to ALTER TABLE). Am I right or can we imagine situations where ANALYZE TABLE will be helpful with InnoDB…
Comment: How fast can you sort data with MySQL ?
September 24, 2007 By 26 Comments
… a full ordered insert (for example) for innodb. My large table spends hours ‘sorting result’, even though I can get top…-cuts taken by mysql where limit is used. Perhaps a table-scan, with a running best-of list, for example. That… feeding un-ordered PK to innodb directly then doing alter table engine=innodb to “defragment” PK. Am I on the right track?
Post: Pretty-formatted index fragmentation with xtrabackup
March 15, 2011 By 4 Comments
… InnoDB tables and indexes. This can be useful to examine whether you’d benefit from “defragmenting” your MySQL database with OPTIMIZE TABLE, although… top of the code listing. The output looks like this: TABLE INDEX TOT_PAGES FREE_PAGES PCT_FULL art.link_out104…
Post: MySQL Binaries Percona build10
December 11, 2008 By 12 Comments
…appeared under some workloads. The -percona release includes: | innodb_check_defrag.patch | Session status to check fragmentation of the last …| GPL | The names are Innodb_scan_* | | userstatsv2.patch | SHOW USER/TABLE/INDEX statistics | V2 | Google | GPL | Added INFORMATION_SCHEMA.*_STATISTICS | | …
Post: Percona build7 with latest patches
November 14, 2008 By 26 Comments
…patches we recently announced. The -percona release includes: | innodb_check_defrag.patch | Session status to check fragmentation of the last …| GPL | The names are Innodb_scan_* | | userstatsv2.patch | SHOW USER/TABLE/INDEX statistics | V2 | Google | GPL | Added INFORMATION_SCHEMA.*_STATISTICS | | …
Comment: InnoDB: look after fragmentation
January 22, 2010 By 12 Comments
I just ran ‘ALTER TABLE tracker ENGINE=InnoDB’; it takes forever with 20 million rows. I wonder if there’s a better way to defrag on production! 
Post: More patches
November 11, 2008 By 2 Comments
… in SHOW STATUS, more Our patches (thanks Yasufumi!) – innodb_check_defrag.patch, Show how sequential was execution of query, more – innodb… internal InnoDB memory allocations, more – microsec_process.patch – adds new table INFORMATION_SCHEMA.PROCESSLIST with information of time of query execution…
Comment: Beware large Query_Cache sizes
March 25, 2007 By 3 Comments
… into the sorted list of free blocks. Flush query cache defragments the query cache, so regular flushing is one possible way… might be less painful than a flush before a rare table update, even though it’ll remove all of the cached…
