Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 543479FA4E1 for ; Wed, 19 Sep 2007 16:13:42 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 59493-09-2 for ; Wed, 19 Sep 2007 16:13:33 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from web30002.mail.mud.yahoo.com (web30002.mail.mud.yahoo.com [209.191.69.19]) by postgresql.org (Postfix) with SMTP id 569029F9D1C for ; Wed, 19 Sep 2007 16:13:35 -0300 (ADT) Received: (qmail 63549 invoked by uid 60001); 19 Sep 2007 19:13:33 -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=FRmi+Z3201ONfVO1MNLnokPmzN4WcL+SHLJJzWi5WkcKUWtv2aTSFghF13F+gZ/EPkE6CIfV+ZFGgOPmX/E6epdpOyXDUrcXpiqy/BRiuHCSEIFnHVa2yxXnAKA7k3QMN6AC+LRbj21Hp6sEPsNSe/DNgFv+XmZneU+tF63ZXh8=; X-YMail-OSG: pqYaS6AVM1kpRHfUtHEq9SC9i3i11ZbU4Z2mdzZJMoSbHVKeIpnYlwZj9iKPQndTrw-- Received: from [209.191.106.123] by web30002.mail.mud.yahoo.com via HTTP; Wed, 19 Sep 2007 12:13:33 PDT X-Mailer: YahooMailRC/651.50 YahooMailWebService/0.7.134 Date: Wed, 19 Sep 2007 12:13:33 -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-42887485-1190229213=:63342" Message-ID: <618808.63342.qm@web30002.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/321 X-Sequence-Number: 26866 --0-42887485-1190229213=:63342 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable No, changing to fsync off didn't improve performance at all.=0ASettings=0Aw= ork_mem =3D 64MB=0Amax_stack_depth =3D 7MB #in the old server is 8MB but if= I set in here give me the ulimit error=0Amax_fsm_pages =3D 204800=0Aeffect= ive_cache_size =3D 512MB=0AAtuvacuum is off.=0A=0AI have run vacuum full an= d vacuum analyze. The databases are freezed (there are no insert, update or= delete operations)!!! =0AIn both servers the query plans are identical wit= h the same costs too!!!=0A=0AIt's really weird, I don't see high values mon= itoring disk. Cpu usage is about 5% and sometimes a tips of 40 % of disk us= age=0A when the query is finishing (the first 3 minutes there are some tips= of 11 to 16%).=0A=0A=0A=0A=0A=0A=0A----- Mensaje original ----=0ADe: Mark = Lewis =0APara: brauagustin-susc@yahoo.com.ar=0ACC: pgs= ql-performance@postgresql.org=0AEnviado: mi=E9rcoles 19 de septiembre de 20= 07, 15:03:06=0AAsunto: Re: [PERFORM] Low CPU Usage=0A=0AOn Wed, 2007-09-19 = at 10:38 -0700, brauagustin-susc@yahoo.com.ar wrote:=0A> Hi all.=0A> Recent= ly I have installed a brand new server with a Pentium IV 3.2=0A> GHz, SATA = Disk, 2GB of Ram in Debian 4.0r1 with PostgreSQL 8.2.4=0A> (previously a 8.= 1.9).=0A> I have other similar server with an IDE disk, Red Hat EL 4 and=0A= > PostgreSQL 8.2.3=0A> =0A> I have almost the same postgresql.conf in both = servers, but in the new=0A> one (I have more work_mem than the other one) t= hings go really slow.=0A> I began to monitor i/o disk and it's really ok, I= have test disk with=0A> hdparm and it's 5 times faster than the IDE one.= =0A> Running the same queries in both servers in the new one it envolves=0A= > almost 4 minutes instead of 18 seconds in the old one.=0A> Both databases= are the same, I have vacuum them and I don't know how=0A> to manage this i= ssue.=0A> The only weird thing is than in the older server running the quer= y it=0A> uses 30% of CPU instead of 3 o 5 % of the new one!!!=0A> What's is= happening with this server? I upgrade from 8.1.9 to 8.2.4=0A> trying to so= lve this issue but I can't find a solution.=0A> =0A> Any ideas?=0A=0AIt cou= ld be a planning issue. Have you analyzed the new database to=0Agather up-= to-date statistics? A comparison of EXPLAIN ANALYZE results=0Afor an examp= le query in both systems should answer that one.=0A=0AAnother possibility b= ecause you're dealing with lower-end drives is that=0Ayou have a case of on= e drive lying about fsync where the other is not.=0AIf possible, try runnin= g your test with fsync=3Doff on both servers. If=0Athere's a marked improv= ement on the new server but no significant change=0Aon the old server then = you've found your culprit.=0A=0A-- Mark Lewis=0A=0A=0A---------------------= ------(end of broadcast)---------------------------=0ATIP 2: Don't 'kill -9= ' the postmaster=0A=0A=0A=0A=0A=0A=0A=0A Las =FAltimas noticias sobre = el Mundial de Rugby 2007 est=E1n en Yahoo! Deportes. =A1Conocelas!=0Ahttp:/= /ar.sports.yahoo.com/mundialderugby --0-42887485-1190229213=:63342 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
No, changing to fsync off didn't im= prove performance at all.
Settings
work_mem = =3D 64MB
max_stack_depth =3D 7MB #in the old server is 8MB but if I set = in here give me the ulimit error
max_fsm_pages =3D 204800
effective_c= ache_size =3D 512MB
Atuvacuum is off.

