[MySQL FAQ]系列 -- 数据不算大,备份却非常慢

问题

环境
硬件:DELL 1950, 146G SAS 15K RPMS * 2, 8G Ram
软件:2.6.9-55.ELsmp x86_64, mysql 5.1.x

现象
2个库,其中1个业务库下有20多个表,表文件大小总量不到2G。
另一个为日志库,下400多个表,大致是每天会产生5个表,其中有一个表较大,约400MB,总量约40多GB。
每次备份耗时较长,最严重的一次花了5个多小时才完成。
业务库为当前活动库,日志库则主要用作备份,每天日志归档,过期数据表很少有读写请求。
InnoDB Buffer Pool总共分配了2G,从系统命令 top 结果来看,mysqld 只分配了 1.7G 内存,buffer pool 并没有全部耗尽。
SHOW ENGINE INNODB STATUS 结果中也看到了,buffer pool 确实没用完,还有不少空闲的。
备份时,观察 vmstat 结果,发现 bibo 的量较大,而且两个的值基本相当,备份其中一个表约 500MB,耗时 46 秒。
按照这个耗时计算,全部备份出来也不需要5个多小时,这是为什么呢?

分析
大家先分析下,看是什么原因,稍后给出答案 :)

 

原因 其实问题原因很简单,但一般人不容易想到。那就是,那些历史的日志表,由于长时间不读取,大部分数据没有在innodb buffer中。所以,每次备份时,大部分数据都要产生大量的物理读,然后再产生物理写,然而该服务器只有2块硬盘,I/O性能有限,所以备份非常慢。

这时候,我们可以有几种解决办法:
1. 删除过期日志表,或者放到线下的归档数据库上
2. 如果innodb buffer还有大量空闲的话,可以不定期执行select * from table,将这部分数据load到buffer中,减少备份时的物理I/O,提高速度

技术相关:

评论

我有一个想法,是否可以调整mysql的block size大小与文件系统的块大小一致,或者是n* 文件系统的block size?这样是否可以提高数据的读写性能?

嗯,这个时候适合innodb的情况,它的page可以设置固定大小,但其他引擎可能就不太适合了;另外,innodb plugin 1.0.4+以后,每个innodb表都可以自定义page size了

MySQL方案、培训、支持

会不会是因为表太多,并且打开了PER_TABLE?

那我大概知道了,因为我们这边有一个产品是1000个数据库,每个数据库里有100张表,已经极度优化过了,因为必须开启per_table,否则单个innodb数据文件的后果就是数据库慢死,这种表单个都不大,但数量居多的情况下,打开关闭表是很费时的,IO也会居高不下,CPU消耗最高,不知老叶的情况还有别的因素不?

这也是其中的一个因素,不过不是最关键的,呵呵。该库下,确实有几百个表,不过还不算太多。

MySQL方案、培训、支持

-_-# ,不知从你前面亮出的各种信息,能否猜得到结果,我是猜不到了。。

借这里问两个问题:
1)MYSQL触发器能否实现良种操作在一个触发器中?oracle可以实现,没找到MYSQL的能否可以,但初步试验是不成。 例如: create trigger my_trigger before insert or before update on table.....

2)MYSQL触发器或过程能否自定义异常处理及异常信息显示内容,例:我在触发器中执行一系列操作后,根据某一条件满足后,抛出我自定义的错误信息显示内容。

1. 写2个trigger
2. 可以,多看看手册吧

过程里可以实现,触发器里似乎不可以,比如我TRG里违反了约束,错误显示为我自定义的内容

你好,我有个有问题和你说的一样(1000个数据库,每个数据库里有100张表),就是表多达到5W张,但不大,打开关闭都很慢,导出就更慢了,导出速度0.5M每分钟左右,不知道你们是怎么处理的?

这么bt的设计,这5w个表都是在线需要用的吗?不是的话,把历史表备份起来,放到异地,线上只保留确实需要的表。

还有就是max_allowed_packet的值可以设大一点,这样应该也能加快dump的速度

请问是用何种备份方式?

想不出来,公布答案吧

据说mysqlhotcopy备份更快

猜2个原因:碎片太多,业务并发写太多。

猜2个原因:业务库碎片太多;业务写太多

是不是用了single-transaction参数导致事务太大?

感觉是磁盘不是异步的IO啊

嗯,是I/O方向的问题,不过不是因为非异步I/O引发的

MySQL方案、培训、支持

备份的时候 show engine innodb status \G有什输出么?

你想关注哪方面的内容,现场不在我这,有些信息暂时无法提供,sorry。

MySQL方案、培训、支持

春花烂漫@内衣馆 大量出口原单 欧美日韩 潮流 内衣 睡衣-女装/流行女装-淘宝网,自己打个广告,哈哈。