Could you please answer these questions also. What does data_free field from SHOW TABLE STATUS shows? When should we run optimize table for innodb tables?
I read various blogs. They said data_free shows free space inside the innodb tables. But after doing *optimize table*, the value inside data_free is still the same. If there is no change in the value of data_free, then what *optimize table* does here? Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Trainer and Database Security Specialist On Thu, Aug 28, 2014 at 2:21 AM, shawn l.green <shawn.l.gr...@oracle.com> wrote: > Hello Geetanjali, > > > On 8/26/2014 1:11 AM, geetanjali mehra wrote: > >> Hello to all, >> >> I want to know whether my innodb index is fragemented. Is it possible to >> know? >> >> Best Regards, >> Geetanjali Mehra >> Senior Oracle and MySQL DBA Corporate Trainer and Database Security >> Specialist >> >> > Just like every other piece of data stored in an InnoDB tablespace, the > index data is stored in pages. At maximum capacity, 15KB of the 16KB > assigned to each page can consist of data. At worst, about half of a 16K > page will contain data. This is because each page is one leaf in a BTREE > structure. > > If you add data to a page and you would exceed that 15K limit, we would > need to split that page. That means that each page (the existing page and > the one we just created) will now have a near-equal share of the data that > was on the original page. > > That empty space is reused as much as possible to avoid another page > split. If removing data from a table makes it possible to combine two > adjacent leaves in the B-tree, we will. The page that once held the extra > information is marked as 'available' and it can be filled with more index > information later or filled with actual table data. > > A page is a page is a page. InnoDB decides what goes on a page. > > So... > Is an index ever fragmented? No (depending on your definition of > fragmentation) > > Will there ever be some free space within the index tree? Always. > > Can index pages be scattered (non-contiguous) within a tablespace file? > Yes. > > Will rebuilding a table ensure that the index pages are made contiguous? > No. > > Do these same answers apply to the actual data stored on a table? Yes. > > http://dev.mysql.com/doc/refman/5.6/en/innodb-file-defragmenting.html > http://dev.mysql.com/doc/refman/5.6/en/innodb-file-space.html > > Does that help? > -- > Shawn Green > MySQL Senior Principal Technical Support Engineer > Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. > Office: Blountville, TN > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > >
