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

I want to know how much space the MySQL table takes up on disk, but it looks trivial. Shouldn't this information be provided in INFORMATION_SCHEMA.TABLES? It's not that simple!

I want to know how much space the MySQL table takes up on disk, but it looks trivial. Shouldn't this information be provided in INFORMATION_SCHEMA.TABLES? It's not that simple!

This seemingly simple problem is actually very complex in MySQL. MySQL supports many storage engines (some of which do not store data on disk at all) and different storage data formats. For example, the InnoDB storage engine provides three "basic" formats for MySQL 5.7, including row_formats and two compressible categories.

To simplify: how do we find the table size of the InnoDB table stored in its own tablespace on disk (provided innodb_file_per_table=1).

Before we get the answer, let's show the pre-obtained chart (bulk data insert table) run through sysbench:

Find the size of the MySQL table on disk

This figure shows the table size defined by data_length and index_length obtained from INFORMATION_SCHEMA.TABLES. You can expect tables to grow by leaps and bounds (sometimes by 10GB or more) as the data grows.

The chart does not match the way the data changes on disk, and it gradually grows (as expected):

-rw-r- 1 mysql mysql 220293234688 Jan 25 17:03 sbtest1.ibd-rw-r- 1 mysql mysql 220310011904 Jan 25 17:03 sbtest1.ibd-rw-r- 1 mysql mysql 222499438592 Jan 25 17:07 sbtest1.ibd

As we can see from this experiment, MySQL does not really maintain the values of data_length and index_length in real time, but refreshes them regularly-and irregularly. Some data refreshes in the second half of the chart become more regular. This is different from the first part of the chart, which seems to update the statistics every time 10% of the rows change. Table_rows, data_free or update_time, which are also updated in real time.

To get more accurate real-time information from information_schema in MySQL 5.7, you need to do two things:

Disable innodb_stats_persistent

Enable innodb_stats_on_metadata

Both of them will come at a serious price.

Disabling persistence statistics means that InnoDB must refresh statistics every time the server starts, which is costly and may result in unstable query plans between restarts. Is there a better way? It turns out there is.

You can view the tablespace information table through INNODB_SYS_TABLESPACES to see the actual file size. Unlike index_length and data_length, INNODB_SYS_TABLESPACES updates in real time, requiring no special configuration:

Mysql > select * from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES where name='sbinnodb/sbtest1'\ gateway * 1. Row * * SPACE: 42NAME: sbinnodb/sbtest1FLAG: 33FILE_FORMAT: BarracudaROW_FORMAT: DynamicPAGE_SIZE: 16384ZIP_PAGE_SIZE: 0SPACE_TYPE: SingleFS_BLOCK_SIZE: 4096FILE_SIZE: 245937209344ALLOCATED_SIZE: 2459372666881 row in set (0.00 sec)

The advantage of using this table is that it also handles the new feature InnoDB Page Compression which correctly shows the difference between file_size (the logical file size on disk) and allocated_size (the space allocated for this file and can be significantly reduced).

Finally, let's take a look at how different ways of InnoDB compression affect the information provided in information_schema.

Mysql > select * from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES where name='sbinnodb/testcomp' Gateway * 1. Row * * SPACE: 48NAME: sbinnodb/testcompFLAG: 33FILE_FORMAT: BarracudaROW_FORMAT: DynamicPAGE_SIZE: 16384ZIP_PAGE_SIZE: 0SPACE_TYPE: SingleFS_BLOCK_SIZE: 4096FILE_SIZE: 285212672ALLOCATED_SIZE: 1130045441 row in set (0.00 sec)

If you use the old InnoDB compression (InnoDB table compression), you will see the compressed data size shown in data_length and index_length as a result. For example, avg_row_length will be much lower than you expected.

If you use the new InnoDB compression (InnoDB page compression) in MySQL 5.7, you will see the value corresponding to the file size instead of the allocated size as shown in information_schema.

Conclusion

Answer the trivial question, "how much space does this table take up on disk?" It's not really a simple question in MySQL-obvious data can get the wrong answer.

Check the INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES to get the actual file size value of the InnoDB table.

Original link: https://www.linuxprobe.com/find-mysql-tables.html

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.