Thread: PostgreSQL run as process in windows
Hello all.
I have more than 500 users connect to Postgres in windows 2008 R2. I change registry make postgres service support 512 connect. But is there any good way make postgres run as process not service. Is it safe run postgres as process in windows.
Thank you very much (sorry for my English)
Tuan Hoang Anh
On 1/2/2013 6:40 PM, tuanhoanganh wrote: > I have more than 500 users connect to Postgres in windows 2008 R2. I > change registry make postgres service support 512 connect. But is > there any good way make postgres run as process not service. Is it > safe run postgres as process in windows. a service *is* a type of process. yes, you could setup postgres to run in a command line window within a desktop session, starting and stopping it with pg_ctl ... you'd want to create a user specifically for this, and have that user own the postgres data directory. you would need to leave this user logged onto the console at all times, or postgres would exit when the user session terminates.
On 01/03/2013 10:40 AM, tuanhoanganh wrote: > Hello all. > I have more than 500 users connect to Postgres in windows 2008 R2. I > change registry make postgres service support 512 connect. But is > there any good way make postgres run as process not service. Is it > safe run postgres as process in windows. Running PostgreSQL directly via pg_ctl, not as a service, will not change how it performs under load at all. It will not help you service more than 500 concurrent connections. You really need to put a connection pool in place to limit the number of concurrent workers. Look at PgBouncer or PgPool-II. As far as I know neither of them runs on Windows; you might want to think about a Linux box as a front-end. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 1/2/2013 7:45 PM, Craig Ringer wrote: > You really need to put a connection pool in place to limit the number of > concurrent workers. Look at PgBouncer or PgPool-II. As far as I know > neither of them runs on Windows; you might want to think about a Linux > box as a front-end. 2nd and 3rd the emotion. of course, a pooler only works right if the client applications are modified to open a connection, do a transaction, and close the connection. if the clients continue to hold idle connections, the pooler won't do anything useful for you. typically a database server get the best overall throughput if you limit the number of concurrent queries to maybe 2-4 times the CPU core count, give or take how fast your storage IO is and how many pending IO operations can be in the pipe.
On Thu, Jan 3, 2013 at 10:45 AM, Craig Ringer <craig@2ndquadrant.com> wrote:
Running PostgreSQL directly via pg_ctl, not as a service, will not
change how it performs under load at all. It will not help you service
more than 500 concurrent connections.
You really need to put a connection pool in place to limit the number of
concurrent workers. Look at PgBouncer or PgPool-II. As far as I know
neither of them runs on Windows; you might want to think about a Linux
box as a front-end.
Thanks for your help. But when use PgBouncer, it run as service and may be it only support 512 connect (Because windows service only support default 125 connect, change registry windows can support 512 connect).
Is there any other way?
Thanks you very much (Sorry for my English)
On Thu, Jan 3, 2013 at 11:04 AM, John R Pierce <pierce@hogranch.com> wrote:
:-( My application continue to hold idle connections.
of course, a pooler only works right if the client applications are modified to open a connection, do a transaction, and close the connection. if the clients continue to hold idle connections, the pooler won't do anything useful for you.
On 01/03/2013 12:16 PM, tuanhoanganh wrote:
On Thu, Jan 3, 2013 at 10:45 AM, Craig Ringer <craig@2ndquadrant.com> wrote:Running PostgreSQL directly via pg_ctl, not as a service, will not
change how it performs under load at all. It will not help you service
more than 500 concurrent connections.
You really need to put a connection pool in place to limit the number of
concurrent workers. Look at PgBouncer or PgPool-II. As far as I know
neither of them runs on Windows; you might want to think about a Linux
box as a front-end.Thanks for your help. But when use PgBouncer, it run as service and may be it only support 512 connect (Because windows service only support default 125 connect, change registry windows can support 512 connect).Is there any other way?
What's your host Windows OS? Windows Server 2008 R2? Windows 7?
It's hard to find details on the limits of the various versions. In general, for higher loads I would strongly favour using Linux, where TCP/IP connection limit problems just go away and where you can run a properly supported PgBouncer.
-- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Thu, Jan 3, 2013 at 11:52 AM, Craig Ringer <craig@2ndquadrant.com> wrote:
What's your host Windows OS? Windows Server 2008 R2? Windows 7?
I am runing Windows Server 2008 R2
FYI: There's a kernel sockets leak in the 64-bit edition of that OS in combination with multiple CPU cores (meaning on any slightly modern CPU). You might be running into that now or later. See: http://support.microsoft.com/?id=2577795
The issue is over a year old and there's still no Windows update that fixes it, except for the hotfix in linked article. Apparently the fix will be in SP2, but there's no ETA for that.
On Wednesday, January 2, 2013, John R Pierce wrote:
On 1/2/2013 7:45 PM, Craig Ringer wrote:You really need to put a connection pool in place to limit the number of
concurrent workers. Look at PgBouncer or PgPool-II. As far as I know
neither of them runs on Windows; you might want to think about a Linux
box as a front-end.
2nd and 3rd the emotion.
of course, a pooler only works right if the client applications are modified to open a connection, do a transaction, and close the connection. if the clients continue to hold idle connections, the pooler won't do anything useful for you.
If you can get away with pooling at the transaction level rather than the session level, then you should still get a benefit even if the connections are persistent. (If each of the 500 connections is as connected as a different PG role, you wouldn't get a benefit from transaction pooling, but in that case you probably wouldn't get a benefit from session pooling, either.)
Cheers,
Jeff
On 01/03/2013 04:46 PM, Alban Hertroys wrote: > FYI: There's a kernel sockets leak in the 64-bit edition of that OS in > combination with multiple CPU cores (meaning on any slightly modern > CPU). You might be running into that now or later. > See: http://support.microsoft.com/?id=2577795 > > The issue is over a year old and there's still no Windows update that > fixes it, except for the hotfix in linked article. Apparently the fix > will be in SP2, but there's no ETA for that. I'd just put a Linux box in as a front-end, personally. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 01/03/2013 12:51 PM, tuanhoanganh wrote:
Idle CONNECTIONS are fine.On Thu, Jan 3, 2013 at 11:04 AM, John R Pierce <pierce@hogranch.com> wrote:of course, a pooler only works right if the client applications are modified to open a connection, do a transaction, and close the connection. if the clients continue to hold idle connections, the pooler won't do anything useful for you.:-( My application continue to hold idle connections.
What you need to avoid is open transactions being held open and idle for long periods.
-- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services