MySQL Clear disk fragmentation

Task background

Received Kingsoft alarm text message, said a database capacity has reached 90% of the watermark, so log on to the console to see the details.

When the console first discovers that the disk capacity of each day does fluctuate, it proves that the "Resource Recovery" module written by the developer is functioning normally,

Then it means that there is no data can be deleted, since the deletion of redundant data and do not want to pay more to expand disk capacity, only from the "Disk fragmentation". and InnoDB the engine cleans up the disk fragments by the command OPTIMIZE .

Specific operation

First, I'll look at all the "disk fragmentation scenarios" and use the following statements:

Or it select table_schema, table_name, data_free, engine from information_schema.tables where table_schema not in (‘information_schema‘, ‘mysql‘) and data_free > 0; can be used, this is data_free all tables with a query greater than 0.

and see what I call this history_device_flow_day table is as follows:

The table data_free is the amount of disk fragmentation, such as I now want to kill history_device_flow_day all the disk fragments, is 975MB, so first query the history_device_flow_day storage engine, using the following statement:

    show table status from jsonlinefssrds where name=‘history_device_flow_day‘;

The above statement jsonlinefssrds is the corresponding database, see the effect is as follows:

The storage engine is InnoDB , then you can start to clean up the fragments of the statement: OPTIMIZE TABLE 数据表表名; , because OPTIMIZE TABLE only the MyISAM , BDB and the InnoDB table function.

After the execution OPTIMIZE TABLE history_device_flow_day; , for about 9 minutes, you will see the words "OK":

It is estimated that some friends will ask, it is not clearly written "Table does not support optimize, doing recreate + analyze instead"? This actually does not matter, actually the disk fragment has been cleared away. We can use one more time to query the disk fragment command to see

It did release more than 900 m.

or use ALTER TABLE 表名 ENGINE = Innodb; (just InnoDB the table can do) to clean up the disk fragments of the purpose, this command on the surface do not do anything, in fact, re-defragment. When performing an optimization operation, the actual execution is an empty ALTER command, but this command will also play an optimization role, it will rebuild the entire table, delete unused empty space.

Add

Why is disk fragmentation generated? That is because a table if you often insert data and delete data, will inevitably produce a lot of unused white space, these spaces are discontinuous fragments, so over time, the table will occupy a lot of space, but in fact, the number of records in the table is very small, this will not only waste space, and query speed is also slower.

Watch out! OPTIMIZEThe operation temporarily locks the table, and the larger the amount of data, the longer it takes, after all, it is not a simple query operation. Therefore, it OPTIMIZE is not appropriate to put the command in the program, no matter how low the number of hits, when the traffic increases, the overall hit rate will rise, which will certainly have a great impact on the operation efficiency of the program. The better way is to be a shell, check MySQL regularly information_schema . TABLES field, the View DATA_FREE field, which is greater than 0, indicates fragmentation, and then starts the script.

Resources

Http://pengbotao.cn/mysql-suipian-youhua.html
http://irfen.me/mysql-data-fragmentation-appear-and-optimization/

Finally, if you feel this article is helpful for your promotion, please do not hesitate to sponsor the hand, brush the following QR code, sponsorship I continue to write more blog posts!

MySQL Clear disk fragmentation