pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Persistent Connects (pg_pconnect)
8+ messages / 6 participants
[nested] [flat]

* Persistent Connects (pg_pconnect)
@ 2000-11-21 12:34 Colleen Williams <colleen@digital-arts.co.uk>
  2000-11-21 13:25 ` Re: Persistent Connects (pg_pconnect) Keith Wong <keith@e-magine.com.au>
  2000-11-21 13:31 ` Re: Persistent Connects (pg_pconnect) Keith Wong <keith@e-magine.com.au>
  2000-11-22 04:22 ` Re: Persistent Connects (pg_pconnect) Itai Zukerman <zukerman@math-hat.com>
  0 siblings, 3 replies; 8+ messages in thread

From: Colleen Williams @ 2000-11-21 12:34 UTC (permalink / raw)
  To: pgsql-sql

Hello all,

I am having some problems with persistent connections.

The PHP scripts in my content management system (CMS) are connecting using 
pg_pconnect(" dbname='MYDB' user='user' password='password'' 
port='5432'  "). I have not been able to connect to the PostgreSQL database 
when I include the host parameter in the pg_pconnect string and I don't 
understand why. Each time one of us uses the CMS, a new persistent 
connection process owned by postgres is started up. With 3 people accessing 
the CMS sporadically, 32 persistent connections are in existence and then 
Linux tells me there are too many connections!  Sometimes I believe 
existing connections are reused but not often!

Has anyone else had this sort of experience with persistent connections? It 
is driving me mad so I would appreciate any help or pointers you might have.

Thanks,
Colleen.

Colleen Williams
colleen@digital-arts.co.uk

0207 484 8825

Digital Arts | British Columbia House | 1 Regent Street | London | SW1Y 4NR
http://www.digital-arts.co.uk




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

* Re: Persistent Connects (pg_pconnect)
  2000-11-21 12:34 Persistent Connects (pg_pconnect) Colleen Williams <colleen@digital-arts.co.uk>
@ 2000-11-21 13:25 ` Keith Wong <keith@e-magine.com.au>
  2 siblings, 0 replies; 8+ messages in thread

From: Keith Wong @ 2000-11-21 13:25 UTC (permalink / raw)
  To: Colleen Williams <colleen@digital-arts.co.uk>; pgsql-sql

Hi Colleen,

When you use specify a host parameter... Php will attempt to connect to 
your postgres server using TCP/IP...
if you are not running the postgres backend with the -i option, then these 
connections won't happen. When you don't
specify a host name then Php will use a local Unix socket.

Not sure about the persistant database connection stuff. What version of 
Php are you using?
The only thing I could say, is to try explicitly closing the persistent 
database connections in your Php code.
Maybe there is a bug in Php.

Hope this helps,

