How to defragment data in MySQL5.7

This article is about how to defragment data in MySQL 5.7. Xiaobian thinks it is very practical, so share it with everyone to learn. I hope you can gain something after reading this article. Let's not say much. Let's take a look at it together with Xiaobian.

For the innodb storage engine, for example, deleting a row simply marks it as "deleted" rather than physically deleting it from the index, so space is not actually freed. Innodb's Purge thread asynchronously cleans up unused keys and rows, but still doesn't free up space for the system, resulting in holes in the page.

In versions before MySQL 5.7, you need to use OPTIMIZE TABLE or ALTER TABLE_NAME ENGINE=InnoDB(for example, an independent table space), which is equivalent to creating a new table, performing an export/import, and then deleting the old table.

MySQL 5.7 incorporates Facebook's defragmentation code and requires the following configuration to be added to the my.cnf configuration file:

[mysqld]

innodb_defragment=1

#Turn InnoDB defragmentation on or off.

innodb_defragment_n_pages=16

#How many pages are read at once for consolidation. Range is 2-32, default is 7

When configured this way, the new defragmentation feature replaces the OPTIMIZE TABLE algorithm, speeding up defragmentation time and eliminating new table generation.

The following is a description of the status parameters:

Innodb_defragmentation_compression_failures: Number of failures to recompress pages while defragmenting

Innodb_defragment_failures: Number of times the defragment operation failed (if there are compressible pages)

Innodb_defragment_count: Number of defragment operations

The above is how to defragment data in MySQL 5.7, Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.