PostgreSQLで大量データを扱う際にあったことをまとめます。
まず前提ですが、１つのヘッダー情報に対して数百個の明細情報を持つもの更新するとします。この情報を別DBへ連携する処理を行うものとします。
実際に行った処理は以下のとおりです。
１．ヘッダーデータを取得
２．ヘッダーデータを別DBに追加
すでにある場合には更新
３．明細データを追加
４．明細データを１行ずつ別DBに追加
すでにある場合には更新
１から３は特別問題なく実行されました。
ここで、問題となったのは４の追加更新の処理です。
この更新が１時間たっても終わらないという事象が起きました。
また、更新先のDBのあるサーバーの容量が急激に減少していました。
まず更新元のデータの取得に問題があるか、更新先のDBのどちらに問題があるか確認を行いました。
①更新元データ
こちらはヘッダーから明細への関連については問題なく、明細数分のデータを取得していることがわかりました。800件程度のレコードを取得して、連携していました。
数は多いですが、DBの更新ということでは問題のない件数だと思います。
なので、問題はないと判断しました。
②更新先DB
実行時のDBの状況を確認しました。
[問題点]
１．オートバキュームが更新先ではないテーブルに対して行われている
２．DBのログにチェックポイントが大量に発行されていると出力
なぜ別テーブルのオートバキュームの実行やチェックポイントが発行されるのかを調査を行うと、更新先のテーブルにとあるトリガーが問題を引き起こしていることが分かりました。
内容は更新対象のテーブルに追加更新があると、ヘッダーの情報を含めて統合した内容を今回の更新先ではないテーブル追加するという内容でした。
このトリガーの実行計画を取得すると１行追加するだけに２秒かかっていることがわかりました。
800行の更新を行うと、1600秒かかる事になります。単純計算で20分以上かかることになります。
ここに、オートバキュームが重なると更に実行時刻が遅くなり、実行時間が１時間以上かかる状態になっていました。
では、このトリガーがなぜ２秒以上かかっているかということですが、処理自体に問題がありました。
追加更新が実行されると、対象データのヘッダーに関連するすべての明細データを取得し集計した結果を取得した件数分別テーブルへ追加または更新を行うようになっておりました。
実際のデータから考えると800件のデータの更新がある場合、最大で800件分の更新が800回行われることになります。
PostgreSQLの特徴の一つに、更新を行うと元データに無効のフラグを設定し新しくデータを追加するという動きを行っています。なので800件✖800回なので640000件分追加が実行される事になります。さらに800件✖799回分の更新は無効データになります。
これによって、大量の無効データに対ししてオートバキュームが実行され、さらに更新件数が大量にあるということでチェックポイントが大量に作成されている状態になっていました。
[対応策]
・トリガーの停止
・別テーブルへの更新をプログラムで行い、明細の件数だけ追加更新するように変更
[今回の教訓]
・更新処理は面倒でも余計なレコードへの更新を行わないようにする
テストで問題なくても大量データになると問題になる場合があるので、更新処理は必要最低限の更新にしましょう。なおして実行すると、800件分の更新が全体で3秒程度で行えるようになりました。