TOP>企業情報>コラム>技術情報>はじめましてPostgreSQL! Vol.4

はじめましてPostgreSQL! Vol.4

はじめましてPostgreSQL!

PostgreSQLに使いやすいGUIツールやOracle Databaseの互換機能が追加されたPostgres Plus Advanced Serverについて解説します。

(全4回連載)

Vol.4 Postgres Plus Advanced Server(体験編2)


前回はPostgres Plus Advanced ServerのOracle Database互換性機能を中心にご説明しました。Postgres Plus Advanced Serverは、Oracle Databaseで培ったスキルを活用することができます。そのため、PostgeSQLの活用はハードルが高そうだと感じるユーザであっても、より簡単に採用できるRDBMSの選択肢を広げることができるのではないでしょうか。

企業内で幅広く活用されるRDBMSには、高い信頼性や可用性、性能が求められますが、運用管理の容易性や、性能問題への対処方法の多様性、性能問題調査の容易性も重要な要素となります。

今回は、Postgres Plus Advanced Serverで機能強化されているSQLヒント、Postgres Enterprise ManagerのIndex Advisor、DRITA (Dynamic Runtime Instrumentation and Tracing Architecture)といった、性能問題への対処方法の多様性と、性能問題調査の容易性を実現する機能をご紹介します。

SQLヒント


PostgreSQLやOracle DatabaseなどのRDBMSでは、アプリケーションから発行されたSQLを「解析」して「実行」しています。「解析」では、SQLの構文チェック、参照されているオブジェクトの存在チェックが行われた後、データにアクセスするための経路であるアクセスパスを決定しています。例えば、友人との待ち合わせの場所に行く場合、目的地まで、どの経路を使って、どんな交通手段で移動するのが一番効率的かを考えるのと似ています。代表的なアクセスパスとしては、テーブルを1行、1行読み取って、条件に合うデータを取り出す「全表スキャン」と、本の巻末の索引と同じく、テーブルの各列のデータとそのデータの格納位置を示すアドレス情報を持つ「索引」を利用した「索引スキャン」が存在します。また、目的地に行く場合も、交通状況や交通機関の遅延情報などを考慮して、目的地までのルートを探すように、RDBMSもテーブルに格納されている行数や、列に格納されている値の種類、値の分布などの統計情報と呼ばれる情報を利用して、表に対して最もコストの低いと考えられるアクセスパスを選択します。なお、この役割を果たす機能をオプティマイザやプランナと呼んでいます。

前置きが長くなりましたが、SQLヒントとは、オプティマイザにアクセスパスを明示的に指定する機能となります。

では、なぜ、オプティマイザにアクセスパスを明示的に指定する機能が付加されているのでしょうか。

これは、オプティマイザが選択するアクセスパスが必ずしも最短ルートではない可能性があるからです。Oracle Databaseなどの商用RDBMSでもSQLヒントは実装されており、実際に活用されています。オプティマイザがアクセスパスを選定する際は、テーブルの統計情報と、SQLの条件値から、どのくらいのデータにアクセスするのかを評価し、全表スキャンを選択するのか、索引スキャンであれば、どの索引を利用するのか、テーブルを結合するのであれば、どのような順番でテーブルを結合していくのか、結合方法はソートマージ結合、ネスティッドループ結合、ハッシュ結合のいずれを選択するのかなどを、一定のコスト計算式により導き出しています。SQL実行前に統計情報やSQLで指定される条件値からアクセスパスを推定するので、オプティマイザが選定したアクセスパスとは異なるアクセスパスの方が実行時間が速くなる事象は起こりえます。特に、テーブルの統計情報と実際のデータの分布が異なっていた場合は発生しやすいと言えます。

オプティマイザが選択するアクセスパスよりも、適したアクセスパスが判明している場合、個々のSQLにヒントを埋め込んでアクセスパスを指定する(固定化する)チューニングを施すことになります。

しかし、このSQLヒント機能はPostgreSQLでは実装されていません。PostgreSQLの開発コミュニティのwikiページ「OptimizerHintsDiscussion 」には、SQLヒントへの見解が示されています。PostgreSQLの開発コミュニティが示す見解のように、SQLヒントは個々のSQLにヒントを埋め込むため、アップグレードによりオプティマイザが改善されたり、テーブルに格納されているデータ量やデータの分布が変わった場合、アクセスパス選択の柔軟性が失われることになります。また、ほとんどの場合、オプティマイザは人間よりも最適なアクセスパスを選ぶのも事実です。そのため、PostgreSQLの開発コミュニティの見解のようにSQLヒント機能を実装しないという考え方も理解できるのですが、SQLヒントによってSQLをチューニングする手法が一般化している現実を考えると、Postgres Plus Advanced ServerでSQLヒントが実装されているのは、現実的な解であると考えられます。

なお、PostgreSQLの場合も、特定のアクセスパスを無効化もしくは有効化したり、コスト計算をするサーバパラメータをセッション単位で指定することができるので、ある程度、アクセスパスをコントロールすることは可能です。

