Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 2BF909FA465 for ; Fri, 21 Sep 2007 14:30:55 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 95828-07 for ; Fri, 21 Sep 2007 14:30:46 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from web30006.mail.mud.yahoo.com (web30006.mail.mud.yahoo.com [209.191.69.23]) by postgresql.org (Postfix) with SMTP id DE0B69F9A42 for ; Fri, 21 Sep 2007 14:30:47 -0300 (ADT) Received: (qmail 57678 invoked by uid 60001); 21 Sep 2007 17:30: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:MIME-Version:Content-Type:Message-ID; b=bERvvGcsYGcSH6PX9ypBQ1j1ACMidzuYWWbGwpxvs9fncoXYRe6vPG/yjlDOOK6al1vOP8K5bAlR5uNQrNIzQY50n/49R1zTinik1+q6zMb02Iy7PA0XOBVFzktv+IIvtZ6K4VXC5G5B+5eLn7sfNAbW2cQ5/hew0c1vYC8yIh4=; X-YMail-OSG: 0yfNB54VM1lIdCz5pFgxb79TmlOEbM5LuFU2uVgAqQGJRyW4Cjqqjfz6QBb9XAT8YbhCSJCFm_BcXvSYge4CYO50quZjHtUJpAKUCbNSpQ703tjnfcB4EPCYDZpqqg-- Received: from [68.142.201.143] by web30006.mail.mud.yahoo.com via HTTP; Fri, 21 Sep 2007 10:30:45 PDT X-Mailer: YahooMailRC/651.50 YahooMailWebService/0.7.134 Date: Fri, 21 Sep 2007 10:30:45 -0700 (PDT) From: brauagustin-susc@yahoo.com.ar Subject: Re: Low CPU Usage To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1748347442-1190395845=:47531" Message-ID: <121711.47531.qm@web30006.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/372 X-Sequence-Number: 26917 --0-1748347442-1190395845=:47531 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable I'm doing several tests.=0ARight now I did a VACUUM FULL ANALYZE in both se= rvers.=0AIn the old one vacuum runs for about 354 seconds and in the new on= e 59 seconds.=0A=0AThen I have ran=0AEXPLAIN ANALYZE=0ASELECT *=0AFROM fact= _ven_renta fvr, dim_producto_std_producto dpp=0AWHERE=0A fvr.producto_std_= producto_sk =3D dpp.producto_sk=0A=0AI have found that the plans aren't exa= ctly the same.=0AThis is the plan for the old server:=0AHash Join (cost=3D= 449.55..8879.24 rows=3D136316 width=3D904) (actual time=3D50.734..1632.491 = rows=3D136316 loops=3D1)=0A Hash Cond: (fvr.producto_std_producto_sk =3D d= pp.producto_sk)=0A -> Seq Scan on fact_ven_renta fvr (cost=3D0.00..6044.= 16 rows=3D136316 width=3D228) (actual time=3D0.029..452.716 rows=3D136316 l= oops=3D1)=0A -> Hash (cost=3D403.69..403.69 rows=3D3669 width=3D676) (ac= tual time=3D50.582..50.582 rows=3D3669 loops=3D1)=0A -> Seq Scan on= dim_producto_std_producto dpp (cost=3D0.00..403.69 rows=3D3669 width=3D67= 6) (actual time=3D0.023..19.776 rows=3D3669 loops=3D1)=0ATotal runtime: 202= 2.293 ms=0A=0AAnd this 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=3D1)=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=3D136316 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 -> Se= q Scan on dim_producto_std_producto dpp (cost=3D0.00..403.69 rows=3D3669 w= idth=3D677) (actual time=3D0.003..3.157 rows=3D3669 loops=3D1)=0ATotal runt= ime: 553.619 ms=0A=0A=0AI see an "outer" join in the plan for the new serve= r. This is weird!!! There 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 plans different? May the backup recovery process have h= ad an error in the new server when restoring?=0A=0AI appreciate some help.= =0ARegards Agustin=0A=0A----- Mensaje original ----=0ADe: "brauagustin-susc= @yahoo.com.ar" =0APara: pgsql-performance@po= stgresql.org=0AEnviado: mi=E9rcoles 19 de septiembre de 2007, 14:38:13=0AAs= unto: [PERFORM] Low CPU Usage=0A=0AHi all.=0ARecently I have installed a br= and 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 ser= ver with an IDE disk, Red Hat EL 4 and PostgreSQL 8.2.3=0A=0AI have almost = the same postgresql.conf in both servers, but in the new one (I have more w= ork_mem than the other 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 fast= er than the IDE one.=0ARunning the same queries in both servers in the new = one it envolves almost 4 minutes instead of 18 seconds in the old one.=0ABo= th databases are the same, I have vacuum them and I don't know how to manag= e this issue.=0AThe 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!!!=0AWhat's= is happening with this server? I upgrade from 8.1.9 to 8.2.4 trying to sol= ve this issue but I can't find a solution.=0A=0AAny ideas?=0ARegards=0AAgus= tin=0A=0A=0A=0A=0A =0AEl Mundial de Rugby 2007=0ALas =FAltimas noticia= s en Yahoo! Deportes:=0A=0Ahttp://ar.sports.yahoo.com/mundialderugby=0A=0A= =0A=0A=0A=0A Los referentes m=E1s importantes en compra/ venta de auto= s se juntaron:=0ADemotores y Yahoo!=0AAhora comprar o vender tu auto es m= =E1s f=E1cil. Vist=E1 ar.autos.yahoo.com/ --0-1748347442-1190395845=:47531 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
I'm doing several tests.
Right now I did a VACUU= M 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 A= NALYZE
SELECT *
FROM fact_ven_renta fvr, dim_producto_std_producto dp= p
WHERE
  fvr.producto_std_producto_sk =3D dpp.producto_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=3D13631= 6 width=3D904) (actual time=3D50.734..1632.491 rows=3D136316 loops=3D1)
