Prev: psql: edit function, show function commandspatch
Next: [HACKERS] Alias to rollback keyword
From: Dimitri Fontaine on 1 Apr 2010 15:17
Josh Berkus <josh(a)agliodbs.com> writes:
> Oh, I agree. Since we have a separate WALSender limit, it seems
> counter-intuitive and difficult-to-admin to have the WALSenders also
> limited by superuser_connections. They should be their own separate
> connection pool, just like the other "background" processes.

+1

Regards,
--
dim

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

From: Robert Haas on 20 Apr 2010 06:52
On Tue, Apr 20, 2010 at 5:47 AM, Fujii Masao <masao.fujii(a)gmail.com> wrote:
> On Tue, Apr 20, 2010 at 11:04 AM, Robert Haas <robertmhaas(a)gmail.com> wrote:
>> Instead of doing this, could we just change the logic in InitPostgres?
>>
>> Current logic says we hit the connection limit if:
>>
>>        if (!am_superuser &&
>>                ReservedBackends > 0 &&
>>                !HaveNFreeProcs(ReservedBackends))
>>
>> Couldn't we just change this to:
>>
>>        if ((!am_superuser || am_walsender) &&
>>                ReservedBackends > 0 &&
>>                !HaveNFreeProcs(ReservedBackends))
>>
>> Seems like that'd be a whole lot simpler, if it'll do the job...
>
> It's very simple, but prevents superuser replication connection
> from being established when connection limit exceeds for
> non-superusers. It seems strange to me that superuser cannot use
> superuser_reserved_connections slots. If we'd like to forbid
> replication connection to use the slots, I think that we should
> just get rid of a superuser privilege from it instead.

Let's just stop for a second and think about why we have
superuser_reserved_connections in the first place. As I understand
it, the point is that we want to make sure that superusers don't get
locked out of the database, because superuser intervention might be
necessary to recover from whatever series of unfortunate events has
caused all of the connection slots to get used up. For example, if
there are several different applications that connect to the database,
the superuser might like to log in and see which application has
requested more than its usual allotment of connections, or the
superuser might like to log in and terminate those backends which, in
his judgement, ought to be terminated. In other words, the purpose of
superuser_reserved_connections is to allow the database to recover
from a bad state that it has gotten into: specifically, a state where
all the connection slots have been used up and regular users can't
connect.

If replication connections can use up superuser_reserved_connections
slots, then it's very possible that this safety valve will fail
completely. If the server is being flooded with connection attempts,
and one of the streaming replication connection dies, then a regular
backend will immediate grab that slot. When the streaming replication
slave automatically tries to reconnect, it will now grab one of the
superuser_reserved_connections slots, putting us one step closer to
the bad scenario where there's no way for the superuser to log in
interactively and troubleshoot.

In other words, I don't care whether or not the replication connection
is or is not technically a superuser connection. What I think is
important is trying to preserve the ability for a superuser to log in
interactively and clean up the mess even when the regular supply of
connections is maxed out.

....Robert

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

From: "Kevin Grittner" on 20 Apr 2010 09:35
Robert Haas <robertmhaas(a)gmail.com> wrote:

> I don't care whether or not the replication connection is or is
> not technically a superuser connection. What I think is important
> is trying to preserve the ability for a superuser to log in
> interactively and clean up the mess even when the regular supply
> of connections is maxed out.

+1

-Kevin

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

From: Fujii Masao on 20 Apr 2010 09:47
On Tue, Apr 20, 2010 at 7:52 PM, Robert Haas <robertmhaas(a)gmail.com> wrote:
> Let's just stop for a second and think about why we have
> superuser_reserved_connections in the first place.  As I understand
> it, the point is that we want to make sure that superusers don't get
> locked out of the database, because superuser intervention might be
> necessary to recover from whatever series of unfortunate events has
> caused all of the connection slots to get used up.  For example, if
> there are several different applications that connect to the database,
> the superuser might like to log in and see which application has
> requested more than its usual allotment of connections, or the
> superuser might like to log in and terminate those backends which, in
> his judgement, ought to be terminated.  In other words, the purpose of
> superuser_reserved_connections is to allow the database to recover
> from a bad state that it has gotten into: specifically, a state where
> all the connection slots have been used up and regular users can't
> connect.
>
> If replication connections can use up superuser_reserved_connections
> slots, then it's very possible that this safety valve will fail
> completely.  If the server is being flooded with connection attempts,
> and one of the streaming replication connection dies, then a regular
> backend will immediate grab that slot.  When the streaming replication
> slave automatically tries to reconnect, it will now grab one of the
> superuser_reserved_connections slots, putting us one step closer to
> the bad scenario where there's no way for the superuser to log in
> interactively and troubleshoot.
>
> In other words, I don't care whether or not the replication connection
> is or is not technically a superuser connection.  What I think is
> important is trying to preserve the ability for a superuser to log in
> interactively and clean up the mess even when the regular supply of
> connections is maxed out.

Yeah, I agree with you, but the difference is only how to achieve.
ISTM that there are three choices:

1. Heikki's proposal
> ReservedBackends = superuser_reserved_connections + max_wal_senders
> MaxBackends = max_connections + autovacuum_max_workers + max_wal_senders + 1

2. My proposal
Remove superuser privilege from replication connection

3. Your proposal
Treat superuser replication connection like non-superuser one

Since 3. is confusing for me, I like 1. or 2.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

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

From: Tom Lane on 20 Apr 2010 19:53
Robert Haas <robertmhaas(a)gmail.com> writes:
> Current logic says we hit the connection limit if:

> if (!am_superuser &&
> ReservedBackends > 0 &&
> !HaveNFreeProcs(ReservedBackends))

> Couldn't we just change this to:

> if ((!am_superuser || am_walsender) &&
> ReservedBackends > 0 &&
> !HaveNFreeProcs(ReservedBackends))

As of the patch I just committed, that code is not reached anymore by a
walsender process. However, it shouldn't be hard to put a similar test
into the walsender code path.

regards, tom lane

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

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: psql: edit function, show function commandspatch
Next: [HACKERS] Alias to rollback keyword