0

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
  • Does it matter? table_open_cache is the real limit. Plus a couple of innodb_* settings. (No, I don't know where 90162 could be coming from.) – Rick James Mar 5 '18 at 16:02
0

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:

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
  • 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 other idea? – Samuel Mutel Feb 28 '18 at 13:06
  • I had mentioned that mysqld may autoadjust. Try setting open_files_limit=100000(which is larger that 90162), stop mysql, and start mysql and see. – RolandoMySQLDBA Feb 28 '18 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.