Innode表空间碎片优化

原创 2015年07月09日 10:58:02

一:MySQL数据存储方式(Innodb)

1.      数据表的框架结构:.frm 文件(数据库服务器层)

2.      数据和索引存储(Innode引擎层)

1共享表空间:

  ibdata:系统表空间文件,存储Innodb系统信息和用户数据库表数据和索引,所有表共用。

2独立表空间:

  ibdata:数据库所有表信息,没有在这里面记录的表将会提示不存在。同时用来启动Innodb。

  .ibd:存储每个表对应的数据库和索引。

通过分析Innodb数据的存储方式,发现确实Innodb不适用mysql hot copy。即直接备份数据库文件夹来备份数据库。

 

二:测试共享表空间碎片是否会重复利用

测试共享表空间删除数据后,新插入数据会使用表空间碎片

drop table

1.  原始数据


 

2.  删除T_LOCATION




3.  导入原表数据



经过测试,将表drop 后,重新插入数据库,会使用原来的表空间。具体使用比例可能会跟表空间产生碎片大小有关。

delete 记录

1.      原始T_LOCATION时,共享表空间为3.8G,为了实现删除1千万数据后有导会数据,重新建立T_LOCATION_dummy来备份1千万需要删除的数据。


 

2.      删除T_LOCATION中1千万数据,共享表空间增长了0.4G(猜测mysql 的delete操作中有在表空间复制数据的操作)。


 

3.      在T_LOCATION中再插入刚刚删除的那1千万记录,表空间没有增长,利用了表中的碎片空间。


 

总结:利用delete 删除表中的记录时,共享表空间不会减小反而增大。猜测mysql在删除数据时会对删除数据进行备份操作,这个暂时没有找到验证资料,后期补充。delete删除数据库产生的表空间碎片是可以重复利用的。利用比例和具体碎片大小以及插入数据大小有关。

drop partition

1.      利用drop partition删除表分区后,共享表空间大小没有变化

 


 

总结:通过实验,利用drop partition删除表分区共享表空间大小没有改变。也就说删除表分区后表空间也不会释放,但是产生的碎片也同理可以被重复使用。

三:linux 文件系统和文件大小限制

1.      查询相关资料显示,ext3 格式的文件系统blocks size为4k。单个文件大小限制为2T,文件系统大小限制为8T,同时使用可以使用  ulimit –f 查看用户文件大小权限限制。

2.      查询相关资料,表空间限制4,000,000,000个数据库页,而每个数据库页大小为16K,所以理论上表空间最大可以达到64T

 

linux 文件系统和文件大小限制:

http://www.xifenfei.com/2204.html

http://blog.163.com/mail_zcw/blog/static/4126077420113290121997/

 

mysql Innodb表空间限制:

http://blog.sina.com.cn/s/blog_91339bff0100v2xq.html

http://blog.csdn.net/duck_arrow/article/details/8255377

 

四:共享表空间碎片回收

目前没有找到好的回收共享表空间碎片的方法,网络资料多处显示也没有好的办法回收。但是通过上面的实验可以看出,当我们删除部分数据后,共享表空间大小没有改变,再次恢复数据时,表空间大小也几乎没有改变。说明虽然共享表空间的碎片没有自动回收,但是却还是可以被重复利用,填充。利用率(50%-100%)可能和表空间碎片的大小以及分配方法有关(固定大小分配,最优大小分配等等),这和磁盘空间分配一样的道理。

有人在是官方使用手册中也有提到共享表空间没有好的回收方法:

http://dev.mysql.com/doc/refman/5.6/en/innodb-data-log-reconfiguration.html

   参考资料-innodb表空间介绍:

http://www.docin.com/p-412074893.html

 

所以目前大多数方法都是把共享表空间转化成独立表空间。

1.共享表空间和独立表空间对比:

1共享表空间所有数据和索引存储在ibdata文件中,所有的表共享这个比表空间。而独立表空间则每一个表都有自己独立的表空间,分区表也会有自己独立的表空间,这样就方便对分区表的数据进行管理。(但是删除分区的话,不可以直接删除分区表对应的ibd文件,会导致整张表在ibdata文件中启动失败。删除分区用drop partition)

2因为独立表空间每一个表都会有自己的.ibd文件,所以通过复制表空间文件就可以使这个对应的表在同一个数据库实例中移动。