=   Hash Cond: (fvr.producto_std_producto_sk =3D dpp.producto_sk)
&nb= sp; ->  Seq Scan on fact_ven_renta fvr  (cost=3D0.00..6044.16 = rows=3D136316 width=3D228) (actual time=3D0.029..452.716 rows=3D136316 loops=3D1)
&nb= sp; ->  Hash  (cost=3D403.69..403.69 rows=3D3669 width=3D676) = (actual time=3D50.582..50.582 rows=3D3669 loops=3D1)
   &= nbsp;    ->  Seq Scan on dim_producto_std_producto d= pp  (cost=3D0.00..403.69 rows=3D3669 width=3D676) (actual time=3D0.023= ..19.776 rows=3D3669 loops=3D1)
Total runtime: 2022.293 ms

And th= is is the plan for the new server:
Hash Join  (cost=3D412.86..9524.= 13 rows=3D136316 width=3D905) (actual time=3D9.421..506.376 rows=3D136316 l= oops=3D1)
  Hash Cond: ("outer".producto_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.3= 18 rows=3D136316 loops=3D1)
  ->  Hash  (cost=3D403.69= ..403.69 rows=3D3669 width=3D677) (actual time=3D9.385..9.385 rows=3D3669 l= oops=3D1)
        ->  Seq Scan on dim_producto_std_producto dpp  (cost=3D0.00..= 403.69 rows=3D3669 width=3D677) (actual time=3D0.003..3.157 rows=3D3669 loo= ps=3D1)
Total runtime: 553.619 ms


I see an "outer" join in th= e 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 seconds and for = the new one for about 301 seconds.
Why are plans different? May the back= up recovery process have had an error in the new server when restoring?
=
I appreciate some help.
Regards Agustin

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

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 compra/venta de autos= se juntaron:
Demotores y Yahoo!.=0AAhora comprar o vender tu auto es m= =E1s f=E1cil.
Visit=E1 http://ar.autos.yahoo.com/ --0-1748347442-1190395845=:47531--