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

目的

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

実際にはサイトレスポンスが異様に遅い事がありログを見たところ
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)

実施

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)

vacuum設定の変更

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)

投稿者プロフィール

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

機械化/効率化/システム構築を軸に人に喜んで頂ける物作りが大好きです。
個人ブログではRaspberryPiを利用したシステムやロボット作成も
実施しております。

スカイアーチネットワークスで一緒に働きましょう!

コメントを残す

メールアドレスが公開されることはありません。

Time limit is exhausted. Please reload CAPTCHA.

ABOUTこの記事をかいた人

開発会社での ASP型WEBサービス企画 / 開発 / サーバ運用 を経て 2010年よりスカイアーチネットワークスに在籍しております 機械化/効率化/システム構築を軸に人に喜んで頂ける物作りが大好きです。 個人ブログではRaspberryPiを利用したシステムやロボット作成も 実施しております。 スカイアーチネットワークスで一緒に働きましょう!