Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 8B5879FB219 for ; Fri, 21 Sep 2007 14:52:03 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 74889-06-5 for ; Fri, 21 Sep 2007 14:51:59 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from web30003.mail.mud.yahoo.com (web30003.mail.mud.yahoo.com [209.191.69.20]) by postgresql.org (Postfix) with SMTP id C35799FB378 for ; Fri, 21 Sep 2007 14:51:58 -0300 (ADT) Received: (qmail 78318 invoked by uid 60001); 21 Sep 2007 17:51:57 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.ar; h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID; b=tjZeszWTFZg3poZl1sVXRucUUr0db8DZvGF0mk07AzIGbnXB2VpE9obyf4bI4eag5jiIhs+qzQOdOMayANDYxIngy3d6e2X8pTrqmr7dOXLCNaLGG8S+z5TSqIj4WxGr3tIazoCCwkfpjRBHkcwtHVg5OJUL/yUNvNS0gItTTEU=; X-YMail-OSG: UGc0xQ8VM1lBtTnYHzEbQ8hTRCDTnT_f3scTqRbeMC1N5v8fF9Wh1yJjCLDtszfyb1r1P4t0heUfcimMqOf4K5kfhgj363CKr1LJ8puzqpd9GgSYCU373N1W5T6owg-- Received: from [68.142.201.144] by web30003.mail.mud.yahoo.com via HTTP; Fri, 21 Sep 2007 10:51:57 PDT X-Mailer: YahooMailRC/651.50 YahooMailWebService/0.7.134 Date: Fri, 21 Sep 2007 10:51:57 -0700 (PDT) From: brauagustin-susc@yahoo.com.ar Subject: Re: Low CPU Usage To: brauagustin-susc@yahoo.com.ar, pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1600521081-1190397117=:77287" Message-ID: <540890.77287.qm@web30003.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/375 X-Sequence-Number: 26920 --0-1600521081-1190397117=:77287 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable I forgot to tell this plan was with Postgres 8.1.9 in the new server with p= ostgres 8.2.4 in the new server the plan is the same as with te old one (th= e little difference in rows retrieved is that the database is yesterday sna= pshot).=0AThis is the plan for the new server with postgres 8.2.4:=0AHash J= oin (cost=3D449.55..8846.67 rows=3D135786 width=3D904) (actual time=3D10.8= 23..467.746 rows=3D135786 loops=3D1)=0A Hash Cond: (fvr.producto_std_produ= cto_sk =3D dpp.producto_sk)=0A -> Seq Scan on fact_ven_renta fvr (cost= =3D0.00..6020.86 rows=3D135786 width=3D228) (actual time=3D0.007..81.268 ro= ws=3D135786 loops=3D1)=0A -> Hash (cost=3D403.69..403.69 rows=3D3669 wid= th=3D676) (actual time=3D10.733..10.733 rows=3D3669 loops=3D1)=0A ->= Seq Scan on dim_producto_std_producto dpp (cost=3D0.00..403.69 rows=3D36= 69 width=3D676) (actual time=3D0.004..2.995 rows=3D3669 loops=3D1)=0ATotal = runtime: 513.747 ms=0A=0AThis query is running for about 200 seconds, doing= dstat I don't see anything weird (regards to low cpu usage 2% or 3%) and n= ormal i/o. In the old server I have 30% of cpu usage an high i/o and run fa= ster!!!=0AThis is really weird.=0A=0A=0A----- Mensaje original ----=0ADe: "= brauagustin-susc@yahoo.com.ar" =0APara: pgsq= l-performance@postgresql.org=0AEnviado: viernes 21 de septiembre de 2007, 1= 4:30:45=0AAsunto: Re: [PERFORM] Low CPU Usage=0A=0AI'm doing several tests.= =0ARight now I did a VACUUM FULL ANALYZE in both servers.=0AIn the old one = vacuum runs for about 354 seconds and in the new one 59 seconds.=0A=0AThen = I have ran=0AEXPLAIN ANALYZE=0ASELECT *=0AFROM fact_ven_renta fvr, dim_prod= ucto_std_producto dpp=0AWHERE=0A fvr.producto_std_producto_sk =3D dpp.prod= ucto_sk=0A=0AI have found that the plans aren't exactly the same.=0AThis is= the plan for the old server:=0AHash Join (cost=3D449.55..8879.24 rows=3D1= 36316 width=3D904) (actual time=3D50.734..1632.491 rows=3D136316 loops=3D1)= =0A Hash Cond: (fvr.producto_std_producto_sk =3D dpp.producto_sk)=0A -> = Seq Scan on fact_ven_renta fvr (cost=3D0.00..6044.16 rows=3D136316=0A widt= h=3D228) (actual time=3D0.029..452.716 rows=3D136316 loops=3D1)=0A -> Has= h (cost=3D403.69..403.69 rows=3D3669 width=3D676) (actual time=3D50.582..5= 0.582 rows=3D3669 loops=3D1)=0A -> Seq Scan on dim_producto_std_pro= ducto dpp (cost=3D0.00..403.69 rows=3D3669 width=3D676) (actual time=3D0.0= 23..19.776 rows=3D3669 loops=3D1)=0ATotal runtime: 2022.293 ms=0A=0AAnd thi= s is the plan for the new server:=0AHash Join (cost=3D412.86..9524.13 rows= =3D136316 width=3D905) (actual time=3D9.421..506.376 rows=3D136316 loops=3D= 1)=0A Hash Cond: ("outer".producto_std_producto_sk =3D "inner".producto_sk= )=0A -> Seq Scan on fact_ven_renta fvr (cost=3D0.00..6044.16 rows=3D1363= 16 width=3D228) (actual time=3D0.006..107.318 rows=3D136316 loops=3D1)=0A = -> Hash (cost=3D403.69..403.69 rows=3D3669 width=3D677) (actual time=3D9.= 385..9.385 rows=3D3669 loops=3D1)=0A =0A -> Seq Scan on dim_producto= _std_producto dpp (cost=3D0.00..403.69 rows=3D3669 width=3D677) (actual ti= me=3D0.003..3.157 rows=3D3669 loops=3D1)=0ATotal runtime: 553.619 ms=0A=0A= =0AI see an "outer" join in the plan for the new server. This is weird!!! T= here are the same databases in both servers.=0AThe old one runs this query = for about 37 seconds and for the new one for about 301 seconds.=0AWhy are p= lans different? May the backup recovery process have had an error in the ne= w server when restoring?=0A=0AI appreciate some help.=0ARegards Agustin=0A= =0A----- Mensaje original ----=0ADe: "brauagustin-susc@yahoo.com.ar" =0APara: pgsql-performance@postgresql.org=0AEnviad= o: mi=E9rcoles 19 de septiembre de 2007, 14:38:13=0AAsunto: [PERFORM] Low C= PU Usage=0A=0AHi all.=0ARecently I have installed a brand new server with a= Pentium IV 3.2 GHz, SATA Disk, 2GB of Ram in Debian 4.0r1 with PostgreSQL = 8.2.4 (previously a 8.1.9).=0AI have other similar server with an IDE disk,= Red Hat EL 4 and PostgreSQL 8.2.3=0A=0AI have almost the same postgresql.c= onf in both servers, but in the new one (I have more work_mem than the othe= r one) things go really slow. I began to monitor i/o disk and it's really = ok, I have test disk with hdparm and it's 5 times faster than the IDE one.= =0ARunning the same queries in both servers in the new one it envolves almo= st 4 minutes instead of 18 seconds in the old one.=0ABoth databases are the= same, I have vacuum them and I don't know how to manage this issue.=0AThe = only weird thing is than in the older server running=0A the query it uses 3= 0% of CPU instead of 3 o 5 % of the new one!!!=0AWhat's is happening with t= his server? I upgrade from 8.1.9 to 8.2.4 trying to solve this issue but I = can't find a solution.=0A=0AAny ideas?=0ARegards=0AAgustin=0A=0A=0A=0A=0A = =0AEl Mundial de Rugby 2007=0ALas =FAltimas noticias en Yahoo! Deportes= :=0A=0Ahttp://ar.sports.yahoo.com/mundialderugby=0A=0A=0A=0A=0A=0A=0A=0A=0A= =0ALos referentes m=E1s importantes en compra/venta de autos se junta= ron:=0ADemotores y Yahoo!.=0AAhora comprar o vender tu auto es m=E1s f=E1ci= l. =0A Visit=E1 http://ar.autos.yahoo.com/=0A=0A=0A=0A=0A=0A Los refer= entes m=E1s importantes en compra/ venta de autos se juntaron:=0ADemotores = y Yahoo!=0AAhora comprar o vender tu auto es m=E1s f=E1cil. Vist=E1 ar.auto= s.yahoo.com/ --0-1600521081-1190397117=:77287 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
I forgot to tell this plan was with Postgres 8.1.9 = in the new server with postgres 8.2.4 in the new server the plan is the sam= e as with te old one (the little difference in rows retrieved is that the d= atabase is yesterday snapshot).
This is the plan for the new server with= postgres 8.2.4:
Hash Join  (cost=3D449.55..8846.67 rows=3D135786 w= idth=3D904) (actual time=3D10.823..467.746 rows=3D135786 loops=3D1)
&nbs= p; Hash Cond: (fvr.producto_std_producto_sk =3D dpp.producto_sk)
  = ->  Seq Scan on fact_ven_renta fvr  (cost=3D0.00..6020.86 rows= =3D135786 width=3D228) (actual time=3D0.007..81.268 rows=3D135786 loops=3D1= )
  ->  Hash  (cost=3D403.69..403.69 rows=3D3669 width= =3D676) (actual time=3D10.733..10.733 rows=3D3669 loops=3D1)
    &n= bsp;   ->  Seq Scan on dim_producto_std_producto dpp = ; (cost=3D0.00..403.69 rows=3D3669 width=3D676) (actual time=3D0.004..2.995= rows=3D3669 loops=3D1)
