1クエリで一度に大量のレコードをupdateしてはいけない
2億レコードくらい入ってるテーブルにこれ流したらディスク逼迫して死んだ。
mysql> UPDATE table SET col = NULL;
数時間後、繋がってるサービスの画面が「500 Internal Server Error」。 みたらディスク空きが0になってた。
なにがでかくなってたかというと
# ls -alh /var/lib/mysql (抜粋) -rw-rw---- 1 mysql mysql 18G Jan 8 16:58 ibdata1
ちなみにinnodb_file_per_tableは有効(デフォルト)である。 要はテーブルの実データはibdata1には入ってない。 のにこの容量である。
というわけで調べたら、トランザクションの一貫性のため、ibdata1にはUNDOログ(更新前データのスナップショット)が書き込まれるとの事。1トランザクションで全レコードを更新しようとしたため、2億レコード分のスナップショットを作るはめになったらしい。ちなみにこのファイル、一度でかくなったら小さくなることはない。つまり詰んだ。
おかげでリカバリために多くの労力を費やすはめになったのであった。
もう一度言う。
1クエリで一度に大量のレコードをupdateしてはいけない。