Go to Qiita Advent Calendar 2024 Top
LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@hadachi_db

PostgreSQL autovacuum のコストチューニング例

Last updated at Posted at 2024-10-07

はじめに

PostgreSQL の VACUUM(autovacuum) にはコストベースで負荷調整をするためのパラメータが多く用意されています。
この記事では想定される QPS をベースに autovacuum のコストパラメータを調整した事例を紹介します。

各種パラメータ自体の詳細な説明や考え方などはマニュアルや他記事を参照いただければと思います。

背景

ベースとなるマシンスペックやプラットフォームとして期待される最大 QPS をもとに autovacuum 負荷のデフォルト値を考慮する機会があったのでその内容を記事にしています。
また、デフォルトの autovacuum 系パラメータで autovacuum による io 負荷のスパイクが問題となったことがありましたので、その解消策としても本稿の内容を実施しています。

以下に留意いただければと思います。

  • 特定のワークロードにあわせたチューニングではない
  • 別途アラーティングや想定から外れた場合の運用も考慮する必要がある

前提：VACUUM(autovacuum) のコストベースチューニング

まず、前提となるロジックを簡単に記載しておきます。
VACUUM には "指定値まで io すると VACUUM を sleep させる" という仕組みがあります。
考え方と動作フローとしては以下の通りで、太字の部分がパラメータとしてチューニング可能となっています。

  • VACUUM で行う各 read/write 操作にコストを設定する。 (vacuum_cost_page_*)
  • VACUUM 実施時、それぞれの操作を行った際に↑で設定されているコストを加算する
    • ※これはインスタンス全体で global であることに注意が必要。
  • 加算されたコストが一定数になったらプロセスを sleep 状態にする。 (vacuum_cost_limit)
  • sleep 状態が一定時間過ぎたら VACUUM 操作を再開する (vacuum_cost_delay)

image.png

今回は基本的に autovacuum での sleep までのコスト上限である autovacuum_vacuum_cost_limit のみで調整することを考え、他のコスト系パラメータは固定にします。
autovacuum_vacuum_cost_limit を利用する理由は、使用 cost は autovacuum 操作の total で計算されるので、並列実行数が増減したとしてもホスト全体で流れる vacuum 負荷はほぼ変わらないはずで、「ホストでの使用リソースを目標値まで制限したい」という目的に沿っていると考えたためです。

autovacuum_vacuum_cost_limit は idle までのコスト許容量なので、増やせば増やすほど vacuum が稼働している時間が増加することになります。

autovacuum_vacuum_cost_limit autovacuum の負荷 時間当たりの処理できる deadtuple
増加させる ↑ ↑
減少させる ↓ ↓

調整にあたっての考え方

目標は autovacuum による負荷を常にホストリソースの一定以下に抑え、DB 処理への影響少なくしたい というところになります。

とはいいつつも、deadtuple の増加比率に autovacuum による回収が追い付かなければ肥大化による容量圧迫、scam パフォーマンス劣化につながりますので、autovacuum の回収速度もある程度確保する 事が必要になります。

上記のことから、以下を満たす autovacuum_vacuum_cost_limit 等のパラメータを考えていくことにします。

  • deadtuple の回収速度
    • 対象 DB で想定される最大時間当たり deadtuple 生成量を回収できること
    • 時間当たり update/delete の行数(難しければ暫定的に QPS とする)
  • autovacuum の db 処理への影響
    • deadtuple の回収速度要件を満たしつつホストリソースを何割かに制限する
      • CPU 使用率
      • io 帯域使用率

今回は想定される最大 QPS を deadtuple 生成量とざっくりあてはめ、5000 QPS(deadrows/s) とします。1
また、autovacuum に利用できるホストリソース は 20% までと考えます。

スパイクを避けつつも deadtuple の回収速度を担保したいため、autovacuum については稼働中ほぼ常に実行されていることを許容します。2

検証

目標が決まったので、まずは想定している HW spec においてどの程度 deadtuple を回収できるのかをまず確認します。

