Top > MyPage
 
 
XML - icon
XML

PostgreSQLパフォーマンスチューニング

2009年5月2日 kurosawa

設定ファイルチューニング

PostgreSQLの設定はデフォルトではロースペックなサーバ上動作するように設定されています。これだと高性能なサーバでもその性能を生かしていないことになります。

実際の効果を確かめながら細かく最適値を探るのが本来なのですが、デフォルトの設定のままで使うより「とりあえず大まかにこれだけ設定しておけ」というのがあるようです。基本的なチューニングは修正する項目と値がある程度決まっているようです。

最初はこんなもんと割り切って設定することにします。

PostgreSQLの設定ファイルpostgresql.confはデータベースのファイルがある場所（通常/usr/local/pgsql/data/）以下に存在します。

・shared_buffers=1000

（1単位= 8KB）

PostgreSQLのデータベースエンジン（バックエンド）が使用する共有メモリ上のバッファ領域を指定します。

PostgreSQLはデーモン管理プロセス（postmaster）がリクエストを受け取ってそれをバックエンドサーバプロセス（postgresプロセス）に送ります。

shared_buffersは大きすぎても小さすぎても性能が低下します。

PostgreSQL ver7系では、性能のピークは8000〜10000（約80M）の範囲にあります。

PostgreSQL ver8系では、性能のピークは100000（約800M）付近にあります。

150000程度までは性能が低下しないようです。

共有メモリバッファに1GBを割り当てる場合

1,000,000,000 ÷ 8,192 ≒ 120,000

shared_buffers=120000

データベースのサイズより大きいバッファをとっても意味が無いし、アプリケーション用の使用する分を考慮する必要があります。

データベースで使用されるディスク領域はSQLで求められます。

--データベースの使用されるディスク領域を求める
SELECT pg_size_pretty(pg_database_size('sanpai'));

--テーブルの使用されるディスク領域を求める
SELECT pg_size_pretty(pg_total_relation_size('kaikake'));

とりあえず搭載メモリの1/4〜1/2を目安にして設定して、そのあと実際のパフォーマンスをみながら修正していくのがよさそうです。

搭載メモリ2GBの1/4に設定する場合

500,000,000 ÷ 8,192 ≒ 60,000

shared_buffers=60000

shared_buffersを無理に増やすとpostmaster起動時に次のようなエラーが出ます。

FATAL: could not create shared memory segment: Invalid argument

これは１つのプロセスが確保できる共有メモリの最大値を超えている場合に起きます。

エラー時に表示されるメッセージには、割り当てに失敗したサイズが正確に記載されています。

エラー時に表示されるメッセージには、割り当てに失敗したサイズが正確に記載されているので、その値をLinuxの場合は/etc/sysctl.confに記述してやります。

  /etc/sysctl.conf

# Controls the maximum shared segment size, in bytes
kernel.shmmax = 4294967295

# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 268435456

shmallはシステムが許す共有メモリの総量で、shmmaxより同じかそれ以上になっていないといけない。shmmaxはbytes単位で、shmallはpage単位で指定する。

1pageあたり何bytesかは以下の方法で調べる（たぶん4096bytes）。

# cat <> EOF |
#include <stdio.h>
main() { printf ("%d bytes\n",getpagesize()); }
EOF
gcc -xc - -o /tmp/getpagesize
/tmp/getpagesize; rm -f /tmp/getpagesizeリターン

一時的に（OS再起動まで）に設定する場合は、

/proc/sys/kernel/shmmax
/proc/sys/kernel/shmall

にそれぞれ記述します。

設定値を確認するにはコマンドipcs -mlで確認します。postmasterを起動させてから、

# ipcs -ml
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 163840
max total shared memory (kbytes) = 0
min seg size (bytes) = 1

とします。

・max_connections=100

バックエンドに同時接続する最大数を決定します。

とりあえずそのまま100でもよいかも。

バックエンド一つ起動させると数メガバイト〜10メガバイト消費するらしい。

・wal_buffers=8

（1単位= 8KB）

WALデータ用の共有メモリ内のバッファ数です。

PostgreSQLではデータを更新する際に、どのような変更を行うのかを記録として残すようにしており、これをトランザクションログWAL（write ahead log）といいます。トランザクションがコミットされるまでWALを保存しておくためのバッファです。

32〜64くらいに設定しておけばよいらしいです。

wal_buffers=64

・checkpoint_segments=3

トランザクションログファイルの数をしてします。8か16あたりが妥当らしいです。

checkpoint_segments=16

ということで、最終的には以下のパラメータを利用。

shared_buffers=60000
max_connections=100
wal_buffers=64
checkpoint_segments=16

しかし、あくまでデフォルトよりはまだよかろうということであって、適切かどうかはPostgreSQLの備える統計情報収集機構を使って調べる必要があります。

postgresql.confの中の

・stats_start_collector

・stats_block_level

・stats_row_level

・stats_command_string

をそれぞれonにします。

stats_start_collector=on #統計情報を保存する場合はtrueしない場合はfalseです。

stats_block_level=on #ブロックレベルの情報を保存します。

stats_row_level=on #行レベルの情報を保存します。

stats_command_string=on #trueにすると、その瞬間に実行しているSQLを保存します。

