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/