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

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

ERROR 1040 (HY000): Too many connections

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