Thread: Keeping processes open for re-use

Keeping processes open for re-use

From
Hilary Forbes
Date:
Dear All<br /><br /> Looking at the processes running on our server, it appears that each time a web server program
makesa call to the database server, we start a new process on the database server which obviously has a start up cost.
InApache, for example, you can say at start up time,that you want the machine to reserve eg 8 processes and keep them
openat all times so you don't have this overhead until you exceed this minimum number. Is there a way that we can
achievethis in Postgres? We have a situation whereby we have lots of web based users doing short quick queries and
obviouslythe start up time for a process must add to their perceived response time.<br /><br /> Thanks<br /> Hilary<br
/><br/><br /><br /><p> Hilary Forbes<br /> DMR Limited (UK registration 01134804) <br /> A DMR Information and
TechnologyGroup company (<a eudora="autourl" href="http://www.dmr.co.uk/"><font
color="#0000FF"><u>www.dmr.co.uk</u></font></a>)<br /> Direct tel 01689 889950 Fax 01689 860330 <br /> DMR is a UK
registeredtrade mark of DMR Limited<br /> ********************************************************** 

Re: Keeping processes open for re-use

From
Csaba Nagy
Date:
On Thu, 2006-11-09 at 13:35, Hilary Forbes wrote:
> [snip] Is there a way that we can achieve this in Postgres? We have a
> situation whereby we have lots of web based users doing short quick
> queries and obviously the start up time for a process must add to
> their perceived response time.

Yes: google for "connection pooling". Note that different solutions
exist for different programming languages, so you should look for
connection pooling for the language you're using.

HTH,
Csaba.



Re: Keeping processes open for re-use

From
imad
Date:
Yes. This is connection pooling. You can find a lot of examples from
the internet on connection pooling, rather source codes. Also keep in
mind that connection pools can be maintained on the application as
well as the database server side. Check which one suits you.


--Imad
www.EnterpriseDB.com


On 11/9/06, Hilary Forbes <hforbes@dmr.co.uk> wrote:
>  Dear All
>
>  Looking at the processes running on our server, it appears that each time a
> web server program makes a call to the database server, we start a new
> process on the database server which obviously has a start up cost. In
> Apache, for example, you can say at start up time,that you want the machine
> to reserve eg 8 processes and keep them open at all times so you don't have
> this overhead until you exceed this minimum number. Is there a way that we
> can achieve this in Postgres? We have a situation whereby we have lots of
> web based users doing short quick queries and obviously the start up time
> for a process must add to their perceived response time.
>
>  Thanks
>  Hilary
>
>
>
>
>
>  Hilary Forbes
>  DMR Limited (UK registration 01134804)
>  A DMR Information and Technology Group company (www.dmr.co.uk)
>  Direct tel 01689 889950 Fax 01689 860330
>  DMR is a UK registered trade mark of DMR Limited
>  **********************************************************

Re: Keeping processes open for re-use

From
Shane Ambler
Date:
Csaba Nagy wrote:
> On Thu, 2006-11-09 at 13:35, Hilary Forbes wrote:
>> [snip] Is there a way that we can achieve this in Postgres? We have a
>> situation whereby we have lots of web based users doing short quick
>> queries and obviously the start up time for a process must add to
>> their perceived response time.
>
> Yes: google for "connection pooling". Note that different solutions
> exist for different programming languages, so you should look for
> connection pooling for the language you're using.
>

If you are using PHP then persistent connections may be a simpler way if
it is enough for your needs.

Basically replace pg_connect with pg_pconnect

Other languages may have a similar option.

http://php.net/manual/en/features.persistent-connections.php


--

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz

Re: Keeping processes open for re-use

From
"Joshua D. Drake"
Date:
On Fri, 2006-11-10 at 12:39 +1030, Shane Ambler wrote:
> Csaba Nagy wrote:
> > On Thu, 2006-11-09 at 13:35, Hilary Forbes wrote:
> >> [snip] Is there a way that we can achieve this in Postgres? We have a
> >> situation whereby we have lots of web based users doing short quick
> >> queries and obviously the start up time for a process must add to
> >> their perceived response time.
> >
> > Yes: google for "connection pooling". Note that different solutions
> > exist for different programming languages, so you should look for
> > connection pooling for the language you're using.
> >
>
> If you are using PHP then persistent connections may be a simpler way if
> it is enough for your needs.

I would actually suggest pg_pool over pg_pconnect.

Joshua D. Drake


>
> Basically replace pg_connect with pg_pconnect
>
> Other languages may have a similar option.
>
> http://php.net/manual/en/features.persistent-connections.php
>
>
--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




Re: Keeping processes open for re-use

From
"Jean-Max Reymond"
Date:
2006/11/10, Joshua D. Drake <jd@commandprompt.com>:
>
> I would actually suggest pg_pool over pg_pconnect.
>

Please, can you explain advantages of pg_pool over pg_connect ?

--
Jean-Max Reymond
CKR Solutions Open Source
Nice France
http://www.ckr-solutions.com

Re: Keeping processes open for re-use

From
Richard Huxton
Date:
Jean-Max Reymond wrote:
> 2006/11/10, Joshua D. Drake <jd@commandprompt.com>:
>>
>> I would actually suggest pg_pool over pg_pconnect.
>>
>
> Please, can you explain advantages of pg_pool over pg_connect ?

He said pg_pconnect (note the extra "p"). This provides permanent
connections to the database from PHP. However, you will end up with one
connection per Apache process running (without running multiple Apache
setups to serve different content types). Since many processes will be
serving images or static content you have a lot of wasted, idle,
connections.

Now, pg_pool allows you to maintain a small number of active connections
(say 20 or 50) and have PHP connect to your pgpool server. It allocates
the next free connection it holds and so lets you minimise the number of
connections to PostgreSQL.

HTH
--
   Richard Huxton
   Archonet Ltd