これは エムスリー Advent Calendar 2022 17 日目の記事です。
こんにちは、基盤開発チームの高島(id:rst76)です。 有名なボクサー、モハメド・アリの言葉に "It isn’t the mountains ahead to climb that wear you out; it’s the pebble in your shoe." というものがあるそうです。山に登るから疲れるのではなく靴の中に小石があるから疲れるんだ、という意味ですね。今日はそんな小石を取り除いた話をしたいと思います。
前提
多くのシステムには古いデータを削除する機能が備わっていると思います。エムスリーで運用している認証システムの 1 つにもありました。これは 1 日 1 回 AWS Lambda で実行しており、下図のような構成のテーブルに対して、子テーブル B, C のデータを削除してから親テーブル A のデータを削除していました。
実際の SQL は以下のようなものです。特に凝ったことはしていません。
DELETE FROM C WHERE xxx; DELETE FROM B WHERE xxx; DELETE FROM A WHERE NOT EXISTS (SELECT 1 FROM B WHERE B.a_id = A.id) AND NOT EXISTS (SELECT 1 FROM C WHERE C.a_id = A.id);
課題
この機能、ほとんどの場合は問題なく動作するのですが、削除対象のデータが多いと時間がかかり、 AWS Lambda のタイムアウトに引っかかって失敗することがありました(1 年に 1 回くらい)。 そうすると削除処理はロールバックされて、翌日以降も失敗が続いてしまうので、手動でデータを削除する必要があります。いくらコピー&ペーストで済むとはいえ、本番のデータを手動で削除するというのは決して望ましい状態ではありません。 原因を調査して根本解決しようと試みました。
遅くなるのは 3 つ目の SQL で、これは削除対象のデータが 15000 件くらいあると 15 分ほどかかってしまいます。
けれども実行計画を見てもそれほどコストは高くありませんし、 DELETE
を SELECT
に変えて実行すると 15 秒くらいで結果が返ります。
なぜ削除のときだけ遅くなるのかさっぱり分からず、再発しないのをいいことに 1 年ほど放置していました。
原因
先日、また同様のタイムアウトが発生したので、今度こそと思い調査したのですが、やはり原因が分からず、最近エムスリーに参画したメンバーに相談しました。すると子テーブル B, C の外部キー列 a_id
にインデックスが張られていないのが原因ではないかという意見をもらいました。
PostgreSQL だと、外部キー制約をつけても参照側のテーブルにインデックスは作成されないのだそうです。
さらに、似たような環境を準備して再現実験までしてもらいました。
ただインデックスをつけても、削除 SQL のコストは実行計画上あまり変わりません。 ではなぜ、これだけ性能が改善されたのかというと、原因は外部キー制約にありました。 この処理では、子テーブルから参照されていないデータのみを親テーブルから削除します。 けれども外部キー制約がある以上、子テーブルから参照されているかどうかに関わらず、親テーブルのデータを削除するたびに子テーブルを(フル)スキャンしてチェックする必要があります。 それはインデックスがないと遅くなりますね……。 外部キー制約によるチェックのコストは実行計画に現れないので、私の頭から完全に抜け落ちてしまっていたのでした。
まとめ
というわけで、長らく気になっていた小石を、メンバーに相談して取り除いた話をしました。これで山登りに集中できます!
エムスリーでは、小さな課題から大きなアーキテクチャまで、いろいろ相談できるメンバーを募集しています。ぜひぜひご応募ください!