pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jeffrey D. Brower <jeff@pointhere.net>
To: Nikk Anderson <Nikk.Anderson@parallel.ltd.uk>
To: 'Scott Buchan' <sbuchan@technicacorp.com>
To: pgsql-performance@postgresql.org
Subject: Re: Large difference between elapsed time and run time
Date: Tue, 11 Mar 2003 09:24:20 -0500
Message-ID: <00fb01c2e7d9$e804bfa0$0b02a8c0@pointhere.net> (raw)
References: <DA1274E682D3734B8802904A9B36124C298CD0@nic-nts1.nic.parallel.ltd.uk>

RE: [PERFORM] Large difference between elapsed time and run time for queriesExcuse me for butting into this conversation but I would LOVE to know exactly 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 the database using PHP4 I hit 45-50 second response times.  I even tried changing the connection to a persistent connection with pg_pconnect and I get the same thing.  I installed the database on the http machine and the responses are much quicker, but still not quite ideal.

My question is how are you accomplishing the connection pooling?

     Jeff 
  ----- Original Message ----- 
  From: Nikk Anderson 
  To: 'Scott Buchan' ; pgsql-performance@postgresql.org 
  Sent: Tuesday, March 11, 2003 3:46 AM
  Subject: Re: [PERFORM] Large difference between elapsed time and run time 


  Hi, 

  I have noted similar issues in the past - and seemed then that most of the 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 connections never close) then the execution time was far quicker. 

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

  Nikk 

  -----Original Message----- 
  From: Scott Buchan [mailto:sbuchan@technicacorp.com] 
  Sent: 10 March 2003 18:58 
  To: pgsql-performance@postgresql.org 
  Subject: [PERFORM] Large difference between elapsed time and run time for queries 



  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. 

view thread (8+ messages)  latest in thread

Message-ID: <00fb01c2e7d9$e804bfa0$0b02a8c0@pointhere.net>
Permalink:  ../00fb01c2e7d9$e804bfa0$0b02a8c0@pointhere.net/
Also on:    postgresql.org/message-id/00fb01c2e7d9$e804bfa0$0b02a8c0@pointhere.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: jeff@pointhere.net, Nikk.Anderson@parallel.ltd.uk, sbuchan@technicacorp.com
  Subject: Re: Large difference between elapsed time and run time
  In-Reply-To: <00fb01c2e7d9$e804bfa0$0b02a8c0@pointhere.net>
  References: <DA1274E682D3734B8802904A9B36124C298CD0@nic-nts1.nic.parallel.ltd.uk> <00fb01c2e7d9$e804bfa0$0b02a8c0@pointhere.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox