へぼいいいわけ このページをアンテナに追加 RSSフィード

2008年03月21日

凄まじい量のテキストデータをデータベースに挿入しようとすると止まってしまう

止まってしまう

2ちゃんねるニュース速報板の書き込みデータ*1データベースMySQL)挿入しようとすると、どうしても途中で止まってしまう又は多重に挿入されてしまいます。確認しているだけでも8万スレッド以上400万書き込み以上のデータがあります。


スペックいろいろ

CPUSempron2800+
メモリ1GB
HDD200GB
PHP5.2.5
MySQL5.0.51a-tritonn

挿入するテーブル

CREATE TABLE thread(
id INT NOT NULL AUTO_INCREMENT,
saba VARCHAR(64),
ita VARCHAR(32),
number INT,
title VARCHAR(256),res SMALLINT NOT NULL,
PRIMARY KEY (id)
) DEFAULT CHARSET sjis ENGINE = MyISAM;

CREATE TABLE kakikomi(
id INT NOT NULL AUTO_INCREMENT,
thread_id INT NOT NULL,
no SMALLINT,
name VARCHAR(128),
mail VARCHAR(128),
time INT NOT NULL,
kakikomi_id VARCHAR(12),
be INT,
text TEXT,
PRIMARY KEY (id)
) DEFAULT CHARSET sjis ENGINE = MyISAM;

CREATE FULLTEXT INDEX thread_index USING MECAB ON thread(title);
CREATE FULLTEXT INDEX kakikomi_index USING MECAB, SECTIONALIZE ON kakikomi(text, name, mail, kakikomi_id);

tritonnを使い全文検索を行うためのindexも作成しています。


試した方法

PHPで1行づつ

まず最初はPHPディレクトリに格納されているデータを順次読み込みながら1行づつデータベースに挿入してみました。すると100万行ほど挿入したところで止まってしまいます。何度実行しても同じ結果です。さらにスクリプト止まるまで1日近くかかる上にtopコマンドで監視してみるとロードアベレージ10をキープしています。vmstatコマンドで調べてIOの部分で凄まじく負荷がかかっていることが分かりました。分かっただけでも良い収穫でした。


LOAD DATA INFILEの巻

次はLOAD DATA INFILE*2というMySQLの機能?を使って挿入してみることにしました。これならMySQLだけで行うことができ最初にファイルを作成して後は挿入するだけという状態にするので、なんとなくIO負荷が少ないイメージがあります。これを行うためには挿入するデータをTSV形式のデータファイルに変換してそれを使うのですが、その作業をPHPでthreadテーブルにデータを挿入しながら行ったところ、ファイルサイズが2GBを超えたところでスクリプトが止まってしまいました。どうやらPHPで生成するファイルサイズに制限がかかっているようなんですが、php.iniを見てもそれらしい記述はなくググっても対処法が書いてあるサイトが無いので生成するファイルを複数のファイルに分散させることにしました。5000スレッドごとに区切ったら20個近いファイルが生成されました。

LOAD DATA CONCURRENT LOCAL INFILE './data.txt' INTO TABLE kakikomi;

こんな感じで挿入を行ったのですが1つのファイル当たり1日近くもかかる上に、その数が多いので途中で挿入するのが面倒になりやめました。ちなみに処理実行中のロードアベレージは5から7くらいです。


またPHPで1行づつ

LOAD DATA INFILEが面倒になり今度はまたPHPで1行づつ挿入することにしました。前々回の失敗を踏まえ一気にやろうとはせず1回の処理数に300スレッドづつという制限を設けました。実行してみると1回の処理が大体30〜40分で終わるので、cronで1時間間隔で実行するようにし放置しました。3日ほどたったころ様子を見てみると止まることなく動き続けているようで、挿入スレッド数は3万を超え挿入書き込み数は300万近くまでいっていました。これは順調だなと思いtopコマンドで実行プロセスを見てみると、恐ろしいことに30〜40分で終了していたはずのスクリプトが10,20と起動しています。あわてて止めても多重挿入された*3データは凄まじい量で無知な自分が重複データを消すこともできず、さっさとあきらめました。


次の方法は・・・

実は現時点ではまだ挿入できていません。LOAD DATA INFILEで何日もかけて行うのが結局一番楽なような気がしてたのでそうする予定です。もっと良い超高速な方法があったら誰か教えてください。

試した方法には書いていませんが、新規取得したデータをデータベースに挿入する作業も並行して行っています。これが結構負荷を出してるのでこっちの見直しも必要なんでしょうね。


何とか手っ取り早く挿入できないかなぁ。

参考になったサイト

Tritonnプロジェクト - Tritonnプロジェクト 〜 MySQL+Sennaによる全文検索 〜

LOAD DATA INFILE 構文でデータのインポート! SQL講座 -Smart

CSVファイルのインポートについて - 教えて!goo

MySQLについて 四

*1:2007年8月頃からほぼ全ての書き込み

*2:テキストファイルをデータベースに挿入する命令。速いらしい。

*3スクリプトが多重実行される想定をしていなかったので・・・^^;