X-Original-To: pgsql-performance@postgresql.org Received: from localhost.localdomain (208-41-234-242.client.dsl.net [208.41.234.242]) by postgresql.org (Postfix) with ESMTP id 86917475CE5 for ; Tue, 11 Mar 2003 09:17:07 -0500 (EST) Received: from endeavour (endeavour.pointhere.net [192.168.2.11]) by localhost.localdomain (8.11.6/8.11.6) with SMTP id h2BE7co29797; Tue, 11 Mar 2003 09:07:38 -0500 Message-ID: <00fb01c2e7d9$e804bfa0$0b02a8c0@pointhere.net> Reply-To: "Jeffrey D. Brower" From: "Jeffrey D. Brower" To: "Nikk Anderson" , "'Scott Buchan'" , References: Subject: Re: Large difference between elapsed time and run time Date: Tue, 11 Mar 2003 09:24:20 -0500 Organization: A Basic Marketing Company MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00F8_01C2E7AF.FF0B9F40" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Archive-Number: 200303/76 X-Sequence-Number: 1374 This is a multi-part message in MIME format. ------=_NextPart_000_00F8_01C2E7AF.FF0B9F40 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: [PERFORM] Large difference between elapsed time and run time for querie= sExcuse me for butting into this conversation but I would LOVE to know exac= tly how you manage that pooling because I have this same issue. When I run= a test selection using psql I get sub-second response time and when I use = the online (a separate machine dedicated to http) and do a pg_connect to th= e database using PHP4 I hit 45-50 second response times. I even tried chan= ging the connection to a persistent connection with pg_pconnect and I get t= he same thing. I installed the database on the http machine and the respon= ses are much quicker, but still not quite ideal. My question is how are you accomplishing the connection pooling? Jeff=20 ----- Original Message -----=20 From: Nikk Anderson=20 To: 'Scott Buchan' ; pgsql-performance@postgresql.org=20 Sent: Tuesday, March 11, 2003 3:46 AM Subject: Re: [PERFORM] Large difference between elapsed time and run time= =20 Hi,=20 I have noted similar issues in the past - and seemed then that most of th= e overhead bottleneck was due to establishing a new connection in the front= end. As soon as I started using connection pooling, with connections made= when the app initialises, and then recycled for each request (i.e. the con= nections never close) then the execution time was far quicker.=20 I have also noticed that sparc processor speed, num processors, disk spac= e and memory seems to makes little difference with postgres (for us anyway!= ) performance - e.g. performance no better with dual sparc 450mhz, 2 scsi d= isks, 1Gb mem - than on a single processor 400 mhz Netra, 256Mb ram with a = single IDE disk! Nikk=20 -----Original Message-----=20 From: Scott Buchan [mailto:sbuchan@technicacorp.com]=20 Sent: 10 March 2003 18:58=20 To: pgsql-performance@postgresql.org=20 Subject: [PERFORM] Large difference between elapsed time and run time for= queries=20 Hello,=20 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 (runnin= g explain analyze on the query using ./psql ) for each of my quer= ies. The database is being ran on a sunfire 880 with 4 750mhz processors w= ith 8 G RAM running solaris 8 I am simulating 200 user connections each running 6 select queries on 1 i= ndexed 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 long= er (few seconds) to display the results. If I reduce the number of concurr= ent connections to 100 then the run time and elapsed time for the queries a= re 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 lu= ck. 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.=20 Thanks.=20 ------=_NextPart_000_00F8_01C2E7AF.FF0B9F40 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: [PERFORM] Large difference between elapsed time and = run time for queries
Excuse me for butting into this conversation but= I=20 would LOVE to know exactly how you manage that pooling because I have this = same=20 issue.  When I run a test selection using psql I get sub-second respon= se=20 time and when I use the online (a separate machine dedicated to http) = and=20 do a pg_connect to the database using PHP4 I hit 45-50 second response=20 times.  I even tried changing the connection to a persistent connectio= n=20 with pg_pconnect and I get the same thing.  I installed the datab= ase=20 on the http machine and the responses are much quicker, but still not quite= =20 ideal.
 
My question is how are you accomplishing the con= nection=20 pooling?
 
     Jeff
----- Original Message -----
Fro= m:=20 Nikk Anderson
To: 'Scott Buchan' ; pgsql-performance@postgr= esql.org=20
Sent: Tuesday, March 11, 2003 3:46= =20 AM
Subject: Re: [PERFORM] Large diffe= rence=20 between elapsed time and run time

Hi,

I have noted similar issues in the past - and seemed th= en that=20 most of the overhead bottleneck was due to establishing a new connection = in=20 the front end.  As soon as I started using connection pooling, with= =20 connections made when the app initialises, and then recycled for each req= uest=20 (i.e. the connections never close) then the execution time was far quicke= r.=20

I have also noticed that sparc processor speed, num=20 processors, disk space and memory seems to makes little difference with= =20 postgres (for us anyway!) performance - e.g. performance no better with d= ual=20 sparc 450mhz, 2 scsi disks, 1Gb mem - than on a single processor 400 mhz= =20 Netra, 256Mb ram with a single IDE disk!

Nikk

-----Original Message-----
Fr= om: Scott=20 Buchan [mailto:sbuchan@technicacorp.com<= /A>]=20
Sent: 10 March 2003 18:58
To= :=20 pgsql-performance@postgresql.org
Subject: [PERF= ORM]=20 Large difference between elapsed time and run time for queries

Hello,

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

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

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


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

Any help would be appreciated.

Thanks.

------=_NextPart_000_00F8_01C2E7AF.FF0B9F40--