Re: fragmentation in innodb index
Thanks to all, Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Trainer and Database Security Specialist On Fri, Aug 29, 2014 at 11:59 AM, Hartmut Holzgraefe hart...@skysql.com wrote: On 08/29/2014 08:15 AM, geetanjali mehra wrote: 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? Without seeing actual values I can only give an educated guess: * If you are not using innodb_file_per_table then data_free is for the whole table space, not just the individual table you're looking at. Even if that table was fragmented before running OPTIMIZE the overall unallocated storage space within the table space won't change much ... * If you have innodb_file_per_table=1 and you are seeing data_free values round about 4MB what you're seeing is simply pre-allocated space. See also the last paragraph on http://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html File-per-table tablespace files are auto-extending regardless of the value of innodb_autoextend_increment. The initial extensions are by small amounts, after which extensions occur in increments of 4MB. So unless you have innodb_file_per_table set and see data_free values substantially larger than 4MB there's nothing to worry about ... -- Hartmut Holzgraefe, Principal Support Engineer (EMEA) SkySQL - The MariaDB Company | http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: fragmentation in innodb index
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
Re: fragmentation in innodb index
On 08/29/2014 08:15 AM, geetanjali mehra wrote: 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? Without seeing actual values I can only give an educated guess: * If you are not using innodb_file_per_table then data_free is for the whole table space, not just the individual table you're looking at. Even if that table was fragmented before running OPTIMIZE the overall unallocated storage space within the table space won't change much ... * If you have innodb_file_per_table=1 and you are seeing data_free values round about 4MB what you're seeing is simply pre-allocated space. See also the last paragraph on http://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html File-per-table tablespace files are auto-extending regardless of the value of innodb_autoextend_increment. The initial extensions are by small amounts, after which extensions occur in increments of 4MB. So unless you have innodb_file_per_table set and see data_free values substantially larger than 4MB there's nothing to worry about ... -- Hartmut Holzgraefe, Principal Support Engineer (EMEA) SkySQL - The MariaDB Company | http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: fragmentation in innodb index
Senior Oracle and MySQL DBA Corporate Trainer and Database Security Am I the only one worried about that line, then? -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: fragmentation in innodb index
On 8/29/2014 5:51 AM, Johan De Meersman wrote: Senior Oracle and MySQL DBA Corporate Trainer and Database Security Am I the only one worried about that line, then? yes. I welcome help from anyone willing. Expertise and willingness both are important. --bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: fragmentation in innodb index
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
fragmentation in innodb index
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
