先说下最近遇到的问题,我们的一个项目数据统计用MySQL,为了防止并发insert MySQL撑不住,我们先把数据插入到redis的list里边,然后使用后台脚本读取插入到MySQL中,运行了小半年,基本没问题,但最近由于用户越来越多,导致redis的list中堆积了很多任务,原因是因为MySQL的插入速度很慢,之前没遇到瓶颈也就没有分析过,现在就需要分析和解决问题了。
我们现在的数据量大概是每天250万左右记录,分散插入到8张不同的表中,最大的表一个月大概2500万条记录。首先部署了一个slave,先解决下查询问题,不然插入任务多的时候,查询基本挂掉,非常之慢,slave部署之后,查询问题基本解决。接下来解决插入问题,检查下MySQL的数据目录,发现ibdata1有58G,原因是因为一开始没注意,部署时候没有配置innodb单独表空间,不知道是不是因为这个导致插入慢呢?google之,也没发现ibdata1过大会导致性能瓶颈的问题,不过我还是尝试开启innodb单独表空间选项,
innodb_file_per_table = 1
这个需要重启MySQL,不过,只有新创建的表才会使用独立表空间,ibdata1并不会因为这个而减小,需要使用 mysqldump导出所有数据,然后重新建立MySQL导入数据后生成新的ibdata1,目前数据太大,不方便尝试这个,为了简单验证下,将本月的所有表都修改为独立表空间,执行下面命令
这个执行也非常之慢,8张表执行了5个多小时,调整之后发现insert语句的速度并没有明显的改善,接着,正好是月末,马上到月初,那么重新建立MySQL,使用独立表空间,然后新的数据都使用新的数据库,结果发现,效果依然不明显。没办法,继续优化my.cnf参数,google了一些优化文章,又请教了之前的老同事,调整之后效果也并不是特别明显,接着换关键词google,发现了这篇文章http://blog.51yip.com/mysql/1369.html,按照这篇博文的策略修改了一下,发现性能变好了许多,
innodb_flush_log_at_trx_commit这个参数设置为0之后,性能好了一些,我们的统计数据,可以容忍丢小小部分数据,所以设置了0,提高性能主要还是关闭
autocommit,我们是使用PHP写后台脚本来插入数据到MySQL,1000条数据一个事务提交一次,发现性能好太多了,插入基本没有压力了,类似这样,
没有进行准确的测试,不过我们redis的队列不会堆积了,MySQL插入基本不会延迟。
在没有解决问题之前,以为要放弃MySQL换其他策略了,现在发现使用MySQL依然没有问题,当初使用MySQL主要是因为SQL还是比较强大,一些统计数据一个SQL语句搞定,经过这个发现MySQL还是比较强大,认为MySQL性能差大多是自己没用好。
--转自