pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PHP Connections
7+ messages / 4 participants
[nested] [flat]

* PHP Connections
@ 2003-08-05 17:44 David Busby <busby@pnts.com>
  2003-08-05 18:46 ` Re: PHP Connections Lynna Landstreet <lynna@gallery44.org>
  2003-08-05 18:51 ` Re: PHP Connections scott.marlowe <scott.marlowe@ihs.com>
  2003-08-06 08:23 ` Re: PHP Connections Gerd Terlutter <gerd@MplusB.de>
  0 siblings, 3 replies; 7+ messages in thread

From: David Busby @ 2003-08-05 17:44 UTC (permalink / raw)
  To: pgsql-php

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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: PHP Connections
  2003-08-05 17:44 PHP Connections David Busby <busby@pnts.com>
@ 2003-08-05 18:46 ` Lynna Landstreet <lynna@gallery44.org>
  2003-08-05 19:07   ` Re: PHP Connections scott.marlowe <scott.marlowe@ihs.com>
  2 siblings, 1 reply; 7+ messages in thread

From: Lynna Landstreet @ 2003-08-05 18:46 UTC (permalink / raw)
  To: David Busby <busby@pnts.com>; pgsql-php

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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: PHP Connections
  2003-08-05 17:44 PHP Connections David Busby <busby@pnts.com>
  2003-08-05 18:46 ` Re: PHP Connections Lynna Landstreet <lynna@gallery44.org>
@ 2003-08-05 19:07   ` scott.marlowe <scott.marlowe@ihs.com>
  2003-08-05 19:45     ` Re: PHP Connections Lynna Landstreet <lynna@gallery44.org>
  0 siblings, 1 reply; 7+ messages in thread

From: scott.marlowe @ 2003-08-05 19:07 UTC (permalink / raw)
  To: Lynna Landstreet <lynna@gallery44.org>; +Cc: David Busby <busby@pnts.com>; pgsql-php

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.




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: PHP Connections
  2003-08-05 17:44 PHP Connections David Busby <busby@pnts.com>
  2003-08-05 18:46 ` Re: PHP Connections Lynna Landstreet <lynna@gallery44.org>
  2003-08-05 19:07   ` Re: PHP Connections scott.marlowe <scott.marlowe@ihs.com>
@ 2003-08-05 19:45     ` Lynna Landstreet <lynna@gallery44.org>
  2003-08-05 19:46       ` Re: PHP Connections scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Lynna Landstreet @ 2003-08-05 19:45 UTC (permalink / raw)
  To: pgsql-php

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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: PHP Connections
  2003-08-05 17:44 PHP Connections David Busby <busby@pnts.com>
  2003-08-05 18:46 ` Re: PHP Connections Lynna Landstreet <lynna@gallery44.org>
  2003-08-05 19:07   ` Re: PHP Connections scott.marlowe <scott.marlowe@ihs.com>
  2003-08-05 19:45     ` Re: PHP Connections Lynna Landstreet <lynna@gallery44.org>
@ 2003-08-05 19:46       ` scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 0 replies; 7+ messages in thread

From: scott.marlowe @ 2003-08-05 19:46 UTC (permalink / raw)
  To: Lynna Landstreet <lynna@gallery44.org>; +Cc: pgsql-php

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.  :-)




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: PHP Connections
  2003-08-05 17:44 PHP Connections David Busby <busby@pnts.com>
@ 2003-08-05 18:51 ` scott.marlowe <scott.marlowe@ihs.com>
  2 siblings, 0 replies; 7+ messages in thread

From: scott.marlowe @ 2003-08-05 18:51 UTC (permalink / raw)
  To: David Busby <busby@pnts.com>; +Cc: pgsql-php

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.




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: PHP Connections
  2003-08-05 17:44 PHP Connections David Busby <busby@pnts.com>
@ 2003-08-06 08:23 ` Gerd Terlutter <gerd@MplusB.de>
  2 siblings, 0 replies; 7+ messages in thread

From: Gerd Terlutter @ 2003-08-06 08:23 UTC (permalink / raw)
  To: David Busby <busby@pnts.com>; +Cc: pgsql-php

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        #
--------------------------------------------------------





^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2003-08-06 08:23 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-05 17:44 PHP Connections David Busby <busby@pnts.com>
2003-08-05 18:46 ` Lynna Landstreet <lynna@gallery44.org>
2003-08-05 19:07   ` scott.marlowe <scott.marlowe@ihs.com>
2003-08-05 19:45     ` Lynna Landstreet <lynna@gallery44.org>
2003-08-05 19:46       ` scott.marlowe <scott.marlowe@ihs.com>
2003-08-05 18:51 ` scott.marlowe <scott.marlowe@ihs.com>
2003-08-06 08:23 ` Gerd Terlutter <gerd@MplusB.de>

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