Location via proxy:   [ UP ]  
[Report a bug]   [Manage cookies]                

PostgreSQL チューニング方法

tech


　色々と調べてみて一番分かりやすいサイトを参照して、PostgreSQLのチューニングを行った。
　メインとなるのはメモリの割り当て調整。
　DBシステムはどれだけメモリキャッシュから処理ができるかがポイントとなるので、
　一番大変なshared_buffersのチューニング手順について明記する。


　※ちなみに参考したサイトはこちらになります。
Stray Penguin - Linux Memo (PostgreSQL-5)


1.運用テーブルの容量算出


　まずは自分が使用しているDBユーザのIDを調べる。
　
※以下のコマンドで#はSQLコマンド、$Linuxコマンドラインを表す。

#\c life_pv
#SELECT usesysid FROM pg_user WHERE usename='postgres';


　調べたIDで保持しているテーブルが使用しているページ数を取得。
　ページ数は通常8KBなので、8をかける。
　ここからの数値計算はpostgreSQLのSQLコマンドを使用して行う。


#SELECT sum(relpages)*8 FROM pg_class WHERE relowner=10;
61496(KB)


2.shared_buffrersの計算


　1.で求めたテーブルサイズからそれらを収めるのに必要な値を求める。目安は、
　shared_buffers = (テーブルサイズ + 512K) / バッファブロックサイズ　なので算出する。
　ブロックサイズは通常8KBなので、ページ数を再度算出。


#Select (61496 + 512) / 8;
7751(ページ)


　上記計算だと最低限として、shared_buffers = 7751　必要となる。（今後を考えて8000くらいかな)
　今後のデータ容量も考えて余裕を持った数値とする。


3.カーネルのshmmaxの計算


　Postgresで必要な共有メモリ量が算出できたので、カーネルからの割り当てを調整する。
　カーネルの共有メモリ量を決める主なパラメータは、「shmmax」「shmall」となり、
　/proc/sys/kernel/shmmaxとshmallを操作する。
　現在、共有メモリに割り当てられている値を確認する方法は下記。(多分rootで実行する必要有)


$cat /proc/sys/kernel/shmmax
419430400(バイト)


　デフォルトでは32MBとなっている。上記の計算から求めた値からすると61MB程度なので足りない。
　そこで共有バッファの割り当てについて調整をする。
　shmmaxの計算式は下記の通り。
　ceil(250K + (8.2K * shared_buffers) + (14.2K * max_connections))
　max_connectionsを300と想定して、これをPostgresで計算する。


#select ceil(250 + 8.2 * 8000 + 14.2 * 300);
70110(KB) = 69(MB)


　これが共有メモリとして割り当てしてほしい容量となる。
　ではこれからshmmaxの設定をする。
　これからの計算はbashを使用して行う。(切り良くするために、16MBの倍数に調整する。)


$echo $((70110 / 1024))
68(MB) → 80(MB)
$echo $((80 * 1024 * 1024))
83886080
$echo 83886080 > /proc/sys/kernel/shmmax


　shmmaxの設定は以上で完了。


4.カーネルのshmallの計算


　shmallはシステムが許す共有メモリの総量で、先ほどのshmmaxより同じかそれ以上になっていないといけない。
　ただし、shmallがバイト数で明記されているか、ページ数で明記されているかが環境によって異なる(多くのカーネルはページ数指定となっている)
　バイト数で調べる方法は下記の通り。


$ipcs -l
max total shared memory (kbytes) = 8388608
$echo $((8388608 / 1024))
8192(MB)


　8GBも割り当てられているので、データの修正は不要。もしメモリが足りないようであれば、この値を変更する。
　仮に16GBに変更するとした場合。


$echo $((16384 * 1024 * 1024))
171719869184
$echo 171719869184 > /proc/sys/kernel/shmall


　
　尚、上記コマンドではOS再起動時に初期化されてしまうので、下記の記述を/etc/sysctl.confに明記する。


kernel.shmmax = 83886080


5.設定結果の確認


　shared_buffersの値が適切かどうか測定をする。
　まずは統計情報を収集する機能を有効にする必要があるので、postgresql.confの下記に値を修正。


stats_start_collector = on
stats_block_level = on
stats_row_level = on


　いずれの値もオーバーヘッドが発生するのチューニング完了後はOFFにする。
　（stats_row_levelはautovacuumで使用する場合はONにしておく)
　書き換えが隆昌したら、設定を有効にする。


$pg_ctl reload


　統計情報がとのように取られているか確認する。
　psqlでコマンドを実行して、どの程度キャッシュからヒットしているか確認する。


postgres=# select * from pg_stat_database;
datid | datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit
-------+-------------+-------------+-------------+---------------+-----------+----------
10793 | postgres | 1 | 32435 | 3 | 82 | 364
66165 | test | 0 | 24733491 | 2883 | 7815 | 117367


　blks_readがディスクから読み込んだブロック数、blks_hitはキャッシュから読み込んだブロック数を表す。
　つまりblks_hitの値が大きいほど、キャッシュから読み込んでいることになる。
　上記の例だとblks_hitの値が大きいことが確認できるので、チューニングとしては上々。
　もしblks_readの値が増えていくようであれば、shared_buffersの値が不足していることになる。
　再度shared_buffersの調整、shmmaxの調整をし、最適な値を求める。


なかなか勉強になりました。
※尚、このチューニング方法が100％正しいとはいえないのでご注意を。