Re: Patch for reserved connections for replication users

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.

On the other hand, wor on logical replication is done, which will have
predefined slots for it
(http://wiki.postgresql.org/wiki/BDR_User_Guide#max_logical_slots)
This will also consume slots from max_wal_senders
(http://wiki.postgresql.org/wiki/BDR_User_Guide#max_wal_senders) With
that, I think the best approach is to build a pool around replication
only connections, despite it's possible kind. Information about them
will only be available through pg_stat_replication. When a connection
limit is hit, it is clear wether it is a normal connection or a
replication connection and where the user should look for further
information about it.
Also nobody would have to calculate how many connections would have to
be reserved for what service.

I have yet to take a look how background worker connections are handled
(seperate pool or unified with normal connections), because for them
the same limitations apply. We want them running despite the load from
applications or users.

I will report back, when I had more time to look into it.

regards,

Stefan Radomski