pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedKeeping processes open for re-use
7+ messages / 7 participants
[nested] [flat]
* Keeping processes open for re-use
@ 2006-11-09 12:35 Hilary Forbes <hforbes@dmr.co.uk>
0 siblings, 2 replies; 7+ messages in thread
From: Hilary Forbes @ 2006-11-09 12:35 UTC (permalink / raw)
To: pgsql-performance
<html>
Dear All<br><br>
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.<br><br>
Thanks<br>
Hilary<br><br>
<br><br>
<x-sigsep><p></x-sigsep>
Hilary Forbes<br>
DMR Limited (UK registration 01134804) <br>
A DMR Information and Technology Group company
(<a href="http://www.dmr.co.uk/"; eudora="autourl"><font color="#0000FF"><u>www.dmr.co.uk</a></u></font>)
<br>
Direct tel 01689 889950 Fax 01689 860330 <br>
DMR is a UK registered trade mark of DMR Limited<br>
**********************************************************</html>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Keeping processes open for re-use
@ 2006-11-09 12:56 Csaba Nagy <nagy@ecircle-ag.com>
parent: Hilary Forbes <hforbes@dmr.co.uk>
1 sibling, 1 reply; 7+ messages in thread
From: Csaba Nagy @ 2006-11-09 12:56 UTC (permalink / raw)
To: Hilary Forbes <hforbes@dmr.co.uk>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Keeping processes open for re-use
@ 2006-11-09 17:55 imad <immaad@gmail.com>
parent: Hilary Forbes <hforbes@dmr.co.uk>
1 sibling, 0 replies; 7+ messages in thread
From: imad @ 2006-11-09 17:55 UTC (permalink / raw)
To: Hilary Forbes <hforbes@dmr.co.uk>; +Cc: pgsql-performance
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
> **********************************************************
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Keeping processes open for re-use
@ 2006-11-10 02:09 Shane Ambler <pgsql@007Marketing.com>
parent: Csaba Nagy <nagy@ecircle-ag.com>
0 siblings, 1 reply; 7+ messages in thread
From: Shane Ambler @ 2006-11-10 02:09 UTC (permalink / raw)
To: Csaba Nagy <nagy@ecircle-ag.com>; +Cc: Hilary Forbes <hforbes@dmr.co.uk>; pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Keeping processes open for re-use
@ 2006-11-10 02:19 Joshua D. Drake <jd@commandprompt.com>
parent: Shane Ambler <pgsql@007Marketing.com>
0 siblings, 1 reply; 7+ messages in thread
From: Joshua D. Drake @ 2006-11-10 02:19 UTC (permalink / raw)
To: Shane Ambler <pgsql@007Marketing.com>; +Cc: Csaba Nagy <nagy@ecircle-ag.com>; Hilary Forbes <hforbes@dmr.co.uk>; pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Keeping processes open for re-use
@ 2006-11-16 17:39 Jean-Max Reymond <jmreymond@gmail.com>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 7+ messages in thread
From: Jean-Max Reymond @ 2006-11-16 17:39 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Keeping processes open for re-use
@ 2006-11-17 11:20 Richard Huxton <dev@archonet.com>
parent: Jean-Max Reymond <jmreymond@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Richard Huxton @ 2006-11-17 11:20 UTC (permalink / raw)
To: Jean-Max Reymond <jmreymond@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-11-17 11:20 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-09 12:35 Keeping processes open for re-use Hilary Forbes <hforbes@dmr.co.uk>
2006-11-09 12:56 ` Csaba Nagy <nagy@ecircle-ag.com>
2006-11-10 02:09 ` Shane Ambler <pgsql@007Marketing.com>
2006-11-10 02:19 ` Joshua D. Drake <jd@commandprompt.com>
2006-11-16 17:39 ` Jean-Max Reymond <jmreymond@gmail.com>
2006-11-17 11:20 ` Richard Huxton <dev@archonet.com>
2006-11-09 17:55 ` imad <immaad@gmail.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