max-connections cannot be raised over 4190

[Trent Lloyd]

If you set max-connections to a value larger than 4190, it will not be honored. The reason is that MySQL caps the max_connections limit based on the value of open_files_limit, the "nofile" ulimit.

On bionic percona-cluster-5.7 LimitNOFile is hardset in the unit to 5000 and the charm has no code to set an override. This seems to be a change over xenial where this setting worked.

This is also report in /var/log/mysql/error.log:
2020-11-24T08:11:38.888629Z 0 [Warning] Changed limits: max_connections: 4190 (requested 12000)

We need to set an override.conf for LimitNOFile with max_connections > 4190 to allow for the setting to take effect - this is a limit being hit in practice with larger OpenStack deployments.

Further background: https://www.percona.com/blog/2017/10/12/open_files_limit-mystery/

[Trent Lloyd (lathiat)]

The following neutron-api bug is related to this, it is common to have neutron-api making 400-1600 connections per node consuming either a large part of all of this limit in 3 node HA setups:
https://bugs.launchpad.net/charm-neutron-api/+bug/1905810

[Trent Lloyd (lathiat)]

Review submitted:
https://review.opendev.org/c/openstack/charm-percona-cluster/+/764645

[Trent Lloyd (lathiat)]

Released in 21.01

[Trent Lloyd (lathiat)]

This appears to have been merged, and released in 21.01. Can someone update the milestone?

[Shunde Zhang (shunde-zhang)]

This fix adds an override file /etc/systemd/system/mysql.service.d/charm-nofile.conf
This is fine if percona cluster already exists and the max connection config is set to a new value.

However, if a new percona cluster is created with a max connection value, this override file is not used because when the percona cluster is created and started for the first time, the started service is <email address hidden>, therefore the override file it uses is /etc/systemd/system/mysql@.service.d/charm-nofile.conf, which has a '@' symbol in the path.

This is how it looks like when a new percona cluster is just created.

root@juju-fb8079-ost-3:~# systemctl status mysql
● mysql.service - Percona XtraDB Cluster daemon
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
  Drop-In: /etc/systemd/system/mysql.service.d
           └─charm-nofile.conf
   Active: inactive (dead) since Thu 2021-07-01 00:42:27 UTC; 10min ago
  Process: 1932 ExecStop=/etc/init.d/mysql stop (code=exited, status=0/SUCCESS)
 Main PID: 512 (code=exited, status=0/SUCCESS)

root@juju-fb8079-ost-3:~# systemctl status <email address hidden>
● <email address hidden> - Percona XtraDB Cluster daemon with config /etc/default/mysql.bootstrap
   Loaded: loaded (/lib/systemd/system/mysql@.service; disabled; vendor preset: enabled)
   Active: active (running) since Thu 2021-07-01 00:42:34 UTC; 11min ago
  Process: 2145 ExecStart=/etc/init.d/mysql start ${EXTRA_ARGS} (code=exited, status=0/SUCCESS)
  Process: 2043 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 2185 (mysqld_safe)
    Tasks: 102 (limit: 4703)
   CGroup: /<email address hidden>
           ├─2185 /bin/sh /usr/bin/mysqld_safe --wsrep-new-cluster
           └─2808 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/percona-xtradb-cluster --plugin-dir=/usr/lib/mysql/plugin --wsrep-provider=/usr/lib/galera3/libgalera_smm.so --

Maybe the charm should create /etc/systemd/system/mysql@.service.d/charm-nofile.conf too?

[Shunde Zhang (shunde-zhang)]

the above bug is created as in https://bugs.launchpad.net/charm-percona-cluster/+bug/1962434