3独立表空间删掉数据对应的空间可以自动回收(drop table 操作除外,)delete, truncate等等

4独立表空间碎片可以手动回收,利用alter table tablename engine=innodb, optimiaze等等。

5当单个独立表空间过大时,如果查询表的范围过大,可能查询共享表空间的效率比独立表空间要高些。因为独立表空间需要一次访问多大打文件。

 

通过上面的比较,我们发现共享表空间增长速度要比独立表空间大。而且数据库达到亿级别的记录时,数据文件也很大不但不方便操作,也不方便数据的管理。对我们来说,是需要定期的备份删除一部分数据的(否则持续增长),所以需要定期优化表空间,而且独立表空间删除数据可以自动优化,处于上面种种对比所以我建议我们将数据库转化为独立表空间,时间越长,后期越难处理。

2.共享表空间转化独立表空间流程

1利用mysqldump 备份所有数据库

mysqldump –uuser –ppossword > dummy.sql

2修改my.cnf配置文件

   innodb_file_per_table=1

3停止mysql服务器,删除ibdata,ib_logfile相关文件,以及业务数据库文件夹

4重启mysql数据库 ,自动生成Innodb相关文件

5利用备份恢复数据库

 

参考资料:

http://chqz1987.blog.163.com/blog/static/5143831120134269463429/

http://blog.fens.me/mysql-ibdata1/

 

 

3共享表空间负载

在my.cnf中设置innodb表空间的大小以及位置

innodb_data_file_path =/disk1/ibdata1:10G;/disk2/ibdata2:10G:autoextend:max:100G

版权声明:本文为博主原创文章,未经博主允许不得转载。

Innode引擎监控的开启的方法

查看当前InnoDB引擎信息 mysql> show innodb status\G 开启InnoDB监控,有四种: 1. innodb_monitor mysql> create table i...
  • zfqzpp
  • zfqzpp
  • 2013年08月03日 14:38
  • 1637

表空间与碎片

在本地管理的表空间中,怎么预防空间碎片问题呢?要想防止,就得很好的理解碎片如何产生。在本地管理的表空间,可以继续选择更细的管理方式:统一尺寸(uniform)还是自动分配(autoallocate)。...

mysql如何消除表空间碎片

MySQL 清除表空间碎片 碎片产生的原因: (1)表的存储会出现碎片化,每当删除了一行内容,该段空间就会变为空白、被留空,而在一段时间内的大量删除操作,会使这种留空的空间变得比存储列表内容所使用的空...

ORACLE表空间和表碎片分析及整理方法

表空间碎片率idle> select a.tablespace_name,sqrt(max(a.blocks)/sum(a.blocks))*(100/sqrt(sqrt(count(a.blocks...
  • x6_9x
  • x6_9x
  • 2016年01月27日 20:54
  • 6980

MySQL 清除表空间碎片

碎片产生的原因(1)表的存储会出现碎片化,每当删除了一行内容,该段空间就会变为空白、被留空,而在一段时间内的大量删除操作,会使这种留空的空间变得比存储列表内容所使用的空间更大;(2)当执行插入操作时,...

ORACLE表空间的碎片整理 20131224

碎片是如何产生的 生成一个数据库时,它会分成称为表空间(tablespace)的多个逻辑段(segment).如系统(system)表空间,临时(temporary)表空间等.一个表空间可以包含多个...

MySQL表空间的碎片整理

当我们对于一个数据量非常大的表进行大量的删除操作后,这个表将会存在非常多的碎片,导致数据库服务器性能下降,这个时候就要求我们对这些表要定期的进行碎片整理以保证数据库的性能。其中一个命令可以帮助我们完成...
  • ErErFei
  • ErErFei
  • 2015年06月29日 16:55
  • 933

MySQL表空间碎片整理

MySQL可以使用alter table tn engine=innodb语句进行表空间碎片整理。而页内记录并不是物理有序的,并且删除记录后,被删除的记录放到页内free链表,可能会产生很多空洞。al...

表空间碎片的发现

今天做周报,查询剩余表空间大小 select  from

查看数据库空间页。表空间页。索引空间页。索引碎片。日志空间的使用情况。

--111-----------------------------------------------------------------------------------------------...
  • SQL77
  • SQL77
  • 2012年07月10日 21:29
  • 1378
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Innode表空间碎片优化
举报原因:
原因补充:

(最多只允许输入30个字)