pg.ddx.io pgsql-php@postgresql.org mailing list archive
help / color / mirror / Atom feedapache-php-postgresql connection question
5+ messages / 5 participants
[nested] [flat]
* apache-php-postgresql connection question
@ 2002-01-31 19:11 tom.kemp@philips.com
0 siblings, 0 replies; 5+ messages in thread
From: tom.kemp@philips.com @ 2002-01-31 19:11 UTC (permalink / raw)
To: pgsql-php
Hi,
I am new to apache, php, and postgresql. I had been tasked with creating a database for use in my work environment, accessible via web browsers. I read up on SQL, postgresql and mysql and chose postgresql. I have the database built and rebuildable via
sql script for postgresql implementation. I am being pushed to rewrite this and use MySQL by my IT department. I don't want to use MySQL, based on some advantages I have seen during comparison research and the expected future size and complexity of the
database. Some advantages that I am using are views, subqueries, triggers and foreign keys.
I have had to try to justify Postgresql ( IT Department). One advantage that I planned to make good use of is views. However, I am being told that if I use web access through apache to PHP to my postgresql server, I am opening a single user connection
and views become irrelevant in regard to permissions. My knowledge/expectation based on reading books was that I could use the database view permissions I set up for in postgresql for a go/no-go on database use of the views via apache server -> php
engine -> connection request to postgresql server.
I am a bit confused now. My understanding was I could use the pg_connect() command, supplying username and password in a php script, and have the connect be based on the security of the database permissions. Is this not true? Can I not do this for each
browser based request?
Also, if I do a persistent connection, it appears that PHP will try to use this connection if it exists, prior to creating a new persistent connection, right? Well, does it only reuse the already connected persistent if it is the same user? How does
this work? Does it check that it is the same user/password (does my script have to supply this in the second persistent connection function) prior to using the preexisting connection? Is it tied to the current browser request?
Thanks in advance for your time and help!
Tom
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: apache-php-postgresql connection question
@ 2002-01-31 21:23 Frank Bax <fbax@sympatico.ca>
2002-02-01 12:04 ` Re: apache-php-postgresql connection question\ Marco Colombo <marco@esi.it>
0 siblings, 1 reply; 5+ messages in thread
From: Frank Bax @ 2002-01-31 21:23 UTC (permalink / raw)
To: tom.kemp@philips.com; +Cc: pgsql-php
At 12:11 PM 1/31/02 -0700, tom.kemp@philips.com wrote:
>Also, if I do a persistent connection, it appears that PHP will try to
>use this connection if it exists, prior to creating a new persistent
>connection, right? Well, does it only reuse the already connected
>persistent if it is the same user? How does this work? Does it check
>that it is the same user/password (does my script have to supply this
>in the second persistent connection function) prior to using the
>preexisting connection? Is it tied to the current browser request?
We've talked a lot about pg_pconnect this week; I'll try to summarise what
we've learned...
Each browser request from client is handled by an apache child process on
server. Apache uses multiple child processes running concurrently to
handle these browser requests.
pg_pconnect will reuse a previously created connection to the database if
the previous connection was made within the same apache child process *and*
the connect string is "identical" (same host, database, username, password).
http://www.php.net/manual/en/features.persistent-connections.php
Using pg_pconnect() over pg_connect() may not always be a huge saving.
Persistant connections are typically desired if your database server is a
separate box from your webserver. If these servers are in the same box,
and query execution time is dominate factor on your system, then there is
minimal saving with pg_pconnect over pg_connect. If you have simple, very
fast queries, then TCP connect and fork/exec (of the postgres backends)
overhead may dominate and pg_pconnect wins again.
If you support many different connect strings, then there is another factor
to consider. You should expect to see many postgres client connections
running on your server:
A) Number of unique connect strings used.
B) Number of active apache child processes.
Multiply A*B to get max number of possible concurrent connections on your
system (max_connections in postgresql.conf). If A*B can go over postgres
connection limit, then you might start getting connection refused messages;
in which case you might consider pg_connect. Otherwise, you move security
code into your php scripts and use a single username for database access.
Frank
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: apache-php-postgresql connection question\
2002-01-31 21:23 Re: apache-php-postgresql connection question Frank Bax <fbax@sympatico.ca>
@ 2002-02-01 12:04 ` Marco Colombo <marco@esi.it>
2002-02-01 12:55 ` Re: apache-php-postgresql connection question Frank Joerdens <frank@joerdens.de>
0 siblings, 1 reply; 5+ messages in thread
From: Marco Colombo @ 2002-02-01 12:04 UTC (permalink / raw)
To: Frank Bax <fbax@sympatico.ca>; +Cc: pgsql-php
On Thu, 31 Jan 2002, Frank Bax wrote:
> At 12:11 PM 1/31/02 -0700, tom.kemp@philips.com wrote:
> >Also, if I do a persistent connection, it appears that PHP will try to
> >use this connection if it exists, prior to creating a new persistent
> >connection, right? Well, does it only reuse the already connected
> >persistent if it is the same user? How does this work? Does it check
> >that it is the same user/password (does my script have to supply this
> >in the second persistent connection function) prior to using the
> >preexisting connection? Is it tied to the current browser request?
>
> We've talked a lot about pg_pconnect this week; I'll try to summarise what
> we've learned...
>
> Each browser request from client is handled by an apache child process on
> server. Apache uses multiple child processes running concurrently to
> handle these browser requests.
>
> pg_pconnect will reuse a previously created connection to the database if
> the previous connection was made within the same apache child process *and*
> the connect string is "identical" (same host, database, username, password).
> http://www.php.net/manual/en/features.persistent-connections.php
> Using pg_pconnect() over pg_connect() may not always be a huge saving.
> Persistant connections are typically desired if your database server is a
> separate box from your webserver. If these servers are in the same box,
> and query execution time is dominate factor on your system, then there is
> minimal saving with pg_pconnect over pg_connect. If you have simple, very
> fast queries, then TCP connect and fork/exec (of the postgres backends)
> overhead may dominate and pg_pconnect wins again.
Of course, we're optimizing for time, here. pconnect has a BIG overhead:
it increases the memory footprint of your application a lot. You see
it on the host which is running postgresql, of course. So use pconnect()
if you've got RAM to spare (and on a large DB server, RAM is never enough).
What I mean here is that it's never a win, it's always a tradeoff.
> If you support many different connect strings, then there is another factor
> to consider. You should expect to see many postgres client connections
> running on your server:
> A) Number of unique connect strings used.
> B) Number of active apache child processes.
>
> Multiply A*B to get max number of possible concurrent connections on your
> system (max_connections in postgresql.conf). If A*B can go over postgres
> connection limit, then you might start getting connection refused messages;
> in which case you might consider pg_connect. Otherwise, you move security
> code into your php scripts and use a single username for database access.
>
> Frank
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: apache-php-postgresql connection question
2002-01-31 21:23 Re: apache-php-postgresql connection question Frank Bax <fbax@sympatico.ca>
2002-02-01 12:04 ` Re: apache-php-postgresql connection question\ Marco Colombo <marco@esi.it>
@ 2002-02-01 12:55 ` Frank Joerdens <frank@joerdens.de>
2002-02-01 15:14 ` Re: apache-php-postgresql connection question Brent R. Matzelle <bmatzelle@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Frank Joerdens @ 2002-02-01 12:55 UTC (permalink / raw)
To: Marco Colombo <marco@esi.it>; +Cc: Frank Bax <fbax@sympatico.ca>; pgsql-php
On a slightly different note, does anyone know whether this problem is
maybe solved entirely once PHP is fully multithreaded and works with
Apache 2, which is also multithreaded?
Or if you can use PHP with AOLServer to benefit from AOLServer's
multithreadedness and connection pooling capability?
Or if you can use connection pooling through some Java mechanism if you
build PHP as a servlet to run on top of a JVM as described at
http://www.php.net/manual/en/ref.java.php:
-------------------------- snip --------------------------
sapi/servlet builds upon the mechanism defined by ext/java to enable the
entire PHP processor to be run as a servlet. The primary advanatage of
this from a PHP perspective is that web servers which support servlets
typically take great care in pooling and reusing JVMs.
-------------------------- snap --------------------------
Dunno how that might work, through JDBC maybe?
Regards, Frank
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: apache-php-postgresql connection question
2002-01-31 21:23 Re: apache-php-postgresql connection question Frank Bax <fbax@sympatico.ca>
2002-02-01 12:04 ` Re: apache-php-postgresql connection question\ Marco Colombo <marco@esi.it>
2002-02-01 12:55 ` Re: apache-php-postgresql connection question Frank Joerdens <frank@joerdens.de>
@ 2002-02-01 15:14 ` Brent R. Matzelle <bmatzelle@yahoo.com>
0 siblings, 0 replies; 5+ messages in thread
From: Brent R. Matzelle @ 2002-02-01 15:14 UTC (permalink / raw)
To: pgsql-php
--- Frank Joerdens <frank@joerdens.de> wrote:
> On a slightly different note, does anyone know whether this problem
> is
> maybe solved entirely once PHP is fully multithreaded and works
> with
> Apache 2, which is also multithreaded?
No, probably not. According to this libpq is not multi-threaded and
might not work at all on Apache 2:
http://www.php.net/manual/en/faq.obtaining.php
Brent
__________________________________________________
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions!
http://auctions.yahoo.com
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-02-01 15:14 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-31 19:11 apache-php-postgresql connection question tom.kemp@philips.com
2002-01-31 21:23 Re: apache-php-postgresql connection question Frank Bax <fbax@sympatico.ca>
2002-02-01 12:04 ` Re: apache-php-postgresql connection question\ Marco Colombo <marco@esi.it>
2002-02-01 12:55 ` Frank Joerdens <frank@joerdens.de>
2002-02-01 15:14 ` Brent R. Matzelle <bmatzelle@yahoo.com>
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