Thread: Keeping processes open for re-use
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 /> **********************************************************
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.
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 > **********************************************************
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
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
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
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