postgresqlクラスタ全体の参照クエリが重くなった話し

レプリケーション構成中のpostgresqlマスタ、スレープ全台のある特定の参照クエリが重くなった話し

• 重いのは特定のクエリのみ
• indexは正しく使われており、実行計画上重いものではなさそう
• なのに速度が出ない
• 1プロセスあたりのcpu使用率が高い。サチるような処理ではないはず。
• 昨日の状況と比べてみてもcpu使用率が異常値となっている

解決編

• テーブルごとのデッドタプルを確認

select n_dead_tup from pg_stat_user_tables ;

• 巨大なデッドタプルを持つテーブルを確認
• autovacuumしてしているんだけどな
• autovacuum設定値はデフォルトのautovacuum_vacuum_scale_factor=0.2 (20%)
• 結論としてはこれがまずかった。
• 1,000万レコードあるテーブルでは200万デッドタプルがautovacuumのトリガとなる
• ユーザ操作上、大量のデータを更新できる機能があり、一気に80万ほどの更新が走り巨大なデッドタプルが生まれたが、20%の閾値以下なので、autovacuumが走らなかった
• マスタ側DBで対象のテーブルを手動vacuumすることで対応

ある程度の規模のテーブルになるとautovacuum_vacuum_scale_factor=0.2が最適な閾値とはならない可能性が出てくるので、autovacuum運用する時はレコードサイズに注意しておこう。という話し。