编程知识 cdmana.com

How to delete large tables gracefully in MySQL

Preface

Delete table , The command you subconsciously think of may be direct use DROP TABLE " Table name ", It's the practice of a newborn calf , Because when you want to delete the expression space to dozens of G, Even hundreds G Watch time . Such an order goes on ,MySQL Maybe it's just rammed , The external manifestation is QPS It's falling rapidly , Customer requests slow down .

terms of settlement

1. Delete manually in low peak time

This may require DBA Take pains , I got up in the evening and deleted the table .

2. Clear the data first , The last way to delete

for example 1000 Ten thousand data , Write a script every time you delete 20 ten thousand , Sleep for a while , Carry on . In this way, we can also have no perception of users .

This method makes use of linux Knowledge of hard links , To quickly delete , If you don't remember, you can go back and look for it 《 Bird brother linux Private dishes 》

ln data_center_update_log.ibd data_center_update_log.ibd.hdlk

[root@mysql01 sports_center]# ll
 Total usage  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 order , We have one more data_center_update_log.ibd.hdlk file . 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 , Will not affect the real files on the disk , Just subtract the number of references 1. When the number of references becomes 1 When , And then delete the file , To really do IO To delete it .
  • It is by taking advantage of this characteristic that , It will be changed from mysql To delete a large file , Convert to a simple operating system level file deletion , This reduces the need for mysql Influence .

4. land mysql, perform drop Table operations

 Soon ,200 Ten thousand pieces of data only use 1 Seconds to complete , This is done after the hard link is created 
mysql> drop tables data_center_update_log;
Query OK, 0 rows affected (1.02 sec)


mysql> exit
Bye

 Back out , Look at the data catalog again , It turns out that it's just data_center_update_log.ibd.hdlk Hard link file 
[root@mysql01 sports_center]# ll
 Total usage  19903792
-rw-r----- 2 mysql mysql 8447328256 12 month  23 11:35 data_center_update_log.ibd.hdlk
  • although drop table after , The remaining hard link files have been linked with mysql It doesn't matter . But if the file is too large , Direct use rm Command to delete , It will also cause IO Spending soared ,CPU Overload , Which in turn affects MySQL.
  • The method we use here , It can be deleted in a circular block , Slowly clean up the files , It can be done in a script
  • 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 , It will be extended , And the extension reads zero bytes .
5.1 install truncate command
[root@mysql01 ~]# cruncate
-bash: cruncate:  Command not found 
 Usually the operating system will install truncate command , The command is in coreutils Inside the installation package , If not installed, you can use the following command to install 

[root@mysql01 ~]# yum provides truncate
coreutils-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 , The following installation coreutils Installation package :

[root@mysql01 ~]# yum install -y coreutils

5.2 truncate Common options
-c, --no-create -->  Do not create any files  
-o, --io-blocks -->  Think of the size as the number of storage blocks , Not bytes  
-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 Script
  • 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 amount of time , So as to achieve controllable deletion of files
  • attach :truncate_bigfile.sh Script
#! /bin/bash
#

TRUNCATE=/usr/bin/truncate
FILE=$1

if [ 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}"
	done
fi

if [  $? -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 launched :easydb.net WeChat official account :easydb Pay attention to me , Don't get lost !

image

版权声明
本文为[easydba]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201224214721858W.html

Scroll to Top