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

2012年 7月 19日 (木) 00:40:29 JST

$B:4F#$G$9!#(B

> $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$$$^(B
> $B$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%=!<%9%3!<%I$r$A$c$s$HFI$s$G$$$J$$$N$G$9$,!"(BWHERE $B$J$7$N(B DELETE $B$GA49T(B
$B$r:o=|$7$?>l9g$K$O!":o=|$GH/@8$7$?ITMWNN0h$,(B VACUUM $B$J$7$G:FMxMQ2DG=$K(B
$B$J$k$h$&$G$9!#(B

WHERE $B$"$j$N(B DELETE $B$GA49T$r:o=|$7$?>l9g$K$O!"(BVACUUM $B$9$k$^$G:FMxMQ2D(B
$BG=$K$J$j$^$;$s$G$7$?!#(B

DELETE $B$GA49T$r:o=|$7$FITMWNN0h$,H/@8$7!"$=$3$K(B INSERT $B$G9T$,A^F~$5$l(B
$B$k$H$$$&$N$,7+$jJV$5$l!"%F!<%V%k$NBg$-$5$,0lDj$K$J$C$F$$$k$N$@$H;W$$$^(B
$B$9!#(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$,!"%5!<(B
> $B%PB&$N%m%0$r8+$k$H(Bautovacuum$B$G$O#1K|7oDxEY$7$+:o=|$5$l$J$$$N$O2?8N$G(B
> $B$7$g$&$+!#(B

pgAdmin3 $B$N!V%G%C%I%?%W%k!W$O(B pg_stat_user_tables $B$N(B n_dead_tup $B$NCM$r(B
$BI=<($7$F$$$k$N$@$H;W$$$^$9$,!"$3$NCM$O(B DELETE $B$H(B (HOT $B$G$J$$(B) UPDATE
$B$GH/@8$7$?L58z$J9T?t$r ＠ Q$_>e$2$F$$$k$@$1$J$N$G!"FbItE*$K:FMxMQ$5$l$?$+(B
$B$I$&$+$O9MN8$5$l$F$$$J$$$H;W$$$^$9!#(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$O(B
> $B!!2?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$3$l$O(B HOT $B$,F/$$$F$$$k$?$a$G$9!#(B

HOT $B$O%V%m%C%/Fb$K6u$-NN0h$,$J$$$HF/$+$J$$$N$G$9$,!"%F!<%V%k$NKvHx$N%V(B
$B%m%C%/$K6u$-NN0h$,$"$j!"$=$3$G(B HOT $B$,F/$$$FITMWNN0h$NH/@8$,M^$($i$l$F(B
$B$$$k$N$@$H;W$$$^$9!#(B

HOT $B$G99?7$5$l$?9T?t$O(B pg_stat_user_tables $B$N(B n_tup_hot_upd $B$r8+$l$PJ,(B
$B$+$j$^$9!#(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(B
> $B!!$J$j$^$9$,!"%5!<%P%m%0$r8+$k$H0J2<$N$h$&$K(Bautovacuum$B$,<B9T$5$l$?7A(B
> $B!!@W$,$"$j$^$;$s!#(B

$B$3$l$O860x$,$A$g$C$HJ,$+$i$J$$$G$9!#(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(B
> $B$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

$B$O$$!"$=$l$GLdBj$"$j$^$;$s!#(B

$B$=$NA0$K=PNO$5$l$F$$$k!V(B0.01s/0.01u sec$B!W$O$=$l$>$l%7%9%F%`(B (OS) $B$H%f!<(B
$B%6(B (PostgreSQL) $B$N=hM}$K$+$+$C$?;~4V$G$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
> 
> $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


----
Tomoaki Sato <sato ＠ sraoss.co.jp>
SRA OSS, Inc. Japan