postgresql performance tuningまずここを見るオンプレ編

Performanceが悪くてどこから手をつければいいんだろうというあなた。まずはlogを取得して分析しましょう。RDSのPerformance Insightsとか監視ツールが導入されていれば過去との比較ができたり便利そうだけど、なんにも導入されていないまずはオンプレを想定しています。logの見方だったり、分析の仕方はまたおいおい記事にするつもり。

pg_stat_statements

遅いSQLを特定する際に利用するパフォーマンスビュー

How to install

Windows に One Click Installer を使って PostgreSQL をインストールしている場合には、デフォルトで pg_stat_statments モジュールがインストールされている

設定

pg_stat_statments モジュールを使えるようにpostgresql.confに設定しDB再起動するだけです。
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

How to use

https://www.postgresql.jp/document/10/html/pgstatstatements.html
* 累積応答時間が遅いSQL Top10
-- total_exec_timeは累積ミリ秒
select * from pg_stat_statements order by total_exec_time desc limit 10

• 実行当たりの応答時間が遅いSQL Top10
  -- mean_exec_time = total_exec_time/call
  select * from pg_stat_statements order by mean_exec_time desc limit 10

• 統計値をいったんリセットする
  -- performance test直前に実行しておくと以前の統計値とごちゃまぜにならなくて便利
  select pg_stat_statements_reset()

  pg_stat_user_functions

  https://www.postgresql.jp/document/12/html/monitoring-stats.html
  function別に遅延状況を確認できます。
  このパフォーマンスビューはpg_stat_statementsと違ってinstallは必要ありません。なにもしなくても利用可能です。

  How to use

• 累積応答時間が遅いfunction Top10
  -- total_timeは累積ミリ秒
  select * from pg_stat_user_functions order by total_exec_time desc limit 10

• 実行当たりの応答時間が遅いfunction Top10
  select * from pg_stat_user_functions order by total_extime/calls desc limit 10

• self_timeが遅いfunction Top10
  /* total_timeが大きくてもself_timeが少ない場合があります。そういう場合はfunctionからcallしているfunctionが遅いことが考えられます。nestが多い場合は実際のslow functionを捕らえることが困難となります*/
  select * from pg_stat_user_functions order by self_time desc limit 10

• 統計値をいったんリセットする
  -- performance test直前に実行しておくと以前の統計値とごちゃまぜにならなくて便利
  select pg_stat_reset()

explain analyze

https://www.postgresql.jp/document/10/html/sql-explain.html
実行計画をSQL levelで確認する
actual timeが大きい箇所を見ることでtuningすべき箇所を特定可能
下記3rd party toolのinputとする場合はjson出力してください

auto_explain

https://www.postgresql.jp/document/10/html/auto-explain.html
毎回explain analyzeするのは面倒なので実行されたSQLの実行計画をpostgresql.logへ出力する方法です。

how to install

contrib packageを導入する必要があります。
https://tech-lab.sios.jp/archives/8865#auto_explain

設定

postgresql.confへ設定し、DB再起動することで有効になります。
#pg_stat_statementsと併用しない場合はこちら
#shared_preload_libraries = 'auto_explain'
shared_preload_libraries = 'pg_stat_statements,auto_explain'
logging_collector = on
#ここに設定する値以上の応答時間のSQL実行計画を取得する
#0にするとすべてのSQLの実行計画が取得される
#つまり大量のlog出力&アプリ遅延も発生
auto_explain.log_min_duration = 0
auto_explain.log_analyze = true
#functionが発行するSQLも取得する場合はtrueに設定
auto_explain.log_nested_statements=true

3rd party tool

• pgbadger
  Summalize & Visulaizer input postgresql.log
  Sample html output file
  http://pgbadger.darold.net/samplev7.html

  参考資料
  http://pgbadger.darold.net/
  https://www.slideshare.net/tkishimo/postgresql-119950899

  pgbadger利用時に設定するpostgresql.conf設定パラメタ例
  lc_messages = 'C'
  track_functions = all
  log_lock_waits = on
  log_temp_files = 0
  log_checkpoints = on
  #log_connections = on
  #log_disconnections = on
  log_min_duration_statement = 0
  log_line_prefix = '%t [%p]: [%l-1] user=%u, db=%d'
  shared_preload_libraries = 'pg_stat_statements,auto_explain'
  pg_stat_statements.max = 10000
  pg_stat_statements.track = all
  logging_collector = on
  auto_explain.log_min_duration = 0
  auto_explain.log_analyze = true
  auto_explain.log_nested_statements=true

• Parser explain plan
  Query Plan Visualizer input json explain plan
  http://tatiyants.com/postgres-query-plan-visualization/