Zabbixのダッシュボード表示が遅くて困った時の対処方法を検証してみた(PostgreSQL実行計画解析と対処編)

こんにちは。技術部の髙岡です。 前回のブログで、Zabbixのダッシュボードのeventsが遅くて表示されないことがあって、その原因は、実行されるクエリーが遅いためであることと、クエリーを早くする方法を結論だけ紹介しました。

遅いクエリー

今回は、その結論を導き出したプロセスを順を追って説明したいと思います。長々と説明する前に、これから説明するクエリーの課題と解決策のサマリーを書いておきます。

1. 2つのテーブルを結合する前のeventsテーブルの行絞り込みがインデックススキャン+フィルタ処理で実行されていた
→インデックスを追加して時間のかかるフィルタ処理を排除した
2. パラレルクエリーのワーカー毎の処理に時間がかかっていた
→ワーカー数を増やしてワーカー当たりの処理時間を短縮した

PostgreSQLのバージョンは11を使っています。

調査・分析結果

スロークエリーの出力

何はともあれ、クエリーが遅いと思った時は、実行計画の確認です。 そのための準備として、スロークエリーがログに出力されるように、PostgreSQLの設定を変更します。 以下は、postgresql.confのパラメータで、3秒以上かかるクエリーを出力する設定です。

log_min_duration_statement = 3s

ログファイルに出力されたスロークエリー

「期間: 10003.652 ミリ秒」と書いてあるので、10秒近くかかっていることがわかります。

テーブルとインデックスの確認

「events」テーブルと「event_recovery」テーブルの定義を確認してみます。どんなインデックスが張ってあるのかに注目します。

eventidへの主キー以外に、「events_1」と「events_2」のインデックスが張ってあることがわかります。

eventidへの主キー以外に、「event_recovery_1」と「event_recovery_2」のインデックスが張ってあることがわかります。

スロークエリーの実行計画の確認

次に、explainコマンドをanalyzeオプション付きで実行し、このクエリーの実行計画を確認します。

実行計画の見方は、以下の資料が参考になります。
「PostgreSQLクエリ実行の基礎知識 ~Explainを読み解こう~」

性能トラブル改善案

ボトルネックの特定と解決①

上記の実行計画の中で、どの処理が遅くなっているのかを確認するために注目する箇所は、「actual time」です。 実際の処理に要した時間がミリ秒単位で表示されています。実行計画がインデントのようになっている箇所を計画ノードと呼び、インデントの階層が下のノードから順番に処理されます。処理のフローを記載します。

実行計画ツリー

下の階層のノードから順番に「actual time」を見て、処理に時間がかかっているノードを探します。 図では黄色く塗ったノードが遅い処理です。実行計画の18行目に、6秒近くかかっているノードが見つかりました。

Parallel Index Scan using events_1 on events e (cost=0.56..11832.07 rows=844 width=28) (actual time=23.447..6306.476 rows=670 loops=3)

実行計画の18行目~21行目にこのノードの処理の詳細が記載されています。
このクエリーは、「events」テーブルと「event_recovery」テーブルをネストループで結合する処理ですが、結合する前に「events」テーブルの中で結合候補となる行をWHERE句の条件で絞り込んでいます。

WHERE e.source=’0′ AND e.object=’0′ AND e.objectid=’16821′ AND e.eventid=’4020491′ AND e.value=’1′

実行計画の該当するノードを見てみると、WHERE句の絞り込み処理は大きく2つの処理に分かれていることがわかります。
処理を簡略化して表現した図を記載します。

where条件の処理詳細

図中の②、③のフィルタ条件による除外処理はインデックススキャンだけの処理に比べて時間がかかるので、排除したい処理です。実行計画の中に書いてある「Rows Removed by Filter」の1599行をフィルタ条件によって除外しているのです。

そこで、「events_1」インデックスの定義を確認してみます。

“events_1” btree (source, object, objectid, clock)

