こんにちは。
開発メンバのKです。
今回Linux環境でPostgreSQLのメモリチューニングのための設定変更をする機会があったので、変更までの手順をはじめからまとめてみました。
PostgreSQLを使ってはいるけれど、意外とメモリ周りの設定は「なんだか難しそう、よくわからない」そんな印象のために、デフォルト値のままだったりすることもあるかもしれません。
実際にやってみると変更自体はそれほど難しくないので、これを機に一度見直してみましょう。
メモリチューニングって難しそうだけど…
はじめの一歩は、サーバーのスペックやメモリに合わせて適切な設定値に設定することです。
では手順を見ていきます。
ステップ１：サーバーのスペックを把握する
ステップ２：PGTuneで適正な設定値を知る
ステップ３：postgresql.conf設定ファイルを編集する
ステップ４：PostgreSQLに反映させる
ステップ５：パラメータが変更されたことを確認する
設定するための適正値を知る方法
簡単にサーバーのスペックを基に適正値を求めるには「PGTune」というツールがあります。
左側にサーバーのスペックや使用目的などを入力し、ボタンを押すと入力した値を基に、右側に各パラメータ値が表示されます。
ステップ１：サーバーのスペックを把握する
PGTuneに入力するためにサーバーのスペックを調べましょう。
・DB version ⇒postgresqlのバージョン確認コマンド「psql –version」
・OS Type ⇒環境のOSを選択
・DB Type ⇒使用用途に合わせて選択
・Total Memory (RAM)⇒確認コマンド「cat /proc/meminfo | grep MemTotal」
・Number of CPUs ⇒確認コマンド「lscpu | grep CPU」の結果のCPU(s)の部分
・Number of Connections ⇒postgresql.confの「max_connectionsの値」
・Data Storage ⇒確認コマンド「lsblk -d -o name,rota」の結果1:HDD、0:SSD
[参考] lsblk -d -o name,rota
https://www.sublimelms.com/blog/How-to-detect-your-server-has-SSD-or-HDD-hard-disk
ステップ２：PGTuneで適正な設定値を知る
ステップ1で確認したサーバースペック等をPGTuneに入力して、Generateボタンを押します。
今回の結果はこんな値でした。（環境は手順説明用に用意したEC2インスタンスを使用しています）
ステップ３：postgresql.conf設定ファイルを編集する
PGTuneの結果から各パラメータの意味を理解したうえで、変更するパラメータを決めます。そしてpostgresql.confの該当するパラメータの値を変更します。
今回は、以下の項目を変更します。
shared_buffers = 256MB
effective_cache_size = 768MB
maintenance_work_mem = 64MB
wal_buffers = 7864kB
work_mem = 1310kB
min_wal_size = 1GB
max_wal_size = 4GB
ステップ４：PostgreSQLに反映させる
今回は、変更の反映に再起動が必要になるパラメータshared_buffers、wal_buffersを含むのでPostgreSQLを再起動します。
systemctl restart postgresql
パラメータによっては再起動が不要なパラメータもあります。postgresql.confを開いてコメントを見てみましょう。「(change requires restart)」と記載のあるパラメータは再起動が必要ですが、それ以外は再起動せずにリロードで反映させることができます。
変更対象に再起動が必要なパラメータを含まない場合は、リロードでOK！
データベースに接続して SELECT pg_reload_conf();
ステップ５：パラメータが変更されたことを確認する
最後に変更が反映されたことを確認します。
データベースに接続して SHOW shared_buffers;
のように確認したいパラメータを指定して実行します。
変更されていることが確認できました。
これで設定変更までは完了です。
この先は調整になると思うので、手始めに「ある程度の適正値の目安」と「変更の方法」が分かれば、次に進めやすいのではないでしょうか。
※「SHOW ALL;」や「SHOW shared_buffers;SHOW effective_cache_size;SHOW maintenance_work_mem;SHOW wal_buffers;SHOW work_mem;SHOW min_wal_size;SHOW max_wal_size;」でまとめて確認もあり
まとめ
今回、サーバーのスペックを基に適切なPostgreSQL設定値に変更する流れまでを書いてみました。
デフォルト値のままでなかなか手を付けられていなかった方も、まずはPGTuneで適正値を確認して、現在の設定値と比較してみることで、PostgreSQLメモリチューニングのための一つのきっかけになればと思います。
