pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedLarge difference between elapsed time and run time for queries
8+ messages / 5 participants
[nested] [flat]
* Large difference between elapsed time and run time for queries
@ 2003-03-10 18:58 Scott Buchan <sbuchan@technicacorp.com>
2003-03-10 20:07 ` Re: Large difference between elapsed time and run time for queries Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Scott Buchan @ 2003-03-10 18:58 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Large difference between elapsed time and run time for queries
2003-03-10 18:58 Large difference between elapsed time and run time for queries Scott Buchan <sbuchan@technicacorp.com>
@ 2003-03-10 20:07 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2003-03-10 20:07 UTC (permalink / raw)
To: Scott Buchan <sbuchan@technicacorp.com>; +Cc: pgsql-performance
"Scott Buchan" <sbuchan@technicacorp.com> writes:
> 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.
How many rows are these queries returning? AFAICS the differential must
be the cost of transmitting the data to the frontend, which of course
does not happen when you use explain analyze. (I think, but am not
completely sure, that explain analyze also suppresses the CPU effort of
converting the data to text form, as would normally be done before
transmitting it. But given that you don't see a problem at 100
connections, that's probably not where the issue lies.)
> The database is being ran on a sunfire 880 with 4 750mhz
> processors with 8 G RAM running solaris 8
We have seen some other weird performance problems on Solaris (their
standard qsort apparently is very bad, for example). Might be that you
need to be looking at kernel behavior, not at Postgres.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Large difference between elapsed time and run time
@ 2003-03-11 08:46 Nikk Anderson <Nikk.Anderson@parallel.ltd.uk>
2003-03-11 14:24 ` Re: Large difference between elapsed time and run time Jeffrey D. Brower <jeff@pointhere.net>
0 siblings, 1 reply; 8+ messages in thread
From: Nikk Anderson @ 2003-03-11 08:46 UTC (permalink / raw)
To: Scott Buchan <sbuchan@technicacorp.com>; pgsql-performance
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Large difference between elapsed time and run time
2003-03-11 08:46 Re: Large difference between elapsed time and run time Nikk Anderson <Nikk.Anderson@parallel.ltd.uk>
@ 2003-03-11 14:24 ` Jeffrey D. Brower <jeff@pointhere.net>
2003-03-11 20:50 ` Re: Large difference between elapsed time and run time scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 1 reply; 8+ messages in thread
From: Jeffrey D. Brower @ 2003-03-11 14:24 UTC (permalink / raw)
To: Nikk Anderson <Nikk.Anderson@parallel.ltd.uk>; 'Scott Buchan' <sbuchan@technicacorp.com>; pgsql-performance
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Large difference between elapsed time and run time
2003-03-11 08:46 Re: Large difference between elapsed time and run time Nikk Anderson <Nikk.Anderson@parallel.ltd.uk>
2003-03-11 14:24 ` Re: Large difference between elapsed time and run time Jeffrey D. Brower <jeff@pointhere.net>
@ 2003-03-11 20:50 ` scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 0 replies; 8+ messages in thread
From: scott.marlowe @ 2003-03-11 20:50 UTC (permalink / raw)
To: Jeffrey D. Brower <jeff@pointhere.net>; +Cc: Nikk Anderson <Nikk.Anderson@parallel.ltd.uk>; 'Scott Buchan' <sbuchan@technicacorp.com>; pgsql-performance
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Large difference between elapsed time and run time
@ 2003-03-11 14:30 Nikk Anderson <Nikk.Anderson@parallel.ltd.uk>
2003-03-11 14:53 ` Re: Large difference between elapsed time and run time Jeffrey D. Brower <jeff@pointhere.net>
0 siblings, 1 reply; 8+ messages in thread
From: Nikk Anderson @ 2003-03-11 14:30 UTC (permalink / raw)
To: 'Jeffrey D. Brower' <jeff@pointhere.net>; pgsql-performance
Hi,
-----Original Message-----
From: Jeffrey D. Brower [mailto:jeff@pointhere.net]
Sent: 11 March 2003 14:24
To: Nikk Anderson; 'Scott Buchan'; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Large difference between elapsed time and run time
>My question is how are you accomplishing the connection pooling?
I have programmed a connection pool in Java - I am sure something similar is possible in most other languages.
Very basically, the concept is as follows:
> Application initialisation
>>> 1) Create X number of connections to the database
>>> 2) Store connections in an Object
>>> 3) Create an array of free and busy connections - put all new connections in free connection array
>>> 4) Object is visible to all components of web application
> Request for a connection
>>> 4) Code asks for a connection from the pool object (3).
>>> 5) Pool object moves connection from free array, to the busy array.
>>> 5) Connection is used to do queries
>>> 6) Connection is sent back to pool object (3).
>>> 7) Pool object moves the connection from the busy array, back to the free array
I hope that helps!
Nikk
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Large difference between elapsed time and run time
2003-03-11 14:30 Re: Large difference between elapsed time and run time Nikk Anderson <Nikk.Anderson@parallel.ltd.uk>
@ 2003-03-11 14:53 ` Jeffrey D. Brower <jeff@pointhere.net>
0 siblings, 0 replies; 8+ messages in thread
From: Jeffrey D. Brower @ 2003-03-11 14:53 UTC (permalink / raw)
To: Nikk Anderson <Nikk.Anderson@parallel.ltd.uk>; pgsql-performance
RE: [PERFORM] Large difference between elapsed time and run timeYes, this helps a lot. Obviously this is far more complicated than just a simple pg_pconnect to accomplish a speedy reply. I really thought that a persistent connection was supposed to eliminate the overhead time in a connection (well, other than the first time through). But even if I am the only person on the machine it still takes forever to get a response every time I use http. I wondered if I was supposed to have a php program that had its own pconnect and every http call to the PostgreSQL database went to that php program rather than handling it by itself, but I found no indication of that while RTFM. I will give this a try and see if I can get the speed to anything reasonable. Thanks for the quick reply!
Jeff
----- Original Message -----
From: Nikk Anderson
To: 'Jeffrey D. Brower' ; pgsql-performance@postgresql.org
Sent: Tuesday, March 11, 2003 9:30 AM
Subject: Re: [PERFORM] Large difference between elapsed time and run time
Hi,
-----Original Message-----
From: Jeffrey D. Brower [mailto:jeff@pointhere.net]
Sent: 11 March 2003 14:24
To: Nikk Anderson; 'Scott Buchan'; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Large difference between elapsed time and run time
>My question is how are you accomplishing the connection pooling?
I have programmed a connection pool in Java - I am sure something similar is possible in most other languages.
Very basically, the concept is as follows:
> Application initialisation
>>> 1) Create X number of connections to the database
>>> 2) Store connections in an Object
>>> 3) Create an array of free and busy connections - put all new connections in free connection array
>>> 4) Object is visible to all components of web application
> Request for a connection
>>> 4) Code asks for a connection from the pool object (3).
>>> 5) Pool object moves connection from free array, to the busy array.
>>> 5) Connection is used to do queries
>>> 6) Connection is sent back to pool object (3).
>>> 7) Pool object moves the connection from the busy array, back to the free array
I hope that helps!
Nikk
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Large difference between elapsed time and run time for queries
@ 2003-03-11 18:23 Scott Buchan <sbuchan@technicacorp.com>
0 siblings, 0 replies; 8+ messages in thread
From: Scott Buchan @ 2003-03-11 18:23 UTC (permalink / raw)
To: pgsql-performance
Thanks for the quick reply.
I just upgraded from 7.2 to 7.3 since 7.3 uses a different qsort
(BSD-licensed). After running a few tests, I have noticed some performance
gains.
I think another problem that I was having was due to the way I was
performing the tests. I was using the tool "The Grinder" to simulate 300
connections (through JDBC) to the database each running 6 queries without
any connection pooling. Once I figure out how to use connection pooling
with the Grinder, I will try running the tests again.
Do you know of any other performance issues with using Solaris?
Thanks for the help,
Scott
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, March 10, 2003 3:08 PM
To: Scott Buchan
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Large difference between elapsed time and run time
for queries
"Scott Buchan" <sbuchan@technicacorp.com> writes:
> 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.
How many rows are these queries returning? AFAICS the differential must
be the cost of transmitting the data to the frontend, which of course
does not happen when you use explain analyze. (I think, but am not
completely sure, that explain analyze also suppresses the CPU effort of
converting the data to text form, as would normally be done before
transmitting it. But given that you don't see a problem at 100
connections, that's probably not where the issue lies.)
> The database is being ran on a sunfire 880 with 4 750mhz
> processors with 8 G RAM running solaris 8
We have seen some other weird performance problems on Solaris (their
standard qsort apparently is very bad, for example). Might be that you
need to be looking at kernel behavior, not at Postgres.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-03-11 20:50 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-10 18:58 Large difference between elapsed time and run time for queries Scott Buchan <sbuchan@technicacorp.com>
2003-03-10 20:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-11 08:46 Re: Large difference between elapsed time and run time Nikk Anderson <Nikk.Anderson@parallel.ltd.uk>
2003-03-11 14:24 ` Re: Large difference between elapsed time and run time Jeffrey D. Brower <jeff@pointhere.net>
2003-03-11 20:50 ` Re: Large difference between elapsed time and run time scott.marlowe <scott.marlowe@ihs.com>
2003-03-11 14:30 Re: Large difference between elapsed time and run time Nikk Anderson <Nikk.Anderson@parallel.ltd.uk>
2003-03-11 14:53 ` Re: Large difference between elapsed time and run time Jeffrey D. Brower <jeff@pointhere.net>
2003-03-11 18:23 Re: Large difference between elapsed time and run time for queries Scott Buchan <sbuchan@technicacorp.com>
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