[pgsql-jp: 41148] autovacuumの挙動について

[ISHIKAWA Kenichi]

$B$O$8$a$^$7$F!"@P ＠ n$H?=$7$^$9!#(B
PostgreSQL 9.1.2$B$N(Bautovacuum$B$N5sF0$K$D$$$F65$($FD:$-$?$$;v$,$4$6$$$^$9!#(B
$BD9J8$G@?$K?=$7Lu$"$j$^$;$s$,!"$h$m$7$/$*4j$$CW$7$^$9!#(B

$B$^$:%F%9%H$KEv$?$j0J2<$N$h$&$J%F!<%V%k$r:n ＠ .$7$^$7$?!#(B
CREATE TABLE test_tbid
(a_num numeric(10,0) NOT NULL,
  b_num numeric(38,0),
  c_var character varying(10),
  d_var text,
  e_date date,
  CONSTRAINT p_key1 PRIMARY KEY (a_num )
)
WITH ( OIDS=FALSE);
ALTER TABLE test_tbid
  OWNER TO postgres;

$B#1!K%$%s%5!<%H(B&$B%G%j!<%H=hM}$G$N5?LdE@(B
$B>e5-$N%F!<%V%k$KBP$7$F0J2<$N=hM}$r(B100$B2s$[$I7+$jJV$7$^$7$?!#(B
$B-!(B1$BK|7o$N(BINSERT$B$r<B9T$7$F%3%_%C%H!JLs#7IC!K(B
$B-"!V(Bdelete from test_tbid$B!W$GA47o:o=|$7$F%3%_%C%H!JLs#1IC!K(B
$B$3$N>l9g$O#1J,4V$GLs#7K|7o$N!V%G%C%I%?%W%k!W$,H/@8$9$k$N$G$9$,!"(B
$B%5!<%PB&$N%m%0$r8+$k$H(Bautovacuum$B$G$O#1K|7oDxEY$7$+:o=|$5$l$J$$$N$O2?8N$G$7$g$&$+!#(B
$B$J$*(Bautovacuum$B$N<B9T8e$O!V%G%C%I%?%W%k!W$O#07o$KLa$j$^$9!#(B

[2012-07-10 10:49:03 JST][4044] LOG:  automatic vacuum of table "testdb_pos.public.test_tbid": index scans: 1
	pages: 0 removed, 492 remain
	tuples: 10264 removed, 5907 remain
	system usage: CPU 0.01s/0.01u sec elapsed 0.28 sec
[2012-07-10 10:49:03 JST][4044] LOG:  automatic analyze of table "testdb_pos.public.test_tbid" system usage: CPU 0.00s/0.00u sec elapsed 0.09 sec
[2012-07-10 10:49:33 JST][1924] WARNING:  archive_mode enabled, yet archive_command is not set
[2012-07-10 10:50:03 JST][2268] LOG:  automatic vacuum of table "testdb_pos.public.test_tbid": index scans: 1
	pages: 0 removed, 553 remain
	tuples: 10272 removed, 5208 remain
	system usage: CPU 0.00s/0.00u sec elapsed 0.50 sec

$B#2!K(BUPDATE$B;~$N5?LdE@(B
$B-!>e5-%F!<%V%k$K#1K|7o$N%G!<%?$r%$%s%5!<%H$7$?8e$K!"(B
$B!!(B1000$B7o$N(BUPDATE$B$H%3%_%C%H!JLs#1IC!K$r<+F0$G(B1000$B2s$[$I7+$jJV$7$^$7$?!#(B
$B!!$3$N;~$K(BpgAdmin$B>e$N!V%?%W%k$N99?7!W$O(B1000$B7oC10L$GA}$($F$$$/$N$G$9$,!"(B
$B!!!V%G%C%I%?%W%k!W$O(B1000$B7oC10L$GA}2C$7$J$$>l9g$,$"$j$^$9!#$=$l$O2?8N$G$7$g$&$+!#(B
$B!J!V%G%C%I%?%W%k!W$NA}J,$O(B700$B!A(B900$B7o$,B?$$5$$,$7$^$9!K(B
$B-">e5-$N(BUPDATE$B$r<B9TCf$K(BpgAdmin3$B$r8+$k$H;~!9!V%G%C%I%?%W%k!W$O#07o$K$J$j$^$9$,!"(B
$B!!%5!<%P%m%0$r8+$k$H0J2<$N$h$&$K(Bautovacuum$B$,<B9T$5$l$?7A ＠ W$,$"$j$^$;$s!#(B
$B!!-!-"$H$b$K(BHOT$B5!G=$,4X78$7$F$$$k$N$G$7$g$&$+!#(B

[2012-07-10 14:39:18 JST][1924] WARNING:  archive_mode enabled, yet archive_command is not set
[2012-07-10 14:40:04 JST][4004] LOG:  automatic analyze of table "testdb_pos.public.test_tbid" system usage: CPU 0.00s/0.04u sec elapsed 0.07 sec
[2012-07-10 14:40:18 JST][1924] WARNING:  archive_mode enabled, yet archive_command is not set
[2012-07-10 14:41:04 JST][980] LOG:  automatic analyze of table "testdb_pos.public.test_tbid" system usage: CPU 0.00s/0.06u sec elapsed 0.09 sec
[2012-07-10 14:41:13 JST][1924] WARNING:  archive_mode enabled, yet archive_command is not set
[2012-07-10 14:42:04 JST][3484] LOG:  automatic analyze of table "testdb_pos.public.test_tbid" system usage: CPU 0.00s/0.04u sec elapsed 0.07 sec
[2012-07-10 14:42:13 JST][1924] WARNING:  archive_mode enabled, yet archive_command is not set
[2012-07-10 14:43:04 JST][2228] LOG:  automatic analyze of table "testdb_pos.public.test_tbid" system usage: CPU 0.00s/0.04u sec elapsed 0.07 sec

$B#3!K%P%-%e!<%`%m%0$K$D$$$F(B
$B0J2<$N%m%0$K$*$$$F!V(Bsystem usage$B!W$N9T$O$I$N$h$&$K2r<a$9$l$P$$$$$N$G$7$g$&$+!#(B
$B!V(BCPU$B$O(Bvacuum$B=hM}$K(B0.28$BIC$+$+$j$^$7$?!W$HH=CG$7$FNI$$$N$G$7$g$&$+!#(B
[2012-07-10 10:49:03 JST][4044] LOG:  automatic vacuum of table "testdb_pos.public.test_tbid": index scans: 1
	pages: 0 removed, 492 remain
	tuples: 10264 removed, 5907 remain
	system usage: CPU 0.01s/0.01u sec elapsed 0.28 sec

$B$J$*8=:_(Bautovacuum$B4X78$N%Q%i%a!<%?$O0J2<$N ＠ _Dj$K$7$F$"$j$^$9!#(B
$B!&(Bautovacuum_naptime $B!!(B1$BJ,(B
$B!&(Bautovacuum_vacuum_threshold$B!!(B10000
$B!&(Bautovacuum_vacuum_scale_factor$B!!(B0
$B!&(Blog_autovacuum_min_duration$B!!(B0
$B!&(Btrack_counts $B!!(BON

$B0J>e$*<j?t$G$9$,$h$m$7$/$*4j$$CW$7$^$9!#(B