Help us understand the problem. What is going on with this article?

Postgresql サーバを移行したらAUTO VACUUM が実行されず、データベースが肥大化した時の話

More than 3 years have passed since last update.

Postgresqlデータベースの引越しを検討されている方や、引越し後にデータベースの肥大化に悩まされている方の参考になればと。

What happened

Live環境のPostgresqlデータベースの引越しを行って数週間後、Postgresqlのdata volumeがこれまでにない異常なペースで消費されていることに気付く。

Drill down

  • record数の増加状況は移行前後で特に大きな違いはない。
  • Liveのデータベースを定期的にstaging環境にrestore (+ データのクレンジング) しているが、staging環境と比較しても明らかに大きなサイズになっている。stagingにrestoreすると15GB程度のテーブルが、liveでは100GB以上。。
  • 以上の2つから、vacuum周りに何か問題があるのでは?とあたりをつける。

Check auto-vacuum execution

select n_live_tup,n_dead_tup,schemaname, relname,last_vacuum,last_autovacuum from pg_stat_all_tables where n_dead_tup != 0;

このクエリの結果を見た所、データベース内で最もレコード数が多く、更新/削除も頻繁に行なわれているテーブル上で、AUTO VACUUMが実行されていないことを発見。(last_autovacuum がnullだった。)

Investigation why

  • AUTO VACUUMのtriggerは autovacuum_vacuum_thresholdautovacuum_vacuum_scale_factor というパラメータで制御されており、以下の条件を満たす時にAUTO VACUUMが実行される。
autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * NUMBER_OF_ROWS > Dead Tuple 

https://www.postgresql.org/docs/9.3/static/runtime-config-autovacuum.html

  • autovacuum_vacuum_threshold のdefaultは50autovacuum_vacuum_scale_factor のdefaultは0.2。 問題となっているテーブルには2億件以上のレコードが格納されており、ざっくりいうと、4000万件以上の更新/削除が発生しないと、AUTO VACUUMがtriggerされない状況になってしまっていた。

Fix

  • autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor は両方データベースの再起動無しにqueryで上書きできるので、以下のクエリを実行
begin;
ALTER TABLE TARGET_TABLE SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE TARGET_TABLE SET (autovacuum_vacuum_threshold = 100000);
commit;
  • しばらく放置し、AUTO VACUUMが実行されたことを確認!!

But...

ディスク容量の異常な増加はストップしたが、AUTO VACUUMは不要になった領域を再利用可能にするだけなので、Postgresqlとしてのディスクの利用容量は減らなかった。。

https://www.postgresql.org/docs/9.3/static/routine-vacuuming.html

The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system,

Take away

  • Postgresqlの引越しをするときは、autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor の設定を見直す。レコード数が多い、かつ、update/deleteが頻繁に行なわれているテーブルがある時は特に注意。
  • 引越し後、しばらくの間はディスクの利用状況、AUTO VACUUMの実行状況をチェックする。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした