Re: Large difference between elapsed time and run time

["scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com>]

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.