Add charm config for max_total_connections

[Facundo Ciccioli]

On a deployed cloud using mysql-innodb-cluster we started observing some API calls (like openstack flavor show ...) return with 500.

nova-cloud-controller's logs were showing "Too many connections to MySQL Router." Then the n-c-c's local mysql router was showing a lot of

2022-04-22 10:55:08 routing WARNING [7fd12e4ea700] [routing:jujuCluster_rw] reached max active connections (512 max=512)

The other n-c-c units are borderline with the amount of connections:

$ juju run -a nova-cloud-controller -- sudo ss -Onatp state connected \'sport = 3306\' \| grep -wF \`pgrep mysqlrouter\` \
| wc -l
- Stdout: |
    512
  UnitId: nova-cloud-controller/0
- Stdout: |
    480
  UnitId: nova-cloud-controller/1
- Stdout: |
    478
  UnitId: nova-cloud-controller/2

And this is who's using the connections on one of the n-c-c units, in case it's of any use (maybe we want to investigate why there's so many, but I'm not sure):

$ sudo ss -Onatp state connected | grep -f <(sudo ss -Onatp state connected 'sport = 3306' | grep 2250194 | awk '{print $5;}' | cut -d: -f2 | sort -nu) | awk '!/mysqlrouter/ && $6 != "" {print $6;}' | cut -d\" -f2 | sort | uniq -c
    173 apache2
      5 haproxy
      1 jujud
    232 nova-conductor
    128 nova-scheduler

The charm doesn't currently feature a config for modifying the max_total_connections mysql router option [1] and so the default of 512 is being used. Maybe it should be possible to change this default.

[1] https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-conf-options.html#option_mysqlrouter_max_total_connections

[Edward Hope-Morley (hopem)]

This is a dup of https://bugs.launchpad.net/charm-mysql-router/+bug/1929826 (although max_connections will need to be replaced with max_total_connections since the former is deprecated).

[Eric Chen (eric-chen)]

Indeed. I think we should replace max_connections by max_total_connections directly.

[DUFOUR Olivier (odufourc)]

Although "max_connections" is being replaced, as a workaround for now you can use the latest/edge branch of the charm which has for now this legacy option configurable.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.opendev.org/c/openstack/charm-mysql-router/+/840435

[Billy Olsen (billy-olsen)]

While max_connections / max_total_connections can be used here to increase the number of connections to the charm instance, the number of workers seems far higher than what you probably want and reduction of workers should actually be considered.

[Nobuto Murata (nobuto)]

Subscribing ~field-high.

This is now affecting one project where a customer is doing a migration from bionic to focal and percona-cluster to mysql-innodb-cluster + mysql-router. Initiating many connections from a unit with a bunch of workers was fine as long as the db server side has enough max_connections. However, after the migration, the client side of max_total_connections are making client initiated connections fail.

[Billy Olsen (billy-olsen)]

The fact that you are running out of connections on the client side should be an indicator that you might have too many worker processes in place. The only place where I have seen this be a potential problem is in the nova cloud controller charm where you have the scheduler and api both taking up lots of connections due to the number of workers and the thread pool size.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/charm-mysql-router/+/840435
Committed: https://opendev.org/openstack/charm-mysql-router/commit/6fb39ff8b904230fe5ac4b551dd93364964a3cad
Submitter: "Zuul (22348)"
Branch: master

commit 6fb39ff8b904230fe5ac4b551dd93364964a3cad
Author: Eric Chen <email address hidden>
Date: Wed May 4 09:56:11 2022 +0000

    Add max_total_connections configuration option

    Add new configuration max_total_connections

    Closes-Bug: #1969927
    Change-Id: Id0e6b878f3857f6c590f00f64d6bcaedb51094fc

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/jammy
Review: https://review.opendev.org/c/openstack/charm-mysql-router/+/850918

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/charm-mysql-router/+/850918
Committed: https://opendev.org/openstack/charm-mysql-router/commit/947b636037f192f130462b5857d27c5924b5b18d
Submitter: "Zuul (22348)"
Branch: stable/jammy

commit 947b636037f192f130462b5857d27c5924b5b18d
Author: Eric Chen <email address hidden>
Date: Wed May 4 09:56:11 2022 +0000

    Add max_total_connections configuration option

    Add new configuration max_total_connections

    Closes-Bug: #1969927
    Change-Id: Id0e6b878f3857f6c590f00f64d6bcaedb51094fc
    (cherry picked from commit 6fb39ff8b904230fe5ac4b551dd93364964a3cad)