PostgreSQLのトラブルシュートとチューニング

NTT オープンソースソフトウェアセンタ 笠原 辰仁

 

この記事は、gihyo.jp & Let's Postgres 連動企画「今こそ！PostgreSQL」の第6回記事です。第6回目は、PostgreSQLのエラーメッセージや内部情報を見て、発生している問題の特定とその対策となるチューニングを紹介します。なお、トラブルの発生・予兆を適切に捕捉するためにも、ログの設定や稼動統計情報の監視をしておきましょう。

エラーメッセージについて

トラブルと対策の前にエラーメッセージのレベルについて説明しておきます。PostgreSQLは複数のエラーレベルを影響範囲や深刻度によって使い分けています。エラーレベルそれぞれの解釈の仕方を下記の表にまとめてみました。

データベース管理者は、深刻な状況である PANIC と、性能情報を含む LOG レベルのメッセージに注意しましょう。一方、アプリケーション開発者は SQL やデータの異常が疑われる FATAL や ERROR レベルをチェックしましょう。

エラーレベル 説明
PANIC PostgreSQLのインスタンス全体に影響する事象が発生したことを示します。全ての接続は一旦切断され、PostgreSQLが停止します。
FATAL あるセッション内で問題が発生し、切断されたことを示します。問題のあったセッション以外には特に影響がありません。
ERROR あるトランザクション内で問題が発生し、アボート (ロールバック) されたことを示します。他のトランザクションやセッションには特に影響がありません。
WARNING 「警告 (WARNING)」の名前が付いていますが、実際にはあまり重要なメッセージには使われていません。
LOG データベース管理者が注目すべき、パフォーマンスや内部処理のエラーに関する情報が出力されます。

トラブルと対策

PostgreSQLの運用において、比較的遭遇しやすいトラブルとその代表的な対策を表にまとめました。PostgreSQLは比較的分かりやすいエラーメッセージを出力します。また、稼動統計情報を用いると問題の判別も明確になります。各種情報をこまめにチェックして、適切な運用・設定を行いましょう。

接続に関するトラブル

よくあるエラーメッセージ

エラーメッセージ FATAL: connection limit exceeded for non-superusers
原因 非スーパユーザによるサーバへの接続数が (max_connections - superuser_reserved_connections) を超過しています。
対策 max_connections を増やします。ただし、増やしすぎるとメモリ不足によるスワップが起きやすくなります。また、pgpool などのコネクションプールを導入し、実際の接続数を減らすのも有効です。
 
エラーメッセージ FATAL: sorry, too many clients already
原因 サーバへの接続数が max_connections を超過しています。
対策 前述のconnection limit exceeded～と同様の対策が有効です。なお、スーパーユーザでの処理の際にこのメッセージが出ているようであれば、superuser_reserved_connectionsを増やすことを検討して下さい。
 

チェックポイントに関するトラブル

よくあるエラーメッセージ

エラーメッセージ LOG: checkpoints are occurring too frequently
原因 チェックポイント処理が頻発しています。
対策 checkpoint_segmentsの値を増やしましょう。更新処理が多いケースでは32～64程度まで値を引き上げてください。データのロード時だけ出ているケースでは、WALをスキップするよう工夫するのも有効です。

性能に関するトラブル

性能に関するトラブルでありがちなのは、インデックスに関連するものです。明らかに性能が出ていない場合は、まず想定したインデックスが使われているか (=表スキャンになっていないか) を、システムビュー pg_stat_user_tables や pg_stat_user_indexes で確認しましょう。また、遅い SQL が特定できている場合には、EXPLAIN を使うとさらに詳しく調査できます。

よくある状況

状況など ロードが遅い
推測される原因 ロード対象のテーブルにインデックスやトリガがセットされたままになっている。
対策 データロード対象のテーブルのインデックスを削除 (DROP) しておきましょう。削除したインデックスはロード後に再作成します。また、ロード時に不要となるトリガも削除するか、無効化 (DISABLE TRIGGER) しておきましょう。
 
状況など インデックスが張ってあるのに検索が遅い
推測される原因 パラメータ設定やANALYZEが適切に行われていない。
対策 effective_cache_sizeがデフォルトのままであれば、物理メモリの50%程度まで引き上げましょう。併せて、random_page_costを3～2くらいまで引き下げてみましょう。ANALYZEのし忘れにも注意しましょう。
 
