PostgreSQL Advent Calendar 2014 の 13日目です。
Advent Calendar を今年もやってみたいと思って、枠が空いていたので飛び込んでみました。
昨日は osapon さんの libpqxx を使ってみたでした。
概要
PostgreSQL のパフォーマンスチューニングは大きく下記に分かれます。
- システムチューニング
- SQL チューニング
ここでは Linux 上で動かしていることを前提に、それぞれ説明します。
システムチューニング
システムチューニングの概要
システムチューニングとは、OS または PostgreSQL の設定を変更することです。
それぞれ順に説明します。
OS チューニング
PostgreSQL では特にメモリ関連でOSパラメータを設定変更すると、高速化効果が得られます。
特に下記のカーネルパラメータに注意します。
- vm.dirty_background_ratio
- メモリ全体に対してこの割合を超えて dirty メモリが増えると、バックグラウンドで（低い優先順位で）ライトバック処理を開始します。
- vm.dirty_ratio
- メモリ全体に対してこの割合を超えて dirty メモリが増えると通常の優先順位でライトバック処理を開始します。
- vm.dirty_writeback_centisecs
- dirty データがライトバックされる間隔を 100分の1秒単位で指定します
- vm.zone_reclaim_mode
- 新しいLinux カーネルでは 0 と指定する。0 を指定することで NUMA アーキテクチャのマシンにおいて、zone reclaim 機能を無効化できるので、キャッシュが捨てられることを抑止できます
これらは
$ /sbin/sysctl vm.dirty_writeback_centisecs
などのコマンドによって現在の設定値を確認できます。
さらに PostgreSQL 9.3 未満では、kernel.shmmax、 kernel.shmall の OS パラメータを設定する必要がありました。今回は説明を割愛します。
ディスク、ファイルシステム
通常ファイルシステムでは、アクセスするたびに atime （最終アクセス日時）の更新処理を行います。 アクセス日時の更新処理は RDB 用途では無意味です。 noatime オプション付きでファイルシステムをマウントすることによって、atime の更新処理をスキップでき、高速化できます。/etc/fstab を編集し、 noatime と nodiratime 属性を付け、ファイル、ディレクトリの両方を atime 更新処理を無効化しましょう。
XFS を使う場合は、nobarrier, noatime, noexec, nodiratime を付与することで、高速化できます。
パラメータチューニングではないですが、RAID 0（ストライピング）の利用、SSD の利用などのハードウェア変更により高速化する方法もあります。
また、トランザクションログ(PostgreSQL では WAL と呼ぶ）をデータと別のファイルシステム上に配置することも有効です。ディスク IO を分散できます。通常 $PGDATA/pg_xlog ディレクトリですが、 initdb するときに -X オプションを付けて実行することで、指定したディレクトリに WAL ファイルを配置することができます。
postgresql.conf
postgresql.conf の設定変更としては、特に下記のパラメータの変更が効果的です。
- max_connections
- RDBMS への同時接続数です。WEB などのユーザが同時にアクセスすることが想定される環境であれば大きく設定し、バッチ処理など同時アクセス数が少ない環境では小さくします。大きく設定しすぎるとメモリ消費量が増えてしまいます。必要な同時接続数を想定してそれを満たす最小限の値とします。
- shared_buffers
- データベースが使用する共有メモリバッファの値を指定します。値が大きすぎると OS がバッファとして使える容量が減り、逆に性能が低下します。データベース専用サーバであれば総メモリの 25% 程度が良いとされています。
- effective_cache_size
- カーネルや PostgeSQL の共有バッファなど PostgreSQL が使用するバッファ領域、ディスクキャッシュとして利用可能な大きさの推定値です。この値はオプティマイザが利用するもので正確な値は必要ありません。この値を大きくするとオプティマイザがインデックスを利用した問合せプランを選択しやすくなります。総メモリの 50% 程度に設定します。
- work_mem
- ソートやテーブルのジョインなどのときに使われるメモリ量です。この値を大きくすると、テーブル結合処理などが高速化されます。この値はセッションごとに設定できます。メモリを要するセッションで都度 大きな値を設定することが望ましい、と言えます。１回の問合せあたりではなく、問合せの中のソート処理のたびに work_mem の値のメモリを消費します。ソート処理は ORDER BY、DISTINCT、マージ結合で行われます。１回の問合せで多数のテーブルを結合する場合 work_mem にソート処理の回数を乗じた分のメモリがその１回の問合せで必要になります。
- maintenance_work_mem
- vacuum 処理で使われるメモリ量です。最大で 256MB、512MB 程度で十分です。あまり大きくしても意味はありません
- checkpoint_segments
- トランザクションログ（WAL:Write Ahead Log）を 16MB サイズのWAL セグメントというファイルとして書き出すときに、チェックポイント処理も行う頻度。10 に設定すると 160MB のトランザクションログを書き出すたびにチェックポイントを行うことになります。チェックポイント処理は重いため、この値を大きくし頻度を減らすことで性能改善が見込めます。ただし大きくしすぎると今度はディスク容量を大量に必要とします。またデータベースのリカバリにも時間を要するようになります
- checkpoint_completion_target
- チェックポイントの書き出し処理を分散し、ゆっくり書き出すようにするときに使います。checkpoint_segments を大きい値とした上で checkpoint_completion_target を 0.9 に設定することで処理性能が改善できます。
- wal_buffers
- トランザクションログ(WAL) のためのバッファのサイズを指定します。以前は設定が必要でしたが、PostgreSQL 9.1 以降では自動調整されるため、デフォルト値のままで問題ありません。
- fsync
- off とすることでディスクへの書き込みが非同期化でき、高速化できます。fsync を off とするときは同時に full_page_writes も off にすることでページの一部のみの書き込みが許容されるようになり、さらなる高速化効果が得られます。ただし、代償に書き込み順の保証、クラッシュ時の復元可能性はなくなります。たいていの場合は、後述の synchronous_commit=off とすれば高速化効果としては十分で、fsync=off とする必要はありません。
- synchronous_commit
- off とすると同期コミットが無効化され、高速化できます。秒間トランザクション数を増やしたい場合は特に有益です。ただし、クラッシュ時の復元可能性は担保されるものの、データ損失のリスクはあります。synchronous_commit=off はセッション単位で指定できるので、性能計測の結果、意味がある場合に、該当箇所のみ synchronous_commit=off とするのが良いやり方です。
- random_page_cost
- デフォルト値は 4.0 ですが、高IOPS な RAID 構成の磁気ディスクを使う場合は 2.0 ～ 3.0 の値、 SSD の場合は 1.1 に設定することで高速化できます。この値を小さくすると、seq scan ではなく index scan が選択されやすくなる効果があります。random_page_cost の値もセッションごとに指定できます。なお、どうしても狙った実行計画にできないときは enable_*join パラメータを off と指定する方法もありますが、あまり推奨されないと思われます。
- effective_io_concurrency
- 通常は１度に１ブロックずつ取り出す処理を行いますが、efffective_io_concurrency を設定すると、データを先読みして複数ブロックを同時に取得することができます。efffective_io_concurrency の値はディスクドライブの数を指定すると良いようです。たとえば RAID 10 （ミラーリング＋ストライピング）で構成している場合は 4 と指定します。
SQL チューニング
下記のノウハウを使って、テーブル設計、SQL設計を見直すことで高速化が見込めます
- 大量の問合せを行う場合は１文ずつ適用するのではなく、トランザクションを利用してまとまった数でコミットすることにより高速化できます。
- アプリケーションサーバの負荷が高い場合は、ストアドプロシージャを利用することで、DBサーバに処理を移す方法もあります。
- テスト実行用のテーブル、マスタデータが別にありデータ損失が問題とならないテーブルであれば CREATE UNLOGGED TABLE でテーブルを作成すると WAL の処理をなくし高速化できます。
- また、他のセッションから見える必要のない一時テーブルであれば、 CREATE TEMPORARY TABLE とすると、同様に WAL の処理を省略できます
- 全件削除するときは DELETE ではなく TRUNCATE を使うと高速化できます
- 上記を発展させて、大量 DELETE を TRUNCATE で処理できるようにテーブルパーティショニングする方法もあります。
- テーブルパーティショニングすれば、テーブル継承、Constraint Exclusion 機能の活用による高速化も可能です。
- 不要なインデックスを削除すると、更新処理を高速化できます。
- 特に HOT(Heap Only Tuples) 更新の適用条件にうまくハマると劇的な高速化効果があります
- HOT 更新の機能はインデックスを更新する場合は動作しません。不要なインデックス削除により、インデックス更新をなくすことで高速化できます。
- HOT 更新が行われやすくするため、更新が多いテーブルでは FILLFACTOR の値を 90 とか 85 とか小さい値に設定します。
- レコード長が短くすることでデータ取得が高速化できます。
- 数値や日付・日時を text型で保管しないこと、固定長の char 型より text 型を使うこと、decimal より smallint 、integer 型などを使うことなどにより、レコード長を節約します
- 参照処理ではインデックスがうまく使われるようにすると高速化できます
- アプリケーションの特性をよく理解して、インデックスの要否を判断しましょう。
- 特にインデックスが使われている列だけを SELECT する処理であれば、Index Only Scan 機能により劇的な高速化が見込める可能性があります。不要な列は取得しないようにしましょう。
- 大量の INSERT が遅いときは、COPY にします
- COPY でも遅いときは pg_bulkload を使う方法もあります
- すべての index を drop してから copy 処理を行い、最後に create index するという裏技もあります。
- 更新が少なく、いつも同じ複雑な View を参照している場合は PostgreSQL 9.3 の目玉新機能マテリアライズドビューを使う案もあります。
- PostgreSQL 9.3 のマテリアライズドビューは更新時に元テーブルに強烈なロックをとるので使い方に注意が必要です。
- PostgreSQL 9.4 ではマテリアライズドビューの更新時のロックの問題は緩和されました。
- PostgreSQL の機能を勉強しましょう。特に下記の内容は高速化に有益です。プログラム／APサーバで処理することなく、SQL だけでかなり高度な処理を実現できます。
- 共通テーブル式（Common Table Expression）
- Window関数
- 再帰SQL
- 高度な関数（配列関数、文字列関数）
- 関数インデックス、部分インデックス
- explain analyze の読み方
注意点
最後に、いくつかチューニング上の注意点を書いておきます。
- PostgreSQL のデータは RAMディスク(tmpfsなど) に配置してはいけません。そもそもたいした高速化効果が得られませんし、突然のクラッシュなどがありえます。
- どうしても RAM ディスク上にデータを配置したいときは tablespace を RAM ディスク上に配置するのではなく RAM ディスク上で initdb します。
- 自分のアプリケーションを理解せずに闇雲に設定しないようにしましょう。どんな SQL が主に実行されているのか、どんなハードウェア上で動かしているのか理解した上で設定します。
- 自動バキュームを止めて手動バキュームに置き換えてはいけません。
- むしろ自動バキュームをより頻繁にするように設定しましょう。
- 自動バキューム関連の設定はテーブルごとに可能です。更新頻度が高いテーブルの自動バキュームの頻度を特に高くすることもできます。
- 自動バキュームを止めると、テーブルの肥大化、不正確な統計情報、Index Only Scan の効果の低下などにより、むしろ速度は低下します。
- トライ＆エラーは必須です。自分のアプリケーションで試行錯誤してみましょう。
- 常にもっとも新しい PostgreSQL のバージョンを使うことは性能向上の観点で非常に有益です。Minor リリースも含めて最新のものを利用しましょう。