この記事は公開されてから半年以上経過しています。情報が古い可能性がありますので、ご注意ください。

目的

特定時間にサイトパフォーマンスが落ちてしまう事を防止したい

実際にはサイトレスポンスが異様に遅い事がありログを見たところ
autovacuumが実施されていたケースが多いかと思います。

環境

EC2インスタンスより pgbenchを実行
RDS PostgreSQL

設定項目	値
DBインスタンス	pgsql-test.xxxxx.ap-northeast-1.rds.amazonaws.com
DB	testdb
ユーザ	pguser

こちらのエントリでは、下記を実施します。
1. autovacuum が実施される状態を作る (pgbench)
2. vacuum 及び autovacuum が実施された最終時刻を確認
3. 特定テーブルの autovacuum を抑制

autovacuumを停止した後は必ずメンテナンススクリプト等で都合の良い時間帯で vacuumを実施するようにしましょう

準備

[root@ip-xxxxxx ~]$ /usr/local/pgsql/bin/pgbench -i -s10 -U pguser -h pgsql-test.xxxxx.ap-northeast-1.rds.amazonaws.com testdb
Password:
creating tables...
100000 of 1000000 tuples (10%) done (elapsed 0.38 s, remaining 3.42 s).
200000 of 1000000 tuples (20%) done (elapsed 1.26 s, remaining 5.05 s).
300000 of 1000000 tuples (30%) done (elapsed 3.03 s, remaining 7.07 s).
400000 of 1000000 tuples (40%) done (elapsed 5.37 s, remaining 8.06 s).
500000 of 1000000 tuples (50%) done (elapsed 5.80 s, remaining 5.80 s).
600000 of 1000000 tuples (60%) done (elapsed 7.75 s, remaining 5.17 s).
700000 of 1000000 tuples (70%) done (elapsed 10.01 s, remaining 4.29 s).
800000 of 1000000 tuples (80%) done (elapsed 10.43 s, remaining 2.61 s).
900000 of 1000000 tuples (90%) done (elapsed 12.64 s, remaining 1.40 s).
1000000 of 1000000 tuples (100%) done (elapsed 13.08 s, remaining 0.00 s).
vacuum...
set primary keys...
done.

testdb=> select relname, seq_scan, seq_tup_read, idx_scan, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum from pg_stat_all_tables where relname like 'pgbench%';
     relname      | seq_scan | seq_tup_read | idx_scan | n_live_tup | n_dead_tup |          last_vacuum          |        last_autovacuum
------------------+----------+--------------+----------+------------+------------+-------------------------------+-------------------------------
 pgbench_branches |    71390 |       539310 |        0 |         10 |        164 | 2014-07-10 08:57:15.398869+00 | 2014-07-10 05:02:05.198489+00
 pgbench_tellers  |    56532 |      5392200 |        0 |        100 |          0 | 2014-07-10 08:57:15.405037+00 | 2014-07-10 08:58:14.467415+00
 pgbench_history  |        0 |            0 |          |      10000 |          0 | 2014-07-10 02:48:56.913997+00 |
 pgbench_accounts |        1 |      1000000 |   107843 |    1000000 |      25601 | 2014-07-10 02:48:56.686276+00 |
(4 rows)

[root@ip-xxxxxx ~]$ /usr/local/pgsql/bin/pgbench -i -s10 -U pguser -h pgsql-test.xxxxx.ap-northeast-1.rds.amazonaws.com testdb

Password:

creating tables...

100000 of 1000000 tuples (10%) done (elapsed 0.38 s, remaining 3.42 s).

200000 of 1000000 tuples (20%) done (elapsed 1.26 s, remaining 5.05 s).

300000 of 1000000 tuples (30%) done (elapsed 3.03 s, remaining 7.07 s).

400000 of 1000000 tuples (40%) done (elapsed 5.37 s, remaining 8.06 s).

500000 of 1000000 tuples (50%) done (elapsed 5.80 s, remaining 5.80 s).

600000 of 1000000 tuples (60%) done (elapsed 7.75 s, remaining 5.17 s).

700000 of 1000000 tuples (70%) done (elapsed 10.01 s, remaining 4.29 s).

800000 of 1000000 tuples (80%) done (elapsed 10.43 s, remaining 2.61 s).

900000 of 1000000 tuples (90%) done (elapsed 12.64 s, remaining 1.40 s).

1000000 of 1000000 tuples (100%) done (elapsed 13.08 s, remaining 0.00 s).

