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
Best Answer
You may have to login to MariaDB and run
and see if this is
32000
or90162
. mysqld may be capping it.It is entirely possible that open_files_limit is getting autoadjusted. The Documentation says
Default:5000, with possible adjustment
andMaximum:platform dependent
.Here is how
mysqld_safe
andmysqld
interact:mysqld_safe
will pass values tomysqld
mysqld
may override values passed frommysqld_safe
According to MySQL Documentation on
mysqld_safe
:What you may need to do is remove
open_files_limit
from my.cnf and letmysqld_safe
assign the value it knows tomysqld
.Then, restart
mysqld
.UPDATE 2018-02-28 08:24 EST
You may need to add
[mysqld_safe]
header inmy.cnf
and putopen_files_limit=32000
, like this:However, don't restart mysqld. This may not properly terminate
mysqld_safe
because it contains an indefinite loop to check the return code ofmysqld
when it terminates. There is a certain return value that letsmysqld_safe
know to relaunchmysqld
. This means that mysqld_safe does not terminate when it handle amysqld
restart.It is better to do the restart in two steps
Doing
service mysql stop
will terminatemysqld
and then exit the indefinite loop inmysqld_safe
, terminatingmysqld_safe
. Then, launchingservice mysql start
will launch a freshmysqld_safe
process. Give it a try and let us know.If you cannot edit
my.cnf
, then specify the option on the command-line: