たけまるの日記

たけまるの日記です。web関係の技術ネタが多いですが、好きなことを適当に書いています。

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してはいけない。

参考

https://hiroakis.com/blog/2013/08/22/mysql-%E3%81%AA%E3%81%9Cibdata1%E3%81%8C%E8%82%A5%E5%A4%A7%E5%8C%96%E3%81%99%E3%82%8B%E7%90%86%E7%94%B1%E8%A8%98%E4%BA%8B%E3%81%AE%E6%84%8F%E8%A8%B3/