Re: choosing the right platform - Mailing list pgsql-performance

On Wed, Apr 09, 2003 at 10:51:34AM -0600, scott.marlowe wrote:
> Secondly, look carefully at using persistant connections in large numbers.
>
> While persistant connections DO represent a big savings in connect time,
> the savings are lost in the noise of many PHP applications.
>
> i.e. my dual PIII swiss army knife server can initiate single persistant
> connections at 1,000,000 a second (reusing the old ones of course).
> non-persistant connects happen at 1,000 times a second.  Most of my
> scripts run in 1/10th of a second or so, so the 1/1000th used to connect
> is noise to me.

My $0.02 from my experience with Sybase and DB2:
It's not the connection *time* that's an issue, it's the amount of
resources (mostly memory) used by each database connection. Each db2
connection to a database uses 4-8 meg of memory; on my pgsql system,
each connection appears to be using about 4M. This is the resident set,
which I believe indicates memory that basically can't be shared. All
this memory is memory that can't be used for buffering/caching; on a
system with a hundred connections, it can really start to add up.

If your PHP is written in such a way that it does all the database work
in one section of code, and only holds a connection to the database in
that one section, then you can potentially have a lot of apache
processes for each database connection.

Of course, all this holds true wether you're using pooling or not. How
much pooling will help depends on how expensive it is for the *database*
to handle each new connection request, and how your code is written.
Since it's often not possible to put all you database code in one place
like I mentioned above, an alternative is to connect right before you do
an operation, and disconnect as soon as you're done. This doesn't add
much (if any) expense if you're using pooling, but it's a very different
story if you're not using pooling.

> If you are going to use persistant connections, it might work better to
> let apache have only 20 or 40 children, which will force the apache
> children to "round robin" serve the requests coming in.
>
> This will usually work fine, since keeping the number of apache children
> down keeps the number of postgresql backends down, which keeps the system
> faster in terms of response time.  Turn keep alive down to something short
> like 10 seconds, or just turn it off, as keep alive doesn't really save
> all that much time in apache.

Very important advice. Generally, once you push a database past a
certain point, your performance degrades severely as the database
thrashes about trying to answer all the pending queries.
--
Jim C. Nasby (aka Decibel!)                    jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"