max open files limit prevents max_connections over 214 on systemd

[Mario Splivalo]

With the default mysql-server installation on systemd versions of Ubuntu (xenial and above) setting max_connections to more than 214 will not work, due to systemd imposed limits:

root@juju-c2777d-default-1:~# grep max_connections /etc/mysql/mysql.conf.d/mysqld_settings_charm.cnf
max_connections = 5500

root@juju-c2777d-default-1:~# grep max_connections /var/log/mysql/error.log
2017-10-05T12:41:16.958726Z 0 [Warning] Changed limits: max_connections: 214 (requested 5500)
2017-10-05T12:41:19.198391Z 0 [Warning] Changed limits: max_connections: 214 (requested 5500)

root@juju-c2777d-default-1:~# grep open_files /var/log/mysql/error.log
2017-10-05T12:41:11.824199Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-10-05T12:41:16.958668Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 27500)
2017-10-05T12:41:19.198342Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 27500)

As can be seen, mysql tries to increase the open files limit to 27500, but due to systemd imposed limitation it fails to do so, and then it selects maximum possible number of connections, based on open files limit.

This was the problem with default setups with pre-systemd installs, but upstart allowed mysqld to change the limit of open files before mysqld drops privileges from root to mysql system user.
Even before that, on sysV init, there was a wrapper, mysqld_safe, which run as root, used ulimit to change the open files limit and then started mysqld as mysql user.

Would it make sense to ship mysql-server packages with mysql.service unit files so that there is no limit for open files, or so that open files limit is substantially higher than 1024?

This was all discussed when Ubuntu 15.10 came out, for mysql-5.6, in bug LP: #1434758, but I am still bringing this up for more recent mysql versions.

[Launchpad Janitor (janitor)]

Status changed to 'Confirmed' because the bug affects multiple users.

[Mario Splivalo (mariosplivalo)]

This can be easily circumvented manually by creating a systemd unit override file for mysql service.

One should create a file, named "/etc/systemd/system/mysql.service.d/limits.conf", with this contents (just two lines):

[Service]
LimitNOFILE=infinity

This fix is now implemented in mysql juju charm: https://github.com/marcoceppi/charm-mysql/issues/20

[Nish Aravamudan (nacc)]

Should this be fixed in Debian? Or at least reported there? So that it does not add to the delta?

And is the underlying proposed fix to add the Service LimitNOFILE change to the default mysql service file?

[Robie Basak (racb)]

This sounds reasonable to go into Debian as well, thanks. If someone can please propose and test a commit against current Debian VCS, I'll be happy to push it.

[Launchpad Janitor (janitor)]

Status changed to 'Confirmed' because the bug affects multiple users.

[Robie Basak (racb)]

Is https://salsa.debian.org/rbasak-guest/mysql/merge_requests/3 acceptable to everyone? This sets LimitNOFile to 5000 based on upstream.

[Mario Splivalo (mariosplivalo)]

That is better thatn what it is now, but! What is the rationale behind not letting systemd limit the number of open files?
The limit exists in MySQL anyways, and for mysql administrator this is just an additional option that needs to be changed. I also imagine most of MySQL admins will set LimitNOFILE to Infinity anyways, and control open-files-limit.

So, +1 on setting LimitNOFile to 5000, but with suggestion to setting it to INFINITY.

[Launchpad Janitor (janitor)]

This bug was fixed in the package mysql-5.7 - 5.7.21-1ubuntu1

---------------
mysql-5.7 (5.7.21-1ubuntu1) bionic; urgency=medium

  * Merge from Debian.
  * Remaining changes:
    - Revert "Build with libmecab-dev" since libmecab-dev is in
      universe.

mysql-5.7 (5.7.21-1) unstable; urgency=high (security fixes)

  * Imported upstream version 5.7.21 to fix security issues:
    - http://www.oracle.com/technetwork/security-advisory/cpujan2018-3236628.html
    - CVE-2018-2562 CVE-2018-2565 CVE-2018-2573 CVE-2018-2576
    - CVE-2018-2583 CVE-2018-2586 CVE-2018-2590 CVE-2018-2591
    - CVE-2018-2600 CVE-2018-2612 CVE-2018-2622 CVE-2018-2640
    - CVE-2018-2645 CVE-2018-2646 CVE-2018-2647 CVE-2018-2665
    - CVE-2018-2667 CVE-2018-2668 CVE-2018-2696 CVE-2018-2703
    - CVE-2017-3737
    (Closes: #887477)

 -- Lars Tangvald <email address hidden> Fri, 02 Feb 2018 11:58:04 +0100