目的
特定時間にサイトパフォーマンスが落ちてしまう事を防止したい
実際にはサイトレスポンスが異様に遅い事がありログを見たところ
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を実施するようにしましょう
準備
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
[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が走っている事を確認
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
[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設定の変更
|
1 2 3 4 |
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が走らない事を確認
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
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) |
投稿者プロフィール
