Heroku Postgres での VACUUM の管理

最終更新日 2022年11月29日(火)

データベースのバキューム
肥大化の確認
VACUUM のバリアント
自動バキュームを使用した自動的なバキューム
手動バキューム

Postgres では、MVCC と呼ばれるメカニズムを使用してデータベース内の変更を追跡します。副作用として、一部の行が “使用されない” 状態になり、実行中のどのトランザクションにも表示されなくなります。使用されなくなった行は DELETE 操作だけでなく、UPDATE や、ロールバックする必要があるトランザクションによっても生成されます。

これらの使用されなくなった行を消去するために、データベースには定期的なメンテナンスが必要です。これは基本的に、ガベージコレクションの形式になります。通常、このメンテナンスは自動的に実行されますが、その詳細を理解し、必要に応じてメンテナンス設定をチューニングすると有効な場合があります。

データベースのバキューム

このクリーンアップを管理するための組み込みのメカニズムは VACUUM と呼ばれます。これは通常のコマンドとして実行できますが、Postgres には、VACUUM プロセスをメンテナンスタスクとしてバックグラウンドで自動的に実行し、必要に応じて古いデータの定期的な消去を試みるための機能も含まれています。このプロセスは、一連の設定パラメータに基づいてメンテナンスを実行します。

多くのアプリケーションでは Heroku のデフォルト設定で十分ですが、状況によっては、いくつかの変更を行ったり、手動のアクションを実行したりすることが必要です。

肥大化の確認

VACUUM を実行する必要があるかどうかを確認するには、テーブルとインデックスの “肥大化” に関する情報を提供するクエリを実行できます。肥大化とは、使用されなくなった行のために、ディスク上のこれらのデータベースオブジェクトによって占有される余分な領域のことです。これを確認するための最も簡単な方法は、Heroku CLI の pg-extras プラグインのインストールです。

インストールしたら、次のコマンドを実行して肥大化を確認できます。

$ heroku pg:bloat DATABASE_URL --app sushi
 type  | schemaname |    object_name          | bloat |   waste
-------+------------+-------------------------+-------+-----------
 table | public     | users                   |   1.0 | 109 MB
 table | public     | logs                    |   1.0 | 47 MB
 index | public     | queue_classic_jobs_pkey |   3.1 | 25 MB
 table | public     | reviews                 |   2.2 | 16 MB
 table | public     | queue_classic_jobs      |  32.5 | 1512 kB
...

“bloat” 列は、肥大化として存在する元のテーブルの部分の係数である肥大化係数を示しています。これは比率であるため、単位はありません。"waste" 列は、システム内の各テーブルとインデックスの合計の肥大化 (バイト単位) を示しています。

Postgres では肥大化を考慮せずにクエリが計画されるため、テーブルまたはインデックスの肥大化係数が非常に大きいと、一部のクエリでパフォーマンスが低下する場合があります。

過剰な肥大化のしきい値はクエリパターンやテーブルのサイズによって異なります。特に 100 MB を超えるテーブルの場合、一般には、肥大化係数が 10 を超えるときは常に調査する必要があります。

データベースでのバキュームを確認するには、別の pg-extras コマンドを使用できます。

$ heroku pg:vacuum-stats DATABASE_URL --app sushi
 schema |         table      | last_vacuum | last_autovacuum  |    rowcount    | dead_rowcount  | autovacuum_threshold | expect_autovacuum
--------+--------------------+-------------+------------------+----------------+----------------+----------------------+-------------------
 public | queue_classic_jobs |             | 2013-05-20 16:54 |         82,617 |         36,056 |         16,573       | yes
 public | logs               |             | 2013-05-20 16:27 |              1 |             18 |             50       |
 public | reviews            |             | 2013-05-20 01:36 |             87 |              0 |             67       |
 public | users              |             | 2013-05-20 16:28 |              0 |             23 |             50       |
...

これにより、各テーブルが最後にバキュームされた日時と、それが手動のアクションまたは自動バキュームバックグラウンドワーカーのどちらで行われたかが通知されます。また、その特定のテーブルの自動バキュームをトリガーする使用されなくなった行のしきい値の行数と、自動バキュームの実行が予測されるかどうかも表示されます。

VACUUM のバリアント

肥大化は、VACUUM が確実に定期的に実行されるようにすることによって抑制し、抑制できなくなった場合は VACUUM FULL を実行することによって削減できます。自動バキュームプロセスでは、通常の、FULL でない VACUUM コマンドのみが実行されます。

VACUUM FULL はより徹底的なクリーンアップを実現し、(通常の VACUUM のように、その領域に使用可能のフラグを付けるだけではなく) 実際に肥大化を削減しますが、はるかに重量級の操作でもあります。VACUUM FULL は実際にテーブル全体を書き換えるため、他のどのステートメントも (単純な SELECT クエリでさえ) 並列に実行できなくなります。一般には、VACUUM FULL がまったく必要なくなるように、自動バキュームを十分に積極的な設定の状態に維持することをお勧めします。

テーブルが一時的なデータ (ワークキューなど) を追跡するためにのみ使用されているような場合は、代わりに TRUNCATE コマンドを実行すると役立ちます。このコマンドにより、テーブル内のすべてのデータがバッチ操作で削除されます。非常に肥大化したテーブルの場合は、これが DELETE や VACUUM FULL よりはるかに高速になる場合があります。

