关于InnoDB表的page利用率和optimize table
上一篇我们介绍了ibd_used这个工具,我们用来量化看表数据文件的page使用率。这里用来说明optimize table这个命令的问题和优化。
实例准备
在MySQL数据库test中,建一个InnoDB存储引擎的表,代码如下所示:
CREATE TABLE `tb` ( `seq_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `a` varchar(32) DEFAULT NULL, `b` varchar(32) DEFAULT NULL, `c` varchar(32) DEFAULT NULL, `d` char(255) DEFAULT NULL, Primary key (seq_id), KEY a (a), KEY bc (b,c), KEY cb (c,b) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
执行语句为“insert into tb(a,b,c) values(randstr, randstr, randstr);” randstr是客户端程序生成的长度30字节的随机字符串。30个线程并发,每个线程插入1w条记录。
等待更新完成后(包括purge完成,从系统的vmstat上看无任何io),执行./ibd_used tb.ibd 0 100000000,可以从最后4行看到各个索引的page平均利用率如下图。
说明: 这里留着一个“异常”不提,后文再叙。
Optimize table 效果
我们知道Optimize table是用来作表整理的, 执行一下 optimize table tb,再看ibd_used的结果。
说明:这里我们发现,pk的page利用率明显提升,是optimize效果,但是其他几个索引的page利用率却没有明显效果。为什么呢?
1) 首先是上面没有提的那个“异常”,既然是自增主键,为什么在optimize之前,pk的利用率不高?原因是多线程插入,虽然seq_id是递增申请,但不能保证是递增更新到page上。而通过optimize后,等于是单线程重新整理了。
2) 为什么其他索引的page利用率没有提升,这个就涉及到optimize table的内部执行过程。如下:
a) 建一个临时表,表结构与tb相同
b) 按照tb主键顺序将tb数据一行行的插入到临时表中
c) 删掉tb,临时表重命名为tb
所以我们看到对于其他索引,插入的值仍然是随机的过程。
改进的思路
我们知道MySQL数据库InnoDB存储引擎在5.1的时候innodb_plugin里面就有fast index creatation了,上述过程如果改成如下:
a) 建一个临时表,表结构与tb相同
b) 删掉临时表的所有非聚簇索引
c) 按照tb主键顺序将tb数据一行行的插入到临时表中
d) 建立临时表的所有非聚簇索引
e) 删掉tb,临时表重命名为tb
这样在执行步骤d)时,每个非聚簇索引都是按照排序好方式构建,则能让所有的索引page都很“紧凑”。
Percona版本的 expand_fast_index_creation参数
在Percona版本中新增了这个参数,默认值是OFF,需要配置文件设置ON或者通过set命令热修改。
当设置为ON时,则optimize table tb实现的就是上述我们说到的改进流程。从ibd_used看到执行结果看到的效果如下:
小结
所以当你需要通过optimze table优化表空间,
若是使用percona版本则最好先打开expand_fast_index_creation;
若是官方版本,则建议自己写脚本建临时表,按照上述的过程a~e来执行,达到最优的效果。
建议继续学习:
- Linux如何统计进程的CPU利用率 (阅读:14332)
- linux内核研究笔记(一)内存管理 – page介绍 (阅读:8586)
- Linux下CPU的利用率 (阅读:4907)
- 查看InnoDB的磁盘空间利用率 (阅读:2839)
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:丁奇 来源: MySQLOPS 数据库与运维自动化技术分享
- 标签: page 利用率
- 发布时间:2012-05-03 00:00:36
- [44] 界面设计速成
- [39] Oracle MTS模式下 进程地址与会话信
- [38] 视觉调整-设计师 vs. 逻辑
- [35] 如何拿下简短的域名
- [35] 程序员技术练级攻略
- [35] IOS安全–浅谈关于IOS加固的几种方法
- [33] android 开发入门
- [33] 图书馆的世界纪录
- [32] 【社会化设计】自我(self)部分――欢迎区
- [30] 读书笔记-壹百度:百度十年千倍的29条法则