View InnoDB disk space utilization _ MySQL

View InnoDB disk space usage bitsCN.com
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. The statistical value of the usage of each index in the last few actions of the./ibd_used tb. ibd N1 N2>/tmp/r (attachment: ibd_used.rar http: // uploadfile/2012/0429/20120429085005514.rar author Ding Lin .tbbitsCN.com