Shulou(Shulou.com)06/01 Report--

This article introduces the MYSQL PAGE page and how to query the ibd file through innochecksum, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Q: how big is a PAGE page in MYSQL

The answer is short and crisp, 16K, I think this is the first reaction and answer of most people, there is nothing wrong with this answer. But how much of this 16k is the real data stored in your table?

At this time, 95% of the people are willing to have.

As we all know, the physical unit in which MySql stores data is not a row, but a data page, and the default is an 16KB data unit. In fact, the size of the MYSQL page can be changed, can be 8K can be 32K unique page size is actually a definition of a MYSQL page size parameters, while UNIV_PAGE_SIZE_SHIFT is also to modify the default page size of MYSQL related to the amount, if your page size is 16K then the number of UNIV_PAGE_SIZE_SHIFT must be set to 14,2 times 14 times their own, that is 16K. (16384) (by the way, how much can a row of MYSQL store? Aftertaste)

Each page is assigned a 32-bit integer page number that creates a relationship between pages and limits the size, 232x16 KiB = 64 TiB, which is the maximum storage capacity of a table. So the relationship between the size of a table and a single page is shown in the official figure below. If the page is a variable, everything else remains the same.

In fact, the format of MYSQL pages is also classified. 38 bytes in the file header of each data page are not occupied in vain. He is mainly responsible for the following functions.

1 monitor the correctness of the page data, FIL_PAGE_CHECKSUM, (remember the widely available page monitoring tools provided by MYSQL) 4 bytes

2 FIL_PAGE_OFFSET, which can actually be understood as the coding of each page of a table made up of pages to see how many pages there are in this physical file and the overall location of the page you are visiting.

4 bytes

3 FIL_PAGE_PREV FILE_PAGE_NEXT, we are all saying that MYSQL is the data storage structure of the B+ tree, in which these two values express the form of a bi-directional linked list in the B+ data tree, because they are pointers that specify the address of a page in front of the current page and point to the address of the next page of the current page. So there is a "two-way linked list" between each data page. This is very important to extract data when querying data.

4 FIL_PAGE_LSN modified log sequence location LSN

5 the type of FIL_PAGE_TYPE current page is Btree leaf node, UNDO LOG, Inode, insertbuffer free list, insertbuffer bitmap, system page, transaction system data, BLOB page, file space header, extension description page, etc.

6 defined in a page of the LSN_FLUSH_LSN system tablespace, independent of ordinary data pages

7 FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID page data that tablespace

About the type of data stored in the current ibd file, you can use the tools that come with mysql

Innochecksum to check, here we open an ibd file in MYSQL

In the case of a page with MYSQL Per = 1, I can see that there are 1337 indexed pages in this file, our index node INODE PAGE is only one, and there are 452 newly allocated pages, one insert buffer bitmap page, one file file spave header.

Let's add these numbers together, 1792 * 16384 Universe 1024ax 1024 = 28MB

We can also verify the ibd file from the actual current table.

So the storage space of the file is consistent with the size of the data storage page resulting from the combination of the information of the data record page that we exported from the Ibd file.

There is a question right now.

Q: is there any page in the 28MB data space that can still write data?

SELECT round ((data_length+index_length) / 1024Universe 2) FROM information_schema.tables WHERE table_schema='employees' AND table_name='dept_emp'

In practice, it is not appropriate to obtain the data space occupied by the table through the statement of SQL in the way of database, and to obtain the space through innochecksum.

By comparison, the current usage of data storage space obtained through innochecksum is 20.89 MB.

And get 17.09MB by statement.

Which is more accurate?

We are working on the current table

OPTIMIZE TABLE dept_emp; let the space in the table be sorted out once.

And then I'll take a look at the change in the number. Calculated by SQL, the result is 24.55.

Once again, we use innochecksum to query the ibd file, which takes up about 20.77 data space.

At the same time, we have also done some other table space usage, as well as the calculation of free space, which can prove that obtaining the ibd space usage of the current table through SQL is not consistent with the actual table usage under LINUX.

However, it is stable and accurate to obtain the usage of the data table through innochecksum. In addition, after OPTIMIZE, the space used to calculate the table through SQL fluctuates greatly, while innochecksum will not be affected, and can accurately return the actual disk space usage.

About the MYSQL PAGE page and how to query the ibd file through innochecksum to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.