errno: 24 - Too many open files

errno: 24 - Too many open files

errno: 24 - Too many open files

You might see this error inside MySQL's error log file or during mysql_upgrade operation.

Can't open file: ‘./db/table.frm' (errno: 24 - Too many open files)

To solve this issue, you will need to change the open files limit at system and db service level.


--> To check and change this limit at the system level, we use ulimit client tool to set it dynamically.

* Check current system open file limit.
root@test:~# ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 515256
max locked memory       (kbytes, -l) 64
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) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 515256
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited
root@test:~# ulimit -n
1024
root@test:~# ulimit -Hn
4096
root@test:~# ulimit -Sn
1024

* Change system open file limit on the fly(dynamically).
root@test:~# ulimit -n 65535

root@test:~# ulimit -Hn
65535
root@test:~# ulimit -Sn
65535
root@test:~# ulimit -n
65535
root@test:~# ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 515256
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 65535
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 515256
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited 


--> To make the permanent changes of open files limit at the system level, Please update below file with below 2 lines for mysql user. So once the server will be rebooted, our changes will be persistent. 

vim /etc/security/limits.conf
*
*
mysql hard nofile 65535
mysql soft nofile 65535



--> On most OS like CentOS, Ubuntu, RHEL: Locate your mysqld.service file and change the LimitNOFILE value which is present under [Service] section to 65535. 

vim /usr/lib/systemd/system/mysqld.service
*
*
*
# Sets open_files_limit
LimitNOFILE = 65535
service mysql restart




Photo by Patrick Tomasso on Unsplash



Comments

  1. Don't modify the /usr/lib/systemd files, they'll just get overritten when that service is next updated. You need to use overrides or "systemctl edit" to append the systemd service file locally. -Ex

    ReplyDelete

Post a Comment

Popular

SLEEP thread causing "Waiting for table metadata lock"

MySQL Memory Calculator

How to properly shutdown MySQL before any maintenance activity

How to create CentOS VM using Vagrant

ERROR 1040 (HY000): Too many connections

set --server-id to enable either a master or a slave

GTID Replication (Skip Transaction using empty transaction)

Master has purged binary logs containing GTIDs that the slave requires

How to Prepare for MySQL Certification Exam