自動バキュームを使用した自動的なバキューム

肥大化を管理するための最も効果的な方法として、必要に応じて自動バキューム設定を調整します。

テーブルが VACUUM の対象である場合に変更を行うことができます。これは、次の 2 つの設定によって制御されます (Heroku では、この変更をテーブルごとにしか行うことができない)。

$ heroku pg:psql
=> ALTER TABLE users SET (autovacuum_vacuum_threshold = 50);
ALTER TABLE
=> ALTER TABLE users SET (autovacuum_vacuum_scale_factor = 0.2);
ALTER TABLE

このしきい値は、必要とされる使用されなくなった行の生の数であり、スケール係数は、使用されなくなった行として存在する必要があるテーブル内のライブ行の部分の係数です。これらのデフォルト値は 50 と 0.2 です。

これらの 2 つが一緒になり、次の数式に従って実際のしきい値 (上記の pg:vacuum-stats を参照) が構成されます。

vacuum threshold = autovacuum_vacuum_threshold +
    autovacuum_vacuum_scale_factor * number of rows

大きなテーブルでは、バキュームの進行をより早く開始できるようにするために、このスケール係数を減らす必要があります。非常に小さなテーブルの場合は、このしきい値を増やすことができます。ただし、これは一般には必要ありません。

さらに、自動バキュームには、システムが VACUUM アクティビティでいっぱいにならないようにするためのコストベースの速度制限メカニズムが組み込まれています。ただし、ビジー状態のデータベースでは、このメカニズムによって自動バキュームの進行が遅くなり、過剰な肥大化につながる場合があります。

それを回避するには、バックオフ設定をより積極的になるように変更できます。これらの変更は、データベースレベルで行うことができます。

$ heroku pg:psql
=> select current_database();
 current_database
------------------
 dd5ir2j6frrtr0
(1 row)
=> ALTER DATABASE dd5ir2j6frrtr0 SET vacuum_cost_limit = 300;
ALTER DATABASE
=> ALTER DATABASE dd5ir2j6frrtr0 SET vacuum_cost_page_dirty = 25;
ALTER DATABASE
=> ALTER DATABASE dd5ir2j6frrtr0 SET vacuum_cost_page_miss = 7;
ALTER DATABASE
=> ALTER DATABASE dd5ir2j6frrtr0 SET vacuum_cost_page_hit = 0;
ALTER DATABASE

コスト制限によって、自動バキュームが強制的に中断されるまでに獲得できる “コスト” の大きさ (I/O 操作の単位) が決定されます。また、コスト遅延によって、その中断の期間 (ミリ秒単位) が決定されます。これらの設定は、自動バキュームと手動バキュームの両方に影響を与えます (自動バキュームのみのバリアントが存在しますが、現時点で Heroku Postgres ではテーブルごとにしか設定できない)。コスト制限は、デフォルトでは 200 に設定されています。コスト制限を増やすか (最大 1000 程度)、または vacuum_cost_page_* パラメータを調整すると、自動バキュームのより効率的な進行に役立つ場合があります。

手動バキューム

データベースに定期性の高いワークロードが存在する場合は、ピークを外れた時間帯に単純なワーカープロセスを使用して VACUUM (ロックが問題にならない場合は VACUUM FULL でも可能) を “手動で” 実行し、それを Heroku Scheduler などのツールでトリガーすることがより効率的である場合があります。

手動の VACUUM には、いつ “開始される” かに関するしきい値はありません。常に、VACUUM コマンドを実行することによってトリガーされます。コストベースのバックオフも (自動バキュームと同様に) 適用されますが、これはデフォルトで無効になっています (vacuum_cost_delay は 0 に設定されている)。手動の VACUUM が通常のワークロードに与える影響が大きすぎることがわかった場合は、この値をテーブルごとに増やすことができます。

VACUUM を実行するには、目的のデータベースへの psql シェルを開き、次のコマンドを入力します。

$ heroku pg:psql
=> VACUUM;
WARNING:  skipping "pg_authid" --- only superuser can vacuum it
WARNING:  skipping "pg_database" --- only superuser can vacuum it
WARNING:  skipping "pg_tablespace" --- only superuser can vacuum it
WARNING:  skipping "pg_pltemplate" --- only superuser can vacuum it
WARNING:  skipping "pg_auth_members" --- only superuser can vacuum it
WARNING:  skipping "pg_shdepend" --- only superuser can vacuum it
WARNING:  skipping "pg_shdescription" --- only superuser can vacuum it
WARNING:  skipping "pg_db_role_setting" --- only superuser can vacuum it
VACUUM

表示される警告は予測されるものであり、無視できます。また、手動バキュームを必要とするテーブルが 1 つまたは 2 つだけの場合は、VACUUM を特定のテーブルに制限することもできます。

 $ heroku pg:psql
 => VACUUM users;
 VACUUM

VACUUM を実行する場合は、その進行状況に関するより詳細な情報を得るために VERBOSE キーワードを追加できます。

$ heroku pg:psql
d7lrq1eg4otc3i=> VACUUM VERBOSE;
INFO:  vacuuming "public.reviews"
INFO:  index "reviews_pkey" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "reviews_user_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "users": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
...
VACUUM

慎重に管理された自動バキューム設定により、手動バキュームが必要になることはめったにありませんが、その仕組みを理解しておくことが重要です。

Categories