DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=jvORL3Cv6LYa4THi6hwle5dYp9AJt1fgdL6O+Qunz1jRBvIi5TdvHy/prXvo/ln6jE
	0bACR2TO6KiG1jNPUrlM1lqHWp3WaNBmjooCg8jDI/dLfra8phFa7+3SIDM3BW56sCsu
	yHs5lUnOoxvq3J+Qgi+Iibqc8pgfGayaVc1D0=
MIME-Version: 1.0
In-Reply-To: <516a4a601002251529y7d445a19w15750315db8b5bd8@mail.gmail.com>
References: <516a4a601002251323udcc2b41tf5a09b968bef3a7c@mail.gmail.com>
	<4B86FE2A.1030205@2ndquadrant.com>
	<516a4a601002251529y7d445a19w15750315db8b5bd8@mail.gmail.com>
Date: Wed, 3 Mar 2010 12:09:38 -0500
Message-ID: <603c8f071003030909m1a27372dm4900badeac7c17df@mail.gmail.com>
Subject: Re: dbt2 performance
From: Robert Haas <robertmhaas@gmail.com>
To: Yu-Ju Hong <yuru.hong@gmail.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Thu, Feb 25, 2010 at 6:29 PM, Yu-Ju Hong <yuru.hong@gmail.com> wrote:
> Thanks for the reply.
>
> On Thu, Feb 25, 2010 at 5:48 PM, Greg Smith <greg@2ndquadrant.com> wrote:
>>
>> Yu-Ju Hong wrote:
>>>
>>> 2. Moreover, the disk utilization was high and the "await" time from
>>> iostat is around 500 ms. Could disk I/O limit the overall throughput? T=
he
>>> server has 2 SATA disks, one for system and postgresql and the other is
>>> dedicated to logging (pg_xlog). As far as I understand, modern database
>>> systems should be CPU-bound rather than I/O-bound, is it because I did =
not
>>> perform adequate performance tuning?
>>
>> dbt2 is almost exclusively disk I/O bound once the data set gets big
>> enough. =A0There are some applications where most of the data fits in RA=
M and
>> therefore CPU performance is the limiter. =A0dbt2 is exactly the opposit=
e of
>> such an application though, and the idea that "modern database systems
>> should be CPU bound" is not really true at all. =A0That's only the case =
if the
>> data you're operating on fits in RAM. =A0Otherwise, databases are just a=
s I/O
>> bound as they've always been. =A0Main thing that's changed is there's a =
lot
>> more RAM in systems nowadays.
>
> In my test, there was almost no disk reads (mostly disk writes), so I
> assumed the size of the database didn't cause the performance bottleneck.
> Maybe I was wrong. If so, should I increase shared_buffer?

Well if you're writing a lot of stuff to disk you could easily be I/O limit=
ed.

> Assuming that dbt2 was limited by disk I/O in my experiments, do you thin=
k
> the numbers I got with my server configuration are reasonable?

Since you've provided no details on your hardware configuration I'm
not sure how anyone could express an educated opinion on this
(personally I wouldn't know anyway, but others here would).

> Also, would you mind giving some examples where the applications are CPU
> bound? That could be useful information to me.

You'll typically be CPU bound when you're not I/O bound - i.e. when
the data that your accessing is small enough to fit in memory.

...Robert