Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 59F579FB521 for ; Fri, 21 Sep 2007 16:34:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 08244-05 for ; Fri, 21 Sep 2007 16:33:46 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from web30004.mail.mud.yahoo.com (web30004.mail.mud.yahoo.com [209.191.69.21]) by postgresql.org (Postfix) with SMTP id 77FF29FB51E for ; Fri, 21 Sep 2007 16:33:47 -0300 (ADT) Received: (qmail 8531 invoked by uid 60001); 21 Sep 2007 19:33:46 -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:Cc:MIME-Version:Content-Type:Message-ID; b=lydXiZW5ZvlSRY4ICMHe+G22pbf/VugqOFpHgxNzWBSPE5etOBrNg5uQI6oH+OTejg+mT+A5AQKtd5iwnT5WnDB/1E/w+PsMAlfLcyEwyn34RoTNE6vqyOiYH5BZh7XEeafNOwsOt6Z0at4BtQZ9xSE7jYoQDyDuksNJ12eVv/8=; X-YMail-OSG: A9A68zIVM1n9Av4AHxv1tnP.RwG7zdQUV6ODRYJAUc0qbZF3mZUINmuBo6QDTHw485GZJniD.lt5o8q9oeVHCJEqCfvhrO44Ou4w3Z5F_hsqUyDP9Lg- Received: from [209.191.118.120] by web30004.mail.mud.yahoo.com via HTTP; Fri, 21 Sep 2007 12:33:46 PDT X-Mailer: YahooMailRC/651.50 YahooMailWebService/0.7.134 Date: Fri, 21 Sep 2007 12:33:46 -0700 (PDT) From: brauagustin-susc@yahoo.com.ar Subject: Re: Low CPU Usage To: Bill Moran Cc: Kevin Grittner , pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-861371963-1190403226=:8256" Message-ID: <322942.8256.qm@web30004.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/385 X-Sequence-Number: 26930 --0-861371963-1190403226=:8256 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable > > >> > That's not what it looks like based on the EXPLAIN ANALYZE output.= =0A> > >> > It looks like run time dropped from two seconds to half a secon= d.=0A> > >> =0A> > >> > It seems as though you either have a network delay= delivering the results,=0A> > >> > or your application is slow to read the= m.=0A> > >> =0A> > >> > Exactly how are you arriving at those timings you'r= e reporting to us?=0A> > >> =0A> > >> I have noticed this in a daly proces= s I run which involves normally 45 minutes and with the new server takes 1:= 40.=0A> > >> =0A> > >> Some days ago I began to do some tests with no succe= ss, then I opened PgAdmin with this simply query to read 2 big tables and t= hen compare disk access.=0A> > >> SELECT *=0A> > >> FROM fact_ven_renta fvr= , dim_producto_std_producto dpp=0A> > >> WHERE=0A> > >> fvr.producto_std_= producto_sk =3D dpp.producto_sk=0A> > >> =0A> > >> fact_ven_renta has 1363= 16 rows=0A> > >> dim_producto_std_producto has 3669 rows=0A >> =0A> > >Run = the tests from psql on the same server. As Kevin pointed out, the _server_= is faster, but it appears as if the connection between PGadmin and this ne= w server is slower somehow.=0A> > =0A> > It runs quickly!!! But I don't kno= w how to compare because looks like it retrieve fields by demand, when I pu= t ctrl+end (go to the last record) it use a lot of CPU and disk, run quickl= y anyway.=0A=0A> That's pretty odd. If you use \timing in psql, you can ge= t execution=0A> time for each query, if it helps you track things down.=0A= =0AYes, in the new server running with \timing it consumes 5.6 seconds and = in the old server it consumes 25 seconds.=0A=0A> > Correct me if am I wrong= but, executing PgAdmin in the same server there aren't networks delays!=0A= =0A> Not network, no. But the results of your explains seem to show that t= he=0A> query is executing much faster on the new system than the old, so th= e=0A> problem still becomes, "what is happening after the query completes t= hat=0A> is so slow?" It's just that networking is ruled out.=0A=0AIs conne= cted to full 100Mb, it transfers many things quick to clients. Is running A= pache adn JBoss, transfer rate is good, I did scp to copy many archives and= is as quick as the old server.=0A=0AI have no idea how to continue researc= hing this problem. Now I'm going to do some networks tests.=0A=0A=0A-- =0AB= ill Moran=0ACollaborative Fusion Inc.=0Ahttp://people.collaborativefusion.c= om/~wmoran/=0A=0Awmoran@collaborativefusion.com=0APhone: 412-422-3463x4023= =0A=0A=0A=0A=0A=0A=0A=0A Las =FAltimas noticias sobre el Mundial de Ru= gby 2007 est=E1n en Yahoo! Deportes. =A1Conocelas!=0Ahttp://ar.sports.yahoo= .com/mundialderugby --0-861371963-1190403226=:8256 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
> > >> > That's not wha= t it looks like based on the EXPLAIN ANALYZE output.
> > >> = > It looks like run time dropped from two seconds to half a second.
&= gt; > >>  
> > >> > It seems as though= you either have a network delay delivering the results,
> > >&= gt; > or your application is slow to read them.
> > >> > > >> > Exactly how are you arriving at those timings you= 're reporting to us?
> > >>  
> > >>= ; I have noticed this in a daly process I run which involves normally 45 minutes and with the new server takes 1:40.
> > >>
>= > >> Some days ago I began to do some tests with no success, then= I opened PgAdmin with this simply query to read 2 big tables and then comp= are disk access.
> > >> SELECT *
> > >> FROM = fact_ven_renta fvr, dim_producto_std_producto dpp
> > >> WHE= RE
> > >>   fvr.producto_std_producto_sk =3D dpp.p= roducto_sk
> > >>  
> > >> fact_ven= _renta has 136316 rows
> > >> dim_producto_std_producto has = 3669 rows
 >>
> > >Run the tests from psql on th= e same server.  As Kevin pointed out, the _server_ is faster, but= it appears as if the connection between PGadmin and this new server is slo= wer somehow.
> >
> > It runs quickly!!! But I don't know= how to compare because looks like it retrieve fields by demand, when I put ctrl+end (go to the last record) it use a lot of CPU and disk, run q= uickly anyway.

> That's pretty odd.  If you use \timing= in psql, you can get execution
> time for each query, if it helps yo= u track things down.

Yes, in the new server running with \timing it = consumes 5.6 seconds and in the old server it consumes 25 seconds.

&= gt; > Correct me if am I wrong but, executing PgAdmin in the same server= there aren't networks delays!

> Not network, no.  But = the results of your explains seem to show that the
> query is executi= ng much faster on the new system than the old, so the
> problem still= becomes, "what is happening after the query completes that
> is so s= low?"  It's just that networking is ruled out.

Is connecte= d to full 100Mb, it transfers many things quick to clients. Is running Apac= he adn JBoss, transfer rate is good, I did scp to copy many archives and is as quick as the old server.

I have no idea how to continue r= esearching this problem. Now I'm going to do some networks tests.

--
Bill Moran
Collaborative Fusion Inc.
http://people.colla= borativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Ph= one: 412-422-3463x4023


=0A

Segu=ED de cerca a la Selecci= =F3n Argentina de Rugby
en el Mundial de Francia 2007.
=0Ahttp://ar.s= ports.yahoo.com/mundialderugby --0-861371963-1190403226=:8256--