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
************************************************** ********

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.



---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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
> ************************************************** ********

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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.pe...onnections.php


--

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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.pe...onnections.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




---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly