PostgreSQLをチューニングする

「SAVACAN」担当のMKです。

本記事では、PostgreSQLのパフォーマンスチューニングについて解説します。
アクセス数やデータ量が多くないシステムではあまり効果を実感できませんが、大規模なシステムではパフォーマンスチューニングがシステムの安定稼働に大きく影響します。実際に、チューニングを行った事で処理効率が5倍に向上した事例もあります。

本記事では、主にメモリ割り当て関連のパフォーマンスチューニングに焦点を当てています。カーネル設定や、メモリ割り当て以外のPostgreSQL設定（例: セキュリティやリカバリ関連）は対象外とさせていただいておりますので、ご了承ください。

なお、誤ったチューニングはデータベースの処理遅延やダウンなど予期せぬトラブルを引き起こす可能性があります。PostgreSQLの持つ本来の性能を引き出すためにも、慎重に設定を進めていきましょう。

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

PostgreSQLのチューニングは以下のファイルにて行います。

postgresql.conf
PostgreSQLの実行設定ファイルです。どのような設定でPostgreSQLを実行するか設定します。

/etc/security/limits.d/20-nproc.conf
OS側の制限設定ファイルです。システムで同時に実行されるプロセス数（nproc）の上限を設定します。

/etc/sysctl.conf
Kernelの設定ファイルです。古いLinux環境ではこちらでセマフォを設定する必要がある場合があります。
新しいLinux環境では設定不要な場合が多い為、今回は設定ファイルのご紹介までとします。

パフォーマンスチューニングの設定例

パフォーマンスチューニングの設定例をご紹介します。
ここでは以下を前提条件として解説していきます。

postgresql.conf を以下のように設定します。

max_connections = 2000 			      # 同時接続数
max_prepared_transactions = 2000	　# 同時に準備できるトランザクションの最大数を同時接続数と同等で設定
shared_buffers = 4096MB 		      # テーブルやインデックスのデータをキャッシュする領域（推奨はメモリ搭載量の1/4）
work_mem = 6MB 				          # ソートやハッシュテーブル操作のために使用する。検索中心のシステムなら4MBもあれば十分。
maintenance_work_mem = 256MB 		  # バキューム処理で使用されるメモリ。256MB程度で十分だが多めに確保したい場合は512MBとする。
temp_buffers = 1024MB 			      # それぞれのデータベースセッションが使用する一時バッファの最大メモリ量。サイズの大きい一時テーブルを利用する場合は増やす。
effective_cache_size = 4096MB 		　# PostgreSQLが使用するバッファ領域、ディスクキャッシュとして利用可能なサイズの推定値。実メモリの1/4～1/2程度が推奨。
random_page_cost = 1.1			      # ランダムアクセスのコスト。デフォルトは4.0。HDDの場合は2.0～3.0、SSDの場合は1.1～2.0とすると高速化が見込める。

/etc/security/limits.d/20-nproc.conf の設定は以下になります。

postgres soft nproc 2048 # 実際に有効な制限
postgres hard nproc 2048 # 設定の上限（soft limitはhard limitの設定値以内で値を増加できます）

設定ファイルを保存すると自動で反映されますのでpostgresユーザーでプロセス数の上限を確認します。

[postgres@db1 ~]$ ulimit -u
2048
[postgres@db1 ~]$ 

これで設定が反映され同時接続数が2000あった場合でもプロセスが処理できる設定になりました。

PostgreSQLは[1コネクション1プロセス]のクライアント/サーバモデルを採用しており、同時接続数に応じてシステム上で同時に実行されるプロセス数（nproc）の上限を適切に設定する必要があります。

Linux環境での主だった設定は２のべき乗で設定する事が多いです。これはコンピュータが2進数を基盤に成り立っているためで、2のべき乗に従う方が効率的とされており、本設定例でもこれに沿って２のべき乗にて設定しています。必ずしも合わせなければならないものではありませんが、システム要件的に支障がなければ合わせることをオススメしています。

インデックス設定による性能向上

インデックスの役割とは？

データベースにおけるインデックスは、効率的なデータ検索を実現するための重要な仕組みです。
テーブルのカラムにインデックスを設定することで、対象データを高速に検出・抽出することが可能になります

一方、インデックスがない場合は、テーブル内のデータを先頭から順番に検索する必要があります。この際、データ量が多いほど検索に時間がかかるため、パフォーマンスに大きな影響を与えることがあります。
適切なインデックスの設計は、データベースの高速化に欠かせません。

設定方法

CREATE INDEX index名 ON テーブル名 (対象カラム);

インデックス活用時の注意点

インデックスは、対象カラムがWHERE句に含まれていないと効果がありません。そのため、インデックスを設定する際には、頻繁にクエリのWHERE句に利用されるカラムに設定すると効果的です。
しかし、固定長でないデータ型や曖昧検索など、条件によっては効果が得られない場合もあります。

また、インデックス設定はストレージ容量を消費しますし、テーブル更新時にはインデックスも合わせて更新されるため処理コストを増加させます。特に長大なデータが格納されたカラムのインデックス更新は遅延の原因にもなりますので、インデックスの設定は適切に行う必要があります。

PostgreSQLのduration情報

パフォーマンスチューニングではありませんが、postgresql.conf で duration を設定すると、完了までに指定時間を超過したクエリをログに出力させる事が出来ます。
この設定を利用する事で遅延の原因となるクエリを特定し、パフォーマンス改善のヒントを得ることができます。

例えば、以下のように設定します。

log_destination = 'syslog'
log_min_duration_statement = 1000

上記の例では、1秒（1000ミリ秒）以上かかったクエリがログに記録されます。
ここではログの出力をsyslog（/var/log/messages）としていますが運用方針に合わせて指定ファイルに出力することもできます。

重いクエリを解消して、快適なデータベースライフを送りましょう！

データベースの運用は、継続したメンテナンスが大切

データベースは、今動いているから問題ないというものではありません。蓄積されるデータ量、クエリ条件により、刻一刻と状況が変化します。仕様変更や利用者状況の影響によって負荷傾向が突然大きくが変わることもあります。

常に運用情報を収集・解析し、適切なチューニングを施す事が求められます。場合によっては、新バージョンへのアップデートなど、多角的な視点で対策を検討します。

スペース・アイでは、常に最新の技術情報を収集し、お客様に最適な運用提案を行う事を心がけています。
データベースの運用やチューニング方法でお悩みの際は、是非一度ご相談ください。