vacuum...

set primary keys...

done.

testdb=> select relname, seq_scan, seq_tup_read, idx_scan, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum from pg_stat_all_tables where relname like 'pgbench%';

------------------+----------+--------------+----------+------------+------------+-------------------------------+-------------------------------

pgbench_branches | 71390 | 539310 | 0 | 10 | 164 | 2014-07-10 08:57:15.398869+00 | 2014-07-10 05:02:05.198489+00

pgbench_tellers | 56532 | 5392200 | 0 | 100 | 0 | 2014-07-10 08:57:15.405037+00 | 2014-07-10 08:58:14.467415+00

pgbench_history | 0 | 0 | | 10000 | 0 | 2014-07-10 02:48:56.913997+00 |

pgbench_accounts | 1 | 1000000 | 107843 | 1000000 | 25601 | 2014-07-10 02:48:56.686276+00 |

(4 rows)

実施

auto_vacuum設定を何も変更せずに実行し autovacuumが走っている事を確認

[root@ip-xxxxxx ~]$ /usr/local/pgsql/bin/pgbench -c10 -t1000 -U pguser -h pgsql-test.xxxxx.ap-northeast-1.rds.amazonaws.com testdb
Password:
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 321.557791 (including connections establishing)
tps = 323.035636 (excluding connections establishing)

testdb=> select relname, seq_scan, seq_tup_read, idx_scan, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum from pg_stat_all_tables where relname like 'pgbench%';
     relname      | seq_scan | seq_tup_read | idx_scan | n_live_tup | n_dead_tup |          last_vacuum          |        last_autovacuum
------------------+----------+--------------+----------+------------+------------+-------------------------------+-------------------------------
 pgbench_branches |    84508 |       639320 |        0 |         10 |        141 | 2014-07-10 09:05:39.648791+00 | 2014-07-10 05:02:05.198489+00
 pgbench_tellers  |    67016 |      6392200 |        0 |        100 |          0 | 2014-07-10 09:05:39.65411+00  | 2014-07-10 09:06:14.905845+00
 pgbench_history  |        0 |            0 |          |      10000 |          0 | 2014-07-10 02:48:56.913997+00 |
 pgbench_accounts |        1 |      1000000 |   127843 |    1000000 |      28860 | 2014-07-10 02:48:56.686276+00 |
(4 rows)

testdb=> select now();
              now
-------------------------------
 2014-07-10 09:06:21.412179+00
(1 row)

[root@ip-xxxxxx ~]$ /usr/local/pgsql/bin/pgbench -c10 -t1000 -U pguser -h pgsql-test.xxxxx.ap-northeast-1.rds.amazonaws.com testdb

Password:

starting vacuum...end.

transaction type: TPC-B (sort of)

scaling factor: 10

query mode: simple

number of clients: 10

number of threads: 1

number of transactions per client: 1000

number of transactions actually processed: 10000/10000

tps = 321.557791 (including connections establishing)

tps = 323.035636 (excluding connections establishing)

testdb=> select relname, seq_scan, seq_tup_read, idx_scan, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum from pg_stat_all_tables where relname like 'pgbench%';

------------------+----------+--------------+----------+------------+------------+-------------------------------+-------------------------------

pgbench_branches | 84508 | 639320 | 0 | 10 | 141 | 2014-07-10 09:05:39.648791+00 | 2014-07-10 05:02:05.198489+00

pgbench_tellers | 67016 | 6392200 | 0 | 100 | 0 | 2014-07-10 09:05:39.65411+00 | 2014-07-10 09:06:14.905845+00

pgbench_history | 0 | 0 | | 10000 | 0 | 2014-07-10 02:48:56.913997+00 |

pgbench_accounts | 1 | 1000000 | 127843 | 1000000 | 28860 | 2014-07-10 02:48:56.686276+00 |

(4 rows)

testdb=> select now();

now

-------------------------------

2014-07-10 09:06:21.412179+00

(1 row)

vacuum設定の変更

testdb=> alter table pgbench_tellers set (autovacuum_enabled = false);
ALTER TABLE
testdb=> alter table pgbench_tellers set (toast.autovacuum_enabled = false);
ALTER TABLE

testdb=> alter table pgbench_tellers set (autovacuum_enabled = false);

ALTER TABLE

testdb=> alter table pgbench_tellers set (toast.autovacuum_enabled = false);

ALTER TABLE

