Open files limit no longer applied after in place upgrade from 5.6 to 5.7

[Sergiu]

The in place upgrade from 5.6 to 5.7.17 left the mysql service without extended open files limit.
The target system is Ubuntu Server 14.04, while mysql version is the one which includes TokuDB engine. The open files limits were previously set in /etc/security/limits.conf as follows:
* soft nofile 1000000
* hard nofile 1000000
The limits were previously checked as being correctly applied by looking at /proc/mysqlPid/limits.
After upgrade, nofile limit is set to 4096. No server configuration was changed.

If this is not a bug, please kindly suggest what configuration can be applied in order to raise the limits to previous values.
Thank you

[fimbulvetr (fimbulvetr)]

I just setup a 14.04 box yesterday with PS 5.7.17.

The only confirmed way for me to have it honor *any* nofile/LimitNOFILE setting was to add the line:

session required pam_limits.so

to

/etc/pam.d/common-session

Then, perhaps out of pure superstition or old habits, I logged out completely from the box, all of my ssh sessions terminated. (Mainly, this is because I use ssh session sharing (ControlMaster) which requires me to log out of every session before a new ssh session to the box gives me a legitimately new login)

Once this was done, 14.04 + PS init.d/mysql script would honor my /etc/security/limits.conf

I can't comment on how your 5.6 script worked, perhaps it was edited to hardcode nofile or maybe it was a bit more featureful than the 5.7 one, but the stock 5.7 one only works with the aforementioned steps.

Unfortunately, 14.04 was a major transitory release between system V, upstart and systemd - which means unless you find exact instructions for your service, you may have to edit some combination of the 3 files to get it all to jive. I am confident that the shipped PS 5.7 init.d script is entirely system V and needs to load that pam_limits.so for pam sessions in order for it to enable reading the limits.conf file.

[Sergiu (sergiuhlihor)]

I confirm that workaround (adding "session required pam_limits.so" to /etc/pam.d/common-session) worked.