「events_1」インデックスによるスキャンが対象とする列は、source列、object列、objectid列、clock列のみで、eventidとvalueは対象外です。
eventidとvalueがインデックスで定義されていないので、インデックススキャンの後でフィルタ条件による除外処理が実行されているのです。
フィルタ条件による除外処理「Filter」を排除するために、インデックススキャンだけで行を絞り込むことができるよう、eventidとvalueを含めたWHERE句の列が全て盛り込まれたIndexを作成してみます。

さて、同じクエリーを実行して早くなるかどうかを確認してみたいところですが、 その前にキャッシュの影響を排除するために、PostgreSQLの再起動とOSキャッシュのクリアを実行します。

余談になりますが、PostgreSQLは、DB自体のキャッシュ領域だけでなく、OSのファイルキャッシュも 積極的に活用するDBなので、2行目のOSキャッシュをクリアするコマンドを実行しています。

同じクエリーを実行してみます。

インデックス追加前は6秒近くかかっていた「events」テーブルの絞り込み処理が、インデックススキャンだけで絞り込むことができるようになった結果、18行目に書いてある通り3秒まで縮まりました。フィルタ条件による絞り込み処理「Filter」も消えました。

ボトルネックの特定と解決②

しかし、後で処理されるノードの中に遅い処理があるため、全体の実行結果は9秒くらいで、残念なことにインデックス追加による対処前と大して変わっていません。

ボトルネックとなっているのは、17行目のNested Loop処理です。「Workers Launched」で指定された2つのワーカープロセスが分担して処理を実行しています。 処理内容をもっと詳しく見てみるために、analayzeコマンドをverbose付きで実行してみました。

17、18行目に書いてある通り、各ワーカーの処理が9秒近くかかっているのです。 そうであるならば、もっとたくさんのワーカーに処理を分担してもらいましょう。
postgresql.confのパラメータを変更してワーカー数の上限を上げます。

max_worker_processes = 20 # (change requires restart)
max_parallel_workers_per_gather = 15 # taken from max_parallel_workers
max_parallel_workers = 15 # maximum number of max_worker_processes that

ワーカー毎に物理CPUが一つ必要なので、マシンのCPUの個数を20個に増やします。 次に、SELECT文の実行計画でパラレルクエリーが機能するようにevnetsテーブルとevent_recoveryテーブルのparallel workersの数を指定します。

再び、explainコマンドをanalzyzeとverboseオプション付きで実行して、より詳細な実行計画を確認してみます。

ワーカーが15個起動し、最も遅いワーカーでも3秒まで縮めることができました。以降のノードの処理時間はミリ秒レベルで遅い処理となっていませんので、クエリー全体の実行時間を3秒近くまで縮めることができました。
この間にvmstatを実行するとprocのb列の値が15、16となり、ディスクI/O待ちとなるワーカーが増えてしまいました。これ以上ワーカーを増やしても実行時間を縮める効果は少ないと思われます。

高性能のディスクに変更すれば、ディスクI/O待ちが減ってもっと早くなるのではないでしょうか。

所感

・パラレルクエリーは、PostgreSQLバージョン9.6以降で実装された機能です。個人的には、「困った時はパラレルクエリーで」と思うくらい、性能面では頼りになる機能だと思っています。諸事情によりRHEL7の同梱パッケージのPostgreSQL 9.2を使うことが多いのですが、このバージョンではパラレルクエリーが使えないのが残念です。
・Zabbix用のDBとして、特に理由がなければMariaDB、MySQLを選ぶことが多いと思いますが、監視データが増えてしまった時の性能トラブル時の対応策の選択肢を考慮すると、PostgreSQLの方が個人的には頼もしいと思っています。Zabbix監視用のDBに限らず、DBを使うシステム全般に言える話しですが。。例えば、MariaDB、MySQLは、テーブル毎に張ることができるインデックスは一つだけですし、パラレルクエリー機能も有りません。今回のような対策は、MariaDB、MySQLで実施することができないのです。

>> 雑誌等の執筆依頼を受付しております。
   ご希望の方はお気軽にお問い合わせください!

ご覧いただきありがとうございます! この投稿はお役に立ちましたか?

役に立った 役に立たなかった

0人がこの投稿は役に立ったと言っています。

コメント投稿

メールアドレスは表示されません。


*