Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id ED0F76501F8 for ; Wed, 26 Nov 2008 11:01:03 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 69063-03 for ; Wed, 26 Nov 2008 11:00:58 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from qb-out-0506.google.com (qb-out-0506.google.com [72.14.204.225]) by mail.postgresql.org (Postfix) with ESMTP id CBF046501A2 for ; Wed, 26 Nov 2008 11:00:57 -0400 (AST) Received: by qb-out-0506.google.com with SMTP id d8so481833qbc.23 for ; Wed, 26 Nov 2008 07:00:55 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=Re4c2YmQ+fCzA+nlQ1DCixOdSFnaPNVY8U5jUYVqc88=; b=ECmA0xP8K7q5FW7pcThgmlmkWHMUnVuvgcQKOogm4wzy6awegB1JJ3c9DMB1Bu2at7 G35vgwIdrEG2uj8dM72pvXoMM+XiqcHXEVGUNpwDe7kyXS7KV5cxU34OY82djGcuUhXc XCtFbU4Am8+BZG0kMZgurBuwJvjGMjJn+Z9+s= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=T4bddH9kjdz4cSMg6Y0RKZVeIdhnQU5fj9us0rPnPpSyzydc5Avo7GATvWXinRF9aM L0uL47taRmOl8octR5Q9e6eys63v/OsVla7r9NUxEdNJyFHzMuHW2/tNGSOU5S2dbdet cCqpo17zPcYMRhuVXbWjKNXcFbJzS+tPj0Mao= Received: by 10.86.84.18 with SMTP id h18mr3851925fgb.69.1227711654550; Wed, 26 Nov 2008 07:00:54 -0800 (PST) Received: by 10.86.51.15 with HTTP; Wed, 26 Nov 2008 07:00:54 -0800 (PST) Message-ID: <3a0028490811260700w6e8d9d86o24effbe0bea2bddb@mail.gmail.com> Date: Wed, 26 Nov 2008 13:00:54 -0200 From: "Rafael Domiciano" To: "Rafael Martinez" Subject: Re: Vacuum Problems Cc: "pgsql-admin@postgresql.org" In-Reply-To: <492D5C87.5090505@usit.uio.no> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9311_21957445.1227711654581" References: <3a0028490811260543x74d02719w775a457999cfc74@mail.gmail.com> <492D5C87.5090505@usit.uio.no> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200811/150 X-Sequence-Number: 30804 ------=_Part_9311_21957445.1227711654581 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline The database has around 40 Gb. If I not use vacuum full everyday the database get very slow. There is no deadlock on the database. The vacuum does a clean in the table and in every index of the table "clifatura". And in the end of the vacuum, seems that vacuum is working har= d on the table (Vacuum is using CPU and memory), but doesn't end. INFO: limpando "public.clifatura" INFO: "clifatura": encontrados 63 vers=F5es de registros remov=EDveis e 69= 06667 n=E3o-remov=EDveis em 238511 p=E1ginas DETAIL: 0 vers=F5es de registros n=E3o vigentes n=E3o podem ser removidas = ainda. Vers=F5es de registros n=E3o-remov=EDveis est=E3o no intervalo de 168 a 428= bytes. Havia 262348 ponteiros de itens n=E3o utilizados. Espa=E7o livre total (incluindo vers=F5es de registros remov=EDveis) =E9 99= 117652 bytes. 2351 p=E1ginas est=E3o ou estar=E3o vazias, incluindo 0 no fim da tabela. 95651 p=E1ginas contendo 87437816 bytes livres s=E3o destinos potenciais. CPU 2.86s/1.08u sec elapsed 92.21 sec. INFO: =EDndice "caeindclf_01" agora cont=E9m 6906667 vers=F5es de registro= s em 39824 p=E1ginas DETAIL: 63 vers=F5es de registros de =EDndices foram apagadas. 0 p=E1ginas de =EDndice foram removidas, 0 s=E3o reutiliz=E1veis. CPU 0.58s/3.18u sec elapsed 26.82 sec. INFO: =EDndice "caeindclf_03" agora cont=E9m 6906667 vers=F5es de registro= s em 43174 p=E1ginas DETAIL: 63 vers=F5es de registros de =EDndices foram apagadas. 0 p=E1ginas de =EDndice foram removidas, 0 s=E3o reutiliz=E1veis. CPU 0.64s/3.18u sec elapsed 23.62 sec. INFO: =EDndice "caeindclf_04" agora cont=E9m 6906667 vers=F5es de registro= s em 36346 p=E1ginas DETAIL: 63 vers=F5es de registros de =EDndices foram apagadas. 0 p=E1ginas de =EDndice foram removidas, 0 s=E3o reutiliz=E1veis. CPU 0.50s/2.63u sec elapsed 19.97 sec. INFO: =EDndice "caeindclf_05" agora cont=E9m 6906667 vers=F5es de registro= s em 35965 p=E1ginas DETAIL: 63 vers=F5es de registros de =EDndices foram apagadas. 0 p=E1ginas de =EDndice foram removidas, 0 s=E3o reutiliz=E1veis. CPU 0.56s/3.06u sec elapsed 12.40 sec. INFO: =EDndice "caeindclf_06" agora cont=E9m 6906667 vers=F5es de registro= s em 32085 p=E1ginas DETAIL: 63 vers=F5es de registros de =EDndices foram apagadas. 0 p=E1ginas de =EDndice foram removidas, 0 s=E3o reutiliz=E1veis. CPU 0.49s/3.08u sec elapsed 10.68 sec. INFO: =EDndice "caeindclf_11" agora cont=E9m 6906667 vers=F5es de registro= s em 28596 p=E1ginas DETAIL: 63 vers=F5es de registros de =EDndices foram apagadas. 867 p=E1ginas de =EDndice foram removidas, 867 s=E3o reutiliz=E1veis. CPU 0.39s/2.11u sec elapsed 9.40 sec. INFO: =EDndice "caeindclf_12" agora cont=E9m 6906667 vers=F5es de registro= s em 46721 p=E1ginas DETAIL: 63 vers=F5es de registros de =EDndices foram apagadas. 287 p=E1ginas de =EDndice foram removidas, 287 s=E3o reutiliz=E1veis. CPU 0.57s/2.59u sec elapsed 21.90 sec. INFO: =EDndice "caeindclf_13" agora cont=E9m 6906667 vers=F5es de registro= s em 28658 p=E1ginas DETAIL: 63 vers=F5es de registros de =EDndices foram apagadas. 336 p=E1ginas de =EDndice foram removidas, 336 s=E3o reutiliz=E1veis. CPU 0.33s/2.19u sec elapsed 17.31 sec. INFO: =EDndice "caeindclf_16" agora cont=E9m 6906667 vers=F5es de registro= s em 16565 p=E1ginas DETAIL: 63 vers=F5es de registros de =EDndices foram apagadas. 399 p=E1ginas de =EDndice foram removidas, 399 s=E3o reutiliz=E1veis. CPU 0.18s/1.88u sec elapsed 13.29 sec. INFO: =EDndice "caeindclf_17" agora cont=E9m 6906667 vers=F5es de registro= s em 28676 p=E1ginas DETAIL: 63 vers=F5es de registros de =EDndices foram apagadas. 1433 p=E1ginas de =EDndice foram removidas, 1433 s=E3o reutiliz=E1veis. CPU 0.26s/2.00u sec elapsed 11.38 sec. INFO: =EDndice "caeindclf_14" agora cont=E9m 6906667 vers=F5es de registro= s em 34523 p=E1ginas DETAIL: 63 vers=F5es de registros de =EDndices foram apagadas. 256 p=E1ginas de =EDndice foram removidas, 256 s=E3o reutiliz=E1veis. CPU 0.34s/2.31u sec elapsed 14.83 sec. INFO: =EDndice "caeindclf_15" agora cont=E9m 6906667 vers=F5es de registro= s em 31569 p=E1ginas DETAIL: 63 vers=F5es de registros de =EDndices foram apagadas. 109 p=E1ginas de =EDndice foram removidas, 109 s=E3o reutiliz=E1veis. CPU 0.34s/2.43u sec elapsed 14.05 sec. INFO: =EDndice "caeindclf_18" agora cont=E9m 6906667 vers=F5es de registro= s em 31570 p=E1ginas DETAIL: 63 vers=F5es de registros de =EDndices foram apagadas. 451 p=E1ginas de =EDndice foram removidas, 451 s=E3o reutiliz=E1veis. CPU 0.31s/2.41u sec elapsed 17.53 sec. INFO: =EDndice "clifatura_pkey" agora cont=E9m 6906667 vers=F5es de regist= ros em 15833 p=E1ginas DETAIL: 63 vers=F5es de registros de =EDndices foram apagadas. 0 p=E1ginas de =EDndice foram removidas, 0 s=E3o reutiliz=E1veis. CPU 0.17s/2.12u sec elapsed 11.04 sec. INFO: =EDndice "caeindclf_10" agora cont=E9m 6906667 vers=F5es de registro= s em 20537 p=E1ginas DETAIL: 63 vers=F5es de registros de =EDndices foram apagadas. 1035 p=E1ginas de =EDndice foram removidas, 1035 s=E3o reutiliz=E1veis. CPU 0.27s/1.97u sec elapsed 13.14 sec. 2008/11/26 Rafael Martinez > Rafael Domiciano wrote: > > Hei > > > I need some help or just some hints. I am having problems with vacuum > > full in one table only: "clifatura". > > That table has today around 7 million rows. > > > > How big is the database? > > > I scheduled on the server a cron job to run VACUUM FULL every day at 23 > > P.M, but on the following day, at 8 A.M. vacuum is yet working on > > "clifatura"; so I have to kill vacuum. > > > > Do you have any locks in this table/database that prevent the vacuum to > be started? > > Why do you use VACUUM FULL everyday? This will lock your database until > it finish. I can count with one hand the times we had to run vacuum full > in our systems in the last 6 years. > > Use a normal vacuum analyze if 'autovacuum' is not enough for you. > > -- > Rafael Martinez, > Center for Information Technology Services > University of Oslo, Norway > > PGP Public Key: http://folk.uio.no/rafael/ > ------=_Part_9311_21957445.1227711654581 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline The database has around 40 Gb.

