On 6/27/2014 9:31 AM, Antonio Fernández Pérez wrote:
> Hi Reindl,
> Thanks for your attention.
> Following the previous mail, I have checked my MySQL's configuration and
> innodb_file_per_table is enabled so, I think that this parameter not
> affects directly to fragmented tables in InnoDB (In this case).
> I would like to know, if is possible, why after execute an analyze table
> command on some fragmented table, after that, appears fragmented again.
InnoDB operates by storing multiple rows on "pages". Each page is 16K.
Of that 1K is reserved for metadata (a tiny index showing where on a
page each row sits, links to various other locations, checksums, ...)
The remaining 15K can be used for your actual data.
If you delete a row of data, that space on a page is made available but
the page does not change size. It is always 16K.
InnoDB stores data in the order of your PK. If you need to insert a new
row between other rows on a 'full' page, then the page needs to split.
This creates 2 new pages that are about 50% full.
If two adjacent pages (A and B) become too 'empty' they can be combined
into one page. This puts the data from both pages onto one of them (page
A, for example). However page B remains empty and becomes available for
any other purpose.
Is that what you are calling 'fragmentation' ?
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN