Re: Patch for reserved connections for replication users

On Sun, Oct 20, 2013 at 1:26 AM, Gibheer <gibheer(at)zero-knowledge(dot)org> wrote:
> On Sat, 19 Oct 2013 12:09:57 +0530
> Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
>> On Thu, Oct 17, 2013 at 8:57 AM, Amit Kapila
>> <amit(dot)kapila16(at)gmail(dot)com> wrote:
>> > On Wed, Oct 16, 2013 at 4:30 AM, Gibheer
>> > <gibheer(at)zero-knowledge(dot)org> wrote:
>> >> On Mon, 14 Oct 2013 11:52:57 +0530
>> >> Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>> >>
>> >>> On Sun, Oct 13, 2013 at 2:08 PM, Gibheer
>> >>> <gibheer(at)zero-knowledge(dot)org> wrote:
>> >>> > On Sun, 13 Oct 2013 11:38:17 +0530
>> >>> > Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>> >>> >
>> >>> >> On Thu, Oct 10, 2013 at 3:17 AM, Gibheer
>> >>> >> <gibheer(at)zero-knowledge(dot)org> wrote:
>> >>> >> > On Mon, 7 Oct 2013 11:39:55 +0530
>> >>> >> > Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>> >>> >> >> Robert Haas wrote:
>> >>> >> >> On Mon, Aug 5, 2013 at 2:04 AM, Andres Freund
>> >>> >> >> <andres(at)2ndquadrant(dot)com> wrote:
>> >> I would be glad, if you could also test the patch again, as I'm
>> >> nearly code blind after testing it for 4 hours.
>> >> I had the problem, that I could not attach as many replication
>> >> connections as I wanted, as they were denied as normal
>> >> connections. I think I got it fixed, but I'm not 100% sure at the
>> >> moment. After some sleep, I will read the code again and test it
>> >> again, to make sure, it really does what it is supposed to do.
>> >
>> > You have forgotten to attach the patch. However, now it is important
>> > to first get the consensus on approach to do this feature, currently
>> > there are 3 approaches:
>> > 1. Have replication_reserved_connections as a separate parameter to
>> > reserve connections for replication
>> > 2. Consider max_wal_sender to reserve connections for replication
>> > 3. Treat replication connections as a pool outside max_connections
>> >
>> > Apart from above approaches, we need to think how user can view the
>> > usage of connections, as pg_stat_activity doesn't show replication
>> > connections, so its difficult for user to see how the connections
>> > are used.
>> >
>> > I am really not sure what is best way to goahead from here, but I
>> > think it might be helpful if we can study some use cases or how
>> > other databases solve this problem.
>>
>> Today I spent some time seeing how other databases (in particular
>> MySQL) achieve it. There seems to be no separate way to configure
>> replication connections, rather if user faced with
>> too_many_connections
>> (https://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html)
>> they allow one spare connection (super user connection) to check what
>> all connections are doing, it seems all connections can be viewed
>> through one common command Show ProcessList
>> (https://dev.mysql.com/doc/refman/5.5/en/show-processlist.html)
>>
>> By above, I don't mean that we should only do what other databases
>> have done, rather it is towards trying to find a way which can be
>> useful for users of Postgresql.
>>
>> Your views/thoughts?
>>
>> With Regards,
>> Amit Kapila.
>> EnterpriseDB: http://www.enterprisedb.com
>>
>
> Hi,
>
> I have accessto MySQL and PostgreSQL at work and it is true, that MySQL
> has not separate pools. It also happend to us, that we lost connection
> from a slave and it was unable to get back into replication on MySQL
> and Postgres, because of some stupid applications.
> One difference is, like you said, that replication connections are
> listed in `show processlist`, where replication connections in postgres
> are listed in a seperate view from the rest of the connections. I think
> the postgres way is the better in this case, as the picture of the
> replication state of the complete cluster can be viewed by one select
> on the master. In MySQL it needs one SQL on each slave.

Going either way (separate management of replication connections or
unified max_connections), user has to understand how to configure
the system, so that it serves his purpose.
Here I think the important thing is to decide which way it would be
easy for users to understand and configure the system.
As an user, I would be happy with one parameter (max_connections)
rather than having multiple parameters for connection management and
understand each one separately to configure the system. However here
many users would be more comfortable if there are multiple parameters
for configuring the system. I was not sure which way users would like
to configure connection management and neither we had consensus to
proceed, thats why I had checked other database to know how users are
configuring connection management in database and it seems to me that
many users are using single parameter.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com