在MySQL中如何有效的刪除一個大表?
Oracle大表的刪除:http://blog.itpub.net/26736162/viewspace-2141248/
在DROP TABLE 過程中,所有操作都會被HANG住。
這是因為INNODB會維護一個全域性獨佔鎖(在table cache上面),直到DROP TABLE完成才釋放。
在我們常用的ext3,ext4,ntfs檔案系統,要刪除一個大檔案(幾十G,甚至幾百G)還是需要點時間的。
下面我們介紹一個快速DROP table 的方法; 不管多大的表,INNODB 都可以很快返回,表刪除完成;
實現:巧用LINK(硬連結)
實測:
root@127.0.0.1 : test 21:38:00> show table status like ‘tt’ \G
*************************** 1. row ***************************
Name: tt
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 151789128
Avg_row_length: 72
Data_length: 11011096576
Max_data_length: 0
Index_length: 5206179840
Data_free: 7340032
Auto_increment: NULL
Create_time: 2011-05-18 14:55:08
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.22 sec)
root@127.0.0.1 : test 21:39:34> drop table tt ;
Query OK, 0 rows affected (25.01 sec)
刪除一個11G的表用時25秒左右(硬體不同,時間不同);
下面我們來對另一個更大的表進行刪除;
但之前,我們需要對這個表的資料檔案做一個硬連線:
root@ # ln stock.ibd stock.id.hdlk
root@ # ls stock.* -l
-rw-rw—- 1 MySQL mysql 9196 Apr 14 23:03 stock.frm
-rw-r–r– 2 mysql mysql 19096666112 Apr 15 09:55 stock.ibd
-rw-r–r– 2 mysql mysql 19096666112 Apr 15 09:55 stock.id.hdlk
你會發現stock.ibd的INODES屬性變成了2;
下面我們繼續來刪表。
root@127.0.0.1 : test 21:44:37> show table status like ‘stock’ \G
*************************** 1. row ***************************
Name: stock
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 49916863
Avg_row_length: 356
Data_length: 17799577600
Max_data_length: 0
Index_length: 1025507328
Data_free: 4194304
Auto_increment: NULL
Create_time: 2011-05-18 14:55:08
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.23 sec)
root@127.0.0.1 : test 21:39:34> drop table stock ;
Query OK, 0 rows affected (0.99 sec)
1秒不到就刪除完成; 也就是DROP TABLE不用再HANG這麼久了。
但table是刪除了,資料檔案還在,所以你還需要最後資料檔案給刪除。
root # ll
total 19096666112
-rw-r–r– 2 mysql mysql 19096666112 Apr 15 09:55 stock.id.hdlk
root # rm stock.id.hdlk
雖然DROP TABLE 多繞了幾步。(如果你有一個比較可靠的自執行程式(自動為大表建立硬連結,並會自動刪除過期的硬連結檔案),就會顯得不那麼繁瑣。)
這樣做能大大減少MYSQL HANG住的時間; 相信還是值得的。
至於原理: 就是利用OS HARD LINK的原理,
當多個檔名同時指向同一個INODE時,這個INODE的引用數N>1, 刪除其中任何一個檔名都會很快.
因為其直接的物理檔案塊沒有被刪除.只是刪除了一個指標而已;
當INODE的引用數N=1時, 刪除檔案需要去把這個檔案相關的所有資料塊清除,所以會比較耗時;
【問題隱患】
由於業務需求不斷變化,可能在DB中存在超大表佔用空間或影響效能;對這些表的處理操作,容易造成MySQL效能急劇下降,IO效能佔用嚴重等。先前有在生產庫drop table造成服務不可用;rm 大檔案造成io跑滿,引發應用容災;對大表的操作越輕柔越好。
【解決辦法】
1.通過硬連結減少mysql DDL時間,加快鎖釋放
2.通過truncate分段刪除檔案,避免IO hang
【生產案例】
某對mysql主備,主庫寫入較大時發現空間不足,需要緊急清理廢棄大表,但不能影響應用訪問響應:
$ll /u01/mysql/data/test/tmp_large.ibd
-rw-r-– 1 mysql dba 289591525376 Mar 30 2012 tmp_large.ibd
270GB的大表刪除變更過程如下:
#(備庫先做灰度)
ln tmp_large.ibd /u01/bak/tmp_tbl.ibd #建立硬連結
-rw-r-– 2 mysql dba 289591525376 Mar 30 2012 tmp_large.ibd
set session sql_log_bin=0;
#不計入bin log節省效能,並且防止主備不一致
desc test.tmp_large;
drop table test.tmp_large;
Query OK, 0 rows affected (10.46 sec) mysql -uroot -e “start slave;”
cd /u01/bak;screen -S weixi_drop_table for i in `seq 270 -1 1 ` ;
do sleep 2;truncate -s ${i}G tmp_tbl.ibd;done
rm -rf tmp_tbl.ibd
【效能比較】
中間ctrl-C一次,可以看到truncate前後io的對比情況,基本上影響不大
檔案大小也成功更新
【工具介紹】
truncate – shrink or extend the size of a file to the specified size
#來自coreutils工具集
wget ftp.gnu.org/gnu/coreutils/coreutils-8.9.tar.gz
tar -zxvf coreutils-8.9.tar.gz
cd coreutils-8.9 ./configure
make
sudo cp src/truncate /usr/bin/
About Me
.............................................................................................................................................
● 本文整理自網路
● 本文在itpub(http://blog.itpub.net/26736162/abstract/1/)、部落格園(http://www.cnblogs.com/lhrbest)和個人微信公眾號(xiaomaimiaolhr)上有同步更新
● 本文itpub地址:http://blog.itpub.net/26736162/abstract/1/
● 本文部落格園地址:http://www.cnblogs.com/lhrbest
● 本文pdf版、個人簡介及小麥苗雲盤地址:http://blog.itpub.net/26736162/viewspace-1624453/
● 資料庫筆試面試題庫及解答:http://blog.itpub.net/26736162/viewspace-2134706/
● DBA寶典今日頭條號地址:http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826
.............................................................................................................................................
● QQ群號:230161599(滿)、618766405
● 微信群:可加我微信,我拉大家進群,非誠勿擾
● 聯絡我請加QQ好友(646634621),註明新增緣由
● 於 2017-08-01 09:00 ~ 2017-08-31 22:00 在魔都完成
● 文章內容來源於小麥苗的學習筆記,部分整理自網路,若有侵權或不當之處還請諒解
● 版權所有,歡迎分享本文,轉載請保留出處
.............................................................................................................................................
● 小麥苗的微店:https://weidian.com/s/793741433?wfr=c&ifr=shopdetail
● 小麥苗出版的資料庫類叢書:http://blog.itpub.net/26736162/viewspace-2142121/
.............................................................................................................................................
使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號(xiaomaimiaolhr)及QQ群(DBA寶典),學習最實用的資料庫技術。
小麥苗的微信公眾號 小麥苗的DBA寶典QQ群1 小麥苗的DBA寶典QQ群2 小麥苗的微店
.............................................................................................................................................
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26736162/viewspace-2143135/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL如何優雅的刪除大表MySql
- MYSQL下如何安全的快速刪除大表MySql
- 如何刪除大表中的資料
- MySQL大表刪除解決方案MySql
- 在Linux系統中如何刪除一個檔案?Linux
- 【MySQL】批量刪除mysql中資料庫中的表MySql資料庫
- 在ASM磁碟組中刪除一個磁碟ASM
- mysql 刪除表中重複的資料MySql
- 如何有效的刪除陣列中符合條件的值?陣列
- 【Oracle】刪除大表操作一則Oracle
- Ubuntu如何刪除Dash中的某一個專案Ubuntu
- 需要在一個1億行的大表中,刪除1千萬行資料
- 如何刪除在Github中建立的專案Github
- 在Linux中,有一個檔案,如何刪除包含“www“字樣的字元?Linux字元
- MySQL刪除資料表MySql
- 在 Linux 中如何刪除檔案中的空行Linux
- 簡單介紹mysql如何刪除資料表和關聯的資料表刪除詳情MySql
- 刪除大表資料
- MySQL在刪除表時I/O錯誤原因分析MySql
- 在Linux中,刪除一個檔案,刪不掉是什麼原因?Linux
- Oracle如何刪除表中重複記錄Oracle
- [MYSQL][1]建立,修改,刪除表MySql
- MySQL定期自動刪除表MySql
- MySQL批量刪除指定字首表MySql
- Oracle如何刪除表中重複記錄保留第一條Oracle
- MySQL資料庫表誤刪除恢復(一)MySql資料庫
- 【MySql】如何刪除 bin logMySql
- mysql 查詢及 刪除表中重複資料MySql
- git刪除歷史中的某個大檔案Git
- PHP 如何根據鍵值刪除一個陣列中的元素PHP陣列
- 如何高效率刪除大表歷史資料
- 利用jQuery如何刪除一個節點jQuery
- cookie的有效期和刪除Cookie
- javascript刪除字串中的最後一個字元JavaScript字串字元
- python 刪除大表資料Python
- 刪除一個使用者下的所有表指令碼指令碼
- postgresql VACUUM 不會從表中刪除死行的三個原因SQL
- 大資料量刪除的思考(一)大資料