­

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

VirMach 便宜 VPS

QNews

QNews