結合戦略を変更して、PostgreSQL データベースのパフォーマンスを向上する
監査ログに多数のエントリがある Automation for Jira のインスタンスでは、Automation for Jira 7.3 にアップグレード後にパフォーマンスの問題が発生する可能性があることがわかっています。この問題は PostgreSQL データベースでのみ発生します。
コンテキスト
パフォーマンスの問題は、Automation for Jira 7.3 のデータ型の変更によって発生するマージ結合戦略ではなく、ハッシュ結合戦略を代用するクエリによって生じます。ハッシュ テーブルが大きすぎて作業メモリの制限のためにメモリにロードできない場合は、データベースがそれらを一時ディスク ファイルに書き込みます。これによって DB Write IOPS が急上昇して他のクエリの速度が低下し、自動化ルールに影響します。
追加情報
調査
この問題を確認するには、データベースでどの結合戦略が使用されているかをチェックします。
データベースで以下のクエリを実行します。
explain analyse select "AO_589059_AUDIT_ITEM_COMP_CGE"."AUDIT_ITEM_ID", "AO_589059_AUDIT_ITEM_COMP_CGE"."COMPONENT_NAME_KEY", "AO_589059_AUDIT_ITEM_COMP_CGE"."COMPONENT", "AO_589059_AUDIT_ITEM_COMP_CGE"."COMPONENT_ID", "AO_589059_AUDIT_ITEM_COMP_CGE"."DURATION", "AO_589059_AUDIT_ITEM_COMP_CGE"."ID", "AO_589059_AUDIT_ITEM_COMP_CGE"."OPTIMISED_IDS", "AO_589059_AUDIT_ITEM_COMP_CGE"."START_TIME", "AO_589059_AUDIT_ITEM_CGE_ITEM"."AUDIT_ITEM_COMPONENT_CHANGE_ID", "AO_589059_AUDIT_ITEM_CGE_ITEM"."AUDIT_ITEM_ID", "AO_589059_AUDIT_ITEM_CGE_ITEM"."CHANGE_FROM", "AO_589059_AUDIT_ITEM_CGE_ITEM"."CHANGE_TO", "AO_589059_AUDIT_ITEM_CGE_ITEM"."FIELD_NAME", "AO_589059_AUDIT_ITEM_CGE_ITEM"."ID", "AO_589059_AUDIT_ITEM_CGE_ITEM"."MESSAGE" from "public"."AO_589059_AUDIT_ITEM_COMP_CGE" "AO_589059_AUDIT_ITEM_COMP_CGE" left join "public"."AO_589059_AUDIT_ITEM_CGE_ITEM" "AO_589059_AUDIT_ITEM_CGE_ITEM" on "AO_589059_AUDIT_ITEM_COMP_CGE"."ID" = "AO_589059_AUDIT_ITEM_CGE_ITEM"."AUDIT_ITEM_COMPONENT_CHANGE_ID" where "AO_589059_AUDIT_ITEM_COMP_CGE"."AUDIT_ITEM_ID" = 10;
結果
戦略が次のとおりである場合
merge join: 正しい戦略を使用しています。パフォーマンスに不満がある場合は、次の「その他のステップ」をご確認ください。hash join: 戦略を変更する必要があります。次の「解決策」のステップに従ってください。nested loop join: 戦略を変更する必要があります。次の「解決策」のステップをご参照ください。
結合戦略に関するその他の詳細は、PostgreSQL のドキュメントに記載されています。
ソリューション
戦略を merge join に変更するには、次のコマンドを実行して問題のあるデータベース テーブルの統計を収集します。これらの統計に基づいて、クエリ プランナーは最も効率的な戦略を決定してそれを変更します。
-
ANALYZE VERBOSE "AO_589059_AUDIT_ITEM_COMP_CGE";を実行します ANALYZE VERBOSE "AO_589059_AUDIT_ITEM_CGE_ITEM";を実行します- [調査] に示されたクエリを実行して、戦略が変更されたことを確認します。
- DB Write IOPS が低下して、パフォーマンスが回復したことを確認します。さらに、次の「その他のステップ」セクションでステップを確認できます。
その他のステップ
作業メモリの増加
パフォーマンスが向上していない場合は、作業メモリを増加できます。これによってデータベースはメモリをさらに読み込めるようになり、テーブルを一時ディスク ファイルに書き込み不要になります。この例では 16MB に設定していますが、さらに増やして結果を監視する必要が生じる場合があります。
SET work_mem = '16MB';データベース サーバーの容量を超えてメモリを増加しないでください。複数のクエリが同時に実行されて、それぞれが許容される 16MB のメモリを使用することにご注意ください。このため、メモリ使用量が指数関数的に増加する可能性があります。