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

* Persistent Connections
@ 2004-01-24 17:32  postgres@countup.de
  0 siblings, 1 reply; 4+ messages in thread

From: postgres@countup.de @ 2004-01-24 17:32 UTC (permalink / raw)
  To: pgsql-performance

<html><head></head><body bgcolor="#ffffff" text="#000000" link="#000000" alink="#000000" vlink="#000000" marginwidth=10 marginheight=10 topmargin=10 leftmargin=10>Hi
<br>
<br>I have a php script and i make a pg_pconnect
<br>If i want to make 4-10 pg_query in that script
<br>Have i to close the connection at end of the script?
<br>(i would say yes, is it right?)
<br>
<br>Sorry I m a little bit confused about the persistent thing!!
<br>Is it smart to use persistent connections at all if i expect 100K Users to hit the script in an hour and the script calls up to 10-15 pg functions?
<br>I have at the mom one function but the server needs 500 ms, its a little bit too much i think, and it crashed when i had 20K users
<br>
<br>Thanks
<br>Bye
<br></body></html>



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

* Re: Persistent Connections
@ 2004-01-25 11:28  Nick Barr <nicky@chuckie.co.uk>
  parent: postgres@countup.de
  0 siblings, 1 reply; 4+ messages in thread

From: Nick Barr @ 2004-01-25 11:28 UTC (permalink / raw)
  To: postgres@countup.de; +Cc: pgsql-performance

Hi,

postgres@countup.de wrote:

> Hi
>
> I have a php script and i make a pg_pconnect
> If i want to make 4-10 pg_query in that script
> Have i to close the connection at end of the script?
> (i would say yes, is it right?)


If you want to make multiple pg_query's in a page you can, and you can 
use the same connection. You dont have to use persistent connections for 
this. Just open the connection and fire off the different queries. The 
persistent connection remains open between different pages loading, 
which is supposedly faster because you dont have the overhead of opening 
the connection.

If you want to use a persistent connection then definitely dont close it 
at the bottom of the page. If you want to use the other connection 
(pg_connect, non-persistent) then you dont have to close this connection 
at the bottom of the page because PHP does it for you, although you can 
if you are feeling nice ;-).

>
> Sorry I m a little bit confused about the persistent thing!!
> Is it smart to use persistent connections at all if i expect 100K 
> Users to hit the script in an hour and the script calls up to 10-15 pg 
> functions?
> I have at the mom one function but the server needs 500 ms, its a 
> little bit too much i think, and it crashed when i had 20K users
>

Use the persistent connection but make sure the parameters in 
postgresql.conf match up with the Apache config. The specific settings 
are MaxClients in httpd.conf and max_connections in postgresql.conf. 
Make sure that max_connections is at least as big as MaxClients for 
every database that your PHP scripts connect to.

> Thanks
> Bye






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

* Re: Persistent Connections
@ 2004-01-26 06:35  Randolf Richardson <rr@8x.ca>
  parent: Nick Barr <nicky@chuckie.co.uk>
  0 siblings, 1 reply; 4+ messages in thread

From: Randolf Richardson @ 2004-01-26 06:35 UTC (permalink / raw)
  To: pgsql-performance

"nicky@chuckie.co.uk (Nick Barr)" stated in 
comp.databases.postgresql.performance:

> postgres@countup.de wrote:
[sNip]
>> Sorry I m a little bit confused about the persistent thing!!
>> Is it smart to use persistent connections at all if i expect 100K 
>> Users to hit the script in an hour and the script calls up to 10-15 pg 
>> functions?
>> I have at the mom one function but the server needs 500 ms, its a 
>> little bit too much i think, and it crashed when i had 20K users
> 
> Use the persistent connection but make sure the parameters in 
> postgresql.conf match up with the Apache config. The specific settings 
> are MaxClients in httpd.conf and max_connections in postgresql.conf. 
> Make sure that max_connections is at least as big as MaxClients for 
> every database that your PHP scripts connect to.

    	Do you happen to have (or know where to get) some sample configuration 
files for Apache 2 and PostgreSQL for this?  The documentation I've found 
so far is pretty sparse, and sample files would be very helpful.

    	THanks in advance.

-- 
Randolf Richardson - rr@8x.ca
Vancouver, British Columbia, Canada

"We are anti-spammers.  You will confirm
subscriptions.  Resistance is futile."

Please do not eMail me directly when responding
to my postings in the newsgroups.



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

* Re: Persistent Connections
@ 2004-04-20 15:49  Thomas Swan <tswan@idigx.com>
  parent: Randolf Richardson <rr@8x.ca>
  0 siblings, 0 replies; 4+ messages in thread

From: Thomas Swan @ 2004-04-20 15:49 UTC (permalink / raw)
  To: Randolf Richardson <rr@8x.ca>; +Cc: pgsql-performance

Randolf Richardson wrote:

>"nicky@chuckie.co.uk (Nick Barr)" stated in 
>comp.databases.postgresql.performance:
>
>  
>
>>postgres@countup.de wrote:
>>    
>>
>[sNip]
>  
>
>>>Sorry I m a little bit confused about the persistent thing!!
>>>Is it smart to use persistent connections at all if i expect 100K 
>>>Users to hit the script in an hour and the script calls up to 10-15 pg 
>>>functions?
>>>I have at the mom one function but the server needs 500 ms, its a 
>>>little bit too much i think, and it crashed when i had 20K users
>>>      
>>>
>>Use the persistent connection but make sure the parameters in 
>>postgresql.conf match up with the Apache config. The specific settings 
>>are MaxClients in httpd.conf and max_connections in postgresql.conf. 
>>Make sure that max_connections is at least as big as MaxClients for 
>>every database that your PHP scripts connect to.
>>    
>>
>
>    	Do you happen to have (or know where to get) some sample configuration 
>files for Apache 2 and PostgreSQL for this?  The documentation I've found 
>so far is pretty sparse, and sample files would be very helpful.
>
>    	
>  
>
Beware that persistent connections in PHP behave a little differently 
than you would think.    The connections stays open between an apache 
process and postgres.   So each process has its own connection and you 
may not hit the same process on each request to the apache server.    
Temporary tables are not dropped automatically between refreshes on 
persistent connections.  An example of this is to enable persistent 
connections and execute "CREATE TEMPORARY TABLE foo ( id INTEGER );"  

$conn = pg_pconnect( ... );
if (!$result = pg_query($conn, "CREATE TEMPORARY TABLE tmp_foo ( id 
INTEGER );")) {
   echo pg_result_error($result) ;
} else {
   echo "created ok!";
}

After a couple of refreshes you will get an error that states the table 
already exists.   This was a pain to learn, especially while I was doing 
these operations inside of transactions.

On most of my servers the connect time for postgresql was 6ms or less, 
so I disabled persistent connections altogether so that I could be 
assured that temporary tables and all php launched postgresql sessions 
were properly reset.

As far as I know, there is no way to reset the sesssion ( cleaning up 
temporary tables, etc ) automatically with an SQL statement without 
closing the connection




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


end of thread, other threads:[~2004-04-20 15:49 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-24 17:32 Persistent Connections postgres@countup.de
2004-01-25 11:28 ` Nick Barr <nicky@chuckie.co.uk>
2004-01-26 06:35   ` Randolf Richardson <rr@8x.ca>
2004-04-20 15:49     ` Thomas Swan <tswan@idigx.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