コードロード

エラー討伐

【PHP】cronで1億件のデータを移行

1億件のIoT計測データのデータ移行を行った話。

cronでやることになった背景

cronでデータ移行することになった背景はこんな感じ。

カラム名は仮です。

  1. 年ごとの計測テーブルBに保存されている、ある期間の数種類の製品の計測データを、四半期ごとの計測テーブルA-Xテーブルに移行したい(1〜3月の計測データならA-1、4〜6月ならA-2、7〜9月ならA-3、10〜12月ならA-4)。
  2. 移行時に気を付ける点は下記だった。
    1. Bテーブルの製品番号( product_no)と計測日時(datetime )の組み合わせと、同じ組み合わせのレコード(つまり同じ計測データ)が既にAテーブルにも保存されている可能性があった。
    2. Aテーブル内では容量の関係で同じ計測データはなるべく重複しないようにする(事情により、 product_nodatetime の組み合わせで uniqueキーは貼られていない)。
    3. Cテーブルにも、Bテーブルの製品番号( product_no)と計測日時(datetime )の組み合わせが同じレコードが存在する場合がある。BテーブルからAテーブルへ移行する際、Cテーブルにも同じ組み合わせのレコードが存在する場合は、そのデータもAテーブルへ一緒に移行する。
  3. 上記のように、ちょっと複雑な条件だったので、dumpファイルを作って一気に移行する作戦はできなかった。
  4. 結果、PHPでプログラムを書いて少しずつ移行するように確定。
  5. このとき、一気に全データを取得するようにするとメモリが尽きるし、タイムアウトで処理が途中で途切れてしまう懸念があったので、安全に処理が完了するように少しずつデータ移行することにした。流れは下記の通り。
    1. Bテーブルから条件に該当するデータをちょうどいい量SELECT
    2. aで取得したデータを1件ずつループ
      1. product_nodatetime が同じ組み合わせのレコードが、既にAテーブルに存在するか確認。
      2. あれば continue
      3. なければ、Cテーブルにも同じ組み合わせのレコードがないか確認して、あればSELECT
      4. BテーブルとCテーブルのデータをAテーブルへINSERT。
      5. 移行完了をログに吐き出す
    3. 繰り返し
  6. これを人の手で都度確認してると大変なので、cronを書いてやってしまおう!となった。

プログラムの実装

詳細は書かないが、頑張って実装。

レコード数が膨大なので、発行されるSQLが負荷のかからないSQLかどうかを、EXPLAINで確認しながら実装する。

  • EXPLAINの確認項目
項目 確認事項
type indexかALLじゃないか
rows 多すぎないか
Extra Using index condition
Using where
Using index
あたりか

処理の流れはざっとこんな感じ。

cronの設定

phpの場所の確認

$ which php
/usr/bin/php

現在設定されているcronの確認

$ crontab -l
crontab: no crontab for name

cronタブを vi で開いて、1時間おきにPHPプログラムを実行したかったので、下記のように記載して、 :wq で保存。

$ crontab -e

* */1 * * * /usr/bin/php /var/www/cron/migration_test.php

保存したら設定完了と出てくる。

crontab: installing new crontab

cronが実行されているか確認。

$ sudo tail -f cron

Apr 16 23:01:01 xxxx CROND[]: (xxxx) CMD (php /var/www/cron/migration.php)

ちゃんと実行されてるっぽい。

でもデータベースを確認すると、なぜか更新されていない。

ログを吐くようにしてみる。

再度 cronタブを開いて、ログを吐くように追記。

$ crontab -e

* */1 * * * /usr/bin/php /var/www/cron/migration_test.php >> /var/www/cron/migration.log 2>&1

これでもう一度cronを実行してみて、吐き出されたログを確認してみると、 fopen() でファイルを開こうとしている箇所で怒られていた。

failed to open stream: No such file or directory in /var/www/.....

ググってみると、cronで実行するプログラムでは相対パスではなくて絶対パスで指定する必要があるらしい。

絶対パスに修正して、再度cron実行!

これでちゃんと少しずつデータの移行が行われ始めました。

1億件なので時間はかかりますが、少しずつ。

参考

qiita.com

chusotsu-program.com

nippondanji.blogspot.com