How to clean up Alibaba Cloud MySQL space

Today I received a disk warning notification from Alibaba Cloud. I checked and found that 80G of the 100G space had been used. If I decide to delete the data before January 1, 2018, I will find that the available disk space has not decreased after deletion, but has increased rapidly. This made me very anxious, and the database was locked soon.

I quickly searched Baidu and found out that after deleting, the disk would not be reduced, and I had to execute OPTIMIZE TABLE +表名. Later, I found a savior, but the command failed. It turned out that there was insufficient space. The database was locked and this command could not be executed. I was at a loss and decided to pause the server first. Just when it was paused, a miracle happened. There was more than 5G of available space, so I could execute OPTIMIZE TABLE to optimize the table. However, after 3 minutes of execution, the command returned an error. I saw that there was insufficient space again and the table was locked again.

The final decision was to export a table and transfer it to a sql file. It took 40 minutes to complete the export. Then I deleted the table. The server suddenly had 50G more available space. So I ran the sql file again to restore the data, which took a full 150 minutes. After the execution, 70G of disk space was used. Then OPTIMIZE TABLE +表名was executed. Because one of the tables was too large, it took nearly 40 minutes to execute. During this period, the disk usage rate once soared to 100%, but fortunately it just passed.

After deleting data, the MySQL space will not be reduced. You need to execute OPTIMIZE TABLE . OPTIMIZE TABLE will generate a large number of temporary files. If the original table is large, it will take up a lot of space. Therefore, when executing this command, make sure there is enough disk space. Otherwise, executing other operations may cause insufficient disk space, lock the database, and cause execution failure.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links