Re: [GENERAL] PostgreSQL run as process in windows

[Craig Ringer]

On 01/03/2013 12:51 PM, tuanhoanganh wrote:
> On Thu, Jan 3, 2013 at 11:04 AM, John R Pierce  > 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.
Idle CONNECTIONS are fine.

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

Re: [GENERAL] PostgreSQL run as process in windows

[Craig Ringer]

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



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL run as process in windows

[Jeff Janes]

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

Re: [GENERAL] PostgreSQL run as process in windows

[Alban Hertroys]

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.

Re: [GENERAL] PostgreSQL run as process in windows

[tuanhoanganh]

On Thu, Jan 3, 2013 at 11:52 AM, Craig Ringer  wrote:

> What's your host Windows OS? Windows Server 2008 R2? Windows 7?
>

I am runing Windows Server 2008 R2

Re: [GENERAL] PostgreSQL run as process in windows

[Craig Ringer]

On 01/03/2013 12:16 PM, tuanhoanganh wrote:
> On Thu, Jan 3, 2013 at 10:45 AM, Craig Ringer  > 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

Re: [GENERAL] PostgreSQL run as process in windows

[tuanhoanganh]

On Thu, Jan 3, 2013 at 11:04 AM, John R Pierce  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.

Re: [GENERAL] PostgreSQL run as process in windows

[tuanhoanganh]

On Thu, Jan 3, 2013 at 10:45 AM, Craig Ringer  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)

Re: [GENERAL] PostgreSQL run as process in windows

[John R Pierce]

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.







--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL run as process in windows

[Craig Ringer]

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



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL run as process in windows

[John R Pierce]

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.







--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PostgreSQL run as process in windows

[tuanhoanganh]

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