Keith.
At 12:34 PM 21/11/2000 +0000, Colleen Williams wrote:
>Hello all,
>
>I am having some problems with persistent connections.
>
>The PHP scripts in my content management system (CMS) are connecting using 
>pg_pconnect(" dbname='MYDB' user='user' password='password'' 
>port='5432'  "). I have not been able to connect to the PostgreSQL 
>database when I include the host parameter in the pg_pconnect string and I 
>don't understand why. Each time one of us uses the CMS, a new persistent 
>connection process owned by postgres is started up. With 3 people 
>accessing the CMS sporadically, 32 persistent connections are in existence 
>and then Linux tells me there are too many connections!  Sometimes I 
>believe existing connections are reused but not often!
>
>Has anyone else had this sort of experience with persistent connections? 
>It is driving me mad so I would appreciate any help or pointers you might have.
>
>Thanks,
>Colleen.
>
>Colleen Williams
>colleen@digital-arts.co.uk
>
>0207 484 8825
>
>Digital Arts | British Columbia House | 1 Regent Street | London | SW1Y 4NR
>http://www.digital-arts.co.uk




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

* Re: Persistent Connects (pg_pconnect)
  2000-11-21 12:34 Persistent Connects (pg_pconnect) Colleen Williams <colleen@digital-arts.co.uk>
@ 2000-11-21 13:31 ` Keith Wong <keith@e-magine.com.au>
  2 siblings, 0 replies; 8+ messages in thread

From: Keith Wong @ 2000-11-21 13:31 UTC (permalink / raw)
  To: Colleen Williams <colleen@digital-arts.co.uk>; pgsql-sql

Just looked at the Php documentation... according to that.. it goes...

"An 'identical' connection is a connection that was opened to the same 
host, with the same username and the same password (where applicable)."

Perhaps that means you need to specify the host for this to work.

Let me know on your findings. I'll be interested to know.

Cheers,
Keith :)


At 12:34 PM 21/11/2000 +0000, Colleen Williams wrote:
>Hello all,
>
>I am having some problems with persistent connections.
>
>The PHP scripts in my content management system (CMS) are connecting using 
>pg_pconnect(" dbname='MYDB' user='user' password='password'' 
>port='5432'  "). I have not been able to connect to the PostgreSQL 
>database when I include the host parameter in the pg_pconnect string and I 
>don't understand why. Each time one of us uses the CMS, a new persistent 
>connection process owned by postgres is started up. With 3 people 
>accessing the CMS sporadically, 32 persistent connections are in existence 
>and then Linux tells me there are too many connections!  Sometimes I 
>believe existing connections are reused but not often!
>
>Has anyone else had this sort of experience with persistent connections? 
>It is driving me mad so I would appreciate any help or pointers you might have.
>
>Thanks,
>Colleen.
>
>Colleen Williams
>colleen@digital-arts.co.uk
>
>0207 484 8825
>
>Digital Arts | British Columbia House | 1 Regent Street | London | SW1Y 4NR
>http://www.digital-arts.co.uk




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

* Re: Persistent Connects (pg_pconnect)
  2000-11-21 12:34 Persistent Connects (pg_pconnect) Colleen Williams <colleen@digital-arts.co.uk>
@ 2000-11-22 04:22 ` Itai Zukerman <zukerman@math-hat.com>
  2000-11-22 14:08   ` Re: Persistent Connects (pg_pconnect) Serge Canizares <serge@ephilosopher.com>
  2000-11-22 15:46   ` Re: Persistent Connects (pg_pconnect) Roberto Mello <rmello@cc.usu.edu>
  2 siblings, 2 replies; 8+ messages in thread

From: Itai Zukerman @ 2000-11-22 04:22 UTC (permalink / raw)
  To: Colleen Williams <colleen@digital-arts.co.uk>; +Cc: pgsql-sql

On Tue, 21 Nov 2000 12:34:19 +0000, Colleen Williams <colleen@digital-arts.co.uk> wrote:
> Each time one of us uses the CMS, a new
> persistent connection process owned by postgres is started up. With 3
> people accessing the CMS sporadically, 32 persistent connections are
> in existence and then Linux tells me there are too many connections!
> Sometimes I believe existing connections are reused but not often!

Not sure if this is related, but under the non-threading Apache
server, you get 1 persistent connection per Apache process.  I don't
think you can be sure which process gets the page with the connection
request, so if you have a limit of, say, 50 processes, and your server
is hit fairly often, eventually you'll see 50 connections even though
only a few simultaneous accesses to PHP/PostgreSQL are made.

Or, I could be totally off.

-itai



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

* Re: Persistent Connects (pg_pconnect)
  2000-11-21 12:34 Persistent Connects (pg_pconnect) Colleen Williams <colleen@digital-arts.co.uk>
  2000-11-22 04:22 ` Re: Persistent Connects (pg_pconnect) Itai Zukerman <zukerman@math-hat.com>
@ 2000-11-22 14:08   ` Serge Canizares <serge@ephilosopher.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Serge Canizares @ 2000-11-22 14:08 UTC (permalink / raw)
  To: Itai Zukerman <zukerman@math-hat.com>; pgsql-sql

> Not sure if this is related, but under the non-threading Apache
> server, you get 1 persistent connection per Apache process.  I don't
> think you can be sure which process gets the page with the connection
> request, so if you have a limit of, say, 50 processes, and your server
> is hit fairly often, eventually you'll see 50 connections even though
> only a few simultaneous accesses to PHP/PostgreSQL are made.
>
> Or, I could be totally off.
>
> -itai

Actually, with php 4.0x, the apache processes can gather a pool of persistent connexions.

I had the same sort of problem once, and saw that each apache process was grabbing ~4
postgreSQL connexions and thus maxing out the number available from the server.




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

* Re: Persistent Connects (pg_pconnect)
  2000-11-21 12:34 Persistent Connects (pg_pconnect) Colleen Williams <colleen@digital-arts.co.uk>
  2000-11-22 04:22 ` Re: Persistent Connects (pg_pconnect) Itai Zukerman <zukerman@math-hat.com>
@ 2000-11-22 15:46   ` Roberto Mello <rmello@cc.usu.edu>
  1 sibling, 0 replies; 8+ messages in thread

From: Roberto Mello @ 2000-11-22 15:46 UTC (permalink / raw)
  To: Itai Zukerman <zukerman@math-hat.com>; +Cc: Colleen Williams <colleen@digital-arts.co.uk>; pgsql-sql

Itai Zukerman wrote:
> 
> Not sure if this is related, but under the non-threading Apache
> server, you get 1 persistent connection per Apache process.  I don't
> think you can be sure which process gets the page with the connection
> request, so if you have a limit of, say, 50 processes, and your server
> is hit fairly often, eventually you'll see 50 connections even though
> only a few simultaneous accesses to PHP/PostgreSQL are made.

	One way to work this out is to use a webserver that is fully
multithreaded, with pooled connections to the database that can be
shared among threads as needed. This yields much superior performance
and saves a ton of resources.
	One example of such type of webserver that has been fully multithreaded
and under heavy use since 1995, and has a PHP4 module is AOLserver, that
powers mighty aol.com and digitalcity.com (with mere 32 K hits per
second).
	AOLserver also has a built-in database abstraction layer and Tcl
interpreter (with the option of caching compiled code of scripts), and a
comprehensive API for building massively scalable dynamic websites.

	More info at http://www.aolserver.com

	-Roberto
-- 
Computer Science			Utah State University
Space Dynamics Laboratory		Web Developer
USU Free Software & GNU/Linux Club 	http://fslc.usu.edu
My home page - http://www.brasileiro.net/roberto



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

* Re: Persistent Connects (pg_pconnect)
@ 2000-11-28 11:59 Colleen Williams <colleen@digital-arts.co.uk>
  2000-12-12 23:50 ` Re: Persistent Connects (pg_pconnect) Luca Pregliasco <upa@apuanet.bicnet.it>
  0 siblings, 1 reply; 8+ messages in thread

From: Colleen Williams @ 2000-11-28 11:59 UTC (permalink / raw)
  To: pgsql-sql

Thanks guys and gals, for your quick responses to my query about persistent 
connections (you know who you are). It was much appreciated. I am posting 
the results the results of our investigations in case you are interested 
and also if it might help someone else...

Our investigations of the problem revealed that when using pg_pconnect on 
the one Apache web server which was serving PHP pages which had many static 
elements like jpgs, our postgres processes where being tied up by child web 
processes which just serving lots of static elements. We had a max of 32 
postgres connections while we could have up to 300 child web processes and 
so problems of the CMS locking us out occurs when when we had more web 
processes than postgres resources ie we had a low utilization of postgres 
resources. The solution lies in having more than one web server it seems - 
one to serve database type PHP pages and the other to server static 
elements such as jpgs etc.

As we didn't have time to rebuild our web architecture we have used 
pg_connect instead of pg_pconnect as a short term solution. It didn't 
appear that much slower than when we used pg_connect.

However we had another problem, the solution with using pg_connect instead 
of pg_pconnect worked on DEV but not on PROD. We think it is to do with 
different PHP versions. DEV was using PHP4.0.1p2 and PROD was using 
PHP4.0.3p1. We think maybe there is a variable scoping problem PHP4.0.3p1. 
Perhaps PHP doesn't really work so well with object code and so we 
shouldn't be using object code.

I have used the following code to do the database connect in my db.inc. To 
make the code work in PROD I have had to make $connection a global variable 
(by uncommenting the commented lines) as although it would connect fine and 
return a valid connection index, the next time I went to fetch the query, 
postgres would return an 'invalid postgres resource link' error. However in 
DEV (PHP4.0.1p2) it was working fine - creating a new postgres resource 
when a new query object is created.

If you can see a bug in my code and it is not at all a PHP version problem 
OR if you can confirm it is a PHP version problem please let me know. I 
would like to know if I am just doing it all wrong.

//global $connection;

function db_connect() {
	global $DBNAME, $DBUSER, $DBPASS, $DBPORT;
          //global $connection;
   	$connection = pg_connect("dbname=$DBNAME user=$DBUSER password=$DBPASS 
port=$DBPORT ") or die("Unable to connect to server.");
	return $connection;
}

//db_connect();

Class Query {
     	var $numrows;
           var $query;
	var $errormsg;
	var $conn;
	
	// constructor function
	function Query() {
		$this->conn = db_connect();
	}
     		
	function runquery( $qstr ){				
		$this->query = pg_exec($this->conn, $qstr);
		if ($this->query == 0) {
			$this->errormsg = pg_errormessage($this->conn);
			$this->numrows = 0;
		}
		else {		
			$this->numrows = pg_NumRows($this->query);			
		}
	}
	
	function fetchrow($ind ){
		return pg_fetch_row($this->query,$ind);
	}
	function fetcharray($ind ){
		return pg_fetch_array($this->query,$ind);
	}
}

Cheers,
Colleen.



Colleen Williams
colleen@digital-arts.co.uk

0207 484 8825

Digital Arts | British Columbia House | 1 Regent Street | London | SW1Y 4NR
http://www.digital-arts.co.uk




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

* Re: Persistent Connects (pg_pconnect)
  2000-11-28 11:59 Re: Persistent Connects (pg_pconnect) Colleen Williams <colleen@digital-arts.co.uk>
@ 2000-12-12 23:50 ` Luca Pregliasco <upa@apuanet.bicnet.it>
  0 siblings, 0 replies; 8+ messages in thread

From: Luca Pregliasco @ 2000-12-12 23:50 UTC (permalink / raw)
  To: pgsql-sql

Colleen Williams wrote:

> However we had another problem, the solution with using pg_connect instead
> of pg_pconnect worked on DEV but not on PROD. We think it is to do with
> different PHP versions. DEV was using PHP4.0.1p2 and PROD was using
> PHP4.0.3p1. We think maybe there is a variable scoping problem PHP4.0.3p1.
> Perhaps PHP doesn't really work so well with object code and so we
> shouldn't be using object code.
> 
> I have used the following code to do the database connect in my db.inc. To
> make the code work in PROD I have had to make $connection a global variable
> (by uncommenting the commented lines) as although it would connect fine and
> return a valid connection index, the next time I went to fetch the query,
> postgres would return an 'invalid postgres resource link' error. However in
> DEV (PHP4.0.1p2) it was working fine - creating a new postgres resource
> when a new query object is created.
> 
> If you can see a bug in my code and it is not at all a PHP version problem
> OR if you can confirm it is a PHP version problem please let me know. I
> would like to know if I am just doing it all wrong.

We are having exactly the same problem, with exactly the same
configurations, and we also reached the conclusion about PHP not working
fine with variable scoping in objects.
The code you supplied in example is very similar to our source, this
also confirms our fears.
We are investigating every single difference between the two PHP
versions to figure out a serius fix.

bye

Luca Pregliasco
upa@apuanet.bicnet.it

IF Internet Farm S.p.A.
Via Dorsale 13 - 54100 Massa - Italia



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


end of thread, other threads:[~2000-12-12 23:50 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-11-21 12:34 Persistent Connects (pg_pconnect) Colleen Williams <colleen@digital-arts.co.uk>
2000-11-21 13:25 ` Keith Wong <keith@e-magine.com.au>
2000-11-21 13:31 ` Keith Wong <keith@e-magine.com.au>
2000-11-22 04:22 ` Itai Zukerman <zukerman@math-hat.com>
2000-11-22 14:08   ` Serge Canizares <serge@ephilosopher.com>
2000-11-22 15:46   ` Roberto Mello <rmello@cc.usu.edu>
2000-11-28 11:59 Re: Persistent Connects (pg_pconnect) Colleen Williams <colleen@digital-arts.co.uk>
2000-12-12 23:50 ` Luca Pregliasco <upa@apuanet.bicnet.it>

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