Poor performance - Disk usage

Поиск
Список
Период
Сортировка
От Gaetano Mendola
Тема Poor performance - Disk usage
Дата
Msg-id arv9cq$2no3$1@news.hub.org
обсуждение исходный текст
Список pgsql-admin
Hi all,
I have a Postgres 7.2.2 installation with a
vacuum and a vacuum analyze scheduled all nights
( the vacuum analyze do the same work of a simple
vacuum or not ? )

One table is huge ( not so much huge ):

# select count(1) from jobs;
 count
--------
 418941
(1 row)

and I'm interested only at the row with status in ( 'r', '0'):

# select count(1) from jobs where status in ( 'r', '0');
 count
-------
     5
(1 row)


for this reason I have on that column a partial index:

CREATE  INDEX idx_jobs_status
ON jobs ( status )
WHERE status in ( 'r', '0') ) ;

These are the performance:
# explain analyze select * from jobs where status in ( 'r', '0');
NOTICE:  QUERY PLAN:

Index Scan using idx_jobs_status_r on jobs  (cost=0.00..3.12 rows=1
width=49) (actual time=0.18..2.77 rows=5 loops=1)
Total runtime: 2.89 msec

till now all is fine,  I just finish to do:
1) vacuum full jobs;
2) reindex table jobs;

and I can't do this all night!!
If I do the same query in two days the performance
will be really worst ( 1~2 secs )

Why the vacuum analyze doesn't help ?
It's better the do a reindex table instead of vacuum analyze?

Another question:

in attachment you can find the graph about the disk usage of
directory /var/lib/pgsql/data
how you can see the two vacuum scheduled during the night don't free
any space ( the disk usage increase in a costant way) the only way
to reduce the disk usage is do a vacuum full ( I can do it only on the table
jobs) and do a reindex on the same table, in my configuration file i putted
:

max_fsm_pages = 1000000
vacuum_mem = 32768


should I do some thing else ?
The table jobs is affected only by insert and update.


Ciao
Gaetano



