PostgreSQLのロングトランザクションが引き起こす問題と対処

ロングトランザクションが引き起こす問題についてのメモ

ロングトランザクションが引き起こす問題

• スレーブ側のトランザクションが閉じられないとWALが適用されずレプリケーション遅延を引き起こす
• VACUUMによる不要領域の回収が阻害される
  • DBサイズが肥大化する（一度肥大化するとVACUUM FULLでしか戻せない)
    • pg_repackで対応可 (https://github.com/reorg/pg_repack)
  • 性能劣化に繋がる
  • slave側でhost_standby_feedbackが指定されている場合、slave側のトランザクションでmasterもvacuum出来なくなる
  • 長時間続く場合はXID周回エラーの可能性もある

ロングトランザクション解決

ロングトランザクションの確認
=# select * from pg_stat_activity where 適当な条件;

terminate
=# select pg_terminate_backend(<pid>);

参考: https://qiita.com/yteraoka/items/90f61beb1ade1b002a54

根本対策

idle_in_transaction_session_timeout ...設定しよう！ (9.6+)

監視方法

• pg_statsinfo
• postgres_exporter + Grafana (https://github.com/wrouesnel/postgres_exporter)
  • これが簡単
  • pg_stat_activity_max_tx_duration をグラフ化して、閾値超過したらアラート飛ぶとかにしておく
• pg_stat_activity見るスクリプトを書く