Total runtime: 513.747 ms

This query is r= unning for about 200 seconds, doing dstat I don't see anything weird (regar= ds to low cpu usage 2% or 3%) and normal i/o. In the old server I have 30% = of cpu usage an high i/o and run faster!!!
This is really weird.

=
----- Mensaje original ----
De: "brauagustin-susc@yahoo.com.= ar" <brauagustin-susc@yahoo.com.ar>
Para: pgsql-performance@postgr= esql.org
Enviado: viernes 21 de septiembre de 2007, 14:30:45
Asunto: = Re: [PERFORM] Low CPU Usage

I'm doing several tests.
Right now = I did a VACUUM FULL ANALYZE in both servers.
In the old one vacuum runs = for about 354 seconds and in the new one 59 seconds.

Then I have ran=
EXPLAIN ANALYZE
SELECT *
FROM fact_ven_renta fvr, dim_producto_st= d_producto dpp
WHERE
  fvr.producto_std_producto_sk =3D dpp.prod= ucto_sk

I have found that the plans aren't exactly the same.
This= is the plan for the old server:
Hash Join  (cost=3D449.55..8879.24= rows=3D136316 width=3D904) (actual time=3D50.734..1632.491 rows=3D136316 l= oops=3D1)
  Hash Cond: (fvr.producto_std_producto_sk =3D dpp.produc= to_sk)
  ->  Seq Scan on fact_ven_renta fvr  (cost=3D0= .00..6044.16 rows=3D136316=0A width=3D228) (actual time=3D0.029..452.716 ro= ws=3D136316 loops=3D1)
  ->  Hash  (cost=3D403.69..403= .69 rows=3D3669 width=3D676) (actual time=3D50.582..50.582 rows=3D3669 loop= s=3D1)
        ->  Seq Scan o= n dim_producto_std_producto dpp  (cost=3D0.00..403.69 rows=3D3669 widt= h=3D676) (actual time=3D0.023..19.776 rows=3D3669 loops=3D1)
