DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info.gz) InnoDB File Defragmenting

Info Catalog (mysql.info.gz) InnoDB File space (mysql.info.gz) File space management
 
 15.15.4 Defragmenting a Table
 -----------------------------
 
 If there are random insertions into or deletions from the indexes of a
 table, the indexes may become fragmented. Fragmentation means that the
 physical ordering of the index pages on the disk is not close to the
 index ordering of the records on the pages, or that there are many
 unused pages in the 64-page blocks that were allocated to the index.
 
 A symptom of fragmentation is that a table takes more space than it
 'should take'. How much exactly is that, is difficult to determine. All
 InnoDB data and indexes are stored in B-trees, and their fillfactor may
 vary 50 % - 100 %. Another symptom of fragmentation is that a table
 scan:
      SELECT COUNT(*) FROM t WHERE a_non_indexed_column <> 12345;
 takes more time than 'it should take'. (Above we are fooling the SQL
 optimizer to scan the clustered index, not a secondary index.) Most
 disks can read 10 - 50 MB/s. That can be used to estimate how fast a
 table scan should run.
 
 It can speed up index scans if you periodically perform a "null" `ALTER
 TABLE' operation:
 
      ALTER TABLE TBL_NAME TYPE=InnoDB
 
 That causes MySQL to rebuild the table. Another way to perform a
 defragmention operation is to use `mysqldump' to dump the table to a
 text file, drop the table, and reload it from the dump file.
 
 If the insertions to an index are always ascending and records are
 deleted only from the end, the `InnoDB' file space management algorithm
 guarantees that fragmentation in the index will not occur.
 
Info Catalog (mysql.info.gz) InnoDB File space (mysql.info.gz) File space management
automatically generated byinfo2html