大量のインサートを高速化する / DBIx::Customリファレンス

2011-11-02

大量のインサートを高速化する / DBIx::Customリファレンス

　DBIx::Customは便利なDBIのラッパーですが、大量のinsertなどを連続して行う場合の性能に関しては、生のDBIに比べて性能がかなり劣化します。

　そのような場合は複数のデータを一度に挿入しましょう。DBIx::Customのinsertメソッドには、複数のデータを一度に渡すことができます。

my @params = [{id => 1, title => 'Perl'}, {id => 2, title => 'Ruby'}];
$dbi->insert(\@params, table => 'book');

　大量のinsertを行うときのパフォーマンスの劣化は、DBIと比較して小さな劣化に収まります。

　idオプションが使用できないといことが、複数insertにおける制限です。またフィルタ、タイプルール、バインドタイプのオプションのすべてがない場合には、insertはさらに高速化されます。タイプルールがすでに設定されている場合は、type_rule_offに1に設定することで、タイプルールを無効にすることができます。

　パラメーターの最初のハッシュの値によって、生成されるSQLが決定するということに注意してください。つまり、残りのパラメータには、最初のパラメータと同じ列が含まれている必要があるということに、注意してください。

　ベンチマークスクリプトとベンチマークの結果です。データベースにはSQLiteを使用しました。

use strict;
use warnings;
use Benchmark qw/timethese cmpthese/;

use DBIx::Custom;
my $dbi = DBIx::Custom->connect(
  dsn => 'dbi:SQLite:dbname=:memory:',
);

$dbi->execute("create table book (id, title)");

# 性能の比較
my $result = timethese(100, {
  # 通常
  normal => sub {
    for my $i (1 .. 100) {
      my $param = {id => $i, title => 'Perl'};
      $dbi->insert($param, table => 'book');
    }
  },
  # 複数まとめてインサート
  multiple => sub {
    my @params;
    for my $i (1 .. 100) {
      push @params, {id => $i, title => 'Perl'};
    }
    $dbi->insert(\@params, table => 'book');
  },
    # 生のDBIでステートメントハンドルを再利用
    raw => sub {
      my $sth;
      for my $i (1 .. 100) {
        $sth ||= $dbi->dbh->prepare('insert into book (id, title) values (?, ?)');
        my $id = $i;
        my $title = 'Perl';
        $sth->execute($i, $title);
      }
    }
});
cmpthese($result);

　ベンチマークの結果は、生のDBIを利用するものよりも、若干パフォーマンスが落ちる程度で収まっています。通常のinsertを使う場合に比較して、10倍近い差があるので、大量のインサートを同時に行う場合に、利用するとよいと思います。

           Rate   normal multiple      raw
normal   22.3/s       --     -90%     -92%
multiple  213/s     855%       --     -23%
raw       278/s    1147%      31%       --

バルクインサートで高速化

　データベースがバルクインサートをサポートしている場合は、バルクインサートを行うことができます。現在のところMySQLとPostgreSQLで利用することができます。データベースの機能を使って、複数のデータを一度に挿入できるので、さらなる高速化が期待できます。

$dbi->insert($params, table => 'book', bulk_insert => 1);

MySQLでのbulk_insertでのベンチマーク

　以下はベンチマークスクリプトです。

use strict;
use warnings;
use Benchmark qw/timethese cmpthese/;

use DBIx::Custom;

my $dbi = DBIx::Custom->connect(dsn => 'dbi:mysql:database=usertest',
user => 'root', connector => 1);

# 性能の比較
my $result = timethese(100, {
  normal => sub {
    $dbi->connector->txn(sub {
      $dbi->delete_all(table => 'book');
      for my $i (1 .. 100) {
        my $param = {id => $i, title => 'Perl'};
        $dbi->insert($param, table => 'book');
      }
    });
  },
  multiple => sub {
    $dbi->connector->txn(sub {
      $dbi->delete_all(table => 'book');
      my @params;
      for my $i (1 .. 100) {
        push @params, {id => $i, title => 'Perl'};
      }
      $dbi->insert(\@params, table => 'book');
    });
  },
  bulk_insert => sub {
    $dbi->connector->txn(sub {
      $dbi->delete_all(table => 'book');
      my @params;
      for my $i (1 .. 100) {
        push @params, {id => $i, title => 'Perl'};
      }
      $dbi->insert(\@params, table => 'book', bulk_insert => 1);
    });
  },
  raw => sub {
    $dbi->connector->txn(sub {
      $dbi->delete_all(table => 'book');
      my $sth;
      for my $i (1 .. 100) {
        $sth ||= $dbi->dbh->prepare('insert into book (id, title) values (?, ?)');
        my $id = $i;
        my $title = 'Perl';
        $sth->execute($i, $title);
      }
    });
  }
});

cmpthese($result);

　ベンチマークの結果で、bulk_insertを使うと、通常のinsertの通常の5倍くらいのパフォーマンスがでるようです。

              Rate      normal    multiple         raw bulk_insert
normal      24.0/s          --        -72%        -76%        -83%
multiple    86.2/s        259%          --        -16%        -40%
raw          102/s        326%         18%          --        -29%
bulk_insert  143/s        496%         66%         40%          --