The upgrade was done from version 5.6.28-76.1 to 5.7.17-11 . The original startup script from 5.6 was never touched (it's basically the one from the installer).

I have installed 5.7 on a clean machine with Ubuntu 16.04 and this time nothing is enforced. Any suggestions? (same limits from 14.04 are applied)

[Marcelo Altmann (marcelo.altmann)]

I have confirmed this issue:
When we try to increase the open_files_limit on 5.6 it's enough to change /etc/security/limits.conf, but for 5.7 it only gets applied if we change /etc/pam.d/common-session

MYSQL 5.6 with open_files_limit = 1024000 works

-----
root@vagrant-ubuntu-trusty-64:~# /etc/init.d/mysql restart
 * Stopping MySQL (Percona Server) mysqld [ OK ]
 * Starting MySQL (Percona Server) database server mysqld [ OK ]
 * Checking for corrupt, not cleanly closed and upgrade needing tables.
root@vagrant-ubuntu-trusty-64:~# mysql -u root -e "show variables like '%open_files%';"
+-------------------+---------+
| Variable_name | Value |
+-------------------+---------+
| innodb_open_files | 2000 |
| open_files_limit | 1024000 |
+-------------------+---------+
root@vagrant-ubuntu-trusty-64:~# init 6
root@vagrant-ubuntu-trusty-64:~# Connection to 127.0.0.1 closed by remote host.
Connection to 127.0.0.1 closed.
marcelo.altmann@bm-support01:~/vagrant/187687$ vagrant ssh
Welcome to Ubuntu 14.04.5 LTS (GNU/Linux 3.13.0-117-generic x86_64)
vagrant@vagrant-ubuntu-trusty-64:~$ sudo su -
root@vagrant-ubuntu-trusty-64:~# mysql -u root -e "show variables like '%open_files%';"
+-------------------+---------+
| Variable_name | Value |
+-------------------+---------+
| innodb_open_files | 2000 |
| open_files_limit | 1024000 |
+-------------------+---------+
root@vagrant-ubuntu-trusty-64:~# cat /etc/security/limits.conf

root soft nofile 1048576
root hard nofile 1048576
root hard memlock unlimited
mysql soft nofile 1048576
mysql hard nofile 1048576
mysql hard memlock unlimited

# End of file
-----

Install MySQL 5.7

-----
root@vagrant-ubuntu-trusty-64:~# apt-get remove percona-server-server-5.6
. . .
root@vagrant-ubuntu-trusty-64:~# apt-get install percona-server-server-5.7
 * Percona Server 5.7.18-14 is started
-----

Limits don't get applied on MySQL 5.7

-----

root@vagrant-ubuntu-trusty-64:~# mysql -u root -e "show variables like '%open_files%';"
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| innodb_open_files | 431 |
| open_files_limit | 1024 |
+-------------------+-------+
root@vagrant-ubuntu-trusty-64:~# init 6
root@vagrant-ubuntu-trusty-64:~# Connection to 127.0.0.1 closed by remote host.
Connection to 127.0.0.1 closed.
marcelo.altmann@bm-support01:~/vagrant/187687$ vagrant ssh
Welcome to Ubuntu 14.04.5 LTS (GNU/Linux 3.13.0-117-generic x86_64)
vagrant@vagrant-ubuntu-trusty-64:~$ sudo su -
root@vagrant-ubuntu-trusty-64:~# mysql -u root -e "show variables like '%open_files%';"
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| innodb_open_files | 431 |
| open_files_limit | 1024 |
+-------------------+-------+...

[Jericho Rivera (jericho-rivera)]

fwiw, mysqld honors the limits as indicated in /etc/security/limits.conf but ignores its own limits:

root@trusty:/root# service mysql restart
 * Stopping Percona Server 5.7.18-14
...
 * Percona Server 5.7.18-14 is stopped
 * Re-starting Percona Server 5.7.18-14
..
 * Percona Server 5.7.18-14 is started
root@trusty:/root#
root@trusty:/root# grep nofile /etc/security/limits.conf
# - nofile - max number of open files
root soft nofile 1048576
root hard nofile 1048576
mysql soft nofile 1048576
mysql hard nofile 1048576
root@trusty:/root#
root@trusty:/root# mysql -uroot -p -e "show global variables like '%open_files%'";
Enter password:
+-------------------+---------+
| Variable_name | Value |
+-------------------+---------+
| innodb_open_files | 2000 |
| open_files_limit | 1048576 | <---
+-------------------+---------+
root@trusty:/root# grep -B1 open /etc/my.cnf
[mysqld]
open-files-limit=1024000
--
[mysqld_safe]
open-files-limit=1024000
root@trusty:/root#

[Agustín (agustin-gallego)]

Added upstream bug related to this.

root@VM-ubuntu14:~# echo "mysql soft nofile 1048576" >> /etc/security/limits.conf
root@VM-ubuntu14:~# echo "mysql hard nofile 1048576" >> /etc/security/limits.conf
root@VM-ubuntu14:~# su mysql --shell /bin/bash --command "ulimit -Hn; ulimit -Sn"
4096
1024
root@VM-ubuntu14:~# echo "open-files-limit=1024000" >> /etc/mysql/mysql.conf.d/mysqld.cnf
root@VM-ubuntu14:~# service mysql stop
...
 * MySQL Community Server 5.7.18 is stopped
root@VM-ubuntu14:~#
root@VM-ubuntu14:~# mv /var/log/mysql/error.log /var/log/mysql/error.log.bak
root@VM-ubuntu14:~#
root@VM-ubuntu14:~# service mysql start
..
 * MySQL Community Server 5.7.18 is started
root@VM-ubuntu14:~# cat /var/log/mysql/error.log | grep -C2 "\[Warning\]" | grep open
2017-05-19T18:00:33.161322Z 0 [Warning] Could not increase number of max_open_files to more than 1024 (request: 1024000)
2017-05-19T18:00:33.161819Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
root@VM-ubuntu14:~# mysql -u root -e "show variables like '%open_files%';"
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| innodb_open_files | 431 |
| open_files_limit | 1024 |
+-------------------+-------+
root@VM-ubuntu14:~# echo "session required pam_limits.so" >> /etc/pam.d/common-session
root@VM-ubuntu14:~# service mysql stop
...
 * MySQL Community Server 5.7.18 is stopped
root@VM-ubuntu14:~#
root@VM-ubuntu14:~# mv /var/log/mysql/error.log /var/log/mysql/error.log.bak.2
root@VM-ubuntu14:~# service mysql start
..
 * MySQL Community Server 5.7.18 is started
root@VM-ubuntu14:~#
root@VM-ubuntu14:~#
root@VM-ubuntu14:~# cat /var/log/mysql/error.log | grep -C2 "\[Warning\]" | grep open
root@VM-ubuntu14:~# mysql -u root -e "show variables like '%open_files%';"
+-------------------+---------+
| Variable_name | Value |
+-------------------+---------+
| innodb_open_files | 2000 |
| open_files_limit | 1048576 |
+-------------------+---------+
root@VM-ubuntu14:~# mysql -u root -e "show variables like '%version%';"
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.18 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.18 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+------------------------------+

[Agustín (agustin-gallego)]

Oops :) I think I messed up, since the URL changed to mysql-server. I have undone the change, and will check how to edit it correctly. Sorry!

[Sveta Smirnova (svetasmirnova)]

Reason for this behavior change is how mysqld_safe is invoked by the startup script.

Version 5.6.37 for Trusty/Xenial:

"${PERCONA_PREFIX}"/bin/mysqld_safe > /dev/null 2>&1 &

Version 5.7.19 for Trusty/Xenial:

su - mysql -s /bin/bash -c "mysqld_safe > /dev/null &"

So for version 5.7 mysqld_safe script invoked as mysql user and cannot change any limits by itself.

This change existed in upstream since, at least, version 5.7.9 (first 5.7 GA version).

[Sveta Smirnova (svetasmirnova)]

Further investigation showed what this change happened when mysql.init script from MySQL 5.7 was merged into build-ps/debian/percona-server-server-5.7.mysql.init

Oracle version 5.6 called mysqld_safe as mysql user while Percona's init script did not.

I am not sure if we should revert this merge at this stage, but having this somewhere in our release notes will be good.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1796