Thread: PHP Connections

List,
    Which way to connect is better for my scripts?

a global
  $db = pg_connect('asdfasdfasdfasdf');
and every function can have
  global $db;
at the top?

or like this?

function db_handle()
{
  return pg_connect('asdfasdfasdfasdfadsf');
}

and everyplace needed use `db_handle()` so I call pg_exec like

$rs = pg_exec(db_handle(),"select everything from everywhere"));

So does that db_handle() make a new connection each time?
I'm really looking for the way to optimise my connection usage.

David Busby
Systems Engineer
busby@pnts.com

on 8/5/03 1:44 PM, David Busby at busby@pnts.com wrote:

> Which way to connect is better for my scripts?
>
> a global
> $db = pg_connect('asdfasdfasdfasdf');
> and every function can have
> global $db;
> at the top?
>
> or like this?
>
> function db_handle()
> {
> return pg_connect('asdfasdfasdfasdfadsf');
> }
>
> and everyplace needed use `db_handle()` so I call pg_exec like
>
> $rs = pg_exec(db_handle(),"select everything from everywhere"));
>
> So does that db_handle() make a new connection each time?
> I'm really looking for the way to optimise my connection usage.

I'm more often found asking questions than answering them here, but I think
I'll have a go at this one. To my knowledge, a new connection is a bigger
hit on the server than a query, so from an efficiency standpoint it's best
to have fewer connections, and just make your queries from an existing
connections where possible. So you definitely don't want to be connecting
anew every time you call a function. Some web hosts who allot you a certain
amount of database traffic per plan and charge extra if you have more weight
connections more heavily than queries.

So the first option you have above is the best - the second would indeed
connect every time you called that function.

On my site I have one global include file with the db connection in it, and
just call it in the head section of each page. The code just references the
$db handle where it needs it, rather than connecting again. Even with that,
I keep wondering if it might be better to use a persistent connection that
would last from page to page rather than each page connecting over again.
But the problem there is that I don't necessarily know which page visitors
are going to come in on, so I need to make sure they always get a
connection, and connecting once per page seems like a reasonable compromise.

But hey, come to think of it, maybe I could turn the pg_connect into a
pg_pconnect and just have it check to see if $db already exists before
connecting - that way there'd be only one connection per site visit! Cool,
your question gave me a new idea... :-)


Lynna
--
Resource Centre Database Coordinator
Gallery 44
www.gallery44.org

On Tue, 5 Aug 2003, David Busby wrote:

> List,
>     Which way to connect is better for my scripts?
>
> a global
>   $db = pg_connect('asdfasdfasdfasdf');
> and every function can have
>   global $db;
> at the top?
>
> or like this?
>
> function db_handle()
> {
>   return pg_connect('asdfasdfasdfasdfadsf');
> }
>
> and everyplace needed use `db_handle()` so I call pg_exec like
>
> $rs = pg_exec(db_handle(),"select everything from everywhere"));
>
> So does that db_handle() make a new connection each time?
> I'm really looking for the way to optimise my connection usage.

All you're really doing here is wrapping your connects in a function.
since you (should) only connect at the top of the script, this function
will only be called once, and the difference in performance is negligable.
But the gains are worth the trouble because you can then switch out common
parts of each function you'd always do rather than cut and paste it into
all your scripts.

Taking this a step fruther, put all your pg_xxx functions
you'll use into an abstract layer that has them named like:

<?php
function db_connect($database){
    $conn_str = "host=w.x.y.z user=fred dbname=$dbname";
    return pg_connect($conn_str);
}

function db_query($query){
    more code here.
}
?>

Toss in some error checking and you can use those functions for things
like controlling which server or username the scripts are connecting from.
Then put them in the php_include path somewhere and just include_once()
the function lib.

This allows you to "switch out" things like databases or database servers
or accounts or what not in one fell swoop with ease.

On Tue, 5 Aug 2003, Lynna Landstreet wrote:

> On my site I have one global include file with the db connection in it, and
> just call it in the head section of each page. The code just references the
> $db handle where it needs it, rather than connecting again. Even with that,
> I keep wondering if it might be better to use a persistent connection that
> would last from page to page rather than each page connecting over again.
> But the problem there is that I don't necessarily know which page visitors
> are going to come in on, so I need to make sure they always get a
> connection, and connecting once per page seems like a reasonable compromise.
>
> But hey, come to think of it, maybe I could turn the pg_connect into a
> pg_pconnect and just have it check to see if $db already exists before
> connecting - that way there'd be only one connection per site visit! Cool,
> your question gave me a new idea... :-)

Be VERY wary of persistant connections.  They are fraught with danger and
can cause your server to present the "too many connections" error message
after being up for a while.

The reason is the dirt simple persistant connection technology in
PHP/Apache.  Each persistant connection stays open until the child process
dies or the connection errors or times out.  Since Postgresql, by default,
doesn't time out idle connections, and apache, by default doesn't kill
it's children every so often, only if there's too many, and postgresql, by
experience never seems to crash (backend or especially postmaster for me)
this means that each persistant connection functioanlly lasts forever.

So, if apache is configured for 150 children (the default) and postgresql
is configured for 32 children (again, the default) then after the server's
been up and under load, it is quite possible for all the connections to
postgresql to get exhausted and for the 33rd connection to get a too many
connection errors.

