How to set open_files_limit on mariadb

Question

I encountered a strange behavior and I would like to understand better what is happening. I set in my.cnf the parameter open_files_limit = 32000 in the [mysql] section. I restarted MariaDB. I have two processes mysqld_safe and mysql.

Result of ps:

root     10439     1  0 11:27 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe 
--datadir=/var/lib/mysql/data
--pid-file=/var/lib/mysql/data/rcentdb02.pid

mysql    10880 10439  0 11:27 pts/1    00:01:14 /usr/sbin/mysqld --basedir=/usr
--datadir=/var/lib/mysql/data --plugin-dir=/usr/lib64/mysql/plugin --user=mysql
--log-error=/var/lib mysql/data/rcentdb02.err --open-files-limit=32000 
--pid-file=/var/lib/mysql/data/rcentdb02.pid --socket=/var/lib/mysql/mysql.sock
--port=3306

Result of cat /proc/$(pgrep mysqld_safe$)/limits | grep "Max open":

Max open files            32000                32000                files

Result of cat /proc/$(pgrep mysqld$)/limits | grep "Max open":

Max open files            90162                90162                files

My question is: why mysql_safe have the good parameter for open_files_limit but not mysqld process?

Thanks.

---

The result of SHOW GLOBAL VARIABLES LIKE 'open_files_limit' is 90162. I tried to remove the open_files_limit from my.cnf but it does not work.

I have always this value 90162 which seem to be setup nowhere ...

Any idea?

---

I setup my.cnf to have:

[mysqld]
open_files_limit = 32000
[mysqld_safe]
open_files_limit = 32000

I stop mysql and start mysql. Result of SHOW GLOBAL VARIABLES LIKE 'open_files_limit'; => 90162

---

I changed the value of open_files_limit to 100000 and now the result of query SHOW GLOBAL VARIABLES LIKE 'open_files_limit' is 100000. So now my question is : I understand that mysql may autoadjust this parameter but how he can choose a value (90162) higher than the system limit ... ?

With root account:

ulimit -Sn => 1024
ulimit -Hn => 4096

With mysql account (I changed nologin to bash for my test):

ulimit -Sn => 32000
ulimit -Hn => 64000

Answer 1

You may have to login to MariaDB and run

SHOW GLOBAL VARIABLES LIKE 'open_files_limit';

and see if this is 32000 or 90162. mysqld may be capping it.

It is entirely possible that open_files_limit is getting autoadjusted. The Documentation says Default:5000, with possible adjustment and Maximum:platform dependent.

Here is how mysqld_safe and mysqld interact:

• mysqld_safe will pass values to mysqld
• mysqld may override values passed from mysqld_safe

According to MySQL Documentation on mysqld_safe:

Many of the options to mysqld_safe are the same as the options to mysqld. See Section 5.1.4, “Server Command Options”.

Options unknown to mysqld_safe are passed to mysqld if they are specified on the command line, but ignored if they are specified in the [mysqld_safe] group of an option file. See Section 4.2.6, “Using Option Files”.

What you may need to do is remove open_files_limit from my.cnf and let mysqld_safe assign the value it knows to mysqld.

Then, restart mysqld.

UPDATE 2018-02-28 08:24 EST

You may need to add [mysqld_safe] header in my.cnf and put open_files_limit=32000, like this:

[mysqld_safe]
open_files_limit=32000

However, don't restart mysqld. This may not properly terminate mysqld_safe because it contains an indefinite loop to check the return code of mysqld when it terminates. There is a certain return value that lets mysqld_safe know to relaunch mysqld. This means that mysqld_safe does not terminate when it handle a mysqld restart.

It is better to do the restart in two steps

service mysql stop && service mysql start

Doing service mysql stop will terminate mysqld and then exit the indefinite loop in mysqld_safe, terminating mysqld_safe. Then, launching service mysql start will launch a fresh mysqld_safe process. Give it a try and let us know.

If you cannot edit my.cnf, then specify the option on the command-line:

service mysql stop && service mysql start --open-files-limit=32000