検証の前提

  • 1000 万行の表を 6 つ用意し、200 万行に update をかけて autovacuum する (並列 autovacuum 数は 6)
  • HOT 影響を無くすため変更列には index を作成しておく
  • キャッシュ影響を避ける(物理 io を発生させる) ためにキャッシュクリアする
  • limit 設定それぞれについて、autovacuum 時のログから 6 テーブル分の時間当たり deadrows 改修数を割り出す
limit=85 の場合の検証コマンド例 
alter system set autovacuum_naptime='5';
alter system set max_wal_size='100GB';
alter system set autovacuum_max_workers='6';
alter system set checkpoint_timeout='3h';
alter system set autovacuum_vacuum_cost_delay='2';
\! sudo systemctl restart postgres
\c

SELECT name, reset_val,setting, unit, short_desc
FROM pg_settings
WHERE name in ('autovacuum_naptime',
               'max_wal_size',
               'autovacuum_max_workers',
               'checkpoint_timeout',
               'autovacuum_vacuum_cost_limit',
               'autovacuum_vacuum_cost_delay');

psql
ALTER SYSTEM SET autovacuum_vacuum_cost_limit='85';

DROP TABLE table1;
DROP TABLE table2;
DROP TABLE table3;
DROP TABLE table4;
DROP TABLE table5;
DROP TABLE table6;
CREATE TABLE table1 (
  id SERIAL PRIMARY KEY,
  filler VARCHAR(32)
);
CREATE TABLE table2 (
  id SERIAL PRIMARY KEY,
  filler VARCHAR(32)
);
CREATE TABLE table3 (
  id SERIAL PRIMARY KEY,
  filler VARCHAR(32)
);
CREATE TABLE table4 (
  id SERIAL PRIMARY KEY,
  filler VARCHAR(32)
);
CREATE TABLE table5 (
  id SERIAL PRIMARY KEY,
  filler VARCHAR(32)
);
CREATE TABLE table6 (
  id SERIAL PRIMARY KEY,
  filler VARCHAR(32)
);
ALTER TABLE table1 SET (log_autovacuum_min_duration = 0,autovacuum_enabled = false);
INSERT INTO table1 (filler) SELECT md5(random()::text) FROM generate_series(1, 10000000);
CREATE INDEX ON table1 (filler);
UPDATE table1 SET filler = md5(random()::text) WHERE id <= (SELECT max(id) FROM table1) * 1 / 5;

ALTER TABLE table2 SET (log_autovacuum_min_duration = 0,autovacuum_enabled = false);
INSERT INTO table2 (filler) SELECT md5(random()::text) FROM generate_series(1, 10000000);
CREATE INDEX ON table2 (filler);
UPDATE table2 SET filler = md5(random()::text) WHERE id <= (SELECT max(id) FROM table2) * 1 / 5;

ALTER TABLE table3 SET (log_autovacuum_min_duration = 0,autovacuum_enabled = false);
INSERT INTO table3 (filler) SELECT md5(random()::text) FROM generate_series(1, 10000000);
CREATE INDEX ON table3 (filler);
UPDATE table3 SET filler = md5(random()::text) WHERE id <= (SELECT max(id) FROM table3) * 1 / 5;

ALTER TABLE table4 SET (log_autovacuum_min_duration = 0,autovacuum_enabled = false);
INSERT INTO table4 (filler) SELECT md5(random()::text) FROM generate_series(1, 10000000);
CREATE INDEX ON table4 (filler);
UPDATE table4 SET filler = md5(random()::text) WHERE id <= (SELECT max(id) FROM table4) * 1 / 5;

ALTER TABLE table5 SET (log_autovacuum_min_duration = 0,autovacuum_enabled = false);
INSERT INTO table5 (filler) SELECT md5(random()::text) FROM generate_series(1, 10000000);
CREATE INDEX ON table5 (filler);
UPDATE table5 SET filler = md5(random()::text) WHERE id <= (SELECT max(id) FROM table5) * 1 / 5;

