116
118

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

PostgreSQLのvacuumについて調べたメモ

Last updated at Posted at 2017-12-06

vacuumとは: そもそも、なぜvacuumが必要なのか

公式ドキュメント: https://www.postgresql.jp/document/9.6/html/sql-vacuum.html

以下公式ドキュメントより引用

VACUUM は、不要タプルが使用する領域を回収します。 
PostgreSQLの通常動作では、削除されたタプルや更新によって不要となったタプルは、テーブルから物理的には削除されません。 
これらのタプルはVACUUMが完了するまで存在し続けます。 そのため、特に更新頻度が多いテーブルでは、VACUUMを定期的に実行する必要があります。

テーブルからレコードを削除する際、論理削除される。
VACUUMは、削除フラグの付いたレコードを物理削除、FULL付の場合は、さらにデフラグを行うようなイメージ。
そのため、頻繁に更新されるテーブルに対して行うと効果的。とのこと。

タプル(tuple)とは

複数個のデータ型で構成される、データの組のこと。らしい。
テーブルの列がデータ型、行が組。

参考: Wikipedia - 組(データベース)

vacuumコマンドとパラメータ

vacuum <options> <table_name>;

オプションなしでvacuumを実行した場合、権限のある全てのテーブルに対してvacuumを行う。

オプション 排他 速度 回復量 備考
なし 不要 削除フラグのついている領域を回収し、再利用可能とする。 回収した領域は、OSに殆ど返されない
FULL 必要 テーブルの内容全体を新しいディスクファイルに領域を余すことなく書き換えるため、OSに未使用の領域を返すことができる。 排他制御が必要になる上、遅い

ちなみに、自動バキュームなんてものもある

その名もずばり autovacuum 。自動バキュームを行う機構(デーモン)。
省略可能だが強く推奨、とのこと。

参考: https://www.postgresql.jp/document/9.6/html/routine-vacuuming.html

デフォルトで無効になっている環境も存在する模様
有効になっているかは、PostgreSQLの設定ファイル postgresql.conf を確認する。

$ sudo vim /var/lib/pgsql/data/postgresql.conf
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------

autovacuum = on                         # Enable autovacuum subprocess?  'on'
                                        # requires track_counts to also be on.
log_autovacuum_min_duration = -1        # -1 disables, 0 logs all actions and
                                        # their durations, > 0 logs only
                                        # actions running at least this number
                                        # of milliseconds.
autovacuum_max_workers = 3              # max number of autovacuum subprocesses
                                        # (change requires restart)
autovacuum_naptime = 1min               # time between autovacuum runs
autovacuum_vacuum_threshold = 50        # min number of row updates before
                                        # vacuum
autovacuum_analyze_threshold = 50       # min number of row updates before
                                        # analyze
autovacuum_vacuum_scale_factor = 0.2    # fraction of table size before vacuum
autovacuum_analyze_scale_factor = 0.1   # fraction of table size before analyze
autovacuum_freeze_max_age = 200000000   # maximum XID age before forced vacuum
                                        # (change requires restart)
autovacuum_vacuum_cost_delay = 20ms     # default vacuum cost delay for
                                        # autovacuum, in milliseconds;
                                        # -1 means use vacuum_cost_delay
autovacuum_vacuum_cost_limit = -1       # default vacuum cost limit for
                                        # autovacuum, -1 means use
                                        # vacuum_cost_limit

各パラメータの説明は公式ドキュメントにある。
参考: https://www.postgresql.jp/document/9.6/html/runtime-config-autovacuum.html

適切な設定値については調査中。

自動的にやってくれる分、手間は省けるが
巨大なテーブルに対して実行すると、vacuum処理に時間がかかりすぎることによって
表面のアプリケーション側に影響を及ぼすことがある。

vacuumすべきか否かの判断

"死んだ"タプルの割合が多くなってきたら頃合い。

参考: ritchiekotzen's blog - postgreSQL で不要領域の比率を出すクエリ

上記サイトのSQLを実行した際の出力例:

         relname          | n_live_tup | n_dead_tup | ratio  
--------------------------+------------+------------+--------
 follow_requests          |          0 |          7 | 100.00
 oauth_access_grants      |         24 |         18 |  42.00
 oauth_applications       |        177 |          0 |      0
 mentions                 |      55841 |        124 |   0.00
 notifications            |       1000 |        106 |   9.00
 conversations            |     445718 |        411 |   0.00
 web_settings             |         61 |         21 |  25.00
 blocks                   |         25 |          8 |  24.00
 web_push_subscriptions   |         34 |         34 |  50.00
 imports                  |          0 |          2 | 100.00
 schema_migrations        |        125 |          0 |      0
 statuses_tags            |      77548 |        334 |   0.00
 statuses                 |     454747 |      32883 |   6.00
 domain_blocks            |         19 |         51 |  72.00
 account_domain_blocks    |          1 |          2 |  66.00
 media_attachments        |      50458 |       3729 |   6.00
 status_pins              |          3 |          3 |  50.00
 preview_cards            |      11106 |        179 |   1.00
 preview_cards_statuses   |      15567 |        950 |   5.00
 tags                     |       7414 |          0 |      0
 oauth_access_tokens      |        297 |         70 |  19.00
 stream_entries           |       9662 |          0 |      0
 session_activations      |        167 |         78 |  31.00
--MORE--

結果の見方

  • relname は、リレーション名(テーブル名)を示す。
  • n_live_tup は、生きているタプルを示す。
  • n_dead_tup は、死んでいる(削除フラグの立った)タプルを示す。
  • ratio は、全タプルのうち死んでいるタプルの比率を計算して示す。

データが少ないようなので、あまり参考にならないかもしれないが
ある程度溜まってきたら(10%とか?)実行したほうがいいかもしれない。

あとがき

  • 自分用にメモした内容です。必要に応じて読み替えてください。
  • こうしたほうがいいよ的なアドバイスを頂けると大変助かります

以上

116
118
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
116
118

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?