View InnoDB disk space utilization

Source: Internet
Author: User


View InnoDB disk space utilization page utilization mainly refers to the space used by each page in btee. We know that the default page size of InnoDB is 16 kb. However, the actual usage is not always full. We define the total usage bytes of all pages divided by the total number of bytes. Before analyzing the theory, we need to get a tool to check it. The instance statistics write a simple tool to read every page in the ibd file and calculate the actual usage bytes of each page to get the utilization rate. We looked for an online library to simulate it. The table has one auto-incrementing primary key and three non-clustered indexes. The conclusion is as follows: create table 'ctu _ factor_risk_99_03 '('seq _ id' bigint (20) unsigned not null AUTO_INCREMENT, 'A' varchar (32) default null, 'B' varchar (32) default null, 'C' varchar (32) default null, KEY a (a), KEY bc (B, c), KEY cb (c, b),) ENGINE = InnoDB default charset = utf8; inserted data in a, B, c are a 30-byte random string. Obviously, the primary key and other indexes should be counted separately. Statistics show that the utilization rate of the primary key page is 71%, and the utilization rate of other indexes is about 52%. The above results are easy to understand. Because data is inserted in ascending order of the primary key, the data on the primary key is "Compact ". The other three indexes are randomly updated and need to be split continuously. How to increase disk space utilization back to the initial issue. In fact, we are concerned about how much space InnoDB uses to save the same data. So our problem is how to make the occupied disk space smaller by storing the same data. There is a very intuitive conclusion. Deleting and recreating these indexes will inevitably reduce the space consumption. After this operation, these indexes become "Compact" before new data is inserted ". Another index that is the same as cb is created, and the utilization rate is as high as 98%. Everyone should know this principle, just to quantify it. Statistical Value of the utilization of each index in the last behavior of/ibd_used tb. ibd N1 N2>/tmp/r after the tool attachment (Attachment: ibd_used.rar) http://up.2cto.com/2012/0429/20120429085005514.rar Author Ding Lin. tb

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.