List:General Discussion« Previous MessageNext Message »
From:shawn l.green Date:June 27 2014 1:59pm
Subject:Re: Optimizing InnoDB tables
View as plain text  
Hello Antonio,

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.
>
> Regards,
>
> Antonio.​
>

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' ?

-- 
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
Optimizing InnoDB tablesAntonio Fernández Pérez24 Jun 2014
  • Re: Optimizing InnoDB tablesWagner Bianchi24 Jun 2014
    • Re: Optimizing InnoDB tablesAntonio Fernández Pérez24 Jun 2014
  • Re: Optimizing InnoDB tablesshawn l.green24 Jun 2014
    • Re: Optimizing InnoDB tablesReindl Harald24 Jun 2014
      • Re: Optimizing InnoDB tablesshawn l.green24 Jun 2014
        • Re: Optimizing InnoDB tablesAntonio Fernández Pérez25 Jun 2014
          • Re: Optimizing InnoDB tablesJohan De Meersman25 Jun 2014
          • Re: Optimizing InnoDB tablesAndre Matos25 Jun 2014
            • Re: Optimizing InnoDB tablesAntonio Fernández Pérez27 Jun 2014
              • Re: Optimizing InnoDB tablesReindl Harald27 Jun 2014
                • Re: Optimizing InnoDB tablesAntonio Fernández Pérez27 Jun 2014
                  • Re: Optimizing InnoDB tablesshawn l.green27 Jun 2014
                  • Re: Optimizing InnoDB tablesJohan De Meersman27 Jun 2014
                    • Re: Optimizing InnoDB tablesAntonio Fernández Pérez30 Jun 2014
                      • Re: Optimizing InnoDB tablesReindl Harald30 Jun 2014