(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