## 编程知识 cdmana.com

### How to delete large tables gracefully in MySQL

### Preface delete table , The command you subconsciously think of might be direct use DROP TABLE " Table name ", This is the way of a newborn calf , Because when you want to delete the expression space to dozens of G, Even hundreds of them G When is your watch . Such an order goes on ,MySQL Maybe it's just rammed , The external manifestation is QPS It's falling rapidly , Customer requests slow down .### Solutions #### 1. Manual deletion at low peak time may require DBA Take pains , I got up in the evening and deleted the list .#### 2. Clear the data first , The last way to delete it is as follows 1000 Ten thousand pieces of information , Write script, delete every time 20 Ten thousand , Sleep for a while , Continue execution . This also makes it possible to have no perception of the user .#### 3. Check the table file （idb Archives ） This is a way to speed up the deletion of hard links linux Hard linked knowledge , To quickly delete , If you don't remember, you can go back and look for it 《 Brother bird linux Private dishes 》ln data_center_update_log.ibd data_center_update_log.ibd.hdlk[ [email protected] sports_center]# ll Total dosage 19903792-rw-r----- 1 mysql mysql 9076 10 month 17 13:15 data_center_update_log.frm-rw-r----- 2 mysql mysql 8447328256 12 month 23 11:35 data_center_update_log.ibd-rw-r----- 2 mysql mysql 8447328256 12 month 23 11:35 data_center_update_log.ibd.hdlk- After executing the above command , We have one more data_center_update_log.ibd.hdlk Archives . This operation does not actually take up disk space , Just added a reference to the file on disk . - When we delete any of these files , Does not affect the actual files on the disk , Just subtract the number of references 1. When the number of references becomes 1 When , Then delete the file , To really do IO To delete it .- It's taking advantage of this feature , It will change from the original mysql To delete large files , Conversion to a simple operating system level file deletion , Thus reducing the amount of mysql Impact of .#### 4. Landing mysql, Execute drop Table operations  Soon ,200 Ten thousand pieces of information only used 1 Seconds to complete , This operation is performed after the hard link is established mysql> drop tables data_center_update_log;Query OK, 0 rows affected (1.02 sec)mysql> exitBye Back out , Look at the data catalog again , It turns out that it's just data_center_update_log.ibd.hdlk Hard linked files [ [email protected] sports_center]# ll Total dosage 19903792-rw-r----- 2 mysql mysql 8447328256 12 month 23 11:35 data_center_update_log.ibd.hdlk#### 5. How to delete correctly ibd.hdlk Hard link files - Although drop table After that , The remaining hard link files have been linked to mysql It doesn't matter . But if the file is too large , Direct use rm Order to delete , It can also cause IO Spending is soaring ,CPU The load is too high , And then it affects MySQL.- The method we use here , You can circle and block delete , Slowly clean up the files , It can be done with one instruction code - Truncate The command is usually used to reduce or expand a file to a specified size . If the file is larger than the specified size , You lose extra data . If the file is short , Then it will be expanded into a suite , And the extension package section reads zero bytes .##### 5.1 Install truncate command [ [email protected] ~]# cruncate-bash: cruncate: The command is not found. Usually the operating system will install truncate command , The order is in coreutils In the installation package , If not installed, you can use the following command to install [ [email protected] ~]# yum provides truncatecoreutils-8.22-24.el7.x86_64 : A set of basic GNU tools commonly used in shell scripts Source ：base Match source ： File name ：/usr/bin/truncate You can see truncate from coreutils The installation package provides , Install below coreutils Installation package ：[ [email protected] ~]# yum install -y coreutils##### 5.2 truncate Common options -c, --no-create --> Don't create any files -o, --io-blocks --> Think of the size as the number of storage blocks , Instead of bits -r, --reference=RFILE --> Refer to the specified file size -s, --size=SIZE --> Set the file size according to the specified bytes ##### 5.3 truncate_bigfile.sh Instruction code - principle ： Use truncate -s Option to specify the file size , Specify the size of each file reduction by script , And sleep Sleep for a certain time , So as to achieve controllable deletion of files - attach ：truncate_bigfile.sh Instruction code #! /bin/bash#TRUNCATE=/usr/bin/truncateFILE=$1if [ x"$1" = x ];then echo "Please input filename in" exit 1;else SIZE_M=$(du -sm "$1" | awk '{print $1}') for i in$(seq "${SIZE_M}" -100 0) do sleep 1 echo "${TRUNCATE} -s ${i}M${FILE}" ${TRUNCATE} -s "${i}"M "${FILE}" donefiif [$? -eq 0 ];then \rm -f "\${FILE}"else echo "Please check file"fi#### Because of regret , So the stars and the moon ; Because of dreams , So I'm desperate ！ Personal blog starts ：easydb.net Wechat public account ：easydb Pay attention to me , Don't get lost ！![image](https://easydb.oss-cn-shenzhen.aliyuncs.com/%E6%A0%87%E5%87%86%E8%89%B2%E7%89

https://cdmana.com/2020/12/20201224215001787G.html

Scroll to Top