ALTER TABLE table6 SET (log_autovacuum_min_duration = 0,autovacuum_enabled = false);
INSERT INTO table6 (filler) SELECT md5(random()::text) FROM generate_series(1, 10000000);
CREATE INDEX ON table6 (filler);
UPDATE table6 SET filler = md5(random()::text) WHERE id <= (SELECT max(id) FROM table6) * 1 / 5;

checkpoint;
\q

sudo systemctl restart postgres
exit
sudo sh -c 'echo 3 > /proc/sys/vm/drop_caches'
sudo su - postgres

psql
SELECT name, reset_val,setting, unit, short_desc
FROM pg_settings
WHERE name in ('autovacuum_naptime',
               'max_wal_size',
               'autovacuum_max_workers',
               'checkpoint_timeout',
               'autovacuum_vacuum_cost_limit',
               'autovacuum_vacuum_cost_delay');
ALTER TABLE table1 SET (autovacuum_enabled = true, toast.autovacuum_enabled = true);
ALTER TABLE table2 SET (autovacuum_enabled = true, toast.autovacuum_enabled = true);
ALTER TABLE table3 SET (autovacuum_enabled = true, toast.autovacuum_enabled = true);
ALTER TABLE table4 SET (autovacuum_enabled = true, toast.autovacuum_enabled = true);
ALTER TABLE table5 SET (autovacuum_enabled = true, toast.autovacuum_enabled = true);
ALTER TABLE table6 SET (autovacuum_enabled = true, toast.autovacuum_enabled = true);

deadtuple 回収速度の検証の結果

弊社 VM 環境 (PostgreSQL 15.4) にて検証した結果、以下のようになりました。

autovacuum_vacuum_cost_limit deadtuple 回収速度
200(default) 32,545 rows/s
85 13,644 rows/s
50 7,812 rows/s
35 6015 rows/s

autovacuum_vacuum_cost_limit = 35 でも目標の 5000 rows/s は(autovacuum がずっと実行されている前提であれば) 満たせることがわかりました。

割り出した cost_limit 値でのホストリソース使用量確認

次はこの値とデフォルト値でホストリソースの使用率を同様の検証で確認し、使用率目標(20%程度)を満たしているか確認します。
詳細な判断基準については弊社環境の Spec 情報が含まれるため省略しますが、以下のような結果になり、リソース制限についても目標値を満たしていました。(max 値)

resource default(limit=200) limit = 35
CPU使用率 1% 1%
write MiB/s 65 MiB/s 10 MiB/s
read MiB/s 30 MiB/s 6 MiB/s
ioutil% 35% 10%
deadrows 回収 32,545 rows/s 6015 rows/s

検証結果から、autovacuum_vacuum_cost_limit = 35 が要件を満たすパラメータであることが確認できました。別途こちらのパラメータでの長期運用検証も行っていますが、そちらについては割愛します。

おわりに

本稿では以下のフローで autovacuum のコストベースチューニングを行いました。

  • 目標となる deadrows 回収率(今回はグローバルな設定値を決めたかったため仮に最大 QPS とした)、ホストリソースの使用率を決定
  • 設定値を使用して以下を検証で確認
    • autovacuum_vacuum_cost_limit の効率確認(deadrows 回収率の確認)
    • 割り出した autovacuum_vacuum_cost_limit でのホストリソース使用率確認

目標 deadrows 回収率= 最大 QPS としていることや、テーブルを 1 index(HOT無効) のみで検証していることから特定のサービス向けのパラメータ設定ではありませんが、個別調整すればある程度の autovacuum チューニングの指針は示せるのではないかと考えています。

  1. 最大 QPS が全て 1 件更新の update または delete だと仮定した場合でも回収できるパラメータとしています。deadtuple の生成量が予めわかればそちらを使用した方が良いと思います。 ↩

  2. autovacuum のトリガータイミングの制御はまた別のパラメータ調整が必要ですが、ここでは割愛します ↩

1
0
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
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?