Postgres Plus Advanced Server で実装されているSQLヒントは表1のように、多くの種類があるわけではありませんが、SQLチューニングで必要となる主要なヒントは網羅されています。

Postgres Plus Advenced Serverで実装されているヒント一覧


次に、Postgres Plus Advanced Server でのSQLヒントの使い方を確認してみましょう。

前回と同様にEMPテーブルを利用します。表2の例のように、USER_IND_COLUMNSディクショナリビューを確認するとEMPNO列に一意索引、JOB列、DEPTNO列にそれぞれ単一列索引、SAL列、COMM列に複合索引が作成されていることがわかります。EMPテーブルから職種がマネージャの情報を確認するSELECTを発行した場合に選択されるアクセスパスをOracle DatabaseではSQL*PlusのAUTOTRACE機能で、Postgres Plus Advanced ServerはEXPLAINコマンドで確認してみます。Oracle DatabaseのオプティマイザはJOB列の索引を利用した索引スキャンを選択し、Postgres Plus Advanced Serverのオプティマイザは全表スキャンを選択することがわかります。EMPテーブルは14行の小さなテーブルですので、Postgres Plus Advanced Serverのオプティマイザは全表スキャンのコストが低いと算出しているようです。INDEXヒントを使用してJOB列の索引スキャンをオプティマイザに指定すると、索引スキャンが選択されることがわかります。

表2:INDEXヒントの利用例

表2:INDEXヒントの利用例

次に、EMPテーブルとDEPTテーブルの結合の例です。DEPTテーブルは部門名や部門の所在地を保持しており、従業員テーブルであるEMPテーブルとの結合はDEPTNO列を条件とします。表3のように、従業員が所属する部門名の一覧を確認するSELECTを実施したところ、Oracle Databaseはソートマージ結合、Postgres Plus Advanced Serverはハッシュ結合を選択しました。ここで、DEPTテーブルを軸とし、EMPテーブルのDEPTNO列の索引であるIND_DEPTNO索引を利用したネスティッドループ結合を選択させる場合、ORDEREDヒント、USE_NLヒント、INDEXヒントを指定することで、アクセスパスをコントロールすることができます。

表3:結合ヒントの利用例

表3:結合ヒントの利用例

上記の例からも分かるように、Postgres Plus Advanced ServerはOracle Databaseと同じアクセスパスを選択するオプティマイザ機能は有していないので、アプリケーションをPostgres Plus Advanced Serverに対応させる場合、性能関連の試験を行う必要がありますが、SQLヒントを利用した従来のチューニング手法を活用することができます。

Index Advisor


Index Advisorは、ある特定のSQLの性能改善に必要な索引をアドバイスしてくれる機能です。キャラクタベースのpg_advise_indexコマンドで動作させることも、GUIツールであるPostgres Enterprise Manager上で利用することもできます。今回はPostgres Enterprise Managerでご説明します。

図1のように、Postgres Enterprise Managerの「SQL Profiler」では、データベースとユーザを指定して取得したSQLトレース情報から、実行時間の長い順やアクセスバッファの多い順など任意の項目でSQLを並べ替え、分析対象とするSQLを選択することができます。また、「SQL Profiler」では、SQLの実行計画を視覚的に確認できるExplainパネルが用意されているので、実行計画を理解する助けにもなります。

図1:Postgres Enterprise ManagerのSQL Profiler

図1:Postgres Enterprise ManagerのSQL Profiler

「SQL Profiler」から「Index Advisor」でアドバイスを受けたいSQLを選択し、メニュー「View」から「Index Adivisor」を選択すると図2のように「Index Advisor」が起動します。

図2:Postgres Enterprise ManagerのIndex Advisor

図2:Postgres Enterprise ManagerのIndex Advisor

画面下の「Suggested Indexes」にはIndex Advisorが作成した方が良いと判断した索引の情報が表示され、画面左の「Actual Plan」には現在選択されている実行計画、画面右の「Hypothetical Plan」にはアドバイスされた索引を作成した後に予想される実行計画が表示されます。このように、Postgres Enterprise Managerの「Index Advisor」を利用すると、索引作成後に予想される実行計画を確認することができるため、索引を実装するかどうかの判断に役立ちます。また、Postgres Enterprise Manager上から索引を作成する場合、対象となる索引を選択して、「Create Indexes for the above selected」をチェックし、「OK」ボタンをクリックします。

開発環境で索引の影響度チェックを行わずに本番環境に索引を作成することはあまり多くないと思いますが、Oracle Databaseでも、性能問題を一刻も早く解決するためにOracle Enterprise ManagerのTuning Advisor でアドバイスされた索引を、そのまま本番環境に実装する英断を下し、性能問題が解決した事案もあるので、柔軟に判断するのが良いと思います。なお、Oracle Database の「Tuning Advisor」やPostgres Plus Advanced Serverの「Index Advisor」のようなチューニングアドバイス機能は、SQLチューニングを実施しなければならない開発者のチューニング工数を削減し、生産性を向上させ、より高品質なシステムを短期間に構築するためのツールとして位置づけて利用するのが良いと思います。