Total runti= me: 2022.293 ms

And this is the plan for the new server:
Hash Joi= n  (cost=3D412.86..9524.13 rows=3D136316 width=3D905) (actual time=3D9= .421..506.376 rows=3D136316 loops=3D1)
  Hash Cond: ("outer".produc= to_std_producto_sk =3D "inner".producto_sk)
  ->  Seq Scan = on fact_ven_renta fvr  (cost=3D0.00..6044.16 rows=3D136316 width=3D228= ) (actual time=3D0.006..107.318 rows=3D136316 loops=3D1)
  ->&nb= sp; Hash  (cost=3D403.69..403.69 rows=3D3669 width=3D677) (actual time= =3D9.385..9.385 rows=3D3669 loops=3D1)
     &nb= sp; =0A ->  Seq Scan on dim_producto_std_producto dpp  (c= ost=3D0.00..403.69 rows=3D3669 width=3D677) (actual time=3D0.003..3.157 row= s=3D3669 loops=3D1)
Total runtime: 553.619 ms


I see an "outer= " join in the plan for the new server. This is weird!!! There are the same = databases in both servers.
The old one runs this query for about 37 seco= nds and for the new one for about 301 seconds.
Why are plans different? = May the backup recovery process have had an error in the new server when re= storing?

I appreciate some help.
Regards Agustin

