Databases Reference
In-Depth Information
Row fragmentation
This type of fragmentation occurs when the row is stored in multiple pieces in
multiple locations. Row fragmentation reduces performance even if the query
needs only a single row from the index.
Intra-row fragmentation
This kind of fragmentation occurs when logically sequential pages or rows are not
stored sequentially on disk. It affects operations such as full table scans and clus-
tered index range scans, which normally benefit from a sequential data layout on
disk.
Free space fragmentation
This type of fragmentation occurs when there is a lot of empty space in data pages.
It causes the server to read a lot of data it doesn't need, which is wasteful.
MyISAM tables might suffer from all types of fragmentation, but InnoDB never frag-
ments short rows; it moves them and rewrites them in a single piece.
To defragment data, you can either run
OPTIMIZE TABLE
or dump and reload the data.
These approaches work for most storage engines. For some, such as MyISAM, they
also defragment indexes by rebuilding them with a sort algorithm, which creates the
indexes in sorted order. There is no way to defragment InnoDB indexes in older
versions of InnoDB, but in more recent versions that include the ability to drop and
build indexes “online” without rebuilding the whole table, you can drop and recreate
the indexes to defragment them.
For storage engines that don't support
OPTIMIZE TABLE
, you can rebuild the table with
a no-op
ALTER TABLE
. Just alter the table to have the same engine it currently uses:
mysql>
ALTER TABLE
<table>
ENGINE=
<engine>
;
In Percona Server with
expand_fast_index_creation
enabled, rebuilding the table in
this way will defragment InnoDB tables and indexes. In standard MySQL, it will de-
fragment only the table (the clustered index). You can emulate Percona Server's func-
tionality by dropping all indexes, rebuilding the table, and then adding the indexes
back to the table.
Don't assume that you need to defragment your indexes and tables—measure them
first to find out. Percona XtraBackup has a
--stats
option that makes it run in a non-
backup mode. This mode prints out index and table statistics, including the amount
of data and free space in pages. This is one way you can find out how fragmented your
data really is. Also consider whether the data could have settled into a nice steady state
that you might disrupt by packing it tightly together, causing future updates to incur
a spike of page splits and reorganizations, which can impact performance until they
reach the steady state again.