最後に、DRITA(Dynamic Runtime Instrumentation and Tracing Architecture)について紹介します。

DRITA(Dynamic Runtime Instrumentation and Tracing Architecture)


データベースの性能障害が発生した時、もしくは、データベースで性能障害が発生したと思われるとの指摘を受けたときに、どのような情報を確認して、性能障害への対策を考えたり、データベースの問題ではないことを説明するでしょうか。

Oracle Databaseの場合、性能障害が発生したと思われる時間帯の待機イベントの情報を参照することで原因の調査を行います。待機イベントとは表4のように各プロセスがどのような処理を行っているのかを常に表している情報です。

Oracle Databaseの場合、StatspackレポートやAWRレポートといったパフォーマンス情報診断レポートを活用して性能障害発生時の待機イベントを確認することで性能障害の原因とその対処方法を検討します。Oracle Databaseでも運用中に性能障害が発生することは珍しくありませんが、セッション単位の待機イベントを評価して瞬間的な性能障害の原因のあたりをつけたり、トレースやダンプを柔軟に取得できたりと、原因調査できる懐が深いところがOracle Databaseが選択される理由の1つだと思います。

表4:Oracle Databaseの待機イベントの一部


PostgreSQLにもOracle Databaseと同様に稼動統計情報が存在しますが、Oracle Databaseの待機イベントに相当する情報を得るためには、動的追跡(Dtrace)やSystemtapによるトレーシングやプロファイリングを利用することになります。テスト環境上の性能評価や動作確認のためにトレーシングやプロファイリングを活用することはできますが、実運用しているデータベースにトレーシングやプロファイリングを行うことは難しく、その結果を評価するにも相応のスキルを要します。そのためPostgreSQLでデータベースの性能障害が発生した際には、稼動統計情報やログに出力された性能関連情報、OS稼動情報を頼りに性能障害の原因を推察する必要が生じます。

Postgres Plus Advanced Serverでは、Oracle Databaseの待機イベントと同様な機能としてDRITA(Dynamic Runtime Instrumentation and Tracing Architecture)が実装されています。ある期間におけるシステム全体やセッションごとに表5のような待機イベントの発生回数や待機時間の情報を稼動レポートとして出力することができます。

表5:Postgres Plus Advanced Serverの待機イベントの一部


DRITAの設定と稼動レポートの取得の方法は、表6のようにOracle DatabaseのStatspackレポートと同様で、DRITAを含む稼動情報のスナップショットを取得し、表7のようにスナップショットの差分を稼動レポートとして出力します。また、DRITAではセッションごとの待機イベントの情報もスナップショットとして蓄積しているため、セッション視点で待機イベントの情報を出力することも可能です。このように、Postgres Plus Advanced ServerはDRITAによる待機イベントが実装されたことで、簡易な方法で性能障害の調査を行え、より多くのエンタープライズ用途で安心して活用できるようになっています。

表6:DRITAのスナップショット取得と取得したスナップショットの確認

表6:DRITAのスナップショット取得と取得したスナップショットの確認

表7:DRITAの稼動状況レポート

表7:DRITAの稼動状況レポート

今回は、SQLヒント、Postgres Enterprise ManagerのIndex Advisor、DRITA(Dynamic Runtime Instrumentation and Tracing Architecture)の3つの性能関連機能をご紹介しました。PostgreSQLの信頼性をベースにしたPostgres Plus Advanced ServerはPostgreSQLで提供されていないエンタープライズ向けの機能を有するため、PostgreSQLの活用範囲を広げる可能性をもったRDBMSです。今後、多くのユーザにて活用され、その活用ノウハウを製品にフィードバックすることによって、より使いやすく進化していくと思われます。

  • Postgres Plus は、EDB Postgres の旧製品名です。


執筆者紹介

岸和田 隆

岸和田 隆(Takashi Kishiwada)

株式会社アシスト データベース技術本部

アシスト入社後、Oracle Database の研修講師、フィールド・ サポート、新バージョンの検証を経て、2007年 自社ブランド 「DODAI」の準アプライアンス製品の企画・開発、2009年 PostgreSQL、2011年 EDB Postgres、MySQL / MariaDB の事業立上を担当。 現在は「データベースのアシスト」を目指した活動を行っている。

岸和田の紹介記事はこちら



関 俊洋

関 俊洋(Toshihiro Seki)

株式会社アシスト データベース技術本部

2006年、株式会社アシスト入社。データベース・システムの構築や運用トラブルの解決といったフィールド・サポート業務を経験し、その後は新製品の検証やハードウェアとデータベースを組み合わせたソリューション(DODAI)の立ち上げに従事。現在はデータベースの価値や魅力を伝えるための執筆・講演活動を行っている。『SQL逆引き大全363の極意』共著。

関の紹介記事はこちら

連載記事一覧


Facebookで情報をお届けしています

Facebookでは、アシストの「今」を週3回のペースでお届けしています。「めげない、逃げない、あまり儲けない」を合言葉に日々頑張っておりますので、応援よろしくお願いします。



ページの先頭へ戻る