Clean up the historical data of the eight nodes tables. on average, each node table has 0.15 billion records _ MySQL

Clears the historical data of the eight nodes tables. on average, each node table has 0.15 billion records bitsCN.com.

Clears the historical data of the eight nodes tables. on average, each node table has 0.15 billion records.

Clean up the historical data of the eight nodes tables. on average, each node table has 0.15 billion records. you need to clear million data records based on the date_created field. This field is not indexed.

Environment introduction

Due to insufficient online disk space, many dynamic tables are truncate, and the disk space still occupies 87%. our nagios alarm threshold value is 80%. Therefore, you will receive emails and text message alerts continuously. Date_created needs to be cleared based on the time field. However, at the beginning of the design, the date_created field was not indexed due to insufficient consideration by the original developer. In addition, the data can be deleted directly without backup.

The problem is that for a table with such a large number of records, it is very difficult to delete records based on the date_created field and there is no index. The size of the enl. ibd file is 29 GB, and the remaining disk space is 32 GB. the gap between the two files is small.

[mysql@xxxx-xxx ide]$ ll -h enl.ibd-rw-rw---- 1 mysql mysql 29G Aug 30 07:21 enl.ibd

In plan planning, shell commands are probably used.

Although date_created has no index, we have done experiments.

mysql> select * from enl where date_created < "2013-01-01 00:00:00" limit 1000;......1000 rows in set (0.00 sec)

It seems that the execution results are good. try again to delete the results.

mysql> delete from enl where date_created < "2013-01-01 00:00:00" limit 1000;Query OK, 1000 row affected (2.00 sec)

It seems that 1000 records can be deleted each time. write the script delete.

for ((i=0;i<1000000;i++));do mysql -uroot -e 'use iden;delete  from enl where date_created < "2013-01-01 00:00:00" limit 1000;';done

After the delete operation is complete, find the business off-peak and execute alter table enl engine = innodb; to release disk space.

BitsCN.com