■

先日のDBインポートツール（http://d.hatena.ne.jp/komamitsu/20091126/1259244177）は、使ってみるとかなり遅かったのでした。過去9年間の全銘柄を入れる場合、半日掛かりそうなので、今後のメンテを考えると何か一工夫必要。

今回のボトルネックは明らかにしょぼしょぼDBサーバー（Celeron 500MHz）なので、INSERTを速くする方法を探してみました。MyISAMにしても体感的にそれ程速くならないし、INDEXを外してのINSERTも微々たる改善。LOAD DATA INFILEは速そうだけど下準備が面倒。

で、見つけたのがこれです => http://dev.mysql.com/doc/refman/5.1/ja/insert-speed.html

1 つのクライアントから同時に多数の行を挿入する場合は、
マルチプルVALUESリストでINSERTステートメントを使用します。
これで独立した INSERT  ステートメントの使用時と
比較して大幅に（場合によっては数倍）速度が上がります。

こんな感じですね。

INSERT INTO hoges (a, b, c) VALUES (1, 2, 3), (4, 5, 6), ....

半信半疑で使ってみたところ、数十分でインポートが完了したので愕然としました。個人的に今年最も衝撃を受けた技術です。

処理をシンプルにしたサンプルコードで、マルチプルINSERTの性能を見てみました。

open Printf

module Insert =
  struct
    type t = {db: Mysql.dbd; buf: Buffer.t; table: string; attrs: string}

    let max_buf_size = 51200

    let create db table attrs =
      let buf = Buffer.create max_buf_size in
      {db = db; buf = buf; table = table; attrs = attrs}

    let flush t =
      ignore (Mysql.exec t.db (Buffer.contents t.buf));
      Buffer.reset t.buf

    let add t vals =
      if (Buffer.length t.buf) + (String.length vals) + 3 >= max_buf_size
      then flush t;

      if Buffer.length t.buf = 0 then
        Buffer.add_string t.buf
          (sprintf "INSERT INTO %s (%s) VALUES" t.table t.attrs)
      else
        Buffer.add_string t.buf ",";
      Buffer.add_string t.buf (sprintf "(%s)" vals)
  end

let time f label =
  let start_time = Unix.gettimeofday () in
  f ();
  let end_time = Unix.gettimeofday () in
  printf "%s: %f sec\n" label (end_time -. start_time)

let _ =
  let mode = ref "" in
  Arg.parse [] (fun s -> mode := s)
    (sprintf "Usage: %s [normal|multi_insert]" Sys.executable_name);
  let db = Mysql.quick_connect ~host:"hoge" ~port:3306 ~user:"hoge"
             ~password:"hoge" ~database:"hoge" ()
  in
  let exec str =
    ignore (Mysql.exec db str)
  in
  let loop =
    match !mode with
    | "normal" ->
      let rec loop i n =
        if i < n then (
          exec (sprintf
            "INSERT INTO test (id, point, description)  VALUES (%d, 123456789, 'AAAAAAAAAABBBBBBBBBBCCCCCCCCCC')"
            i);
          loop (i + 1) n
        )
      in
      loop
    | "multi_insert" ->
      let ins = Insert.create db "test" "id, point, description" in
      let rec loop i n =
        if i < n then (
          Insert.add ins
            (sprintf "%d, 123456789, 'AAAAAAAAAABBBBBBBBBBCCCCCCCCCC'" i);
          loop (i + 1) n
        )
        else Insert.flush ins
      in
      loop
    | _ -> invalid_arg !mode
  in
  exec "START TRANSACTION";
  time (fun () -> loop 0 50000) !mode;
  exec "COMMIT"

MyISAMでの実験結果は

komamitsu@potato:~/lab/ocaml/hogedb$ ./hogedbx multi_insert
multi_insert: 1.853271 sec
komamitsu@potato:~/lab/ocaml/hogedb$ ./hogedb multi_insert
multi_insert: 4.523005 sec
komamitsu@potato:~/lab/ocaml/hogedb$ ./hogedbx normal
normal: 50.512910 sec
komamitsu@potato:~/lab/ocaml/hogedb$ ./hogedb normal
normal: 56.136061 sec

ここでのhogedbxはネイティブコード、hogedbはバイトコードです。

InnoDBでは

komamitsu@potato:~/lab/ocaml/hogedb$ ./hogedbx multi_insert
multi_insert: 5.633428 sec
komamitsu@potato:~/lab/ocaml/hogedb$ ./hogedb multi_insert
multi_insert: 11.821954 sec
komamitsu@potato:~/lab/ocaml/hogedb$ ./hogedbx normal
normal: 61.342647 sec
komamitsu@potato:~/lab/ocaml/hogedb$ ./hogedb normal
normal: 76.224563 sec

となり、

• マルチプルINSERTは通常のやつより最大20倍以上速いときがある（特に最速の組み合わせであるMyISAM & OCamlネイティブコードで顕著）
• INSERTに関してはInnoDBよりMyISAMの方が全体的に速い
• DBがボトルネックっぽいけど、それでもやはりバイトコード版よりもネイティブコードの方が速い

みたいな感じです。

マルチプルINSERTはすげえ速いけれども、残念ながらMySQLだけでしか使えないみたいですねぇ。一昔前の仕事で5000万件超のデータをPostgreSQLにインポートすることになって苦労していたのですが、もしこの機能がPostgreSQLにも存在すれば楽勝だったなぁ、としみじみ。