状況など 列に対する関数処理を含む検索が遅い
推測される原因 関数(式)の影響でインデックスが使用されていない。
対策 WHERE句で列に対して関数を適用しているようなケースでは、関数(式)インデックスを用いる必要があります。
ex) SELECT name FROM tbl WHERE upper(name) = 'ABC';
上記の様なSQLに対しては
CREATE INDEX name_upper_idx ON tbl (upper(name));
としたインデックスを作成しておきましょう。
 
状況など データ配置が断片化し、範囲検索が遅い
推測される原因 テーブルデータの物理編成が乱れている。この場合、検索対象のキーのcorrelationが0に近くなっている。※
対策 範囲検索時のキーとなるカラムをCLUSTERコマンドで最適化しましょう。物理編成を最適化することで、キャッシュヒットの向上が期待できます。

(※) 各列のcorrelationは、pg_statsシステムテーブルで以下の様に確認できます。列の物理編成が整然としている場合は1に近く、乱れているほど0に近くなります。

=# SELECT tablename, attname, correlation FROM pg_stats
   WHERE tablename='accounts';
 tablename | attname  | correlation
-----------+----------+-------------
 accounts  | aid      |    0.892117
 accounts  | bid      |    0.956601
 accounts  | abalance |    0.861149
 accounts  | filler   |           1
 (4 rows)
状況など 定期的に処理全般が遅くなり、特にI/O(read)の発生が目立つ
推測される原因 autovacuumが実施されている。この場合、pg_stat_activityやpsを見ると、サイズの大きなテーブルへautovacuumによるVACUUMが実施されている。
対策 サイズの大きいテーブルへautovacuumによるVACUUMが走り始めると、一時的に高いI/O負荷が発生します。システム性能に影響を及ぼしている場合は、autovacuum_vacuum_cost_limit=200程度に設定をし、atuovacuumを遅延させながら行うと良いでしょう。VACUUM時間は長くなりますが、I/O負荷が下がるため、システムが必要とするI/Oリソースを確保できます。
 
状況など 更新処理が遅い
推測される原因 WALの頻繁な書き込みがネックになっている。
対策 wal_buffersを4MB程度まで引き上げてみましょう。WAL書き込みの頻度を下げることができ、更新処理性能の向上が期待できます。ディスクがバッテリバックアップされている場合には、ライトバックキャッシュを有効にするのも大きな効果があります。また、トランザクション結果を少々失うことを許容できるならば synchronous_commit を off にすると性能が向上します。
 
状況など 負荷をかけてもCPU使用率もI/Oも少なく、全般的に処理が遅い
推測される原因 syslogに大量のログメッセージを出している。この場合、pg_stat_activityやpsを見ると、大部分のPostgreSQLプロセスが idle である。
対策 log_statement='all' や log_min_duration_statement=0 になっている場合は、設定を変更し、大量のSQL文がログに出力されるのを避けます。ログの出力を減らせない場合には、syslog への出力は低速なので、log_destination=syslog から stderr や csvlog に変更します。

その他

以下のメッセージは、LOGレベルなので軽視されがちですが、放置しておくと重大な問題に発展するおそれがあります。注意しましょう。

 

エラーメッセージ LOG: archive command failed with exit code (X)
原因 アーカイブコマンドが失敗しています。
対策 アーカイブコマンド(archive_command)が何らかの原因で失敗しています。主な原因はアーカイブ先ディレクトリの容量不足です。特に、VACUUM時に大量のアーカイブログが生成されることがあり、このような際はディスクフルになりやすいです。アーカイブコマンドが失敗すると、本来削除されるはずのpg_xlog配下のWALが残存し続けるため、放置しておくとPostgreSQLのサービス停止に至るので注意しましょう。

 

 

エラーメッセージ LOG: number of page slots needed (X) exceeds max_fsm_pages (Y)
原因 max_fsm_pagesが不足しています。
対策 バージョン 8.3 以前で、DB単位のVACUUM実施時に表示されるメッセージです。上記のメッセージに続いて、以下のHINTメッセージが出力されていると思います。
HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over (Z)
少なくとも、(Z)の値までmax_fsm_pagesを引き上げると共に、VACUUM FULL か CLUSTER でテーブルを再編成する必要があります。
このメッセージが出力されていた場合、テーブル中のガベージは回収したものの、その領域を再利用するための管理用メモリが不足しています。慢性的にこの上記のメッセージが出力される場合、テーブルがスカスカになり、データベースが肥大化します。

 

今回はPostgreSQLの運用において比較的遭遇しやすいトラブルとその対策を解説しました。上記のトラブルは運用初期に出やすいものだと思います。幸先の良い運用を行うための参考になれば幸いです。

(2009年10月30日 公開)