Changes between Initial Version and Version 1 of PostgreSQLの設定ファイル(その１)

Timestamp:

Nov 8, 2016, 2:10:56 PM (8 years ago)

Author:

admin

Comment:

--

PostgreSQLの設定ファイル(その１)

@@ +1 @@
+[[wiki:WikiStart 戻る]]
+
+= PostgreSQLの設定ファイル =
+
+= postgresql.confの設定の勘所 =
+
+7.1 以降の PostgreSQL には、設定ファイルとして postgresql.conf があります。
+このファイルはデータベースのファイルがある場所（通常 /usr/local/pgsql/data/ ）以下に存在します。
+このファイルでは、以下のような設定を行うことができます。
+
+{{{
+    * 接続に関する設定
+    * メモリの使用に関する設定
+    * オプティマイザに関する設定
+    * ログに関する設定
+    * WALに関する設定
+    * その他
+}}}
+
+本節では、このうちのパフォーマンスに関連するいくつかの設定に関する説明を行います。[[BR]]
+
+== ●活動状況の監視(stats_*) ==
+
+どのようなクエリが要求され、それに対して PostgreSQL がどのように処理したか、その統計情報を得ることができます。これを知ることによって、より良いパフォーマンスを得るためのヒントとすることができます。
+デフォルトの設定ではこの統計情報の収集は行われません。これは、統計のための情報の保存自体がパフォーマンス的なオーバーヘッドになるためです。
+postgresql.conf の設定を変更することによって、統計情報を保存させることができます。
+
+||・stats_start_collector（デフォルト：true）||統計情報を保存する場合は true, しない場合は false です。この設定が true の場合、stats_command_string, stats_row_level, stats_block_level のうちで true が指定されているものに関して、情報を保存します（デフォルトでこれらは全て false です）。||
+||・stats_reset_on_server_start（デフォルト：true）||サーバを再起動するたびに、統計情報をリセットするかどうかを指定します。true の場合、リセットされます。||
+||・stats_command_string（デフォルト：false）||true にすると、その瞬間に実行している SQL を保存します。||
+||・stats_row_level（デフォルト：false）||行レベルの情報を保存します。||
+||・stats_block_level（デフォルト：false）||ブロックレベルの情報を保存します。||
+
+== ●収集情報の取り出し ==
+
+収集・保存された情報は、標準で提供されるいくつかのビューと関数で参照することができます。
+代表的なビュー（ドキュメントから抜粋）
+
+|| 番号 || 変更コスト ||
+||pg_stat_activity || サーバプロセス当たり 1 行の形で、プロセスの PID、データベース、ユーザ、現在の問い合わせを表示します。現在の問い合わせの列はスーパユーザのみアクセスできます。他のユーザからは NULL として読み出されます。(収集器による報告の遅れのため、現在の問い合わせは長時間実行中の問い合わせにおいてのみ現在のものを表します。) ||
+|| pg_stat_database || データベース当たり 1 行の形で、そのデータベースに対して、活動中のバックエンド数、コミットされたトランザクションの総数、ロールバックされたトランザクションの総数、読みとられたディスクブロックの総数、バッファヒット(つまり、バッファキャッシュに対象とするブロックが存在するために防止されたブロック読みとり要求)の総数を表示します。||
+||pg_stat_all_tables||現在のデータベース内の各テーブルに関する、シーケンシャルスキャン、インデックススキャンの総数、各種スキャンによって返されたタプルの総数、挿入、更新、削除されたタプルの総数。||
+||pg_stat_all_indexes ||現在のデータベース内の各インデックスに関する、そのインデックスを使用したインデックススキャン数、読みとられたインデックスタプル、正常に抽出されたヒープタプル数(この値は、古く不要となったヒープタプルを示すインデックスエントリがある時に小さくなります)。||
+||pg_statio_all_tables||現在のデータベース内の各テーブルに関する、そのテーブルから読みとられたディスクブロックの総数、バッファヒット数、そのテーブルに関する全てのインデックスに関するディスクブロック数とバッファヒット数、(存在する場合)そのテーブルの補助的な TOAST テーブルから読みとられたディスクブロック数とバッファヒット数、TOAST テーブルのインデックスに関するディスクブロック数とバッファヒット数。||
+||pg_statio_all_indexes ||現在のデータベース内の各インデックスに関する、そのインデックスの読みとられたディスクブロック数とバッファヒット数。||
+
+※allの部分をsysにすればシステムカタログのみに関する統計情報、userにすればユーザ定義のテーブルのみに関する統計情報を得ることができます。
+
+例えば、次項で説明するキャッシュが効果的に働いているかどうかを知るためにディスクの入出力処理に関する情報を得たい場合には、
+{{{
+stats_start_collector = true
+stats_block_level = true
+}}}
+
+のように設定して PostgreSQL を再起動した後、次のような SQL を実行します。
+
+SELECT relname AS テーブル名,
+       heap_blks_read AS ディスクからの読込,
+       heap_blks_hit AS キャッシュからの読込
+       FROM pg_statio_user_tables ; 
+
+
+結果は例えば以下のようになります。
+
+{{{
+ テーブル名 | ディスクからの読込 | キャッシュからの読込
+------------+--------------------+----------------------
+    hoge    |          8         |        110
+    foo     |         12         |        1410 
+}}}
+
+ディスクからの読み込みに対して、キャッシュから読み込んでいる回数が圧倒的に多いので、この場合はキャッシュが十分有効に働いているということを知ることができます。
+
+代表的な関数（ドキュメントから抜粋）
+|| 関数名（引数の型） || 戻り値の型 ||説明||
+|| pg_stat_get_db_numbackends(oid) || integer ||データベース内で活動中のバックエンド数。||
+|| pg_stat_get_db_xact_commit(oid) || bigint ||データベース内でコミットされたトランザクション。||
+|| pg_stat_get_db_blocks_fetched(oid) || bigint ||データベースに関する、ディスクブロック抽出要求数。||
+|| pg_stat_get_db_blocks_hit(oid) || bigint ||データベースに関する、ディスクブロック要求の内キャッシュに存在した数。||
+|| pg_stat_get_numscans(oid) || bigint ||引数がテーブルの場合、シーケンシャルスキャンの実行回数。引数がインデックスの場合インデックススキャンの実行回数。||
+|| pg_stat_get_tuples_returned(oid) || bigint||         引数がテーブルの場合、シーケンシャルスキャンによって読みとられたタプル数。引数がインデックスの場合、読みとられたインデックスタプル数。||
+|| pg_stat_get_tuples_inserted(oid) || bigint ||        テーブルに挿入されたタプル数。||
+|| pg_stat_get_blocks_fetched(oid) || bigint||  テーブル、または、インデックスに関する、ディスクブロック抽出要求数。||
+|| pg_stat_get_blocks_hit(oid) || bigint ||テーブル、または、インデックスに関する、ディスクブロック抽出要求の内キャッシュに存在した数。||
+|| pg_stat_get_backend_idset()  ||set of integer||現在活動中のバックエンド ID を(1からNまでで、ただし、Nは活動中バックエンド数)設定します。||
+|| pg_stat_get_backend_pid(integer) ||integer ||バックエンドプロセスの PID。||
+|| pg_stat_get_backend_dbid(integer) ||oid ||バックエンドプロセスのデータベース ID。||
+|| pg_stat_get_backend_activity(integer) ||text ||バックエンドプロセスの現在の問い合わせ。(呼び出し実行者がスーパユーザではない場合は NULL。)||
+
+
+例えば、各バックエンドプロセスが現在実行しているクエリに関する情報を得たい場合には、
+{{{
+stats_start_collector = true
+stats_command_string = true
+}}}
+
+のように設定して PostgreSQL を再起動した後、次のような SQL を実行します。
+{{{
+SELECT pg_stat_get_backend_pid(S.backendid) AS プロセスID,
+       pg_stat_get_backend_activity(S.backendid) AS 現在のクエリ
+       FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S;
+       FROM pg_statio_user_tables ; 
+}}}
+
+結果は例えば以下のようになります。
+
+{{{
+ プロセスid | 現在のクエリ
+------------+------------------------------------------
+    2885    | SELECT * FROM "foo"
+    2889    |
+}}}
+
+== ●共有バッファ(shared_buffers) ==
+
+共有バッファは、テーブルのデータをキャッシュするために使用されます。キャッシュは、複数のバックエンドプロセスの間で共有する必要があるため、共有メモリ(shared memory)という、特別なメモリ領域に保持されます。設定shared_buffersでは、この共有メモリのサイズを設定することができます。
+
+共有バッファ上にテーブルのデータがキャッシュされると、次回同じデータを必要とする参照系クエリが発行された場合に、ディスクからデータを読むのではなく、共有メモリ上のデータを参照するようになります。共有メモリからのデータ読み出し速度はディスクと比較して非常に高速です。つまり、より高速に処理を完了させることができるのです。
+
+共有バッファのサイズを大きくしておくと、必要なデータが共有バッファ上に存在する確率が高くなります。つまり、システム全体として、速度改善の可能性が向上することになります。
+
+共有バッファのサイズは可能な限り大きくとるべきです。しかし、物理メモリの量は無限ではありません。そこで、どの程度の領域を実際に確保するかを検討することになります。「他のソフトウェアのメモリ使用に影響を及ぼさない最大値」というのが理想的なサイズです。つまり、スワップが発生しない状態を維持できる最大サイズです。このサイズがどの程度かはケースバイケースですが、空きメモリのサイズを一定期間監視すれば、どの程度のメモリの割り当てが可能か推測することができます。
+
+逆のアプローチとして、最適なパフォーマンスを得るための必要最小限のメモリ量から割り当てるメモリの量を考えるという方法もあります。最適なパフォーマンスを得るためには、「必要なテーブルが全て収まるサイズ」のメモリ量が必要です。前項「活動状況の監視(stats_*)」を行うことによって、現状の設定で足りているかどうかを知ることができます。
+
+なお、設定値に 8192 Byte を掛けた数が確保されるメモリ量の概算になります（実際にはもう少し多くなります）。また、max_connectionsの値を２倍した値以上の数値を設定しなければなりません。
+OSの設定として、OS全体で確保できる共有メモリサイズには上限があります。共有メモリはPostgreSQL以外のソフトウェアも使用するので、システム全体の共有メモリ使用量が上限を超えないようにしなければなりません。現在の共有メモリ使用状況を確認するためには、次のコマンドを実行します。
+
+{{{
+ipcs -m
+}}}
+
+共有メモリの上限を変更することもできますが、OS毎に設定方法は異なります。例えばLinuxでは、/proc/sys/kernel/shmmax というファイルで最大サイズを管理しています。このファイルの内容を次のようにして書き換えれば、共有メモリ最大サイズを133554432バイトに変更できます。
+
+{{{
+echo 133554432 > /proc/sys/kernel/shmmax
+}}}
+ 
+== ●ソートメモリ(sort_mem) ==
+
+ソートを行うための作業領域のサイズを設定します。単位は KByte です。ソート作業で必要な領域がここでの設定値を超えると、一時ファイルへの書き出しが行われ、パフォーマンスに悪影響を及ぼします。
+ソートメモリは下記の処理中に使用されます。
+{{{
+    * ORDER BY
+    * Merge Join
+    * CREATE INDEX
+}}}
+ソートメモリは、共有メモリではなく、通常のメモリ領域に確保されます。また、各 postgres プロセス毎に保持され、一つのソートに対して一つの領域が割り当てられます。従って、ここでの設定値の数倍のメモリが使われることになりますので、大きすぎる値を設定することはスワップの発生を誘発し、パフォーマンスの低下を引き起こします。
+
+= 5.検証例 =
+
+実際にどのようなツールを使って、どのような手順で検証を行えばよいか、簡略式ではありますが具体的な事例をご紹介します。
+●検証条件
+
+shared_buffersがどのように影響するかを簡単なテーブルで検証します。実際には、皆さんのアプリケーションのテーブルに適当なデータを入れ、実際に発行されるSQLで検証を行ってください。
+ここでの検証項目は
+
+{{{
+    * 10000件の全件検索
+    * 10000件のテーブルと100000件のテーブルを結合し、条件を指定して検索
+}}}
+
+とします。インデックスは結合項目に対してのみ張っています。また、vacuumとanalyzeも実行済みです。
+
+同時に処理の要求が来た場合の動作を検証できるように、Apache付属のベンチマークツール「ab」を使用します。また、abによるHTTPリクエストをトリガーとしてSQLを実行するため、PHPを利用します。検証は次のコマンドの結果で行います。
+{{{
+ab -n 10 -c 3 http://localhost/sqltest.php
+}}}
+
+
+キャッシュの利用具合を確認するため、postgresql.confの設定を変更しておきます。
+{{{
+stats_start_collector = true
+stats_reset_on_server_start = true
+stats_block_level = true
+}}}
+
+テーブルは以下のように作成しました。
+{{{
+create table test1(id integer, data1 text, data2 text);
+create table test2(id integer, id2 integer, data1 text, data2 text);
+create index test1_idx on test1 (id);
+create index test2_idx on test2 (id);
+}}}
+
+
+データは、次のスクリプトで作成しました。
+{{{
+#!/usr/bin/perl
+$test1_rows = 1000; $test2_rows = 100000;
+$data2 = "*" x 16; $data3 = "*" x 10000;
+
+print "begin;\n";
+for $i (1..$test1_rows) {
+print "insert into test1 values($i, '$data2', '$data3');\n";
+}
+print "commit;\n";
+
+print "begin;\n";
+for $i (1..$test2_rows) {
+print "insert into test2 values($i, ".($i%3).", '$data2', '$data3');\n";
+}
+print "commit;\n";
+}}}
+
+
+また、データベースへのクエリを発行するPHPプログラムは以下になります
+{{{
+<?
+  $con = pg_connect("user=postgres host=localhost dbname=testdb");
+  $sql = "select * from test1";
+  // $sql = "select * from test1 join test2 using(id) where id=100";
+  // $sql = "select  *  from  test1  join  test2  using(id)  where  id<5000 and id2 % 3 = 1";
+
+$result = pg_exec($con, $sql);
+// print pg_result($result, 0, 2);
+
+?>
+done.
+}}}
+
+
+= ●検証1：SQL実行なし =
+
+まず、SQLを実行を除いた部分のパフォーマンスを調べておきます。結果は次のようになりました（抜粋）。
+{{{
+Requests per second: 123.46 [#/sec] (mean)
+}}}
+１秒間に128.87回、リクエストを処理することができます。この値が大きいほどパフォーマンスが良いということです。
+
+
+= ●検証2：10000件の全件検索 =
+
+実行したSQL(SQL1)は次の通りです。
+{{{
+SELECT * FROM test1
+}}}
+結果は以下のようになりました。
+{{{
+shared_buffers  #/sec   test1.disk
+        test1.cache     test2.disk      test2.cache
+16      3.86    243     11757   255     11781
+100     3.83    209     11791   219     11817
+1000    3.96    23      11977   24      12012
+}}}
+test1.diskは、テーブルtest1に関して、ディスクからデータを取得した回数、test1.cacheは、キャッシュにデータがあった回数を示します。キャッシュにあった回数が多い方が高速に処理が行われます。
+shared_buffersのサイズが小さすぎる場合は、ディスクからの読み込みが頻発しているのがわかります。
+
+
+
+== ●検証3：10000件のテーブルと100000件のテーブルを結合し、条件を指定して検索 ==
+
+実行したSQL(SQL2)は次の通りです。
+{{{
+SELECT * FROM test1 JOIN test2 USING(id) WHERE id=100
+}}}
+結果は以下のようになりました。
+
+{{{
+shared_buffers  #/sec   test1.disk
+        test1.cache     test2.disk      test2.cache
+16      53.48   12      0       1       0
+100     74.63   1       11      1       11
+1000    68.03   1       11      1       11
+}}}
+
+shared_buffersが16の時に、キャッシュが使われていないことがわかります。
+また、他のSQLに比べて、#/secの変化が大きくなっています。これは、SQLの実行結果が他と比べて少ないためだと推測できます。検索結果が多い場合には、全体の処理の中で、データの転送にかかる時間が大半を占めます。それに対して検索結果が少ない場合は全体の実行時間に対する転送時間の占める割合が小さくなります。そうするとキャッシュが使われた／使われていないの違いによる処理時間の違いが占める割合が大きくなりますので、実行時間の数字はその影響を受けやすくなります。
+通常アプリケーションから実行されるSQLは、結果が少ないものが多数を占めますので、キャッシュが使われた／使われていないは、システム全体を見ても大きく影響を及ぼすことになります。
+
+
+次に下記SQL(SQL3）を実行します。
+{{{
+SELECT * FROM test1 JOIN test2 USING(id) WHERE id<5000
+}}}
+
+結果は以下のようになりました。
+
+{{{
+shared_buffers  #/sec   test1.disk
+        test1.cache     test2.disk      test2.cache
+16      3.86    243     11757   255     11781
+100     3.83    209     11791   219     11817
+1000    3.96    23      11977   24      12012
+}}}
+shared_buffersが1000の時だけ、キャッシュが有効に使われていることがわかります。
+
+
+= ●結論 =
+
+これらの検証の結果、この状況ではshared_buffersが100では不足していることがわかります。SQL1とSQL2では十分であるように見えますが、SQL3ではディスクからの読み込みが必要以上に発生しています。
+ここではそれほど大きなパフォーマンスの差異にはつながっていませんが、負荷がもっと高くなったり、扱うテーブルとデータの量が増えた場合には、より顕著に違いが現れると思われます。
+