Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: Large difference between elapsed time and run time

Re: Large difference between elapsed time and run time

From
Nikk Anderson
Date:

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.

Re: Large difference between elapsed time and run time

From
"Jeffrey D. Brower"
Date:
Excuse 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 -----
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.

Re: Large difference between elapsed time and run time

From
"scott.marlowe"
Date:
On Tue, 11 Mar 2003, Jeffrey D. Brower wrote:

> 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?

In PHP, you do NOT have the elegant connection pooling that jdbc and
AOLServer have.  It's easy to build an apache/php/postgresql server that
collapses under load if you don't know how to configure it to make sure
apache runs out of children before postgresql runs out of resources.

You have a connection for each apache child, and they are
per database and per users, so if you connect as frank to db1, then the
next page connects as jenny to db2, it can't reuse that connection.  The
setting in php.ini that says max persistant connects is PER PROCESS, not
total, so if you have that set to 5, and max apache children to 150, you
could theoretically wind up with 749 idle connections after a while.  Not
good.

If your machine is taking more than a few milliseconds to connect to
postgresql, something is very wrong with it.  It could be you're running
out of memory and having a swap storm, or that postgresql front ends are
crashing, or any other problem.  What does top or free show when you are
connecting?  i.e. how much memory is used by swap, how much is cache, how
much is shared, all that jazz.