pg_benchを何回実行しても autovacuumが走らない事を確認

testdb=> select relname, seq_scan, seq_tup_read, idx_scan, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum from pg_stat_all_tables where relname like 'pgbench%';
     relname      | seq_scan | seq_tup_read | idx_scan | n_live_tup | n_dead_tup |          last_vacuum          |        last_autovacuum
------------------+----------+--------------+----------+------------+------------+-------------------------------+-------------------------------
 pgbench_branches |    97681 |       739330 |        0 |         10 |        164 | 2014-07-10 09:07:26.572718+00 | 2014-07-10 05:02:05.198489+00
 pgbench_tellers  |    77495 |      7392200 |        0 |        100 |        102 | 2014-07-10 09:07:26.579097+00 | 2014-07-10 09:06:14.905845+00
 pgbench_history  |        0 |            0 |          |      20000 |          0 | 2014-07-10 02:48:56.913997+00 |
 pgbench_accounts |        1 |      1000000 |   147843 |    1000000 |      30786 | 2014-07-10 02:48:56.686276+00 |
(4 rows)

testdb=> select now();
              now
-------------------------------
 2014-07-10 09:08:14.090072+00
(1 row)
...
testdb=> select relname, seq_scan, seq_tup_read, idx_scan, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum from pg_stat_all_tables where relname like 'pgbench%';
     relname      | seq_scan | seq_tup_read | idx_scan | n_live_tup | n_dead_tup |          last_vacuum          |        last_autovacuum
------------------+----------+--------------+----------+------------+------------+-------------------------------+-------------------------------
 pgbench_branches |   110794 |       839340 |        0 |         10 |        168 | 2014-07-10 09:08:30.25682+00  | 2014-07-10 05:02:05.198489+00
 pgbench_tellers  |    87978 |      8392200 |        0 |        100 |         99 | 2014-07-10 09:08:30.267249+00 | 2014-07-10 09:06:14.905845+00
 pgbench_history  |        0 |            0 |          |      10000 |          0 | 2014-07-10 02:48:56.913997+00 |
 pgbench_accounts |        1 |      1000000 |   167843 |    1000000 |      31924 | 2014-07-10 02:48:56.686276+00 |
(4 rows)

...
testdb=> select relname, seq_scan, seq_tup_read, idx_scan, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum from pg_stat_all_tables where relname like 'pgbench%';
     relname      | seq_scan | seq_tup_read | idx_scan | n_live_tup | n_dead_tup |          last_vacuum          |        last_autovacuum
------------------+----------+--------------+----------+------------+------------+-------------------------------+-------------------------------
 pgbench_branches |   123880 |       939350 |        0 |         10 |        155 | 2014-07-10 09:09:47.869879+00 | 2014-07-10 05:02:05.198489+00
 pgbench_tellers  |    98463 |      9392200 |        0 |        100 |        116 | 2014-07-10 09:09:47.875082+00 | 2014-07-10 09:06:14.905845+00
 pgbench_history  |        0 |            0 |          |      10000 |          0 | 2014-07-10 02:48:56.913997+00 |
 pgbench_accounts |        1 |      1000000 |   187843 |    1000000 |      32562 | 2014-07-10 02:48:56.686276+00 |
(4 rows)

testdb=> select relname, seq_scan, seq_tup_read, idx_scan, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum from pg_stat_all_tables where relname like 'pgbench%';

------------------+----------+--------------+----------+------------+------------+-------------------------------+-------------------------------

pgbench_branches | 97681 | 739330 | 0 | 10 | 164 | 2014-07-10 09:07:26.572718+00 | 2014-07-10 05:02:05.198489+00

pgbench_tellers | 77495 | 7392200 | 0 | 100 | 102 | 2014-07-10 09:07:26.579097+00 | 2014-07-10 09:06:14.905845+00

pgbench_history | 0 | 0 | | 20000 | 0 | 2014-07-10 02:48:56.913997+00 |

pgbench_accounts | 1 | 1000000 | 147843 | 1000000 | 30786 | 2014-07-10 02:48:56.686276+00 |

(4 rows)

testdb=> select now();

now

-------------------------------

2014-07-10 09:08:14.090072+00

(1 row)

...

testdb=> select relname, seq_scan, seq_tup_read, idx_scan, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum from pg_stat_all_tables where relname like 'pgbench%';

------------------+----------+--------------+----------+------------+------------+-------------------------------+-------------------------------

