One possible solution would be to take inspiration from https://github.blog/2016-08-17-context-aware-mysql-pools-via-haproxy/ and use a haproxy-level health check that lets us easily define a server as depooled. I was briefly considering using a conftool-based solution to manage the pooling on haproxy level but can't think of a way to fit our needs (active/backup, ports, ... etc) into the current host-based etcd schema.

I have an untested implementation of depooling a server from a cluster using set server ... status drain: https://gerrit.wikimedia.org/r/c/operations/cookbooks/+/760880/11/cookbooks/sre/wikireplicas/update-views.py#137

Ideally the depooled server would be repooled if the haproxy service was restarted, since the fact that it is depooled in this approach is only in memory and is not tracked via puppet-managed files. If the overall approach seems good I can do some testing.

In T300427#7661326, @taavi wrote:

I was briefly considering using a conftool-based solution to manage the pooling on haproxy level but can't think of a way to fit our needs (active/backup, ports, ... etc) into the current host-based etcd schema.

Coming back to this year and a bit later, now I think conftool can be made work here, and is probably the best answer since that's something .

The schema could be something like:

wikireplica-db-analytics:
  clouddb1017.eqiad.wmnet: [s1, s3]
  clouddb1018.eqiad.wmnet: [s2, s7]
wikireplica-db-web:
  clouddb1013.eqiad.wmnet: [s1, s3]
  clouddb1014.eqiad.wmnet: [s2, s7]

The section->port mapping is standard, so that won't cause problems. On the HAProxy side, we generate backends for both replica types with only the hosts on that section, without the backup servers like today. And, on the frontend, we use nbsrv to send traffic to the other backend if there are no available backends on the primary. The HAProxy logic might look something like this:

# These will be generated from etcd/conftool
backend wikireplica-db-analytics-s1
  mode tcp
  listen ....

backend wikireplica-db-web-s1
  mode tcp
  listen ....

# This will be statically generated by Puppet
frontend wikireplica-analytics-s1
  mode tcp
  bind ::3311

  acl use_backup nbsrv(wikireplica-db-analytics-s1) lt 1
  use_backend wikireplica-db-web-s1 if use_backup
  default_backend wikireplica-db-analytics-s1

@taavi - Thanks so much, that does look really helpful.

The only other thing I think would be helpful is if we could somehow also remove the spof on each of the wikireplica clusters that is provided by the two dbproxy10[18-19] servers.
These two servers still operate individually, with an haproxy configuration that is, effective, the inverse of the other.

Your suggestion above would still retain this requirement, wouldn't it? Or have I misunderstood something?
If we could make it that dbproxy10[18-19] were identical, each supporting both the analytics and web clusters, then it would make it easier to depool each of these servers for maintenance too.

My change doesn't immediately fix the proxy redundancy issue, but it definitely makes it much easier to solve as all of the backend configuration will already be live on all of the proxies. After the change I propose is live, making the proxies redundant is essentially as simple as changing the existing frontends to bind to the specific service VIP, and adding a new frontend with the reverse analytics/web configuration and the other VIP. After that the proxies would have identical configuration.

Or we could use the opportunity to do both changes at the same time, and also combine it with moving the load balancing to our new cloudlb setup and remove the separate dbproxy hosts and the LVS services in the process.

Change 973760 had a related patch set uploaded (by Majavah; author: Majavah):

[operations/puppet@production] Add wiki replica backends to conftool

https://gerrit.wikimedia.org/r/973760

In T300427#9326131, @taavi wrote:

Or we could use the opportunity to do both changes at the same time, and also combine it with moving the load balancing to our new cloudlb setup and remove the separate dbproxy hosts and the LVS services in the process.

This seems like an excellent way forward to me.

That would mean:

• integrating the work on this ticket, correct? T346947: Move wiki replicas behind cloudlb
• whilst effectively making this ticket redundant: T322658: Improve LVS config for wikireplicas (dbproxy1018/dbproxy1019)

I'm not too familiar with the cloudlb service at the moment, but I see that they also run haproxy.
Would we still be taling about using the same conftool based modification and config rewriting as you mentioned in T300427#9325905 ?
Is reloading the haproxy configuration upon a change with confd already handled, or is this something that we would have to add?

In T300427#9326322, @BTullis wrote:

That would mean:

• integrating the work on this ticket, correct? T346947: Move wiki replicas behind cloudlb
• whilst effectively making this ticket redundant: T322658: Improve LVS config for wikireplicas (dbproxy1018/dbproxy1019)

Correct.

I'm not too familiar with the cloudlb service at the moment, but I see that they also run haproxy.
Would we still be taling about using the same conftool based modification and config rewriting as you mentioned in T300427#9325905 ?

Yes, indeed.

Is reloading the haproxy configuration upon a change with confd already handled, or is this something that we would have to add?

Yes. Confd can be told to run an arbitrary command after writing a file, so it can be configured to run /usr/bin/systemctl reload haproxy.service.

Change 973769 had a related patch set uploaded (by Majavah; author: Majavah):

[operations/homer/public@master] cr-labs: permit cloudlb to wiki replicas

https://gerrit.wikimedia.org/r/973769

Change 973761 had a related patch set uploaded (by Majavah; author: Majavah):

[operations/puppet@production] Add wiki replicas to cloudlb

https://gerrit.wikimedia.org/r/973761

Change 973777 had a related patch set uploaded (by Majavah; author: Majavah):

[operations/puppet@production] P:wmcs: wikireplicas: allow access from cloudlb

https://gerrit.wikimedia.org/r/973777

Change 973769 merged by jenkins-bot:

[operations/homer/public@master] cr-labs: permit cloudlb to wiki replicas

https://gerrit.wikimedia.org/r/973769

Change 973760 merged by Majavah:

[operations/puppet@production] Add wiki replica backends to conftool

https://gerrit.wikimedia.org/r/973760

Change 973777 merged by Majavah:

[operations/puppet@production] P:wmcs: wikireplicas: allow access from cloudlb

https://gerrit.wikimedia.org/r/973777

Change 973761 merged by Majavah:

[operations/puppet@production] Add wiki replicas to cloudlb

https://gerrit.wikimedia.org/r/973761

Change 976735 had a related patch set uploaded (by Majavah; author: Majavah):

[operations/puppet@production] P:etcd: generate wiki replica pool accounts

https://gerrit.wikimedia.org/r/976735

Change 976735 merged by Majavah:

[operations/puppet@production] P:etcd: generate wiki replica pool accounts

https://gerrit.wikimedia.org/r/976735

Change 998356 had a related patch set uploaded (by Majavah; author: Majavah):

[operations/puppet@production] wikireplicas: maintain-views: try depooling host on lock failure

https://gerrit.wikimedia.org/r/998356

Change 1006492 had a related patch set uploaded (by Majavah; author: Majavah):

[operations/puppet@production] cloudlb: wikireplicas: shutdown sessions on down servers

https://gerrit.wikimedia.org/r/1006492

Change 998356 merged by Majavah:

[operations/puppet@production] wikireplicas: maintain-views: try depooling host on lock failure

https://gerrit.wikimedia.org/r/998356

Change 1006492 merged by Majavah:

[operations/puppet@production] cloudlb: wikireplicas: shutdown sessions on down servers

https://gerrit.wikimedia.org/r/1006492