Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 808F36338BB for ; Thu, 25 Feb 2010 19:30:00 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 86149-05-5 for ; Thu, 25 Feb 2010 23:29:49 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from qw-out-2122.google.com (qw-out-2122.google.com [74.125.92.24]) by mail.postgresql.org (Postfix) with ESMTP id 8A5A363362A for ; Thu, 25 Feb 2010 19:29:47 -0400 (AST) Received: by qw-out-2122.google.com with SMTP id 8so37604qwh.61 for ; Thu, 25 Feb 2010 15:29:43 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:content-type; bh=1g7iYPABNpCYmd01W+S+8wRnbvn52D4/jhplg1lRDjI=; b=qCIXPl0nNrmr3yL4o2l/+7vUZz48xDNxYJPPdT1ODSXT6XvU/J6gTebVDb6iZbpkQw YgwWXUtvdORA8cXFyD0/fzDq1cymGcClmAsa/zbnY/NH4poVBiQHMPdomlaSEFLlGWj4 3cX7TAqLbWv8HihKhvBmWlJXEB0BfotrcrN4c= 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 :content-type; b=r6QW8Ch5Gn9Men3ulky+41+mPPqbfmJNld0i4P//8nOZ2orrYdgqOC5H1dI12PvnfO NBzshllMe72VD3BCg8c6Q4mlNKiNc1LDbAc4UbOlh9L9xGr7vHJ+yK+ytA5tWz7mOEKw 4Dd8ArKbACMg40j/USR30yrbSvpWaQebNOKU0= MIME-Version: 1.0 Received: by 10.229.213.144 with SMTP id gw16mr44233qcb.3.1267140582788; Thu, 25 Feb 2010 15:29:42 -0800 (PST) In-Reply-To: <4B86FE2A.1030205@2ndquadrant.com> References: <516a4a601002251323udcc2b41tf5a09b968bef3a7c@mail.gmail.com> <4B86FE2A.1030205@2ndquadrant.com> Date: Thu, 25 Feb 2010 18:29:42 -0500 Message-ID: <516a4a601002251529y7d445a19w15750315db8b5bd8@mail.gmail.com> Subject: Re: dbt2 performance From: Yu-Ju Hong To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0016363b933e515068048075286b X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201002/359 X-Sequence-Number: 37780 --0016363b933e515068048075286b Content-Type: text/plain; charset=ISO-8859-1 Thanks for the reply. On Thu, Feb 25, 2010 at 5:48 PM, Greg Smith 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? The >> 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. There are some applications where most of the data fits in RAM and > therefore CPU performance is the limiter. dbt2 is exactly the opposite of > such an application though, and the idea that "modern database systems > should be CPU bound" is not really true at all. That's only the case if the > data you're operating on fits in RAM. Otherwise, databases are just as I/O > bound as they've always been. Main 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? Assuming that dbt2 was limited by disk I/O in my experiments, do you think the numbers I got with my server configuration are reasonable? Also, would you mind giving some examples where the applications are CPU bound? That could be useful information to me. > > By the way: a large increase in checkpoint_segments is the first thing you > should do. If you check the database logs, they're probably filled with > complaints about it being too low. 32 would be a useful starting value, > going much higher for a test that's only 10 minutes long is probably > cheating. > > I increased the checkpoint_segments to 10 when I ran the tests. I'll certainly increase it to 32 and give it a try. > -- > Greg Smith 2ndQuadrant US Baltimore, MD > PostgreSQL Training, Services and Support > greg@2ndQuadrant.com www.2ndQuadrant.us > > Thanks, Yu-Ju --0016363b933e515068048075286b Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Thanks for the reply.

O= n 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 f= rom iostat is around 500 ms. Could disk I/O limit the overall throughput? The 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 RAM and therefore CPU performance is the limiter. =A0dbt2 is exactly the opposite 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 as 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?

Assuming that dbt2 was limited by disk I/O in my experiments, do you think the numbers I got with my server configuration are reasonable?
Also, would you mind giving some examples where the applications are C= PU bound? That could be useful information to me.

By the way: =A0a large increase in checkpoint_segments is the first thing you should do. =A0If you check the database logs, they're probably fill= ed with complaints about it being too low. =A032 would be a useful starting value, going much higher for a test that's only 10 minutes long is probably cheating.


I increased the checkpoint_segments = to 10 when I ran the tests. I'll certainly increase it to 32 and give i= t a try.

=A0
--
Greg Smith =A02ndQuadrant US =A0Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com =A0 www.2ndQuadrant.us


Thanks,
Yu-J= u --0016363b933e515068048075286b--