-= ---- Mensaje original ----
De: "brauagustin-susc@yahoo.com.ar" <braua= gustin-susc@yahoo.com.ar>
Para: pgsql-performance@postgresql.org
E= nviado: mi=E9rcoles 19 de septiembre de 2007, 14:38:13
Asunto: [PERFORM]= Low CPU Usage

Hi all.
Recently I have installed a brand new server with a= Pentium IV 3.2 GHz, SATA Disk, 2GB of Ram in Debian 4.0r1 with PostgreSQL = 8.2.4 (previously a 8.1.9).
I have other similar server with an IDE disk= , Red Hat EL 4 and PostgreSQL 8.2.3

I have almost the same postgresq= l.conf in both servers, but in the new one (I have more work_mem than the o= ther one) things go really slow.  I began to monitor i/o disk and it's= really ok, I have test disk with hdparm and it's 5 times faster than the I= DE one.
Running the same queries in both servers in the new one it envol= ves almost 4 minutes instead of 18 seconds in the old one.
Both database= s are the same, I have vacuum them and I don't know how to manage this issu= e.
The only weird thing is than in the older server running=0A the query= it uses 30% of CPU instead of 3 o 5 % of the new one!!!
What's is happe= ning with this server? I upgrade from 8.1.9 to 8.2.4 trying to solve this i= ssue but I can't find a solution.

Any ideas?
Regards
Agustin

=0A

El Mundial de Rugby 2007
Las =FAltimas noticias en Yahoo! Deport= es:
=0Ahttp://ar.sports.yahoo.com/mundialderugby=


=0A=0A=0A=0A
Los referentes m=E1s importantes en co= mpra/venta de autos se juntaron:
Demotores y Yahoo!.=0AAhora comprar o v= ender tu auto es m=E1s f=E1cil.
Visit=E1 http://ar.autos.yahoo.com/


=0A

El Mundial de Rugby 2007
Las =FAltimas noticias en Yahoo!= Deportes:
=0Ahttp://ar.sports.yahoo.com/mundialderugby --0-1600521081-1190397117=:77287--