optimize 回收表空間的一些說明
- 2019 年 10 月 4 日
- 筆記
optimize命令回收表空間的說明
線上伺服器，有張大表需要用pt-archiver根據時間劃分歸檔大量數據到另一個新表中。原先200G的表，在歸檔完成後，du -hs 顯示依然是200G的大小，刪除了大量的行記錄但是實際上空間是不會釋放的。
這種情況下，我們就要使用optimize命令重建表以達到釋放表空間的目的。
（好像是從5.6.6之後，optimize不鎖表了，但是optimize操作會進行rebuild表操作，要確保磁碟剩餘空間足夠存放新表的大小，不然操作會失敗）
另外，如果在主庫執行optimize table會造成從庫延遲，這種情況下，可以使用 optiminze no_write_to_binlog table xxxx ; 這樣就不會把optimize操作寫入binlog。主庫執行完後，再到從庫執行optimize table操作。
姜承堯的py_innodb_page_info 工具 下載地址：http://pan.baidu.com/s/1c2o0Tag
模擬過程如下：
use test; CREATE TABLE `t` ( `a` int(10) unsigned NOT NULL AUTO_INCREMENT, `b` char(10) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; insert into t (b) select 'aaaaaaa'; insert into t(b) select b from t;
多執行幾次這個命令，造出大量的行數據
然後使用py_innodb_page_info分析page，如下，可以看到存了數據的page很多【下圖紅色部分】
[root@master /root/py_innodb_page_type ]#./py_innodb_page_info.py -v /data/mysql/test/t.ibd page offset 00000000, page type <FileSpace Header> page offset 00000001, page type <Insert BufferBitmap> page offset 00000002, page type <FileSegment inode> page offset 00000003, page type<B-tree Node>, page level <0000> page offset 00000004, page type<B-tree Node>, page level <0000> page offset 00000005, page type<B-tree Node>, page level <0000> page offset 00000006, page type<B-tree Node>, page level <0000> page offset 00000007, page type<B-tree Node>, page level <0000> page offset 00000008, page type<B-tree Node>, page level <0000> page offset 00000009, page type<B-tree Node>, page level <0000> page offset 0000000a, page type<B-tree Node>, page level <0000> page offset 0000000b, page type<B-tree Node>, page level <0000> page offset 0000000c, page type<B-tree Node>, page level <0000> page offset 0000000d, page type<B-tree Node>, page level <0000> page offset 0000000e, page type<B-tree Node>, page level <0000> page offset 0000000f, page type<B-tree Node>, page level <0000> page offset 00000010, page type<B-tree Node>, page level <0000> page offset 00000000, page type <Freshly AllocatedPage> page offset 00000000, page type <FreshlyAllocated Page> Total number of page: 19: Freshly Allocated Page: 2 Insert Buffer Bitmap: 1 File Space Header: 1 B-tree Node: 14 File Segment inode: 1
然後大量刪除數據
delete from test.t where a>100;
開始刪除大量的數據(只保留100條記錄，確保數據應該在第一個數據頁存的下）
然後用hexdump去看下innodb的第二個page資訊
hexdump -C -s 65536 -n 16384 /data/mysql/test/t.ibd
發現這個page的數據還是很多，它們並沒有被真正的刪除 (實際上當一條記錄被刪除後，該空間只是標記為空閑了，它會被加入到空間鏈表裡面)
### hexdump命令說明：
## -s 從啥位置開始取數據，-n 取出多少bytes的數據。
## 因為每個page 16k。InnoDB前3個page是存放其它數據的。第一個data page是從16*1024*3=49152位置開始的。第二個data page是從16*1024*4=65536開始的。
重建下test.t表試試效果：
root@localhost [test]> optimize table t;
再次使用py_innodb_page_info分析page，如下，可以看到page少了很多【下圖紅色部分】，基本上都被回收了。
[root@master /root/py_innodb_page_type ]#./py_innodb_page_info.py -v /data/mysql/test/t.ibd page offset 00000000, page type <FileSpace Header> page offset 00000001, page type <InsertBuffer Bitmap> page offset 00000002, page type <FileSegment inode> page offset 00000003, page type<B-tree Node>, page level <0000> page offset 00000000, page type <FreshlyAllocated Page> page offset 00000000, page type <FreshlyAllocated Page> Total number of page: 6: Freshly Allocated Page: 2 Insert Buffer Bitmap: 1 File Space Header: 1 B-tree Node: 1 File Segment inode: 1
然後再用hexdump去看下innodb的第二個page資訊，發現這個page的數據已經全部是0了，是一個空白的page
[root@master /tmp ]# hexdump -C -s 65536 -n16384 /data/mysql/test/t.ibd 00010000 00 00 00 00 00 00 00 00 00 00 0000 00 00 00 00 |................| * 00014000
