一つの指針として「2012年度WG1活動報告書」として情報を公開しています。9.3新機能による性能影響も合わせて評価することとしました。最適な利用指針を探る評価を実施することとしました。データベースのI/O負荷分散機能であるパーティショニングや、ハードウェアを活用した性能向上の検証を実施しました。更新系処理を新たに評価することとしました。KVMを使った仮想化環境と、LinuxコンテナのDocker環境の検証を実施しました。9.5を対象とした、スケールアップの定点観測を実施しました。これは昨年度に引き続き、参照系・更新系の双方で性能を調査するものです。6と7とでPostgreSQLの性能を比較しました。9.6を対象とした、スケールアップの定点観測を実施しました。9.6の新機能のパラレルクエリや改善が行われたVACUUMについて効果を検証しました。データ型に関する性能評価を行いました。実施体制¶2017年7月20日に開催された2017年度第1回技術部会より、以下の体制で実施しています(企業名順)。日本支社は、「主査」としてWG1の取りまとめ役を担当することになりました。実施スケジュール¶2017年度は、下記スケジュールで活動しました。富士通株式会社(敬称略)よりご提供いただきました。この場を借りて厚く御礼を申し上げます。検証概要¶WG1では、PostgreSQLの新バージョン・新リリースにあわせて、新旧バージョンの性能比較やスケールアップ特性の検証を主な目的にした定点観測を2012年度から実施してきました。2014年度からは、それまでの参照処理に加えて更新処理についても検証を実施し、検証結果の公開を行うようになりました。スケールアップ参照性能の比較および更新性能の比較を行いました。いうベンチマークツールを使用しました。pgbenchはPostgreSQLに付属する簡易なベンチマークツールです(バージョン9.5より前はcontribに付属)。業務をモデル化)を参考にしたシナリオに基づくベンチマークの実行のほか、検索クエリのみを実行するシナリオも搭載されています。カスタムスクリプトを用意することで、独自のシナリオでベンチマークを実行することも可能です。Second)が出力されます。establishing」はこれを含まないTPSを示します。establishing)pgbenchには「スケールファクタ」という概念があり、データベースの初期化モードでpgbenchを起動することにより、任意のサイズのテスト用のテーブルを作成できます。デフォルトのスケールファクタは1で、このとき「銀行口座」に対応する「pgbench_accounts」というテーブルで10万件のデータ、約15MBのデータベースが作成されます。以下に、各スケールファクタに対応するデータベースサイズを示します。他にもテーブルが作成されます。作成されるテーブルのリストを以下に示します。件のデータが作成されます。スケールファクタを増やすとこれに比例して各テーブルのデータが増えます。様々なオプションがあります。使用している主なオプションのみを説明します。本検証では上図構成のうち「DBサーバ#2」と「負荷掛け用」を用いました。検証環境のソフトウェア構成を示します。設定は以下の通りです。これらは参照系検証と更新系検証で共通です。以下の手順で、データベースクラスタを作成しました。initdbでデータディレクトリを作成し、上記に示した設定をpostgresql.confに記述します。$PGDATA/postgresql.confPostgreSQLを起動してベンチマーク用のデータベースを作成します。検証方法(参照系)¶pgbenchコマンドを用いて、ベンチマーク用データベースをスケールファクタ2000で初期化します。モジュールを用います。されているデータベース起動直後の性能低下状態を解消するために用いることができます。これによりテーブルデータはすべてバッファキャッシュに格納されます。pg_prewarm('pgbench_accounts');以下のスクリプトを参照系カスタムスクリプトとして作成して、適度な負荷がかかるようにしました。-S)ではCPUに充分な負荷がかからないためです。[dbname]として実行しました。クライアント数(-c)とスレッド数(-j)を変動させながら、300秒ずつ(-T)実行しています。スレッド数はクライアント数の半分としています。スケールファクタ(-s)にはデータベース初期化時と同じ2000を指定します。1秒毎の進捗レポート取得(-P)と各クエリの平均レイテンシ(-r)も取得する指定にしました。指定によって得られた結果は今回の考察に直接用いることはありませんでした)計測はクライアント数ごとにそれぞれ3回ずつ実行し、その中央値を結果とします。検証結果のTPSのグラフを以下に示します。コア数を超えた辺りのクライアント数でTPSは頭打ちとなっています。10のTPSはほとんどクライアント数においてわずかに9.6を上回る結果となりました。検証方法(更新系)¶pgbenchコマンドを用いて、ベンチマーク用データベースをスケールファクタ2000で初期化します。モジュールを用います。これによりテーブルデータはすべてバッファキャッシュに格納されます。pg_prewarm('pgbench_accounts');以下のスクリプトを更新系カスタムスクリプトとして作成して、適度な負荷がかかるようにしました。[dbname]として実行しました。クライアント数(-c)とスレッド数(-j)を変動させながら、300秒ずつ(-T)実行しています。スレッド数はクライアント数の半分としています。スケールファクタ(-s)にはデータベース初期化時と同じ2000を指定します。1秒毎の進捗レポート取得(-P)と各クエリの平均レイテンシ(-r)も取得する指定にしました。指定によって得られた結果は今回の考察に直接用いることはありませんでした)計測はクライアント数ごとにそれぞれ3回ずつ実行し、その中央値を結果とします。このとき、更新系検証の場合は実行後のテーブルを使い回さず、毎回初期化しています。検証結果のTPSのグラフを以下に示します。コア数を超えた辺りのクライアント数から差が現れ、最大TPSは9.6が10を大きく上回る結果となりました。(更新系)10の更新性能劣化を解明するために別途、追加の検証を行いました。プロセスが実行するシステムコールを調査しました。コマンドを用いることで、実行したシステムコールを取得できます。オプションを用いることでシステムコールの集計を行うことが可能です。オプションを用いることで集計の対象に全子プロセスを含めています。実行開始直後にシステムコール情報取得を開始しています。プロセスが使用するシステムコール図ではそれぞれ実行時間割合の大きい10個のシステムコールを示しています。実行時間割合が一番高いシステムコールが異なっていました。システムコールが最も用いられています。実行されたシステムコールの情報は実行回数とエラー回数も同時に得られます。実行回数とエラー回数を示したのが次の図です。エラー割合の大きさがパフォーマンス低下につながったのではないかと予想されます。これらのシステムコールがどの関数から利用されているかを確認しました。オプションを用いました。用いられていたケースは以下の2通りでした。用いられていたケースは以下の2通りでした。関数で利用されていました。いずれもセマフォを操作する関数から用いられるシステムコールでした。本検証では例年通り、PostgreSQL最新バージョンと旧バージョンとのスケールアップ性能比較検証を行いました。検証前は参照系更新系いずれも性能は大きく変化しないものと予想していました。更新系においては多数の同時接続時において新バージョンの明らかな性能低下が確認されました。更新系について、システムコールの面から追加検証を行いました。10のシステムコールのエラー率が高いことを確認できました。されたことに依るものでした。本変更点はリリースノートからも確認できました。状況証拠ではありますが、これらの結果より更新性能低下の原因はセマフォが変更されたことが大きいとみられます。セマフォの使用を明示的に指定することで確認可能と思われます。(※3)が投稿されています。こちらの結果も本検証と同様に10の性能低下を示す結果でした。その後のコメントでやはりセマフォの変更による性能低下が示唆されておりました。本章では、Windows版PostgreSQLの性能を測定し、Linux版PostgreSQLとの性能の傾向を比較した結果を示します。国内のPostgrreSQLユーザの中にはWindows版PostgreSQLを業務に使っているユーザも少なからずいます。これまでPGEConsでは、Windows版PostgreSQLを用いた性能検証は実施していませんでした。今年度のPGEConsでは、Linux上で実施していた定点観測と同様の測定をWindows版PostgreSQLでも実施し、Linux版PostgreSQLと、Windows版PostgreSQLの性能の傾向を評価しました。本節では、Windows版PostgreSQL検証の環境を説明します。検証環境のソフトウェア構成を示します。本節では、Windows版PostgreSQL検証のモデルを説明します。検証モデルの概要については、「定点観測(スケールアップ検証)」を参考にしてください。データベースクラスタ構成¶Windows版PostgreSQLをインストーラでインストールすると、インストール中にデフォルトのデータベースクラスタを作成します。Files\PostgreSQL\10\dataの下に作成されます。今回の性能検証では、データベースクラスタを外部ストレージには配置せず、またデータベース格納領域とWAL格納領域の分離は行わない構成で実施しています。PostgreSQLの設定¶PostgreSQLの設定として設定ファイルを以下のように変更して測定を実施しました。検証用のベンチマークとして、PostgreSQL標準のベンチマークツールpgbenchを使用しました。(pgbenchの詳細については、「定点観測(スケールアップ検証)」を参考にしてください。初期データの登録は、pgbenchの-iオプションでスケールファクタ=1000のサイズを指定しました。tableの指定はなしとしました。300秒を指定しました。96を与えて実行しました。参照系測定のために、以下のカスタムクエリをpgbenchに与えて実行しました。:row_count;それぞれの同時実行数において、3回の測定を実施し、その測定結果(tps)の中間値を測定値としています。300秒を指定しました。96を与えて実行しました。更新系測定のために、以下のカスタムクエリをpgbenchに与えて実行しました。:aid_val;それぞれの同時実行数において、3回の測定を実施し、その測定結果(tps)の中間値を測定値としています。本節では、Windows版PostgreSQL検証結果を示します。参照系ベンチマークの結果を以下に示します。参照系に関しては、Linux版PostgreSQLと同等の傾向かつ性能になっていると判断できます。CPU使用率とtps/latencyの関係を見ると、CPU使用率が100%近くになるまでは、同時接続数に対してほぼスケールしており、CPU使用率が100%近くになると、tpsは横ばいとなり、latencyが増加していくという傾向があります。このモデルでは、CPUネックになるという結果になりました。tpsの傾向がLinux版とほぼ同等の傾向であることから、CPU使用率の傾向についてもLinux版と大きな差はないと考えられます。更新系に関しては、tpsがピーク付近になると、Linux版と比較してtpsが低下するという傾向になりました。接続数がピークを超えると、Windows版もLinux版もtpsが低下するという傾向にありますが、tpsの劣化度合いが、Linux版よりもWindows版のほうが大きくなっています。CPU使用率についてはtpsのピークおよび、それ以降になってもそれほど大きくなっておらず、I/Oネックになっていることが推測されます。shared_buffersチューニング¶shared_buffersを定点観測用の設定値(実メモリの約10%=約40GB)と、非常に小さな設定値(64MB)で参照系モデルを測定した結果を示します。Windows版PostgreSQLの場合、shared_buffersが小さくてもほとんど変わらない結果になっています。接続数が大きくなった場合には小さな設定値のほうが僅かに高い結果となっています。Windows版PostgreSQLの場合、PostgreSQL文書のshared_buffersの説明に、「Windowsシステムでのshared_buffersの範囲は一般的に64MBから512MBです。」という記述がありますが、その記述どおりの結果が出たと考えられます。wal_sync_methodチューニング¶wal_sync_methodチューニングは、以下の2ステップで実施しました。pg_test_fsyncによる測定pgbenchによる測定pg_test_fsycとは、PostgreSQLを動作させる場合に、最も高速なwal_sync_methodを決定するためのツールです。pg_test_fsyncによる測定を実施した結果を以下に示します。n/a次に更新系ベンチマークで実施した測定を、wal_sync_methodをopen_datasync,fsync_writethrough,fsyncに設定して実施した結果を以下に示します。wal_sync_methodについては、pg_test_fsyncユーティリティを使った事前検証と、実際にpgbenchを用いた測定の両方において、open_datasyncを設定することで、pgbenchでも最も高いtpsを出せるという結果となりました。定点観測モデルを用いて、Windows版PostgreSQLとLinux版PostgreSQLの性能を比較しました。同一スペックのサーバ上でLinux/WindowsのOSの差異による測定を実施した結果、参照のみの場合は、Linux版と同等の性能、更新系の場合には、tpsがピークに到達する前には同等の性能でしたが、ピークを超えた後は、Linuxよりもtpsが低下する傾向があるのを確認しました。この結果から、Windows版PostgreSQLでは、大量の書き込みが発生する場合には、Linux版PostgreSQLと比較すると性能が低い可能性があると推測されます。過負荷状態でなければ、Linux版と同等の性能にはなっており、Windows版PostgreSQLは性能面ではLinux版と遜色はないと考えられます。特定のPostgreSQLパラメータについて、Windows版固有のチューニングが必要か検証しました。shared_buffersについては、Linux版のチューニング方針とは異なり、参照主体のモデルでは、非常に小さな値を設定しても性能上の問題はないという結果となりました。wal_sync_methodについては、open_datasyncを設定することが妥当であるという結果となりました。近年、ビッグデータ活用の流れから、データベースに大量のデータを投入して検索や集計を行い、新たな知見を得ようとする試みが増えています。Intelligence)が典型的です。多数の短小なクエリを大量に処理するような使用方法であるOLTPに対して、OLAPと呼ばれています。一般的に、OLAPではパラレルクエリやパーティショニングの恩恵を受けやすいとされています。OLAP用途で実用的に使用できない状態でした。10で強化されたパラレルクエリによって、PostgreSQLがOLAP用途で実用的に使用できるようになったのかを検証します。本検証では、OLAPにおいて使用される形式のクエリを模したベンチマークテストであるTPC-DSで定義されているクエリを題材として使用します。TPC-DSとは¶TPC-DSは、TPC-Hの後継となるベンチマークテストです。クエリが工夫されています。通常のOLTPよりもCPUの処理性能と、CPUをどの程度うまく使えているかがベンチマーク結果を大きく左右します。TPC-DSでは、99種類/103本のクエリが定義されていますが、本検証では、そのうちのNo.10とN0.11の2種類/2本のクエリについて検証を行いました。10で新規にパラレルクエリ対応したパターンにマッチしたものと判断したことが理由です。No.11以外にはNo.4が該当しますが、事前検証の結果から、No.4は検証対象としたバージョン9.5〜10のすべてで実行時間が長大(6時間強)となることが見込まれたため、検証期間の関係から採用を見送りました。パラレルクエリ¶パラレルクエリとは、一つのクエリを複数のプロセスで分担して並列処理することで、単一プロセスでの処理よりも高速にクエリを実行する処理方式です。SELECT文で1000万レコードを全件検索する場合、検索で使用するプロセスが1個だけだと1プロセスが1000万レコードをすべて検索する必要があります。それに対して、例えば、検索で使用するプロセスが10プロセスあって、それらで分担することができれば、1プロセス当たりの検索対象は100万レコードと10分の1に減ります。複数のプロセスで分担することで高速化を図るため、メニーコア環境での使用で、効果を発揮します。ある処理を複数に分割する配分の制御や受け渡し、そして複数で処理した結果を集めて一つにまとめる処理などの実行時間が長くなります。複数のプロセスで短縮できる時間があまり少ないと、パラレルクエリに変更することでむしろ余計に実行時間がかかってしまうこともあります。そのため、プランナは多くの実行プランの中から、パラレルクエリのほうが効率的に処理できると判断した場合のみパラレルクエリの実行プランを選択します。PostgreSQLでのパラレルクエリ¶2017年現在、PostgreSQLで実装されているパラレルクエリに関する処理は、パラレルスキャン、パラレルジョイン、パラレル集約の三種類です。プランナが処理を分担したほうが効率的だと判断した場合、パラレルクエリのプランを採用します。loops=1パラレルクエリでは、親プロセスに加えワーカーがバックグラウンドワーカープロセスとして起動されて処理を実行するため、検索プロセス数は親プロセスとワーカーの合計となります。各プロセスで処理した結果をGatherノードで集約します。パラレルクエリで起動されるワーカー数は、テーブルサイズによりデフォルトで起動する数が決まっています。プロック数はブロックサイズがデフォルト(8KB)の場合です。パラレルクエリに関連するパラメータ¶パラレルクエリ時に関連する主なパラメータには以下のパラメータがあります。max_parallel_workers_per_gatherパラメータのデフォルト値がバージョン9.6では0のため、デフォルト設定ではパラレルクエリは実行されません。バージョン10では同パラメータのデフォルト値は2であるため、デフォルト設定でもパラレルクエリが実行されます。システムがサポートするバックグラウンドワーカープロセスの最大数を指定します。一つのGatherノードに対して起動できるワーカー数の最大値を指定します。一度に有効となるパラレルワーカープロセスの最大数を指定します。パラレル処理を行うテーブルの最小サイズを指定します。パラレル処理を行うテーブルの最小サイズを指定します。パラレル処理を行うインデックスの最小サイズを指定します。ワーカーを起動するためのコストに対するプランナの推測値を指定します。あるワーカーから、1行を他のプロセスに転送するためのコストに対するプランナの推測値を指定します。max_parallel_workers_per_gatherで指定した値がmax_worker_processesで指定した値を超える場合、起動されるワーカー数はmax_worker_processesの値に制限されます。複数のパラレルクエリが同時に行われる場合、起動されるワーカー数の合計がmax_worker_processesで指定した値に制限されます。このため、プラン作成時のワーカー数よりも実際に起動されるワーカー数が少ない場合があります。10では、max_parallel_workersもあり、パラレルクエリの実行に使用されるワーカー数は、基本的にはこちらに指定した値に制限されます。この値がmax_worker_processesで指定した値を超える場合は、起動されるワーカー数はmax_worker_processesの値に制限されます。検証環境のハードウェア構成の概略を示します。本パラレルクエリ検証ではDBサーバ上でSQLを直接実行する形式としました。検証ではコア数とメモリサイズの構成が異なる下記のDBサーバを使用しました。検証環境のソフトウェア構成を示します。検証項目¶パラレルクエリ検証では以下の2点を検証します。どの程度改善するのか、もしくは悪化するのかを明らかにします。10においては、デフォルトの設定そのものの性能を検証することになります。それが適切なものであるかどうかを検証します。検証Bでは、パラレルクエリの並列度による処理性能の改善状況を確認します。各パラメータの値を変更し、並列度の違いによる処理時間を比較します。OLAP用途におけるクエリに対する性能が、検証Aとの比較でさらに改善する余地があるのかどうかを明らかにします。処理時間を比較します。これにより、パラレルクエリ部分によらない性能改善がどの程度あったのかを明らかにします。本検証で使用するデータモデルは、TPC-DSにおいて定義されているデータモデルに準拠します。Factorは2GBを採用しました。各テーブルのサイズは、以下のとおりです。本検証では、TPC-DSで定義されるNo.10とNo.11のクエリを使用します。それぞれのクエリは、データモデルに対し、以下のデータの抽出を指示します。同一のものの人数を数えよ。実店舗以外には、Web(ECサイト)通販とカタログ通販の2種類がある想定。性能測定にあたり、デフォルトから変更したパラメータは以下のとおりです。測定により値を変更して実施します。PostgreSQLのバッファ上、もしくはOSのディスクバッファ上に常駐させた状態にします。検証A測定方法¶No.10とNo.11のSQLを、psqlを使用して実行します。検証B測定方法¶No.10とNo.11のSQLを、psqlを使用して実行します。測定に際しては、postgresql.confに以下の設定を行い、再起動を行うことで設定の反映を行っています。測定を行う前にはpg_prewarmを用いて、メモリ上にデータを常駐させた状態にしています。2GBバージョン毎の処理時間のグラフと表を以下に示します。性能向上比は、バージョン9.5の結果を、比較対象の各バージョンでの結果で除したものです。以下の性能向上比も、すべてこの方法で計算しています。検証の結果、No.10はバージョン9.6・バージョン10ともにパラレルクエリの実行が行われ、バージョン9.5と比較して、劇的に高速化したことがわかります。No.11ではバージョン10のみパラレルクエリの実行が行われましたが、バージョン9.5と比較してあまり高速化しませんでした。『非相関サブクエリのパラレル実行』に該当したものと考えます。9389.6検証の結果、No.10は、並列処理数の増加にともなって、処理時間が減少する傾向にありました。No.11では並列処理数の増加に伴って処理時間が減少せず、特に検証サーバ2においては処理時間が増加する傾向が見られました。検証Bのクエリの処理時間全体での比較データでは反比例の関係にあることを推測できるものとはなりませんでした。理論と合致する傾向にあるかどうかを検証しました。パラレル実行offを意味します。4.80No.10、No.11のクエリにおいて、パラレルクエリ実行となった部分のみを抜き出して比較したところ、両クエリともに、並列処理数の増加にともなって、処理時間が減少する傾向にありました。グラフの形状からは、パラレルクエリ実行された部分の処理時間と並列処理数の間には、反比例に近い関係がありそうであると推測できます。バージョン10はNo.10、No.11ともに概ね20%の性能向上が認められました。高速化したことが伺えます。パラレルクエリのあるバージョン9.6、およびバージョン10で劇的に高速化しました。バージョン9.6とバージョン10を比較すると、10のほうがより高速化しています。ANALYZEの結果を比較します。executedとなっています。実行計画としてはあったが、実際の実行時に何らか(ジョイン対象のテーブル検索の結果が0件であった、など)の理由で、実行不要と判断され、実行されなかったことを示します。実際のテーブルに対する検索・ジョインの操作が行われた内容が異なっていることになります。実行時間の長大化に拍車がかかった可能性があります。実行時間の数千倍高速化という、劇的な短縮に寄与した可能性があります。上記の数千倍からは大きく乖離しています。実行時間がまったく異なる可能性がある、ということを示しています。注意が必要であると考えます。ANALYZEの結果も同様でしたが、バージョン9.6とバージョン10ではバージョン10のほうが高速化しています。測定で示されたとおり、パラレルクエリ以外での改善によるものと考えられます。パラレルクエリを選択したバージョン10で高速化しました。ANALYZEの結果を比較します。▲印)の違いになります。Sortで、ディスクを作業領域に使用して処理されています。work_memパラメータで指定した値を超えてしまうことにあります。ソート対象となるデータ量が多くなると予想され、メモリの確保を断念したものと考えます。本ケースの場合は、1ワーカーが担当するソート対象のデータ量が3分の1となり、メモリ確保可能と判断したと考えます。Sortを選択可能となっただけでも性能の改善が見込まれます。効率的な処理方式を選択する可能性があるものと考えます。並列処理数と処理時間の間において、正確な反比例の関係が認められませんでした。検証サーバ2では、グラフの形状からは反比例の関係を推測できる形状になっていませんでした。占めていることが示唆されます。全体5.86秒に対して4.11秒強と73%を占めていました。4.11秒から見て、2%〜5%程度のばらつきが見られました。パラレルクエリ外に要した時間について無視できない大きさのばらつきが含まれていたことが示唆されます。グラフとなっています。ここからは、パラレルクエリ実行において、0.3秒〜0.4秒弱の固定的な時間を追加で要していることが示唆されます。グラフが複雑な形状となった可能性があると考えます。ワーカー数2とワーカー数4では0.03%程度の改善とほとんど効果が見られませんでした。その増加分をパラレルクエリ実行で埋められなかったため、と考えます。ワーカー数6とワーカー数8との比較では、ワーカー数8が0.38%の低速化が認められました。処理時間が0.38%微増と、ほとんど変化していないことから、概ね妥当と考えます。検証サーバ2においては、No.11では逆に低速化する傾向が認められました。パラレルクエリ実行の部分以外での要因がもととなっているものと考えます。以上の結果から、パラレルクエリ実行に伴うオーバヘッドの大きさが、パラレルクエリ実行による高速化分を上回る場合がありうると考えます。大きな改善が見込めない場合も存在することが示されたと考えます。PostgreSQLは新たな時代に突入したと感じていましたが、実際に検証を行った結果、パラレルクエリの使用には細心の注意が必要であることが明らかになりました。効果が十分に得られるかどうかの確認が必要であるものと考えます。以上から、現状のパラレルクエリの実装においては、OLAP用途での使用には不安が残る結果となりました。不安なくパラレルクエリを使用できるよう、次期バージョンでのパラレルクエリのさらなる進化と、これを司るプランナ・エグゼキュータのさらなる深化に期待したいです。なかで検索を行う事が多くなっています。得られないと予想されます。上記のようなケースで十分なパフォーマンスが得られるかを検証します。ロジカルレプリケーションを用いた負荷分散は以下のような構成で行います。Masterをデータの更新専用とし、全文検索用のインデックスを設定しません。Slaveをデータ検索専用とし、全文検索用のインデックスを設定します。全文検索は、PostgreSQLに以下の全文検索用のモジュールを使います。