Easy fix, just crank up max backends, right?  Well, sorta.  The rule of
TANSTAAFL (there ain't no such thing as a free lunch.)  If Postgresql has
150+ connections sitting open and idle, it's likely not gonna run as fast
as if it had only 3 or 4 children open.

So, it's generally better to turn down the number of apache children to
control this problem.

But that brings up apache's keep alive mechanism, a standard part of the
http/1.1 protocol, which basically means that whichever request came from
ip w.x.y.z last time will likely get serviced by the same child, which
will stay assigned to that client as long as he clicks every X seconds
(usually 15 or so).

If apache has 30 keep alive connects going, and a max children setting of
150, when a new request from a new client comes along, it will NOT reuse
one of the pre-existing children unless its keep alive has timed out.  So,
if you've got 100 distinct users clicking every 7 to 15 seconds, you may
find apache spawning LOTS of children if you haven't turned it down.

Now, if the max children are set to 30, and apache has 30 keep alive
connections going, it will reuse one of the pre-existing child processes.

That means that whatever page cache was being kept for that client likely
is lost (but still probably in kernel buffers, so no huge loss.)

Now, things get real ugly when you start connecting to different
databases.

Say I have 64 databases, and my scripts have equal chances of using any
one of them depending on which customer site whatever they are hitting.

Now we look at pgsql.max_persistent.  this setting is PER CHILD.  Not for
the whole apache/php server.  If you have this set to unlimited, and your
child processes are hopping all over due to keep alive timeout/out of
children mentioned above, then you could theoretically have 64 persistant
connections open, per child.  That's 1920 connections.   ugh.

It's important to understand that php persistant connects are NOT
connection pooling in the classic java sense, and if not configured
properly, can bring an apache/php/postgresql server to it's knees under
fairly light load.

Put another way, whereas non-persistant connections have a slow degrade
under increasing of load, persistant connections, if improperly
configured, fail catastrophically under increasing load.

Configured properly:

pgsql.max_persistent set to 1 to 4
apache max_children set to 30 to 100
max_connections set to the two numbers above multiplied by each other,
plus an extra dozen or so for fudge factor

they can work quite well and help a busy web site.  But they aren't always
a gain, since having too many connections open at once can cause a
performance issue too.

on 8/5/03 3:07 PM, scott.marlowe at scott.marlowe@ihs.com wrote:

> On Tue, 5 Aug 2003, Lynna Landstreet wrote:
>
>> But hey, come to think of it, maybe I could turn the pg_connect into a
>> pg_pconnect and just have it check to see if $db already exists before
>> connecting - that way there'd be only one connection per site visit! Cool,
>> your question gave me a new idea... :-)
>
> Be VERY wary of persistant connections.  They are fraught with danger and
> can cause your server to present the "too many connections" error message
> after being up for a while. <rest snipped>

Hmmm, OK. I didn't realize they didn't time out. Maybe it's best to leave it
the way it is now, with just one pg_connect being called in the head of each
document. Thanks.


Lynna
--
Resource Centre Database Coordinator
Gallery 44
www.gallery44.org

On Tue, 5 Aug 2003, Lynna Landstreet wrote:

> on 8/5/03 3:07 PM, scott.marlowe at scott.marlowe@ihs.com wrote:
>
> > On Tue, 5 Aug 2003, Lynna Landstreet wrote:
> >
> >> But hey, come to think of it, maybe I could turn the pg_connect into a
> >> pg_pconnect and just have it check to see if $db already exists before
> >> connecting - that way there'd be only one connection per site visit! Cool,
> >> your question gave me a new idea... :-)
> >
> > Be VERY wary of persistant connections.  They are fraught with danger and
> > can cause your server to present the "too many connections" error message
> > after being up for a while. <rest snipped>
>
> Hmmm, OK. I didn't realize they didn't time out. Maybe it's best to leave it
> the way it is now, with just one pg_connect being called in the head of each
> document. Thanks.

they can be set to, but it's by request, not over time.  max requests per
child or some such setting.  Setting it low enough helps prevent certain
problems.  It's just that the default settings for apache/php/postgresql
are all designed to make your box go kaboom if you use persistant
connects.  :-)

David Busby wrote:
> List,
>     Which way to connect is better for my scripts?
>
> a global
>   $db = pg_connect('asdfasdfasdfasdf');
> and every function can have
>   global $db;
> at the top?
>
> or like this?
>
> function db_handle()
> {
>   return pg_connect('asdfasdfasdfasdfadsf');
> }
>
> and everyplace needed use `db_handle()` so I call pg_exec like
>
> $rs = pg_exec(db_handle(),"select everything from everywhere"));
>
> So does that db_handle() make a new connection each time?
> I'm really looking for the way to optimise my connection usage.
>

the glob is better when you use it undefined times in each of your
script. another way is
$dbh = db_handle();
$dbh->doThis(param);
$dbh->doThat(param);

or perhaps write an own class with specials for your page. e.g.
functions like update, getFrom, insertInto, deleteFrom.
best regards,
Gerd

--
--------------------------------------------------------
# Gerd Terlutter        | Mueller+Blanck Software GmbH #
# gerd@MplusB.de        | Gutenbergring 38             #
# gerd.terlutter@web.de | D-22848 Noderstedt           #
# tel:0171/6992579      | tel:+49 40 500 171-1         #
# Buero:040/500171-17   | fax:+49 40 500 171-71        #
--------------------------------------------------------