釣りキチプログラマー翔平の備忘録 
はじめに
この記事では
PostgreSQLのパラメータチューニングの例をまとめました。
パラメータチューニングの例
パラメータチューニングでは、
発生した事象に合わせてpostgresql.confファイルのパラメータを調整します。
典型的なパラメータチューニング例を見ていきましょう！
共有バッファ(shared_buffers)のチューニング
共有バッファにはテーブルファイルやインデックスファイルがキャッシュされる領域です。
共有バッファのおかげで低速なディスクI /Oを減らし、
できる限り高速にメモリ上で読み書き可能になります。
共有バッファのサイズが小さすぎると、
利用されていないページが追い出されるため
ディスクI/Oが発生して処理性能が落ちます。
逆に共有バッファが大きすぎると、
共有バッファ内で目的のページを探すのに時間がかかったり、
チェックポイントでディスクに書き出す際の負荷が高まります。
共有バッファから追い出されても、
直後であればOSのディスクキャッシュに残っていて
高速に読み込める可能性が高いことも共有バッファを
大きくしすぎない理由の1つとして挙げられます。
共有バッファのサイズはデフォルトで128MBと小さいため変更が推奨され、
メモリ1GB以上搭載したデータベースサーバではその25%を設定します。
-- 共有バッファサイズ確認
SHOW shared_buffers;
-- postgresql.conf変更
shared_buffers = 512MB
-- PostgreSQL再起動
pg_ctl restart -D /path/to/your/postgresql/data/directory共有バッファについてはこちらの記事(ライタの箇所)をご覧ください。
内部で動くプロセスの役割を知る〜PostgreSQLのきほん〜
作業メモリ(work_mem)のチューニング
先ほどの共有バッファを大きくしても、
PostgreSQLにクライアントツールなどで接続して、
巨大なテーブルにソートやハッシュ操作をするようなクエリを発行すると
ディスクI/Oが発生して性能が思うように出ない場合があります。
その場合は作業メモリのチューニングが必要です。
作業メモリを大きくすると、ソートやハッシュ操作がメモリ上だけで行えるため、
クエリの性能を向上させられる一方で、
PostgreSQLに繋ぐクライアント毎に確保されるため、
作業メモリを大きくしすぎると、作業メモリ×クライアント分メモリが必要になります。
もし、
クライアントが同時に巨大テーブルにソートをかけるクエリを発行して、
物理メモリよりも大きなメモリ領域が必要になると、
スワップ領域を利用するためディスクI/Oが発生して性能は低下します。
-- 作業メモリサイズ確認
SHOW work_mem;
-- postgresql.conf変更
work_mem = 64MB
-- postgresql.confのリロード
pg_ctl reload作業メモリについてはこちらの記事(バックエンドプロセスの箇所)をご覧ください。
内部で動くプロセスの役割を知る〜PostgreSQLのきほん〜
メンテナンス用作業メモリ(maintenance_work_mem)のチューニング
VACCUM、CREATE INDEX、ALTER TABLEなどのコマンドを
クライアントツールから接続して発行して
メンテナンス操作を行う際、メンテナンス用作業メモリを利用します。
メンテナンス操作時に一時的に大きな値を設定することで、
メンテナンスを高速にできます。
ただし、デフォルト設定では自動バキュームでもメンテナンス用作業メモリを確保するため、
自動バキュームの同時実行数 × メンテナンス用作業メモリ
のメモリを消費してしまうことになることに注意が必要です。
(autovacuum_work_memのデフォルト値が-1で、メンテナンス用作業メモリの設定を参照するため)
メンテナンス用作業メモリを変更する際は
先にautovacuum_work_memを設定しましょう！！
-- 作業メモリサイズ確認
SHOW maintenance_work_mem;
-- postgresql.conf変更
maintenance_work_mem = 128MB
-- postgresql.confのリロード
pg_ctl reloadメンテナンス用作業メモリについてはこちらの記事(バックエンドプロセスの箇所)をご覧ください。
内部で動くプロセスの役割を知る〜PostgreSQLのきほん〜
バックグラウンドライタのディスクI/Oに関するチューニング
バックグラウンドライタがディスクI/O要求を大量に実施すると、
問い合わせ性能が落ちます。
例えばある瞬間に大量の更新が発生する場合には、
書き込みを少し遅延させて、ディスクI/Oの負荷を平準化することも重要です。
ただし、
テーブルデータのディスク書き込みはチェックポイント処理によって
強制的に発生する場合もあるため、
関連するパラメータや更新頻度、更新量、瞬間的な書き込み要求など
総合的な判断して調整する必要があります。
| パラメータ名 | デフォルト値 | 説明 |
|---|---|---|
| bgwriter_delay | 200ms | バックグラウンドライタの動作周期 |
| bgwriter_lru_maxpages | 100 | 一度にばックグラウンドライタが書き込むページ数上限 |
| bgwriter_lru_multiplier | 2.0 | 書き込みが必要になったページのうち、どのくらいの割合を書き込むかの 計算に利用(直近の書き込み量の2倍までしか書き込まないなど) |
バックグラウンドライタプロセスについてはこちらの記事(ライタの箇所)をご覧ください。
内部で動くプロセスの役割を知る〜PostgreSQLのきほん〜
WALライタのディスクI/Oに関するチューニング
WALライタもバックグラウンドライタと同じように
ディスクI/O要求を大量に実施すると、
問い合わせ性能が落ちます。
max_wal_sizeの上限かcheckpoint_timeoutの上限に達すると
チェックポイントが発生し、
共有バッファ上のダーティバッファが全てディスクに書き込まれます。
その間はI/O負荷が高くなり、
問い合わせ性能は落ちるので、
パラメータのチューニングが必要です。
checkpoint_warningの閾値であるチェックポイント間隔が30s以下になると、
ログメッセージが出力されるので、
チェックポイントが多発するようならmax_wal_sizeをチューニングしましょう。
また、トランザクションログは自動的に再利用されるので、
min_wal_size、とmax_wal_sizeを揃えておくのが推奨設定です。
(WALファイル全てを常に再利用可能の状態にするので
不意のディスク枯渇などのトラブルを防げる)
チェックポイントの間隔を伸ばすと、
PostgreSQLがクラッシュした際のリカバリにかかる時間が伸びるため、
リカバリ時間も考慮したチューニングが必要です。
| パラメータ名 | デフォルト値 | 説明 |
|---|---|---|
| walwriter_delay | 200ms | WALライタの動作周期 |
| max_wal_size | 1GB | チェックポイントの間にWALが増加する最大サイズ (このサイズに到達するとチェックポイント処理が実行) |
| min_wal_size | 80MB | リサイクル対象となる古いWALファイルのサイズ (リサイクル可能状態なのが80MBで維持される) |
| checkpoint_timeout | 5min | チェックポイントの間隔 |
| checkpoint_completion_target | 0.9 | インターバルの9割の時間を利用してちょっとずつ ディスク書き込みをする |
WALライタプロセスやチェックポイントについては
こちらの記事(WALライタ、チェックポインタの箇所)をご覧ください。
内部で動くプロセスの役割を知る〜PostgreSQLのきほん〜
列統計情報の精度に関するチューニング
統計情報はサンプリングによって求めているため、
レコードが多い場合やデータのバリエーションが多い場合に
データの出現に偏りがあると精度が悪くなります。
統計情報の精度が悪くなると、
実行計画の精度も悪くなり、
最適なデータアクセスが行えず、
問い合わせに余計な時間がかかることがあります。
統計情報のサンプリング数を増やして精度を上げることは可能ですが、
統計情報作成に時間がかかるようになるので注意が必要です。
-- 特定のテーブルの特定の列の統計情報のサンプリング数を100に変更
ALTER TABLE テーブル名 ALTER COLUMN カラム名 SET STATISTICS 100;
-- 特定のテーブルの特定の列の統計情報再生成
ANALYZE テーブル名 (カラム名);統計情報や実行計画に関しては
こちらの記事をご覧ください。
実行計画を取得して解析する〜PostgreSQLのきほん〜
今回の内容は以上です。
現場で役立つデータベースの基礎知識という
まとめ記事を作ったので是非ご覧ください。
初めてデータベースを触る方に向けて〜新人プログラマー時代の自分に伝えたいこと〜
参考文献
第10章 サーバ設定
［改訂3版］内部構造から学ぶPostgreSQL―設計・運用計画の鉄則
第8章 パフォーマンスチューニング
［改訂3版］内部構造から学ぶPostgreSQL―設計・運用計画の鉄則
