MySQL Bugs: #86388: MySQL 5.7 does not honour open-files-limit as set in my.cnf

Bug #86388	MySQL 5.7 does not honour open-files-limit as set in my.cnf
Submitted:	19 May 2017 20:07	Modified:	9 Oct 2017 16:03
Reporter:	Agustín G	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.18	OS:	Ubuntu (Ubuntu 14.04)
Assigned to:		CPU Architecture:	Any

Description:
MySQL 5.7 doesn't honour the limits set to open-files-limit in the my.cnf file. Instead, if the OS limits for the mysql user are set higher, it uses those.

This was not the case with MySQL 5.6.

I'm aware that the documentation states that limits may be set different, but this seems like a bug, and not really the intended functionality described:

https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_open-files-limit

"mysqld may attempt to allocate more than the requested number of descriptors (if they are available), using the values of max_connections and table_open_cache to estimate whether more descriptors will be needed."

How to repeat:
# Get a fresh Ubuntu 14.04 setup

sudo su -

wget https://dev.mysql.com/get/mysql-apt-config_0.8.6-1_all.deb
dpkg -i mysql-apt-config_0.8.6-1_all.deb 
# configure for 5.7
apt-get update -y && apt-get install -y mysql-server

echo "mysql soft nofile 1048576" >> /etc/security/limits.conf
echo "mysql hard nofile 1048576" >> /etc/security/limits.conf
su mysql --shell /bin/bash --command "ulimit -Hn; ulimit -Sn"

echo "open-files-limit=1024000" >> /etc/mysql/mysql.conf.d/mysqld.cnf

service mysql restart

cat /var/log/mysql/error.log | grep -C2 "\[Warning\]" | grep open
mysql -u root -e "show variables like '%open_files%';"

# Until here, they are ignored because we need to set the following
# in the common-session file for the OS to pick up changes

echo "session required pam_limits.so" >> /etc/pam.d/common-session
su mysql --shell /bin/bash --command "ulimit -Hn; ulimit -Sn"

service mysql restart

cat /var/log/mysql/error.log | grep -C2 "\[Warning\]" | grep open
mysql -u root -e "show variables like '%open_files%';"

# OS user limits are taken, even if they are larger to what
# is used in the my.cnf file

Suggested fix:
As with MySQL 5.6, make the my.cnf settings for open-files-limit be honoured correctly, instead of using the maximum available for the OS user.

Thanks!
Agustín.

Also, in case it was not clear, note that the following is needed for the limits to be taken into account:

# echo "session required pam_limits.so" >> /etc/pam.d/common-session

which was not the case with the 5.6 packages, either.

I would like to add two more scenario:

(1) Instead of modifying common-session, one can login as root with larger open files limit, and restart mysqld, and the open_files_limit will increase. However, as in the case of common-session, it will increase to the user limit which could be much larger which is not correct behavior. [Not clear it is root's limit or mysql's limit as they are set the same].

(2) The other solution is to add "ulimit -n <n>" in /etc/default/mysql which the start up script will source. Again open_files_limit will increase to this limit which is not correct.

If we set up the hard and soft limit specifically in /etc/default/mysql like this:

ulimit -S -n <n>
ulimit -H -n <n>

Then open_files_limit in mysql will increase and it increases to the limit set in my.cnf. This is the only correct workaround so far.

Hi!

Have you tried to do it in the way that is recommended in our mysql-5.7 manual ???

Do you start our server with mysqld_safe, set limits not for mysqld, but for mysqld_safe ???

Do you start mysqld_safe as a root, while passing the `--user` option to mysqld ???

Does the problem persists even then ??? If yes, what is a difference between the value set for mysqld_safe and the one that you get with status variables ???

Thanks in advance for your answers.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hi Sinisa!

Thanks for reviewing this. Let me reply to your comments/questions inline.

>Have you tried to do it in the way that is recommended in our mysql-5.7 manual ???

As mentioned in the initial description, I followed https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_open-files-limit. In it, only mysqld is mentioned, so I used the variable under that section. Even in https://dev.mysql.com/doc/refman/5.7/en/not-enough-file-handles.html, where mysqld_safe is mentioned, mysqld is also mentioned, so it's not clear for me where it should be. Can you send the link to the documentation you mention?

>Do you start our server with mysqld_safe, set limits not for mysqld, but for mysqld_safe ???

Yes, I used `service mysql start`, which in turn calls mysqld_safe. I have now tried setting limits under the [mysqld_safe] section in the my.cnf file, but it is still using maximum limits defined at OS level (in /etc/security/limits.conf).

>Do you start mysqld_safe as a root, while passing the `--user` option to mysqld ???
>Does the problem persists even then ??? If yes, what is a difference between the value set for mysqld_safe and the one that you get with status variables ???

I checked how it's called within /etc/init.d/mysql and it's using the following:

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

I tried with manually starting it as root, and they were correctly applied:

shell> mysqld_safe &
[1] 12815
2017-08-27T10:48:56.162883Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-08-27T10:48:56.227168Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

shell> mysql -u root -e "show variables like 'open_files_limit';"
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| open_files_limit | 1024000 |
+------------------+---------+

On the other hand, using the mysql user, like in the init script, does not:

shell> su - mysql -s /bin/bash -c "mysqld_safe &"
2017-08-27T10:50:51.322621Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-08-27T10:50:51.391651Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

shell> mysql -u root -e "show variables like 'open_files_limit';"
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| open_files_limit | 1048576 |
+------------------+---------+

Best,
Agustín.

Hi!

First of all, you are supposed to read the whole manual and not to ask us to do it for you.

Second, one million file handles is more then enough.

Third, user `root` should start mysqld_safe, which would then switch MySQL server to using user `mysql` privileges (or similar non-root user).

Fourth, you can set also file handles under [mysqld_safe] options header !!!!!

Hi!

I am confused by this comment:

> First of all, you are supposed to read the whole manual and not to ask us to do it for you.

as that seems to go against what is metioned in the first bullet point in this page: https://dev.mysql.com/doc/refman/5.7/en/bug-reports.html

This point states that "Start by searching the MySQL online manual at ...", which is in line with my expectations from a Reference Manual. This type of manual is normally written to be used as a reference (as the name implies), with readers directly accesing relevant sections using an index (or searching, as web based manuals now allow). This is opposed to the cover-to-cover approach of reading a text book.

If this is wrong and, in fact, it is expected that one reads the MySQL Reference Manual cover-to-cover, I think this should be added to the manual itself. As an example, I recommend Donald Knuth's algorithm for reading The Art Of Computer Programming, as it clearly lays out what is the proper way to read that set of books as suggested by the author.

If on the other hand this is true and the MySQL Reference Manual is in fact a Reference manual, I think that the section on open-files-limit (https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_open-files-limit) should be improved, as it currently makes no mention of mysqld_safe. The section for core-file, for example, does mention this explicitly: https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_core-file

Actually, our Reference Manual is correct. mysqld_safe is unnecessary on systems which use systemd. It very clearly describes what should be done if systemd is used. As the vast majority has upgraded to Linux versions with systemd, I do not find it necessary to add anything to our manual, which is indeed a Reference Manual.

Related documentation bug fixed: https://bugs.mysql.com/bug.php?id=87681

Best,
Agustín.