专栏首页二狗的DBA之路optimize 回收表空间的一些说明

optimize 回收表空间的一些说明

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • ansible配置笔记(一)

    编辑/etc/ansible/hosts ,注释掉全部内容,改成自己的节点信息,下面的这些的是符合要求的写法:

    二狗不要跑
  • MySQL 索引条件下推 Index Condition Pushdown

    MySQL 索引条件下推 Index Condition Pushdown 出现在MySQL5.6及之后的版本中,能大幅提升查询效率,原因如下:

    二狗不要跑
  • sql语句查看MySQL数据库大小

    参考链接:http://www.ttlsa.com/mysql/mysql-howto-find-the-database-and-table-size/

    二狗不要跑
  • django 分页

    https://www.jianshu.com/p/77a8ea421e22 https://blog.csdn.net/weixin_42134789/ar...

    晴天Online
  • Django自定义分页

    py3study
  • 13.Django基础之django分页

      我们使用脚本批量创建一些测试数据(将下面的代码保存到bulk_create.py文件中放到Django项目的根目录,直接执行即可。):

    changxin7
  • CRM之分页

      分页功能在网页中是非常常见的一个功能,其作用也就是将数据分割成多个页面来进行显示。

    py3study
  • Linux内存描述之内存页面page--Linux内存管理(四)

    分页单元可以实现把线性地址转换为物理地址, 为了效率起见, 线性地址被分为固定长度为单位的组, 称为”页”, 页内部的线性地址被映射到连续的物理地址. 这样内核...

    233333
  • Discuz分页函数及使用

    该函数在 ./include/global.func.php 文件中定义。函数原型为: string multi(int $num, int $perpage,...

    96php.cn
  • python 分页

            class UserList(models.Model):             username = models.CharField(ma...

    py3study

扫码关注云+社区

领取腾讯云代金券