Re: Large difference between elapsed time and run time

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.