Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id D4E1463373B for ; Thu, 25 Feb 2010 17:27:51 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 19647-01-5 for ; Thu, 25 Feb 2010 21:27:36 +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.27]) by mail.postgresql.org (Postfix) with ESMTP id 892BF6335F8 for ; Thu, 25 Feb 2010 17:27:00 -0400 (AST) Received: by qw-out-2122.google.com with SMTP id 9so99070qwb.61 for ; Thu, 25 Feb 2010 13:26:59 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:date:message-id:subject :from:to:content-type; bh=B3Tc4vcMJpmAjQ8S47L4kM2+4ItcFxeZJknyYt0r4cE=; b=UBr5qSXwGL7AOepgwsAwsKrsW/r8aJoLMEVY+P2MGVNoUmyiPSWl0anRA1RibOQ2gs +ytu8NrYVOksTUHLiAHcHwgLyzp2vuLAewYGjO52lNSOIXXFjNPdI//nszmKeKhgLwrf T2gxiuFCodC21GqBKhLwW7xGMx5np27J9Qsig= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:date:message-id:subject:from:to:content-type; b=SoCrFI194N6Psop7NRXCnwfz5q9Ic7hSRNuXPH266CEzgxYUJgvf7GSzruGkreYw9Q Nu2wl1GmIKrIQfTeonGptbxMkAXIuv4J6TwcK5IBe/M4JSi4OV7Ou5jGIT5fQ8CPPmvr 764vlgZgesP6ENfAxqUZu4Nz9r3x6QOtFtJ1s= MIME-Version: 1.0 Received: by 10.229.230.84 with SMTP id jl20mr10098qcb.88.1267133063912; Thu, 25 Feb 2010 13:24:23 -0800 (PST) Date: Thu, 25 Feb 2010 16:23:53 -0500 Message-ID: <516a4a601002251323udcc2b41tf5a09b968bef3a7c@mail.gmail.com> Subject: dbt2 performance From: Yu-Ju Hong To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=00163630ff212866490480736800 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/357 X-Sequence-Number: 37778 --00163630ff212866490480736800 Content-Type: text/plain; charset=ISO-8859-1 Hi, I have a couple of questions about dbt2 performance. 1. I tested dbt2+postgresql 8.4.2 on my server, but the NOTPM is around only 320~390 with 10 connections and 30 warehouses. Increasing the number of connections did not improve the throughput? The NOPTM number does not seem very high to me. Should I try more configurations to see if it can be improved? Are there any numbers I can compare with (NOPTM and response time)? 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? 3. From "vmstat", the cpus spent around 72% of time idle, 25% waiting for I/O, and only 2~3% left doing real work. I was surprised that the cpu utilization was so low. Is that normal or could it be due to misconfiguration? In my opinion, even if disk I/O may have been stressed, 70% of idle time was still too high. Below are some specs/configurations that I used. Any suggestion is welcome. Thanks! server spec: 4 cores (2*Dual-Core AMD Opteron, 800MHz), 12GB ram 2 SATA disks, one for system and postgresql and the other is dedicated to logging (pg_xlog) postgres configuration: 30 warehouses 256MB shared_buffer 768MB effective_cache_size checkpoint_timeout 1hr (All my tests are within 10 minutes interval, so checkpointing should not interfere the performance) I turned off fsync to see whether the performance could be improved. Yu-Ju --00163630ff212866490480736800 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi,

I have a couple of questions about dbt2 performance.

1. I tested dbt2+postgresql 8.4.2 on my server, but the NOTPM is around only 320~390 with 10 connections and 30 warehouses. Increasing the number of connections did not improve the throughput? The NOPTM number does not seem very high to me. Should I try more configurations to see if it can be improved? Are there any numbers I can compare with (NOPTM and response time)?

2. Moreover, the disk utilization was high and the "await" ti= me 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?

3. From "vmstat", the cpus spent around 72% of time idle, 25% waiting for I/O, and only 2~3% left doing real work. I was surprised that the cpu utilization was so low. Is that normal or could it be due to misconfiguration? In my opinion, even if disk I/O may have been stressed, 70% of idle time was still too high.


Below are some specs/configurations that I used. Any suggestion is = welcome. Thanks!

server spec:
4 cores (2*Dual-Core AMD Opteron, 8= 00MHz), 12GB ram
2 SATA disks, one for system and postgresql and the oth= er is dedicated to logging (pg_xlog)

postgres configuration:
30 warehouses
256MB shared_buffer
768M= B effective_cache_size
checkpoint_timeout 1hr (All my tests are within 1= 0 minutes interval, so checkpointing should not interfere the performance)<= br> I turned off fsync to see whether the performance could be improved.
Yu-Ju
--00163630ff212866490480736800--