pgbench_branches | 110794 | 839340 | 0 | 10 | 168 | 2014-07-10 09:08:30.25682+00 | 2014-07-10 05:02:05.198489+00

pgbench_tellers | 87978 | 8392200 | 0 | 100 | 99 | 2014-07-10 09:08:30.267249+00 | 2014-07-10 09:06:14.905845+00

pgbench_history | 0 | 0 | | 10000 | 0 | 2014-07-10 02:48:56.913997+00 |

pgbench_accounts | 1 | 1000000 | 167843 | 1000000 | 31924 | 2014-07-10 02:48:56.686276+00 |

(4 rows)

...

testdb=> select relname, seq_scan, seq_tup_read, idx_scan, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum from pg_stat_all_tables where relname like 'pgbench%';

------------------+----------+--------------+----------+------------+------------+-------------------------------+-------------------------------

pgbench_branches | 123880 | 939350 | 0 | 10 | 155 | 2014-07-10 09:09:47.869879+00 | 2014-07-10 05:02:05.198489+00

pgbench_tellers | 98463 | 9392200 | 0 | 100 | 116 | 2014-07-10 09:09:47.875082+00 | 2014-07-10 09:06:14.905845+00

pgbench_history | 0 | 0 | | 10000 | 0 | 2014-07-10 02:48:56.913997+00 |

pgbench_accounts | 1 | 1000000 | 187843 | 1000000 | 32562 | 2014-07-10 02:48:56.686276+00 |

(4 rows)

AWS・クラウドのMSP スカイアーチネットワークス株式会社

AWS・クラウド導入における構築から監視・運用保守までをマネージドサービスで提供する、スカイアーチネットワークスの公式サイト。AWS・クラウドを始め、豊富なサーバー管理の経験と安心のセキュリティ対策、専門エンジニアの確かな知識で、お客様のビジネスの成長をサポートします。AWS・クラウド導入における構築から監視・運用保守までをマネージドサービスで提供する、スカイアーチネットワークスの公式サイト。AWS・クラウドを始め、豊富なサーバー管理の経験と安心のセキュリティ対策、専門エンジニアの確かな知識で、お客様のビジネスの成長をサポートします。

投稿者プロフィール

takashi

Japan AWS Ambassadors 2023, 2024
開発会社での ASP型WEBサービス企画 / 開発 / サーバ運用を経て
2010年よりスカイアーチネットワークスに在籍しております

機械化/効率化/システム構築を軸に人に喜んで頂ける物作りが大好きです。

PostgreSQLのautovacuumを特定テーブルでOffにする

目的

環境

準備

実施

auto_vacuum設定を何も変更せずに実行し autovacuumが走っている事を確認

vacuum設定の変更

pg_benchを何回実行しても autovacuumが走らない事を確認

投稿者プロフィール

最新の投稿

ABOUTこの記事をかいた人

NEW POSTこのライターの最新記事

WebスクレイピングにAmazon CloudWatch Synthetic…

AWS re:Invent 2024から読み解く、次世代クラウドコンピューテ…

Bedrock Flows と Amazon Q Developer Age…

Ambassador Global Summit 2024に参加しました！

サービス

インフォメーション

企業情報

会社Facebook

目的

環境

準備

実施

auto_vacuum設定を何も変更せずに実行し autovacuumが走っている事を確認

vacuum設定の変更

pg_benchを何回実行しても autovacuumが走らない事を確認

投稿者プロフィール

最新の投稿

RECOMMENDこちらの記事も人気です。

AWS ヘッドライン - 2019.10.04 -

やってみよう Amazon connect [1] Amazon Con…

RDSのイベントサブスクリプションを設定してみました

AWS ECS を使ってコンテナーを触ってみる

SageMaker Autopilotをやってみた(tutorial 編)

AWS ヘッドライン - 2019.07.23 -

【AWS】EC2インスタンスタイプ追加

AWS Summit Tokyoへ参加してみた No.1

ABOUTこの記事をかいた人

NEW POSTこのライターの最新記事

WebスクレイピングにAmazon CloudWatch Synthetic…

AWS re:Invent 2024から読み解く、次世代クラウドコンピューテ…

Bedrock Flows と Amazon Q Developer Age…

Ambassador Global Summit 2024に参加しました！

タグクラウド

サービス

インフォメーション

企業情報

会社Facebook

タグ

AWS Summit Tokyoへ参加してみた　No.1