デフォルトでオフになっているのはオーバーヘッドがあるからで、統計を確認したらオフにするべきと書いてあるHPもあるが、ほとんどパフォーマンスに影響を与えないので基本的に有効にしておくべきという意見もあります。

postgresql.confの設定をしてPostgreSQLを再起動後、次のようなSQLを実行します。

--１
SELECT datid AS データベースのOID,
       datname AS データベース名,
       numbackends AS 接続しているバックエンド数,
       xact_commit AS コミットされたトランザクション数,
       xact_rollback AS ロールバックされたトランザクション数,
       blks_read AS ディスクから読み込まれたブロック数,
       blks_read AS 共有バッファから読み込まれたページ数
       FROM pg_stat_database;

結果１
"データベースのoid","データベース名","接続しているバックエンド数","コミットされたトランザクション数","ロールバックされたトランザクション数","ディスクから読み込まれたブロック数","共有バッファから読み込まれたページ数"
1,"template1",0,0,0,0,0
11510,"template0",0,0,0,0,0
11511,"postgres",1,271560,56,168765,168765
41911,"slonydb",0,71437,0,63124,63124
91583,"backupSanpai",0,80498,14,119565,119565
96767,"sanpai",5,4041170,9909,838533198,838533198

--２
SELECT relname AS テーブル名,
       heap_blks_read AS ディスクからの読込,
       heap_blks_hit AS キャッシュからの読込
       FROM pg_statio_user_tables;

結果２
"テーブル名","ディスクからの読込","キャッシュからの読込"
"shaban_history",278,7177
"unsoten",0,0
"urikake",47653836,57840001
"kaikake",592643211,76932316
"seisan_tyohyo",739,11760

ディスクからの読み込みよりキャッシュから読み込んでいる回数が多ければ、キャッシュが有効に働いているということになります。

あとはpostgresql.confのshared_buffersの値を変えてはpostmasterを再起動し、テスト、の繰り返しになります。

値が大きくなって見づらい場合、postgresql.confのstats_reset_on_server_start=onとしておくとpostmasterの再起動の度に統計情報がゼロにリセットされます。

I/O処理のチューニング

トランザクションログファイルと他のテーブルファイルやインデックスファイルとはアクセスパターンが違います。

テーブルファイルやインデックスファイルはアプリケーションによってランダムに読み書きされるのに対して、トランザクションログファイルは常にトランザクション処理で追記されるだけでリカバリされない限り読み込みがありません。

なのでトランザクションログファイルと他のファイルを別々のディスクに配置しておくとパフォーマンスがアップする可能性があります。

トランザクションログファイルはpg_xlogディレクトリに作成されるので他のディスクにコピーした後、元のディレクトリにシンボリックリンクを作ってやります。

# ln -s /disk/sbd2/pgdisk/pg_xlog /usr/local/pgsql/data/pg_xlog

複数台のハードディスクがある場合、PostgreSQL 8.0で導入された「テーブルスペース」を使ってデータベースを分散して配置できます。

テーブルスペースとは、データベースオブジェクト（データベース、テーブル、インデックス、シーケンス）を格納する記憶領域のことです。

デフォルトのテーブルスペースの記憶領域は、通常/usr/local/pgsql/dataになるので、すべてのデータベースオブジェクトは/usr/local/pgsql/data以下に格納されていることになります。

テーブルスペースを新たに作り、そこにデータベースオブジェクトを格納してやるとパフォーマンスがアップする可能性があります。

まずCREATE TABLESPACEでテーブルスペースを作ります。

構文：CREATE TABLESPACE <テーブルスペース名> [ OWNER <ユーザ名> ] LOCATION '<ディレクトリ>';

CREATE TABLESPACE disk1 LOCATION '/disk1/pgdata';

データベースオブジェクトを作成する際にテーブルスペース名を指定すると、データベースオブジェクトを任意のテーブルスペースに作成できます。例えば、db1というデータベースをdisk1というテーブルスペースに作成するなら以下のSQL文になります。

CREATE DATABASE db1 TABLESPACE disk1;

すでに作成済みのデータベースオブジェクトを他のテーブルスペースに移動したい場合にはALTER文が使えます。例えば、すでに作成済みのtbl1というテーブルをdisk1というテーブルスペースに移動するなら、以下のSQL文になります。

ALTER TABLE tbl1 SET TABLESPACE disk1;

インデックスを移動する場合

ALTER INDEX tbl1_pkey SET TABLESPACE disk1;

データベースオブジェクトがどのテーブルスペースに存在しているかは次のSQL文で見ることができます。

SELECT
  relname, spcname
FROM
  pg_class c
  LEFT OUTER JOIN pg_tablespace t
  ON c.reltablespace = t.oid
;

spcnameが空の場合デフォルトテーブルスペース、"pg_global"はシステム用のテーブルスペースになります。

デフォルトテーブルスペースにはトランザクションログ他管理情報などさまざまなデータが格納されるので高いI/O負荷がかかっています。

複数台のハードディスクがある場合、デフォルトテーブルスペースにはできるだけデータベースを作成せず、増設したハードディスクにデータベースを作成して用いたほうが負荷分散されてパフォーマンスがアップする可能性があります。

by kurosawa