PostgreSQLのパラメータチューニング

【この記事の所要時間 : 約 6 分

PostgreSQLのパラメータチューニングをやってみた。
参考にしたのは以下のサイト。
スタックアスタリスク – PostgreSQLパフォーマンスチューニング (魚拓)
All Aobut – PostgreSQLのチューニング (魚拓)
ThinkIT – PostgreSQLチューニング実践テクニック (魚拓)
行ったのは、以下のパラメータ
1.postgresql.conf
    ・shared_buffers (共有バッファ)
    ・sort_mem (ソートメモリ)
    ・wal_ buffers (トランザクションログバッファ)
    ・checkpoint_segments (チェックポイントセグメント)
    ・max_fsm_pages (FSM[Free Space Map]))
2.shmmax (カーネルの共有メモリ量)
    /proc/sys/kernel/shmmax
    /etc/sysctl.conf
今回は、キャッシュ機能を有効利用して、キャッシュヒット率を高めるのが大きな目的だったので、以下のサイトの手順によってパラメータに入れるべき数値を計算した。
Stray Penguin – パフォーマンスチューニング (魚拓)

メモリ関係パラメータは扱っているデータベースの大きさやクエリの性格と頻度、実装メモリの量などによって最適値が全く異なるので一概には言えないが、最適値を割り出すための黄金律はある。どうも世の中のドキュメントは断片的で、この作業を包括的に扱ったドキュメントがないようだったので、苦労してこれをまとめるに至った。

第1段階: 運用テーブルサイズの割り出し

テーブル全体で約 400,000KB

第2段階: shared_buffers の計算

shared_buffers = (テーブルサイズ + 512K) / バッファブロックサイズ
バッファ 1 ブロックのサイズは8 Kバイト
400,512/8 = 約 50,000

第3段階: カーネルの shmmax の計算

ceil( 250 K + (8.2 K * shared_buffers) + (14.2 K * max_connections) )
ceil( 250 K + (8.2 K * 50000) + (14.2 K * 32) ) = 410700 KB
= 401 MB
= 476472934 Byte

第4段階: カーネルの shmall の計算

$ ipcs -l
max total shared memory (kbytes) = 8388608
= 8192MB = 8GB

最終段階: キャッシュ効果の確認と値の調整

基本的には、1>2>3>4
1<2 となっているがこれでもいいみたい。
1.実装メモリ
     = 1GB×4 = 4 GB
2.システムの許す共有メモリの総量
     = shmall = 8 GB
3.カーネルの共有メモリ量
     = shmmax = 476472934 Byte = 401 MB
4.PostgreSQLの共有バッファ
     = shared_buffers = 30,000 ページ = 240 MB

ということで、最終的には以下のパラメータを利用。
1.postgresql.conf
    ・shared_buffers = 30000
    ・sort_mem = 4096
    ・wal_ buffers = 96
    ・checkpoint_segments = 16
    ・max_fsm_pages = 100000
2.shmmax (カーネルの共有メモリ量)
    /proc/sys/kernel/shmmax = 476472934
    /etc/sysctl.conf → 476472934
ということで、パラメータを設定後、PostgreSQLを再起動して、キャッシュが十分有効になっているかをディスクからの読み込みとキャッシュからの読み込みがそれぞれどれくらいあるかでチェックする!
事前に、パラメータ変更前のPostgreSQLの統計情報を取得しておき、統計カウンタの値をゼロにして、いくつかのクエリを流してから、以下のSQLで確認してみる。

SELECT relname AS TBL,heap_blks_read AS DSK,heap_blks_hit AS CASHE FROM pg_statio_user_tables;

変更前までは、ディスクからの読み込みとキャッシュからの読み込みが半々くらいだったが、パラメータ変更後は、テーブルによって若干の違いはあるが、キャッシュからの読み込みがほぼ95%以上となった。
うまく当初の目的は達成できた。
これもうまく情報を集めて、公開してくれたStray Penguinさんのおかげです。
ありがとうございました。
以下、参考までに。
http://canalize.jp/archives/008035.php

PostgreSQL完全機能リファレンス―実行例を通して「理解」を深める。
鈴木 啓修
秀和システム
売り上げランキング: 329,087
スポンサーリンク
レクタングル(大)広告
  • このエントリーをはてなブックマークに追加
スポンサーリンク
レクタングル(大)広告

コメントをどうぞ

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です