[Focal/Victoria/Charm-6] DB requests fails when slaves are offline

[Kanshi]

Hello,

We have deployed a 3 nodes MySQL InnonDB cluster (version 8.0.25) with juju for an OpenStack deployment.
We recently faced an issue where the our MySQL cluster faced an outage but we now have the master node back online. The two slave nodes are still down for maintenance. The issue that we face is that 2/3 of all requests to the Horizon dashboard fails with a 500 error. Looking at Nova logs for example the error says that there are too many connections to the MySQL router:

pymysql.err.OperationalError: (1040, 'Too many connections to MySQL Router')

How can this error be fixed is order to have HA when some slaves are down? We have configured the MySQL cluster to accept up to 2000 connections, but according to the documentation the default value on the router side is 512 (which is not modified by the generated configuration from juju): https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-conf-options.html#option_mysqlrouter_max_connections

juju 2.9.0
Model OpenStack 2.8.9
OpenStack Victoria
Ubuntu Focal
MySQL InnoDB 8.0.25
Charm v6

Thanks for your support.

[Alex Kavanagh (ajkavanagh)]

Triaged as wishlist as this is a new feature.

The best course of action would probably be to add a config option like the mysql-server (as indicated in the original bug report post). For completeness, this is:

  max-connections:
    type: int
    default: 600
    description: |
      Maximum connections to allow. A value of -1 means use the server's
      compiled-in default. This is not typically that useful so the
      charm will configure PXC with a default max-connections value of 600.
      Note: Connections take up memory resources. Either at startup time with
      performance-schema=True or during run time with performance-schema=False.
      This value is a balance between connection exhaustion and memory
      exhaustion.
      .
      Consult a MySQL memory calculator like http://www.mysqlcalculator.com/ to
      understand memory resources consumed by connections.
      See also performance-schema.

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

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

commit 01587ab402134ab00cb794eeadd46215cdd425d0
Author: Olivier Dufour-Cuvillier <email address hidden>
Date: Fri Nov 26 12:11:23 2021 +0100

    Add max_connections configuration option

    Allow the user to change the number of connections to the database
    cluster units to their needs.
    The default value is the same as without the configuration option.

    Closes-Bug: #1929826
    Change-Id: I10556e9d373cd8505c4e2e96d2b6031fe4ee9166

[Eric Chen (eric-chen)]

From the documentation, max_total_connections is the new configuration for maximum connections.

https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-conf-options.html#option_mysqlrouter_max_connections

``
The legacy max_connections option sets a value per routing instance, such as one value for read-only, and another for write-only. The max_total_connections option sets one value for all routing instances combined.
``

Do we want to change the configuration to the new one? I try to fix this issue, but it's strange to support both. Any idea?

https://bugs.launchpad.net/charm-mysql-router/+bug/1969927