Shulou(Shulou.com)05/31 Report--

This article mainly explains the "InnoDB disk space utilization check method", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's train of thought slowly in depth, together to study and learn "InnoDB disk space utilization check method"!

Page utilization

It mainly refers to the amount of space used by each page in the btee. We know that InnoDB defaults to a page size of 16k. But the actual use will not always be full.

We define it as the total usage bytes of all page divided by the total number of bytes.

Before theoretical analysis, we need to get a tool and check it out.

Case statistics

Write a simple tool, read each page on the ibd file, calculate the actual usage bytes of each page, and get the utilization.

We found an online library to simulate. There is one self-increasing primary key and three non-clustered indexes in the table. Without affecting the conclusion, it is simplified 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 (BBM C)

KEY cb (cpene b)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

The inserted data are random strings with a length of 30 bytes.

Obviously, the primary key and other indexes should be counted separately. The statistical results show that the utilization rate of primary key page is 71%, and that of other indexes is about 52%.

Simple analysis

The above results are easy to understand. Because data is inserted in the order in which the primary key is incremented, the data on the primary key is "compact". On the other hand, the other three indexes are all randomly updated and need to be split constantly.

How to improve disk space utilization

Let's go back to the original question. In fact, what we care about is how much space InnoDB uses to save the same data. So our question becomes, how to store the same data and make it take up less disk space.

There is a very intuitive conclusion. Deleting and rebuilding these indexes is bound to reduce space consumption. Because after this operation, these indexes also become "compact" before new data is inserted.

Then build the same index as cb, and then run, the utilization rate is as high as 98%. This principle should be known to everyone, it's just a quantification.

Tool attached, usage. / ibd_used tb.ibd N1 N2 > / tmp/r the utilization statistics of each index in the last few behaviors

Thank you for your reading, the above is the content of "InnoDB disk space utilization viewing method". After the study of this article, I believe you have a deeper understanding of the problem of viewing InnoDB disk space utilization, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.