If I not use vacuum full = everyday the database get very slow.

There is = no deadlock on the database.
The vacuum does a clean in the table= and in every index of the table "clifatura". And in the end of t= he vacuum, seems that vacuum is working hard on the table (Vacuum is using = CPU and memory), but doesn't end.

INFO:  limpando "public.clifatura"<= br>
INFO:  "clifatura": encontrados 63 vers=F5es d= e registros remov=EDveis e 6906667 n=E3o-remov=EDveis em 238511 p=E1ginas
DETAIL:  0 vers=F5es de registros n=E3o vigentes n=E3o podem = ser removidas ainda.
Vers=F5es de registros n=E3o-remov=EDveis est=E3o no intervalo de 168 = a 428 bytes.
Havia 262348 ponteiros de itens n=E3o utilizados.
Espa=E7o livre total (incluindo vers=F5es de registros remov=EDveis= ) =E9 99117652 bytes.
2351 p=E1ginas est=E3o ou estar=E3o vazias, incluindo 0 no fim da tabe= la.
95651 p=E1ginas contendo 87437816 bytes livres s=E3o destinos= potenciais.
CPU 2.86s/1.08u sec elapsed 92.21 sec.
INF= O:  =EDndice "caeindclf_01" agora cont=E9m 6906667 vers=F5es= de registros em 39824 p=E1ginas
DETAIL:  63 vers=F5es de registros de =EDndices foram apagadas.
0 p=E1ginas de =EDndice foram removidas, 0 s=E3o reutiliz=E1veis.<= /div>
CPU 0.58s/3.18u sec elapsed 26.82 sec.
INFO:  =EDn= dice "caeindclf_03" agora cont=E9m 6906667 vers=F5es de registros= em 43174 p=E1ginas
DETAIL:  63 vers=F5es de registros de =EDndices foram apagadas.
0 p=E1ginas de =EDndice foram removidas, 0 s=E3o reutiliz=E1veis.<= /div>
CPU 0.64s/3.18u sec elapsed 23.62 sec.
INFO:  =EDn= dice "caeindclf_04" agora cont=E9m 6906667 vers=F5es de registros= em 36346 p=E1ginas
DETAIL:  63 vers=F5es de registros de =EDndices foram apagadas.
0 p=E1ginas de =EDndice foram removidas, 0 s=E3o reutiliz=E1veis.<= /div>
CPU 0.50s/2.63u sec elapsed 19.97 sec.
INFO:  =EDn= dice "caeindclf_05" agora cont=E9m 6906667 vers=F5es de registros= em 35965 p=E1ginas
DETAIL:  63 vers=F5es de registros de =EDndices foram apagadas.
0 p=E1ginas de =EDndice foram removidas, 0 s=E3o reutiliz=E1veis.<= /div>
CPU 0.56s/3.06u sec elapsed 12.40 sec.
INFO:  =EDn= dice "caeindclf_06" agora cont=E9m 6906667 vers=F5es de registros= em 32085 p=E1ginas
DETAIL:  63 vers=F5es de registros de =EDndices foram apagadas.
0 p=E1ginas de =EDndice foram removidas, 0 s=E3o reutiliz=E1veis.<= /div>
CPU 0.49s/3.08u sec elapsed 10.68 sec.
INFO:  =EDn= dice "caeindclf_11" agora cont=E9m 6906667 vers=F5es de registros= em 28596 p=E1ginas
DETAIL:  63 vers=F5es de registros de =EDndices foram apagadas.
867 p=E1ginas de =EDndice foram removidas, 867 s=E3o reutiliz=E1ve= is.
CPU 0.39s/2.11u sec elapsed 9.40 sec.
INFO:  = =EDndice "caeindclf_12" agora cont=E9m 6906667 vers=F5es de regis= tros em 46721 p=E1ginas
DETAIL:  63 vers=F5es de registros de =EDndices foram apagadas.
287 p=E1ginas de =EDndice foram removidas, 287 s=E3o reutiliz=E1ve= is.
CPU 0.57s/2.59u sec elapsed 21.90 sec.
INFO:  = =EDndice "caeindclf_13" agora cont=E9m 6906667 vers=F5es de regis= tros em 28658 p=E1ginas
DETAIL:  63 vers=F5es de registros de =EDndices foram apagadas.
336 p=E1ginas de =EDndice foram removidas, 336 s=E3o reutiliz=E1ve= is.
CPU 0.33s/2.19u sec elapsed 17.31 sec.
INFO:  = =EDndice "caeindclf_16" agora cont=E9m 6906667 vers=F5es de regis= tros em 16565 p=E1ginas
DETAIL:  63 vers=F5es de registros de =EDndices foram apagadas.
399 p=E1ginas de =EDndice foram removidas, 399 s=E3o reutiliz=E1ve= is.
CPU 0.18s/1.88u sec elapsed 13.29 sec.
INFO:  = =EDndice "caeindclf_17" agora cont=E9m 6906667 vers=F5es de regis= tros em 28676 p=E1ginas
DETAIL:  63 vers=F5es de registros de =EDndices foram apagadas.
1433 p=E1ginas de =EDndice foram removidas, 1433 s=E3o reutiliz=E1= veis.
CPU 0.26s/2.00u sec elapsed 11.38 sec.
INFO: &nbs= p;=EDndice "caeindclf_14" agora cont=E9m 6906667 vers=F5es de reg= istros em 34523 p=E1ginas
DETAIL:  63 vers=F5es de registros de =EDndices foram apagadas.
256 p=E1ginas de =EDndice foram removidas, 256 s=E3o reutiliz=E1ve= is.
CPU 0.34s/2.31u sec elapsed 14.83 sec.
INFO:  = =EDndice "caeindclf_15" agora cont=E9m 6906667 vers=F5es de regis= tros em 31569 p=E1ginas
DETAIL:  63 vers=F5es de registros de =EDndices foram apagadas.
109 p=E1ginas de =EDndice foram removidas, 109 s=E3o reutiliz=E1ve= is.
CPU 0.34s/2.43u sec elapsed 14.05 sec.
INFO:  = =EDndice "caeindclf_18" agora cont=E9m 6906667 vers=F5es de regis= tros em 31570 p=E1ginas
DETAIL:  63 vers=F5es de registros de =EDndices foram apagadas.
451 p=E1ginas de =EDndice foram removidas, 451 s=E3o reutiliz=E1ve= is.
CPU 0.31s/2.41u sec elapsed 17.53 sec.
INFO:  = =EDndice "clifatura_pkey" agora cont=E9m 6906667 vers=F5es de reg= istros em 15833 p=E1ginas
DETAIL:  63 vers=F5es de registros de =EDndices foram apagadas.
0 p=E1ginas de =EDndice foram removidas, 0 s=E3o reutiliz=E1veis.<= /div>
CPU 0.17s/2.12u sec elapsed 11.04 sec.
INFO:  =EDn= dice "caeindclf_10" agora cont=E9m 6906667 vers=F5es de registros= em 20537 p=E1ginas
DETAIL:  63 vers=F5es de registros de =EDndices foram apagadas.
1035 p=E1ginas de =EDndice foram removidas, 1035 s=E3o reutiliz=E1= veis.
CPU 0.27s/1.97u sec elapsed 13.14 sec.

=
2008/11/26 Rafael Martinez <r.m.guerrero@usit.uio.no>
Rafael Domiciano wrote:

Hei

> I need some help or just some hints. I am having problems with vacuum<= br> > full in one table only: "clifatura".
> That table has today around 7 million rows.
>

How big is the database?

> I scheduled on the server a cron job to run VACUUM FULL every day at 2= 3
> P.M, but on the following day, at 8 A.M. vacuum is yet working on
> "clifatura"; so I have to kill vacuum.
>

Do you have any locks in this table/database that prevent the vacuum = to
be started?

Why do you use VACUUM FULL everyday? This will lock your database until
it finish. I can count with one hand the times we had to run vacuum full in our systems in the last 6 years.

Use a normal vacuum analyze if 'autovacuum' is not enough for you.<= br>
--
 Rafael Martinez, <r.m.= guerrero@usit.uio.no>
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/

------=_Part_9311_21957445.1227711654581--