begin 666 Graphs for directory_pgsql.png
MB5!.1PT*&@H````-24A$4@```E,```#_! ,````G+DYG````)%!,5$7____U
M]?7(R,B6EI:,C(R"'AX```````#_````S #_``#_``#"\;-O```2,$E$051X
M`>U=.Y/<-A*>0 HVG+J]+3.U_X"K_ >\5>M@,[EJ-V XP6U=ZNC*X647.M %
MDRF0`F472 '^W?4#(-Y DP2YT@P@%0BB&P]^_-!H8@CNX<<>9 C\?)#I=:V?
M`*IC#Q($_M:ADL"$.ATJ*5(=*B%2#P_WG55"K.X[5$*D'F[[`!1"!6I]`,JP
MZK9*AA-J=5LEQ2IKJ^Y>7HXO)_Q_/-[!OSC\'F=QSN4*<K;J#A!BD.Y.+PS"
M+1_NZ<"(<-;%"^"*"[;JY$/U]^?GW[P0G%K95YOT4]D2KRKXY'?2GDV]>GY&
M;MP?<WX5$LJP"H['X^WA\.9 X5>*WU',69[@?SE!ML2K"CY4NGMS>(-('6\?
M<LY"ATK?P+.&"M#*V*J3PRHRZ\ J66!6R72_`:T/Q3Z<SQ:JW "$J<^?`:]R
M`)[/[PU4!;-.`]2)KI!50"D)JQR0.'E]K"*@)E8!"AE;E8*J.*HGX878JAM"
MRK(J/P.FH/)\@NP,SU EO(ALB5<5L%F/NGMS'GU6Y?VJ%%03<8H)AJJH\BT)
M/R0[8RBUB%4/#V]TX,23.;5'%GRQ&2;%@FR)5Q5\-)VTQX<W[R$\803AS0,_
MR75;E6#59*:(6U5O/34`K\16W3P21"/%TPS8;=4A8A4AY$2=58?#KV30&2H[
M`P)(25;!,.M^%2&F(X=-)JE9->_!YO)M%<.39!5,@YU5$ZO\F2]@U3RH+IQ5
M-[",0"')JFZK[ QH6!0>JTM[D5]UZ=XZN^84/U#\I'.ZMVXLU =.A%RRY_-9
M=='K53=LI;JM,@1*'I%5Z:E/$\OX5;.<A4N=`0$3`:ONNU]5I!2@:&S5;>X7
MFV@&O%1;]9G&F8!5LP9@<JQ'F=_7*NB9H=)V*74PK)H%U07:JK.&JK,JHKR?
M@69J`U9=HK>._OA'[90[A^ZM^XPZ'-@PR5EUO3.@7NQDJ.JV:M[27GA;TN??
MR0QH)KO.*KB-I0G[!I!Z)+2DK+K6]2I#*3A*676E[RPP4O-8=96_`]XXG.JL
MH@DG8ZN")?1NJ]*S,^1ZE)IAJV8X"Q?BK4=.N=1;OY?OL;F(=T%],T4$XP$8
M<LTYURL+M_+]@)>P7I5:0N^V*F6K'*K8I)15\YR%S)02^,7\8,.ZLA+OG EK
MPQ(`3^)13\JJ&6;].[=5?UD>^:GO@E7GO_0K3MNSZN;\C@!:P2KO&3"U0=+^
M&M&:53=G@&JG@&WY7+)G4E8Y4)V.L*W&AI>3WFAJ=IDVG@&QKWNQ"I!JRBK:
M]#=!19L!<?,6Y/ NTZ:LPL[OQBINB^)$M(!5/JD`(OH_006[3-_J\!L=IU.3
M_?8M"[[:#)-B@5/BOQQT"4>0+;%"0&T]ZR:=PV^4_N3DZ"0+GO796]YE"IS1
M;^V]0)@X!3CA`,0]N<@JL\M49EG862CIFGN[BZW2C:VV5=GU*@)IHPVYV'F:
MBG:P53#2'PFLU3-@U@4E6V6@(FZULE5LIJC[V[/*-M:25:>[8 ;<9I>I^QBV
M-:L8J$:LLK;J>/*G0,=N<;(-JXA-)MJ85992T.!J5CD/-B<PXJ70Q*^"/CL.
M\[:LTOQM9JOL>M5=':K2K&9E^1G0L,D<-V65:40?5[,J:]9C?JUGU4B]WHE5
MT!:WU(I5@,A>NR$\R\&W>D-6<0-.O)I5\*D3*51KU];MMDV[O/UFJ^VGMHGW
M[_WW6AP)"Z1KZ]:LGWQO/3D`K3TJI9*VRKF_-KD5JQ($7C\#.JSRO*H8J76?
MSR%X1HJWMU7&2FUFJP10E;AD93&K4G<98=N&571#HFBUK=IE!C2/82/U?V-6
MT6UQVC"-OPL;-X+/.8'IKGG%7_X)U<7>.G4E&6W JF0[F-F05<$B3&RL%OI5
MU/='BD>*G3O>WEN?/KF!36UHJ\K6:AFK")U<U)I5.9N([:]FE7468!&T_3.@
M_Q@V$F3;L0J1BMHP)FFUK8*/[1D7%!8]XU'GY"Q@5>DN(VQM655N;3VKY'ML
M%GCKDV><<YB;>NO4VM/4YI3(-:[=>*FW#JPQK'((E$S.9A7RIAQ:LJK<4EM;
ME<3'R9P[`U+?O4EH=+)(T' &-"8I:L,(5MNJS=ZO*AL.PJRAK1*TMMI6;>6M
MF_<M]V$5(?](\7:L@@&VA:W2K*D=VM@J`:6@(ZM9Y?A5CEE*)N6VBOKNN$_&
M7$1WO(FMJOANIO'5MFK&]];%,Z"JD6F2MV#55%DEL9I5]EW09L^ 9T6=WHE5
MT%;9'K9CE;55P4M#T2"4L0H&'T-%>%6B]:RJ-."(5[/*,>NU9T"9MPX^\I?)
M3YX2.8=YK;<^-0")7!LUP0)OO<4S(-U%Y=S+<G(EJV13G^Y"2U9%(R[($,R
MC]0M1;'$5IU7O0MJK-3^MBI )CJMVBISDQDJPJL6V07Y^:E:W8&\):L$/\07
MWRD'I'9D%0$A8NX&ORX+7N\HW'I#*;@$%=S/PFFAPK*H4&=&M)Q5?_'?AH!A
M9AYL!%#E6<5([<4J^]5JQ&5K6Q5#M68&].ZD\LZ*)V7JY*0.@XNU>\*6K(KL
M>)!1F ']&ZNHBR([LF@&1*1&>1NK;57,J@"9Z#0[`X8W6=%EB*(<;TKYHHIC
MI9:L"MX%C:#*>NO696:'^8O-,*F<)[W$6Z<ZGTS-]IAK8[6W_C[ZWOK"=T'C
M^Z?BK%Q.B3U)6<C@7,5Q?E-6U5]P3,R "9.DJ)\)P1@)YMHJ0.J1*HFKJ@G>
M18WK$I]S`M-&;*L$+FATHY,W65';HBBJL)B1;$W4#BBU9%5DG(*,Q P(?4^0
M1U'O$X(Q$LQB%2/U2)7$5>W)J@"9Z#2:`7,W6='%B*(BB0*AJ,*\4DM6"5YP
M]&S52-U*D$?E!'&)&:SR?;>XJGU9->N=!<(C&:ED;C(S($[A-%E\3F9+5M$&
M0#OL^,^X.7_+U+=5!8=9T14DZ#9&`BFK<*QSA37RQ&WH$BUGP!?O31AOYQ;]
M+5/75N7,%&&A*!9%!1JYHF)SHH:VG 'A72M&"WA&&W*]7::X_])LPM1[,?'
MNS._.CDZZ6_;M'+A+E-;P+3Q[&9Q.M>&[E6VQ*=J5>$N4SOT*,5;3&D#9;3+
MM'(?547NB%WJ9-.._HID6UOEHN4-0+,AEV? FKE0=$'-;)6Q4M^0K7*!,J-/
ML\K=95JW&PR5B !9)EF!J!Z!TG:LTEM,HQD0D7JDGHT4)\BC<H*X1'4&E%=5
MZU7+&=!G57R&,V"=4G!MBJY/%%GRI%.B2F1*&[(JP@K]*N[57JQ:LH0^4A<3
M7&_(JK*K#L#=RCC5CE4B"LLH!5H-676J875KIJ*:55"Y&SM&@I*M0J3B$K7&
MLR5:LJH&U0!=%P4ETB*EM(6B7'DE,LV&K(+O"KD?.HE,U?&!%Z]A,;NRBOW%
MKG>;5*Y$86V=BCZ9"NPQ5U6M5]FJ/MJZ32IH(UY;C]'Q<@;9W6MBJ]J:*>IX
M0U9YL*1.`"J9PZRH:XE):(P$&5L%2#V2;EQBL:"AK4JAX^4-U'E!I 0Z6B5M
MJS;@5-,9$#_#Y$$3G@P[L8J!^J9957O!$:"2!2530ZT$JS:A%#35TE8)H-K!
M5@GMX8A :UU9KUK:*L$`I/Y5(U75F!0B5DV2YHF6K IM4W@^F+M8FX047>:2
M&1 'GXPCH[P-W=V6K JA"<\'ZIT@4@(=K>*S:BLS18WMR:KMO77C+.,Q<)BM
MJ+E@@;<>LB@\'Z1D45)%?P:4EUJDN2>K!JD=470I<VV5J;W;JC03)EN5%K?,
MO116R7E8FW['7%67,0-N.O,99K9D5>U=T,%8D]J-5;D;.T8"6EEHNH0>M[&!
M7U5[%W2@"Q5$2J"C5=!6R;57:39E5>5=T&$;5N'UCQC->J*;7Z*EK:J]"SK@
MY4B"DBBQSFZ<:KNR$+J<X?DVWCJYX4_6&3>IYDYYMHWOQ%N7\V^U9DM;)1B
M,D]:T66)O/7:9#JVJZJEK:IM<ANHVX)("73V5VG)*@%4U\FJ<[1U\C5607<D
MUW)6Q5"%,UYX/AC/IV9@% %P.;9J$50R$C!4,MW]M%JRZC5^L:D1="0HFQ!T
M^0P8LTH`E8P#2J:VLU9#5L&;,.5?E[?PUG=<0F_IK9>!.AX'*0V45'%7O8:L
M"B>\Z'SH,Z#Y)$6$39 !4,F"DJGMK-625153A0.P>^N''XE _A^2"CC5;14`
M,@W N_*[H)U5$U0P`(M8`52RH&1J.VNUM%7^B ,W*_A;ID.W5=,`]+"BK9.X
M>Q)R^6^9`E2RH&1J.VNU9-6+9]8CJ&"7J=F.6=G/^=7HV6.EQ+/5-*G]2LS?
M97H'?R#0X17N,O7_END@I8&2*NZJUY!5`52X>1)VXO(`I%VF0[=5VE8%&T>\
M#;D$&$ E"TJFMK-60U8Y@X^2Z#O0?]J<"UE#9U5Z!@R1Z]XZ(#(#JOX,J)\!
M8R)Y.3 `94')U';6VM!6>3#AR=!MU8P!*"."DJGMK+4GJ_K:^ARS+B-"9]70
M;56W5;7!$KW>$4UY049G5;=5-5+%/\0')(I..ZLZJSJK@@G['2&2>*GF<TXP
M&L$2LTYEJY&J:KR&0O?6S7ONR_>E]O?60^;NR:HA&/PC=28Q^%5.D"VQAZ#;
M*KHMDJBS"E"2K<UV5DD(13J=59U58K*(%3NK.JO$9!$K[LFJOK;>5Q:JQ%SR
MN"SS8!2UG7#CQ]<4=+^*T)=$>]JJ0>H7*^IY9Y7@!C)4`L5=53JK`&Z9!>VV
M2LS,%:QZ;WZ2F?&3J>S^*>K^!=FJ)5#);B%#)=/=3VM/5G5OO7OK568O&8#=
M5O5W0<O$ZJS2'WFH_[J\!*HR^$:J3.*;.NXY`PY2OYBAZGZ5@"D,E4!Q5Y55
MK/J!_?6TMZZWF-(V>=Z_-5PQJW[X#V&5@8IV(06[3*_66?CA2%C)H+K[QS7O
M,OWG\?AOX%4&*MIBZFZ=!-5;'K+W=/B=8LZZ=$&15;S%-("*D=(Q0^5E\<D%
M"BJV"G?C:JAHEVD"E*O)*LZ \=\RO1I<\A>:ME5Y_1TDJ4]IN=OUHRX4A9'V
MXHS54)'OE6B>1V]*D/M+A*9$!!5^2R11$1C47.,X*B!$-1VU(*[--!Y+IIRU
M4.%'!N ;#?&EX$<_7M#>10%[E?H"S1THPW=I$I</);A04!=6?H)/L@39V"6H
M)X4D".9V=ZJ\$53QWT(%J+!3L0#ZBH+XE@-4:0&6P*_A1B0A1+"Z,%#.*2Z
MJMA&W*M\=Z>ZUT*%-V_^A4.)F#P(%?IS$2+00J8-X!-HQU!I=L8U+>AN.Z@(
MI\3U%3F"A:+ )BE151XJJ",-%=^]!%3SNSOU<S6KH&W\`'G4*V!"F@IXX>E/
MEM,GIT$V=<XDF%6Q`/'.0@7J<4V@/[>[IA.9!YM)7$V@3<W8$332"0QQO("M
MB >@SHWM"+,*Q4'(0L6]`@L7% !+/[N[4Q7K6355=>F)#I7X#G>H.E1B!,2*
MG54=*C$"8L7.J@Z5& &Q8F=5ATJ,@%AQ8M6_(/SA% N?9Q.K0HXV)NDIPBF6
M3L(#(#Q<X%,=!$<%3B ?<X*GD=I?K<!Z=@D35'_"#^-_8%=U3[VK@*XP$'@U
MT<507ICK7:%;F8N2F\_ &:@L7C:5;UKW8-M#!!4]^^)*&G0:GRT)'+.BR&L%
MN-8$V?3 B]"ZIZ!)UX\K3UP#72"I<6V((1"(U%B':V!L=6VZ"2@+]7 WJ$6=
M3\N&VP(3UYZ!"E:;>+V 5K7Q0IE59AV(,,/:.$&QUM!+C'#E=L6!5VD`!B(-
M1( 6+A3@>N14`V1B#K;$S6&%>(J0`[(@0@GFT"F<[1HBJ&CQ2:_+`5WT]9L$
M+ZM ?^%F&Q$D\-2HZ@1=$Y(``HXYH@N<$4XZA_)!A#6 `HFLYL1:R-=-&*A8
M?U>@G!_BC:URH8*^&#QL`H<#7A6O!M$EXOW&!&::!%T-JD'0:<+;H(7Z4SXO
M#C(D3"P205&J7[>(M7$^5 +=V#E$K(*>0L<A&,)#A_BR=&*""KH-(B070658
MALH0- R8Q PT3AFHN :(&2ILG5#$?"JE(40Y5\L58LU[A@14N*H)@<PH=@H"
MP0-'LK!PP-&$:0B@K#5)=6(5$T4/(JZ!:03%\3^4PB/6S(8;H2(P0 W2%%/]
MF(M@TQ#&INF4.T9=V"F:H K]JES[>#4S@ULD2-,I7S7&KK3<B%RS7,\,Z025
MM$S;/D)MFIV:OZ_3#5&KLZ$2U7J12ATJ\6WM4'6HQ B(%3NK.E1B!,2*P*J?
D?NE!A,#/!Y%:5_KEEY__#Q,]T5H&8I,J`````$E%3D2N0F""
`
end


В списке pgsql-admin по дате отправления:

Предыдущее
От: "Gaetano Mendola"
Дата:
Сообщение: Child itemid in update-chain marked as unused - can't continue repair_frag
Следующее
От: Javier Perez
Дата:
Сообщение: Starting the database server