X-Original-To: pgsql-performance@postgresql.org Received: from mailrelay (technicacorp.com [216.181.29.134]) by postgresql.org (Postfix) with ESMTP id CCC4D4768F9 for ; Mon, 10 Mar 2003 13:55:02 -0500 (EST) thread-index: AcLnNnfFrWGYq6kSSF6KqZb0roRZow== Content-Transfer-Encoding: 7bit Received: from WWWSERVER.technicacorp.com ([216.181.29.149]) by mailrelay with Microsoft SMTPSVC(5.0.2195.5329); Mon, 10 Mar 2003 13:54:23 -0500 Content-Class: urn:content-classes:message Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 Received: by wwwserver with Internet Mail Service (5.5.2656.59) id ; Mon, 10 Mar 2003 13:58:27 -0500 Message-ID: <71F044551C53974EB0735B3737EFE2FC088628@wwwserver> From: "Scott Buchan" To: Subject: Large difference between elapsed time and run time for queries Date: Mon, 10 Mar 2003 13:58:26 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2656.59) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C2E737.08116350" X-OriginalArrivalTime: 10 Mar 2003 18:54:23.0136 (UTC) FILETIME=[770C9A00:01C2E736] X-Archive-Number: 200303/72 X-Sequence-Number: 1370 This is a multi-part message in MIME format. ------_=_NextPart_001_01C2E737.08116350 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset="iso-8859-1" Hello, While running benchmarks for my database, I am seeing a large difference in the elapsed time (from stats collected in the logs) and run time (running explain analyze on the query using ./psql ) for each of my queries. The database is being ran on a sunfire 880 with 4 750mhz processors with 8 G RAM running solaris 8 I am simulating 200 user connections each running 6 select queries on 1 indexed table with 50,000 records. The elapsed time for the queries average around 2.5 seconds while if I run the query using explain analyze while the test is running, the run time is around 300 ms although it takes much longer (few seconds) to display the results. If I reduce the number of concurrent connections to 100 then the run time and elapsed time for the queries are the same. I have tried numerous configurations in the postgresql.conf file. I have set the shared_buffers with values ranging from 75 MB to 4000MB with no luck. I have also tried increasing the sort_mem with no luck. When the test is running, the cpu is well over 50% idle and iostat shows that the processes are not waiting for i/o and disk usage percentage is low. Any help would be appreciated. Thanks. ------_=_NextPart_001_01C2E737.08116350 Content-Transfer-Encoding: 7bit Content-Type: text/html; charset="iso-8859-1"

Hello,

 

While running benchmarks for my database, I am seeing a large difference in the elapsed time (from stats collected in the logs) and run time (running explain analyze on the query using ./psql <database>) for each of my queries.  The database is being ran on a sunfire 880 with 4 750mhz processors with 8 G RAM running solaris 8

 

I am simulating 200 user connections each running 6 select queries on 1 indexed table with 50,000 records. The elapsed time for the queries average around 2.5 seconds while if I run the query using explain analyze while the test is running, the run time is around 300 ms although it takes much longer (few seconds) to display the results.  If I reduce the number of concurrent connections to 100 then the run time and elapsed time for the queries are the same.

 

I have tried numerous configurations in the postgresql.conf file.  I have set the shared_buffers with values ranging from 75 MB to 4000MB with no luck.  I have also tried increasing the sort_mem with no luck.

 

 

When the test is running, the cpu is well over 50% idle and iostat shows that the processes are not waiting for i/o and disk usage percentage is low.

 

Any help would be appreciated.

 

Thanks.

------_=_NextPart_001_01C2E737.08116350--