I have run vacuum full and = vacuum analyze. The databases are freezed (there are no insert, update or delete operation= s)!!!
In both servers the query plans are identical with the same costs= too!!!

It's really weird, I don't see high values monitoring disk. = Cpu usage is about 5% and sometimes a tips of 40 % of disk usage=0A when th= e query is finishing (the first 3 minutes there are some tips of 11 to 16%)= .


----- Mensaje original ----<= br>De: Mark Lewis <mark.lewis@mir3.com>
Para: brauagustin-susc@yah= oo.com.ar
CC: pgsql-performance@postgresql.org
Enviado: mi=E9rcoles 1= 9 de septiembre de 2007, 15:03:06
Asunto: Re: [PERFORM] Low CPU Usage
On Wed, 2007-09-19 at 10:38 -0700, brauagustin-susc@yahoo.com.ar = wrote:
> 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 w= ith PostgreSQL 8.2.4
> (previously a 8.1.9).
> I have other sim= ilar server with an IDE disk, Red Hat EL 4 and
> PostgreSQL 8.2.3
= >
> I have almost the same postgresql.conf in both servers, but i= n the new
> one (I have more work_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 faster than the = IDE one.
> Running the same queries in both servers in the new one it= envolves
> almost 4 minutes instead of 18 seconds in the old one.> Both databases are the same, I have vacuum them and I don't know how<= br>> to manage this issue.
> The only weird thing is than in the o= lder server running the query it
> uses 30% of CPU instead of 3 o 5 %= of the new one!!!
> What's is happening with this server? I upgrade = from 8.1.9 to 8.2.4
> trying to solve this issue but I can't find a s= olution.
>
> Any ideas?

It could be a planning issue.&n= bsp; Have you analyzed the new database to
gather up-to-date statis= tics?  A comparison of EXPLAIN ANALYZE results
for an example = query in both systems should answer that one.

Another possibility because you're dealing with lower-end drives is that
you ha= ve a case of one drive lying about fsync where the other is not.
If poss= ible, try running your test with fsync=3Doff on both servers.  If=
there's a marked improvement on the new server but no significant chang= e
on the old server then you've found your culprit.

-- Mark Lewis=


---------------------------(end of broadcast)------------------= ---------
TIP 2: Don't 'kill -9' the postmaster


=0A=0A=0A=0A

Los referentes m=E1s importantes en compra/venta de autos se juntar= on:
Demotores y Yahoo!.=0AAhora comprar o vender tu auto es m=E1s f=E1ci= l.
Visit=E1 http://ar.autos.yahoo.com/ --0-42887485-1190229213=:63342--