How to permanently raise ulimit 'open files' and MySQL 'open_files_limit'

The default ulimit (maximum) open files limit is: 1024--Which is very low, especially for a web server environment hosting multiple heavy database driven sites.

This ulimit 'open files' setting is also used by MySQL. MySQL automatically sets its open_files_limit to whatever the system's ulimit is set to--at default will be 1024.

NOTE: MySQL can NOT set it's open_files_limit to anything higher than what is specified under ulimit 'open files'--you can set it lower, but not above the 'open files' limit.

Anyhow...

This is how we raise the ulimit

To check the limits:


ulimit -a


You will see something like:


core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 257403
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 257403
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited


Or if you just want to see the "open files" limit under the user you're currently logged in under then do:


ulimit -n


Or to see the soft and hard open files limits individually:


ulimit -Hn
ulimit -Sn


Or if you want to check the limit for a particular user, e.g. mysql, then do:


su mysql ulimit -a


Now... to permanently set the 'open files' limit:

To set the ceiling of the limits available (the max a soft or hard limit can be set by each user).

NOTE: this is set by a PAM during authentication, and NOT at boot. (see below for work around)


vi /etc/security/limits.conf


Add the following to bottom of file to set for everything *:


* soft nofile 1024000
* hard nofile 1024000
* soft nproc 10240
* hard nproc 10240


To set only a specific user, like mysql then put in:


mysql hard nofile 1024000
mysql soft nofile 1024000


Repeat for /etc/security/limits.d/90-nproc.conf


vi /etc/security/limits.d/90-nproc.conf


Add the following:


* soft nofile 1024000
* hard nofile 1024000
* soft nproc 10240
* hard nproc 10240
root soft nproc unlimited


I selected '1024000', which is fairly high; you can surely set this lower to something like '102400'

To temporarily set the open files limit for the user you are currently logged in under (e.g. 'root')


ulimit -Hn 1024000


Now we set open_files_limit in my.cnf (MySQL)-- so the values are loaded at boot time (after rebooting server)

Anyway... on to it:


vi /etc/my.cnf


Insert the following under your [mysqld]:


[mysqld]
open_files_limit = 1024000


Alternatively you can also just set the value under [mysqld_safe], and this will also set the value at boot time:


[mysqld_safe]
open_files_limit = 1024000


Now restart mysqld:


service mysqld restart


Now let's check the at mysql is using the new values:


mysql -u root -p


Once in mysql prompt enter the following:


show global variables like 'open%';


You should see the following:


+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| open_files_limit | 1024000 |
+------------------+--------+
1 row in set (0.00 sec)


Cool! It works.

Next, you should reboot your server and repeat the mysql steps to make sure they are being loaded at bootup.


mysql -u root -p
show global variables like 'open%';


If, after boot, it shows the system default of 1024, that means mysqld didn't apply the settings at boot time. Please continue below to the troubleshooting section.

Troubleshooting and Notes

mysqld process should automatically use/apply open_files_limit in relation to whatever is applied under ulimit or /etc/security/limits.conf (see above).

Meaning if you have the following, in your /etc/security/limits.conf:


* hard nofile 102400
* soft nofile 102400


Or you set the limit temporarily via:


ulimit -Hn 102400


Your MySQL server should automatically use the value under open_files_limit when it restarts as long as they are equal or less than open files limit.

However, from my finding at boot time (after reboot)--under one of my servers (could be different for others)--the /etc/security/limits.conf values are loaded AFTER mysqld starts at boot. Meaning, mysqld doesn't get the updated values, and uses the default 1024 instead.

/etc/security/limits.conf is loaded at PAM authentication and not at boot time.

One fix: simply restart mysqld after the system boots up.


service mysqld restart


Another fix is to set these values at boot time before everything else (permanent):


vi /etc/init.d/mysqld


Add the following:


ulimit -S -n ${DAEMON_FILES_LIMIT:-102400} >/dev/null 2>&1


Or, as noted above (if you skipped directly to the troubleshooting section) add this to your /etc/my.cnf


[myqld_safe]
open_files_limit = 102400


or


[myqld]
open_files_limit = 102400