X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0268453641 for ; Thu, 24 Mar 2005 09:49:44 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 48689-02 for ; Thu, 24 Mar 2005 09:49:42 +0000 (GMT) Received: from felix.adpcl.com (felix.adpcl.com [194.98.137.100]) by svr1.postgresql.org (Postfix) with ESMTP id DA55E53643 for ; Thu, 24 Mar 2005 09:49:41 +0000 (GMT) Received: from pcpatrickxp (unknown [150.175.11.30]) by felix.adpcl.com (Postfix) with SMTP id 976267804; Thu, 24 Mar 2005 10:52:46 +0100 (NFT) Message-ID: <006801c53056$a4585420$1e0baf96@pcpatrickxp> From: "Patrick Vedrines" To: "Simon Riggs" Cc: "performance pgsql" References: <002601c52f0a$2312a4d0$280baf96@PCPATRICK> <1111529891.11750.577.camel@localhost.localdomain> Subject: Re: CPU 0.1% IOWAIT 99% for decisonnal queries Date: Thu, 24 Mar 2005 10:48:33 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0065_01C5305F.06073850" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.314 tagged_above=0 required=5 tests=AWL, HTML_40_50, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200503/499 X-Sequence-Number: 11204 This is a multi-part message in MIME format. ------=_NextPart_000_0065_01C5305F.06073850 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello Simon, > Sounds like your disks/layout/something is pretty sick. You don't > mention I/O bandwidth, controller or RAID, so you should look more = into > those topics. Well seen ! (as we say in France). As I said to Gustavo, your last suspicion took me into a simple disk = test: I've just copyed (time cp) the "aggregate" table files (4 Gb) from = one disk to an another one: it takes 22 mn !(3 Mb/s). That seems to demonstrate that Postgres is not the cause of this issue.=20 I've just untrusted to my system engineer the analysis of my disks... > Setting shared_buffers that high will do you no good at all, as = Richard > suggests. I (and my own tests) agree my you. > You've got 1.5Gb of shared_buffers and > 2Gb data. In 8.0, the scan = will > hardly use the cache at all, nor will it ever, since the data is = bigger > than the cache. Notably, the scan of B should NOT spoil the cache for = A Are you sure of that ? Is Postgres able to say to OS: "don't use the = cache for this query"? > Priming the cache is quite hard...but not impossible. > What will kill you on a shared_buffers that big is the bgwriter, which > you should turn off by setting bgwriter_maxpages =3D 0 Is bgwriter concerned as my application applyes only SELECT ? >=20 > On the other hand...just go for more RAM, as you suggest...but you > should create a RAMdisk, rather than use too large > shared_buffers....that way your data is always in RAM, rather than = maybe > in RAM. I am not an Linux expert: Is it possible (and how) to create a RAMdisk ? Thank a lot for your help ! Patrick ------=_NextPart_000_0065_01C5305F.06073850 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hello Simon,
 
> Sounds like your = disks/layout/something is=20 pretty sick. You don't
> mention I/O bandwidth, controller or = RAID, so you=20 should look more into
> those topics.
Well seen ! (as we say in = France).
As I said to Gustavo, your last = suspicion took me=20 into a simple disk test: I've just copyed (time cp) the "aggregate" = table files=20 (4 Gb) from one disk to an another one: it takes 22 mn !(3 = Mb/s).
That seems to demonstrate that Postgres = is not the=20 cause of this issue.
I've just untrusted to my system = engineer the=20 analysis of my disks...
 
> Setting shared_buffers that high = will do you=20 no good at all, as Richard
> suggests.
I (and my own tests) = agree my=20 you.

> You've got 1.5Gb of = shared_buffers and=20 > 2Gb data. In 8.0, the scan will
> hardly use the cache at = all, nor=20 will it ever, since the data is bigger
> than the cache. Notably, = the scan=20 of B should NOT spoil the cache for A
Are you sure of that ? Is = Postgres able=20 to say to OS: "don't use the cache for this query"?

> Priming the cache is quite = hard...but not=20 impossible.
> What will kill you on a shared_buffers that big is = the=20 bgwriter, which
> you should turn off by setting bgwriter_maxpages = =3D=20 0
Is bgwriter concerned as my application = applyes=20 only SELECT ?

>
> On the other = hand...just go for=20 more RAM, as you suggest...but you
> should create a RAMdisk, = rather than=20 use too large
> shared_buffers....that way your data is always in = RAM,=20 rather than maybe
> in RAM.
I am not an Linux expert: Is it = possible=20 (and how) to create a RAMdisk ?
 
 

Thank a lot for your help = !
 
Patrick
------=_NextPart_000_0065_01C5305F.06073850--