Looyao's Blog

记录一些点滴

优化MySQL Innodb引擎批量insert速度

| Comments

先说下最近遇到的问题,我们的一个项目数据统计用MySQL,为了防止并发insert MySQL撑不住,我们先把数据插入到redis的list里边,然后使用后台脚本读取插入到MySQL中,运行了小半年,基本没问题,但最近由于用户越来越多,导致redis的list中堆积了很多任务,原因是因为MySQL的插入速度很慢,之前没遇到瓶颈也就没有分析过,现在就需要分析和解决问题了。

我们现在的数据量大概是每天250万左右记录,分散插入到8张不同的表中,最大的表一个月大概2500万条记录。首先部署了一个slave,先解决下查询问题,不然插入任务多的时候,查询基本挂掉,非常之慢,slave部署之后,查询问题基本解决。接下来解决插入问题,检查下MySQL的数据目录,发现ibdata1有58G,原因是因为一开始没注意,部署时候没有配置innodb单独表空间,不知道是不是因为这个导致插入慢呢?google之,也没发现ibdata1过大会导致性能瓶颈的问题,不过我还是尝试开启innodb单独表空间选项,

1
innodb_file_per_table = 1

这个需要重启MySQL,不过,只有新创建的表才会使用独立表空间,ibdata1并不会因为这个而减小,需要使用mysqldump导出所有数据,然后重新建立MySQL导入数据后生成新的ibdata1,目前数据太大,不方便尝试这个,为了简单验证下,将本月的所有表都修改为独立表空间,执行下面命令,

1
mysql>ALTER TABLE table_name ENGINE=InnoDB;

这个执行也非常之慢,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条数据一个事务提交一次,发现性能好太多了,插入基本没有压力了,类似这样,

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
$dsn = "mysql:host={$MYSQL_HOST}:{$MYSQL_PORT};dbname={$MYSQL_DB}";
try {
    $db = new PDO($dsn, $MYSQL_USER, $MYSQL_PASS);
} catch (PDOException $e) {
    echo "Failed to get DB handle: " . $e->getMessage() . "\n";
    exit;
}
$db->setAttribute(PDO::ATTR_AUTOCOMMIT, 0);
$db->exec('set names utf8');
$db->exec('set session wait_timeout=3600');

try {
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $db->beginTransaction();
  $db->exec("insert into ...");
  $db->exec("insert into ...");
  ...
  $db->commit();

} catch (Exception $e) {
  $db->rollBack();
  //TODO:回滚处理,没有执行成功的任务要记录重新处理
  echo "Failed: " . $e->getMessage();
}

没有进行准确的测试,不过我们redis的队列不会堆积了,MySQL插入基本不会延迟。

在没有解决问题之前,以为要放弃MySQL换其他策略了,现在发现使用MySQL依然没有问题,当初使用MySQL主要是因为SQL还是比较强大,一些统计数据一个SQL语句搞定,经过这个发现MySQL还是比较强大,认为MySQL性能差大多是自己没用好。

小计一下,顺便感谢上边链接文章博主。

Comments