MD5の変換/逆変換を行うサイトを当ブログのドメイン配下に引っ越しました。サイト名を「MD5の変換/逆変換を行うサイト」から「MD5Decrypter62」に変更しました。
また、ローカルで作成していた36進数版についても「MD5Decrypter36」というサイト名で公開しました。いずれも当ブログのヘッダーリンクから遷移することが可能となっています。
LOAD DATA INFILEによるインサート処理の実装
36進数版のサイトはDBを256個、テーブルを256個に分けたことが原因で最大で65536回のインサートクエリが必要という状態になっています。
あらかじめ各テーブルにインサートするデータをCSV形式でまとめておき、CSVをもとにデータ登録を行うことができるLOAD DATA INFILE構文を利用して結果としてデータ登録にかかる時間を短縮するという方針でプログラムを作成してみました。
CSVを作成するにあたってはCSVの書き出しにfputcsvとfile_put_contentsのどちらを使ったほうが効率が良いのかの検討も行ってみました。
CSVを作成するプログラム（fputcsv）
プログラムは以下のような感じ。
// CSV書き出し用のデータ配列
$insert_data = [];
// 引数で指定した件数分処理
$tmp_register_count = 0;
$end_count = $start_count + $insert_count;
for ($i = $start_count; $i < $end_count; $i++) {
$md5_before = dec_to_b36($i);
$md5_after = md5($md5_before);
$database_name = MD5_DB_PREFIX . substr($md5_after, 0, MD5_DB_SUFFIX_NUM);
$table_name = "md5_" . substr($md5_after, MD5_DB_SUFFIX_NUM, MD5_TABLE_SUFFIX_NUM);
$md5_after = substr($md5_after, (MD5_DB_SUFFIX_NUM + MD5_TABLE_SUFFIX_NUM));
// CSV用の配列データに格納
if (!array_key_exists($database_name, $insert_data)) {
$insert_data[$database_name] = [];
}
if (!array_key_exists($table_name, $insert_data[$database_name])) {
$insert_data[$database_name][$table_name] = [];
}
$insert_data[$database_name][$table_name][$md5_before] = $md5_after;
// CSV登録数をインクリメント
$tmp_register_count++;
}
// CSVファイルに書き出し
if (!empty($insert_data)) {
foreach ($insert_data as $database_name => $table_data) {
foreach ($table_data as $table_name => $record_data) {
$fp = fopen(CSV_DIR . $database_name . "/" . $table_name . ".csv", "a");
foreach ($record_data as $md5_before => $md5_after) {
fputcsv($fp, [$md5_before, $md5_after]);
}
fclose($fp);
}
}
}fputcsvは1行辺りのデータを書き出す関数のためforeachのループ毎に呼び出す必要があります。
CSVを作成するプログラム（file_put_contents）
プログラムは以下のような感じ。
// CSV書き出し用のデータ配列
$insert_data = [];
// 引数で指定した件数分処理
$tmp_register_count = 0;
$end_count = $start_count + $insert_count;
for ($i = $start_count; $i < $end_count; $i++) {
$md5_before = dec_to_b36($i);
$md5_after = md5($md5_before);
$database_name = MD5_DB_PREFIX . substr($md5_after, 0, MD5_DB_SUFFIX_NUM);
$table_name = "md5_" . substr($md5_after, MD5_DB_SUFFIX_NUM, MD5_TABLE_SUFFIX_NUM);
$md5_after = substr($md5_after, (MD5_DB_SUFFIX_NUM + MD5_TABLE_SUFFIX_NUM));
// CSV用の配列データに格納
if (!array_key_exists($database_name, $insert_data)) {
$insert_data[$database_name] = [];
}
if (!array_key_exists($table_name, $insert_data[$database_name])) {
$insert_data[$database_name][$table_name] = "";
}
$insert_data[$database_name][$table_name] .= $md5_before . "," . $md5_after . PHP_EOL;
// CSV登録数をインクリメント
$tmp_register_count++;
}
// CSVファイルに書き出し
if (!empty($insert_data)) {
foreach ($insert_data as $database_name => $table_data) {
foreach ($table_data as $table_name => $csv_data) {
file_put_contents(CSV_DIR . $database_name . "/" . $table_name . ".csv", $csv_data, FILE_APPEND);
}
}
}file_put_contentsの場合は書き込みたい文字列をあらかじめ用意しておくだけです。追記のためにFILE_APPENDオプションを指定する必要があります。
単純なCSVなのでできることとも言える。
2種類のCSV書き出し用のプログラムの比較を行う
さくらのVPS内に設置した36進数版サイトについて2種類のCSV書き出し用のプログラムを実行して処理時間や最大使用メモリの比較を行いました。以下のような感じでflockによる排他制御を入れ、それぞれ100万レコード分のCSVデータを書き出すような感じで実行しました。
*/5 * * * * flock -n /tmp/batch_create_data4_csv.lock php /~~~~/batch_create_data4_csv_file_put_contents.php 1000000 >> /root/batch_create_data4_csv_file_put_contents.log #*/5 * * * * flock -n /tmp/batch_create_data4_csv.lock php /~~~~/batch_create_data4_csv_fputcsv.php 1000000 >> /root/batch_create_data4_csv_fputcsv.log
↓fputcsvの結果
↓file_put_contentsの結果
file_put_contentsの方が最大使用メモリが100MBほど少ないという結果となりました。fputcsvの方はMD5ハッシュ化の前後の文字列を配列で持っているからその辺の差なのかも。
処理時間についてはどちらの場合も一定ではなく70秒～220秒程度の間でした。これは稼働させてるさくらVPSのメモリが1Gのプランであったり、WordPressや他のプログラムなどを動かしている環境ということもあるためしょうがなさそう。ローカル環境で検証してた時の処理時間はどちらも同じくらいでした。
LOAD DATA INFILEによる取り込みを行うプログラム
プログラムは以下のような感じ。
// PHPのメモリ上限を増やす
ini_set('memory_limit', PHP_MEMORY_LIMIT_FOR_BATCH);
// データインサート用のコネクションを作成
$mysqli_conn = create_mysqli_connection();
if ($mysqli_conn->connect_error) {
throw new Exception("mysqli connect failed.");
}
// トランザクション開始
$mysqli_conn->begin_transaction();
// LOAD DATA INFILE処理
$insert_execute_count = 0;
$database_total_count = pow(16, MD5_DB_SUFFIX_NUM);
$table_total_count = pow(16, MD5_TABLE_SUFFIX_NUM);
for ($i = 0; $i < $database_total_count; $i++) {
$database_name = MD5_DB_PREFIX . str_pad(dechex($i), MD5_DB_SUFFIX_NUM, 0, STR_PAD_LEFT);
$mysqli_conn->select_db($database_name);
for ($j = 0; $j < $table_total_count; $j++) {
$table_name = "md5_" . str_pad(dechex($j), MD5_TABLE_SUFFIX_NUM, 0, STR_PAD_LEFT);
if (file_exists(CSV_DIR . $database_name . "/" . $table_name . ".csv")) {
$query = "LOAD DATA INFILE '" . CSV_DIR . $database_name . "/{$table_name}.csv' INTO TABLE {$table_name} FIELDS TERMINATED BY ',' (md5_key, md5_hash_remain)";
if ($mysqli_conn->query($query) !== TRUE) {
throw new Exception("insert error.\nquery : " . $query);
}
$insert_execute_count++;
// 処理済みのCSVを空にする
file_put_contents(CSV_DIR . $database_name . "/" . $table_name . ".csv", "");
}
}
}
// カウンタを更新
$wpdb->update("md5_counter", ['count' => $csv_temp_count], ['id' => 1]);
// DBをコミット
$wpdb->query('COMMIT');
// mysqliコネクションをコミットして閉じる
$mysqli_conn->commit();
$mysqli_conn->close();256個のDBの256個のテーブルに対応するCSVファイルが存在したらLOAD DATA INFILEによる取り込み処理が走るようなイメージです。
MD5のカウンターについてはDBに登録済みの件数を管理するcountカラムとは別に登録前のCSVデータを含めた件数を管理するcsv_temp_countカラムを用意しました。LOAD DATA INFILEによる登録件数はCSVファイルを開いて行数を取得するといったことを行わないと取得できないため今回は取り込み処理が正常終了した際にcsv_temp_countの値でcountを更新することにしました。
なお、処理済みのCSVを空にする処理はMySQL外の処理のため例外が発生したときのロールバックには当然含まれません。そのため、このプログラムを実行する前にcsvの親ディレクトリをtarで圧縮するようにしています。
LOAD DATA INFILEの動作確認を行う
ローカル環境で事前にLOAD DATA INFILEの動作確認を行っていた際、以下のようなエラーが出ました。
secure-file-privはMySQLのDBに対して、CSVの入出力を行う際のベースとなるディレクトリを設定しておくパラメータの様です。空にすることで無効化できるとのこと。
参考：MySQLでCSVファイルを使って結果出力やデータ入力を行う方法 | サービス | プロエンジニア
secure-file-privの値を空にしてMySQL5.7のサービスを再起動しました。
改めてLOAD DATA INFILEのクエリを実行してみたところ今度は以下のような表示となってしまいました。
エラーメッセージを確認したところバックスラッシュが消えて存在しないパスを参照してたり、MySQLのデータディレクトリが指定したパスにくっついたりしている。。
原因の究明はやってませんがプログラム側でパスをバックスラッシュからスラッシュに置換したところうまくCSVデータが取り込めるようになりました。
file_put_contentsとfputcsvで60万レコード分のデータを作成した状態でCSVを取り込むプログラムを実行してみました。インサートクエリの発行回数を画面に出力するようにしてみました。
取り込みに掛かった時間は約1200秒という結果となりました。前回の記事の後半の書いたバルクインサート版の処理は10万件で約1000秒、30万件で約1200秒だったので少しだけ早くなったようなあまり変わっていないような微妙な結果に。。（＾＾；
これは、60万レコードといえど1テーブル辺りに入るレコード数は10レコード程度の計算となってしまうための結果と考えられます。LOAD DATA INFILE版はCSVの作成と取り込み処理が別のプログラム分かれているため、あらかじめ何千万、何億という取り込み対象のデータを用意してもそこまで速度の低下が起こらないと考えられる。
そこで、次はローカルではなく当ブログを稼働させてるVPSサーバーで4桁目まで取り込みが完了しているDBに対して5桁目のデータを作成するという検証を実施してみました。
さくらのVPSで約6000万レコード分のデータを登録する
36進数版において4桁のデータが網羅されている状態とはすなわち36^4=1,679,616レコードのデータが登録されている状態となります。5桁の場合は指数が1個増えて36^5=60,466,176レコードとなります。
以下の画像は5桁のデータの作成が終わった後のMD5のカウンターテーブルになります。
登録に使うCSVについては36進数の5桁の末尾（zzzzz）のCSVデータがちゃんと入っていることを確認しました。CSVディレクトリのサイズをduコマンドで確認したところ2.3GBほどありました。
なお、バッチを実行する際、ApacheやTomcatなどサーバーの処理性能に影響を与えそうなサービスは一旦停止しちゃいました（＾ｑ＾
LOAD DATA INFILEを実行するために必要な権限
データ登録のバッチを実行したところLOAD DATA INFILEのクエリ部分でエラーが発生してしまいました。ローカル環境ではrootユーザーで動かしていましたが、本番環境では実行したユーザーの権限が不足していました。
調べたところLOAD DATA INFILEはFILEという権限を有効にする必要があるとのことなので以下のSQLを実行しました。日本語のリファレンスはMySQL5.6のものしか見つからなかったですが、書いてあることは一緒の模様。
参考：MySQL :: MySQL 5.7 Reference Manual :: 6.2.2 Privileges Provided by MySQL
参考：MySQL :: MySQL 5.6 リファレンスマニュアル :: 6.2.1 MySQL で提供される権限
GRANT FILE ON *.* TO '[mysql_user]'@'localhost';
改めて6000万レコードのデータ登録を実施
FILE権限を付与した後再度バッチを実行したところ、今度は正常に処理が走りました。
以下は実行中のCSVディレクトリの様子です。処理済みのファイルが空で上書きされ0バイトになっていることがわかります。
以下が実行結果の出力になります。
58786560レコード取り込むのに約9000秒掛かりました。
これまでの結果を表にすると以下のようになります。
| 処理内容 | 実行環境 | 処理件数 | 処理時間 (sec) | insert / sec |
| 1行1インサート | ノートPC | 200000 | 3476 | 57.5 |
| バルクインサート | ノートPC | 300000 | 1186 | 252.9 |
| LOAD DATA INFILE | ノートPC | 600000 | 1212 | 495.0 |
| LOAD DATA INFILE | さくらVPS | 58786560 | 9013 | 6,522.4 |
ここにきてだいぶ改善が見られる結果が取得できました。
インサートの処理時間はそれほど増えないものと予想してたのですが実際は増えてました。。（＾ｑ＾
サイトの動作確認
停止していたApache等を再起動した後本番環境で動作確認を行いました。
MD5逆変換 | MD5Decrypter36
メモ
- ノートPC（i7-8550U）の処理結果が遅いのは実行中のCPUの温度が上がりすぎなことが考えられる
- HWMonitorで確認したら80℃とかになってた
- デスクトップPC（i7-4770K）で50万件のバルクインサートを行ったらノートPCの半分くらいの処理時間だった（＾ｑ＾
- レンタル中のVPSサーバーはHDDプランのため、SSDプランで実行したら処理時間が短くなるかも