You are here

Home

Could not increase number of max_open_files

Sun, 2014-12-28 15:37 — oli
Hello all, on some Linux systems I get the following warning during my MySQL database start-up: 
[Warning] Buffered warning: Could not increase number of max_open_files to more than 1024 (request: 8192)
[Warning] Buffered warning: Changed limits: max_connections: 214 (requested 505)
[Warning] Buffered warning: Changed limits: table_cache: 400 (requested 512)

What does it mean and is that something I should care about?
Taxonomy upgrade extras: 
LimitNOFILE
open_files_limit
table_open_cache
file handles
General MySQL and MariaDB Questions

Hello oli,

every user/process on a UNIX system has some user limits. There are some soft (S) and hard (H) user limits. Soft limits a user can change himself up to the hard limits.

You can find you own user limits as follows:

shell> ulimit -aS | grep 'open'
open files                      (-n) 1024

shell> ulimit -aH | grep 'open'
open files                      (-n) 4096

and the user limits of an already running process as follows:

shell> cat /proc/`pidof mysqld`/limits | egrep 'Limit|open files'
Limit                     Soft Limit           Hard Limit           Units    
Max open files            1024                 4096                 files

Unfortunately all common Linux distributions nowadays have too small Max open files hard limit for database systems (also true for Oracle, PostgreSQL and others).

The number of open files your MySQL database should use you can set with the following variable in your my.cnf:

[mysqld_safe]
open_files_limit               = 8192

Now it looks like MySQL does NOT increase the soft limit to the maximum possible (4096) if it fails to increase the wanted value (8192) but just uses the soft limit (1024).

For database system we advice you to increase your open files user limit to at least 8192 or 16384 file handles. Oracle even recommends to set this value to 64k!

This has to be done as follows:

  • Check if the maximum system wide number of file handles (FD) is sufficiently high and if not increase accordingly: 
    shell> cat /proc/sys/fs/file-max
98939

    the total allocated (1), currently unused (2) and maximum file handles (3, same as file-max) can be shown as follows: 
    shell> cat /proc/sys/fs/file-nr
1154    133     98939

  • To make this value bigger use the following commands: 
    shell> sysctl -w fs.file-max=131072
shell> echo "fs.file-max=131072" >> /etc/sysctl.conf

  • Then add the following values to limits.conf: 
    shell> echo "mysql          soft     nofile         16384
mysql          hard     nofile         16384" >> /etc/security/limits.d/91-mysql.conf

  • On Ubuntu system you have to additionally add the following line: 
    shell> echo "session required pam_limits.so" >> /etc/pam.d/common-session

  • After this logout/login from your UNIX session and restart your MySQL database. Now the new settings should work.

And yes: You should care about this setting because it limits your database and has a direct impact on database performance.

Shinguzcomment Sun, 2014-12-28 18:01