上一篇我们介绍了ibd_used这个工具,我们用来量化看表数据文件的page使用率。这里用来说明optimize table这个命令的问题和优化。
实例准备
建一个这样的表
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
所以我们看到对于其他索引,插入的值仍然是随机的过程。
改进的思路
我们知道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来执行,达到最优的效果。
- 大小: 27.7 KB
- 大小: 26.2 KB
- 大小: 28.9 KB
分享到:
相关推荐
MySQL报警,从库的数据库挂了,一直在不停的重启,打开错误日志,发现有张表坏了。innodb表损坏不能通过repair table 等修复myisam的命令操作。
NULL 博文链接:https://dinglin.iteye.com/blog/1501933
py_innodb_page_info工具为《INNODB存储引擎》作者姜承尧写的。 该工具用来分析表空间中的各页得类型和信息,用python编写。 网上多是python2版本的,这里给出python3版本的。
InnoDB存储引擎中默认每个页的大小为16KB,可通过参数innodb_page_size将页的大小设置为4K、8K、16K,在MySQL中可通过如下命令查看页的大小: mysql> show variables like 'innodb_page_size'; 而系统一个磁盘块的...
innodb表损坏不能通过repair table 等修复myisam的命令操作。现在记录下解决过程,下次遇到就不会这么手忙脚乱了。 处理过程: 一遇到报警之后,直接打开错误日志,里面的信息:InnoDB: Database page corruption ...
如果在创建数据库的时候设置innodb_file_per_table=1,这样InnoDB会对每个表创建一个数据文件,然后只需要运行OPTIMIZE TABLE 命令就可以释放所有已经删除的磁盘空间。 运行OPTIMIZE TABLE 表名后,虽然最后会报...
《mysql技术内幕-InnoDB存储引擎》中使用的页分析工具类py_innodb_page_info
Mysql innodb tablespace 表空间实践
py_innodb_page.tar.gz
使用py_innodb_page_info 查看分析各种log以及data file,深入研究mysql的存储引擎底层原理与实现。 mysql innodb undo redo
可以有多个数据文件,如果没有设置innodb_file_per_table的话, 那些Innodb存储类型的表的数据都放在这个共享表空间中,而系统变量innodb_file_per_table=1的话,那么InnoDB存储引擎类型的表就会产生一个独立表空间...
使用py_innodb_page_info 查看分析各种log以及data file,深入研究mysql的存储引擎底层原理与实现
从案例看InnoDB表设计优化
◆5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。 另外,InnoDB表的行锁也不是...
OPTIMIZE TABLE通过制作原来的表的一个临时副本来工作 OPTIMIZE TABLE语法OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] … 如果您已经删除了表的一大部分,或者如果您已经对含有
MySql innodb 引擎表存储分析
mysql innodb类型数据库表 根据ibd文件获取表 space id,用于恢复innodb类型数据表数据
mysql5.6官方文档:14.7 InnoDB Table Compression