Thread: apache-php-postgresql connection question
Hi, I am new to apache, php, and postgresql. I had been tasked with creating a database for use in my work environment, accessiblevia web browsers. I read up on SQL, postgresql and mysql and chose postgresql. I have the database built andrebuildable via sql script for postgresql implementation. I am being pushed to rewrite this and use MySQL by my IT department. I don'twant to use MySQL, based on some advantages I have seen during comparison research and the expected future size andcomplexity 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 userconnection and views become irrelevant in regard to permissions. My knowledge/expectation based on reading books was that I could usethe 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 aphp script, and have the connect be based on the security of the database permissions. Is this not true? Can I not dothis 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 creatinga new persistent connection, right? Well, does it only reuse the already connected persistent if it is the sameuser? How does this work? Does it check that it is the same user/password (does my script have to supply this in the second persistentconnection function) prior to using the preexisting connection? Is it tied to the current browser request? Thanks in advance for your time and help! Tom
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
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
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
--- 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
Yet again, on another note: Has anyone tried DBBalancer from http://dbbalancer.sourceforge.net/ - which is yet another approach to the problem of connection pooling? I've been meaning to play with it for some time, but haven't gotten 'round to it. I keep thinking about ways around PHP persistent connections because IMHO it's a pretty awkward solution. Regards, Frank
On Sat, 2002-02-02 at 05:55, Frank Joerdens wrote: > Yet again, on another note: Has anyone tried DBBalancer from > > http://dbbalancer.sourceforge.net/ > > - which is yet another approach to the problem of connection pooling? > I've been meaning to play with it for some time, but haven't gotten > 'round to it. > > I keep thinking about ways around PHP persistent connections because > IMHO it's a pretty awkward solution. I use it on several production databases, and I have packaged it for Debian. I intend to upload the package into the Debian distribution this month. In my environment it gives around the same performance improvement as persistent connections (5-10 times query performance for small queries) but without the problems of runaway connection levels. From my point of biew the _best_ thing about DBBalancer is that in it's simplest installation you don't have to make any changes to your application other than in the connect statement. It will also work fine with Perl, DBI, or even psql because of the way it does it's pooling - it makes no attempt to parse the query. This makes it easy for me to retrofit existing applications with it. Regards, Andrew. -- -------------------------------------------------------------------- Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267 Are you enrolled at http://schoolreunions.co.nz/ yet?