目的別ガイド：チューニング編
この記事では PostgreSQL のチューニングの概要を紹介します。まずは PostgreSQL のチューニングの勘所を掴んでください。各項目についての詳細はリンク先の記事で紹介していますので、そちらをご覧下さい。
チューニングの流れ
PostgreSQLのチューニングとして必要な作業には何があるのでしょうか？ システムの開発状況やボトルネックによって必要な作業は変わってきますが、およそ以下の事項が挙げられます。
- 1. 情報収集と分析
- チューニングを総当りで試すのは得策ではありません。まずは「ログ」や「稼動統計情報」を収集し、パフォーマンスのボトルネックを見つけることが先決です。特に良くあるトラブルは「トラブルシュートとチューニング」にも対処方法があります。
- 2. チューニングの実施
- 最も影響の大きなボトルネックから順にチューニングしていきます。 ボトルネックが解消されるまで、それ以外の要素をチューニングしても効果が見えにくいことに注意しましょう。
- 3. 繰り返し or 完了の判断
- あるボトルネックが解消されると他のボトルネックが表れることがあります。また情報収集から繰り返してチューニングを続けることになります。もし性能目標を達成できたら、そこでチューニングはひとまず完了としましょう。
以下では、チューニングすべき要素の候補について、概要を説明します。
ハードウェア構成の見直し
もし調達できるのであれば、ハードウェアの強化で乗り切るのも選択肢の一つです。ただし、データベースの用途によって、ハードウェアの構成やどの構成部品を強化すべきかは大きく変化します。
スケールアウト vs. スケールアップ
スケールアウトとスケールアップのどちらの方向でハードウェアを増強すべきかを考える必要があります。
- スケールアウト
- サーバマシンの台数を増やすことでクラスタ全体の性能を向上させます。 各マシンは、pgpool や Slony-I を利用し、レプリケーションを行ってデータを同期させます。 参照負荷の分散はできますが、更新性能は上がりません。
- スケールアップ
- 1台のサーバの構成部品の強化を行うことで性能を向上させます。 地域やユーザごとに完全に分割できるデータを除いて、更新性能の向上にはスケールアップが必要になるでしょう。
次に、構成成部のうち、「ストレージ」「メモリ」「CPU」それぞれで重視すべきケースを確認します。
ストレージを重視すべきケース
昨今はマシンに搭載できるメモリ量もずいぶんと増えてきましたが、データベースのサイズが 100GB を超えるような場合には、さすがに全データをメモリ上に保持することはコスト的に難しいでしょう (2009年現在)。データ量が多い場合には、高速なストレージを用意することが重要になります。また、高速なストレージと言っても、用途によっても変わります。
- OLAP (集計処理)
- シーケンシャルスキャンが主体になり、大量のデータが I/O バスを流れます。I/O コントローラの性能が重要になります。ディスクを増やせる場合には、参照主体なので RAID 5 構成が有効です。
- OLTP
- ランダムアクセスが主体になるため、シーク速度が速いディスクが優先されます。場合によっては SSD (Solid State Drive) も候補に上ります。ディスクを増やせる場合には、もし更新主体ならば RAID 1+0 構成が有効です。(RAID 5 は更新が遅い)
メモリ量を重視すべきケース
データをメモリに保持しきれるか、少し足りないか、という状況では、搭載メモリ量が性能に大きく影響します。ディスクとメモリの間には100倍以上の性能差があるため、できる限りメモリ上にデータを留めておきたいのです。最低でも、インデックスや頻繁にアクセスが発生するテーブルを乗せられるだけのメモリを用意したいところです。
CPU速度を重視すべきケース
処理すべきデータが完全にメモリに収まっている場合には CPU の速度の影響が強くなります。ここで、データ全体量が大きくても、実際に処理の対象になるデータ領域が絞られていれば、実質的に「データ量が少ない」とみなせることに注意しましょう。
また、PostgreSQL は比較的 CPU スケーラビリティの高い DBMS なので、多くのコアが搭載されたマシンの性能も引き出すことができます。
- Scaling PostgreSQL on SMP Architectures -- An Update (PGCon 2007)
アプリケーション要求の調整
開発の初期段階であれば、アプリケーションやサービスが無謀な要求をしていないかも確認しましょう。当然ながら、アプリケーションがどのようなアクセスをするかは、性能に最も大きな影響があります。以下の例は、どれも問題のあるアンチパターンです：
- × 歯抜けのない ID を振りたい
- スケーラビリティが得られない典型例です。ロールバックを考慮すると、歯抜けを無くすにはテーブルロックが必要になり、接続数が増えても性能が伸びません。せっかくシーケンスやserial型が用意されているのですから、歯抜けは許容し、それらでID生成をしましょう。
- × 正確な行数を表示したい
- 行数を表示するためだけに巨大なテーブルを count(*) するのは非効率的です。どうしても行数が必要で、かつフルスキャンを避けたい場合には、概算での表示やトリガでの行数トラッキングが必要になるでしょう。
スキーマ・チューニング
次に見るべきはスキーマ構成です。論理スキーマとデータの物理配置を含めて検討します。
テーブルの物理編成
まずは正規化が重要です。列数が多すぎるテーブルは無いでしょうか？ PostgreSQL では1行のサイズが 2KB を超えると、極端に性能が落ちる場合があります。文字列型の使い分けを始め、効率の良いデータ型を選ぶことも効果があります。
また、1テーブルのサイズが大きすぎるとキャッシュ効率が落ち、メンテナンスもしづらくなります。パーティショニングの「概要」や「使い方」の記事を参考に、テーブルを分割するのも良いでしょう。
データの並び順
必ず INSERT した順番でデータが配置されると誤解していませんか？ それを前提に性能設計すると、運用開始後しばらく経ってからトラブルが発生する恐れがあります。
空のテーブルに対しては概ね INSERT 順で並ぶのですが、UPDATE や DELETE をすると削除した領域を再利用するため、必ずしも INSERT 順では並びません。一緒に参照するデータ散らばってしまうと、処理に時間がかかります。適度に CLUSTER コマンドを発行したり、データの削除に DELETE ではなく TRUNCATE を使って断片化を防ぐ必要があります。
適切なインデックスを張る
WHERE句に頻繁に指定される列にはインデックスが必要です。逆に、利用されないインデックスは更新処理を遅くするだけなので、削除すべきです。
全文検索インデックスに関しては、「テキスト検索の方法とインデックス」も参考にしてください
更新処理でHOTを働かせる
特に更新処理が多い場合には、HOT 機能を有効活用するのが重要になります。HOT に関しては、以下の記事を参考にしてください。
パラメータ・チューニング
PostgreSQL には数多くのパラメータがありますが、チューニングの効果が大きいパラメータを厳選して紹介します：
接続数関連
max_connections は増やしすぎないようにしましょう（正確には、アクティブな接続数を増やし過ぎるのが問題です)。接続数を増やし過ぎないために、pgpool 等で接続を共有するのも有効です。
メモリ関連
shared_buffers, wal_buffers, work_mem, effective_cache_size をメモリ量に応じて設定します。最適値は用途に応じて変わりますが、shared_buffers は搭載メモリの 20% 程度が目安になります。
WAL関連
checkpoint_segments, checkpoint_completion_target を増やすことで、チェックポイント時に性能が不安定になる現象を避けられます。
クエリ・チューニング
SQLチューニング
SQL の書き方ひとつで性能は大きく変わります。また、当初は高速に処理できていたSQLも、データ量の増加や入れ替えなどの結果、非効率な処理になっている場合もあります。
クエリを確認する際、すべてのSQLをチェックするのは大変でしょうから、スロークエリに着目して 統計情報ビューやクエリログを「分析」し、EXPLAIN の出力を見ながらクエリの「改善」を行っておきましょう。
通信方式
SQL の発行の仕方にも注意しましょう。たとえ1回の処理に 10ms しかかからなくても、1画面の表示のために 3000回発行すると合計30秒もかかってしまいます。メモリ消費が許容できる範囲では、なるべく1回のSQLで複数のデータを処理するようにし、通信回数を減らしましょう。場合によっては、PL/pgSQL のようなストアド・プロシージャを使うのも有効かもしれません。
Prepared Statement
SQL は「1.クエリ文字列の解釈」「2.実行計画の生成」「3.データ取得」の順で処理されます。このうち 1.と2.の結果をキャッシュする機能が Prepared Statement (準備された文) です。特に比較的単純なSQLを数多く発行する場合に効果が高いチューニングです。使い方はプログラミング言語ごとで異なりますが、prepare() メソッドや PreparedStatement クラスのような名前になっていることが多いようです。
大量データ投入
データを大量に追加する場合には、通常のオンライン処理とは異なるノウハウが必要になります。「大量のデータを高速に投入するには」を参考にしてみてください。