Mysql Innodb Error Number 24 Means 'too Many Open Files'?

All we need is an easy explanation of the problem, so here it is.

I was in the middle of restoring my database using mysqldump and it errored out this

ERROR 1030 (HY000) at line 637885: Got error 168 from storage engine

And when i check the logs, i am seeing this :-

2021-06-20T02:43:28.598142Z 113 [ERROR] InnoDB: Operating system error number 24 in a file operation.
2021-06-20T02:43:28.604918Z 113 [ERROR] InnoDB: Error number 24 means 'Too many open files'
2021-06-20T02:43:28.604935Z 113 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2021-06-20T02:43:28.604950Z 113 [ERROR] InnoDB: File ./[email protected]/FTS_0000000000029783_0000000000042996_INDEX_1.ibd: 'create' returned OS error 124.
2021-06-20T02:43:28.604956Z 113 [ERROR] InnoDB: Operating system error number 24 in a file operation.
2021-06-20T02:43:28.604960Z 113 [ERROR] InnoDB: Error number 24 means 'Too many open files'
2021-06-20T02:43:28.604964Z 113 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html

This is what my config file currently looks like :-

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
bind-address = 0.0.0.0
#
# * Fine Tuning
#
key_buffer_size     = 32M
max_allowed_packet  = 64M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
max_connections        = 2000
innodb_open_files = 100000
sql_mode= 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
table_open_cache       = 4000
#thread_concurrency     = 10
explicit_defaults_for_timestamp = ON
optimizer_trace_max_mem_size = 1M
back_log = 5000
max_error_count = 1024
event_scheduler = ON

#
# * Performance Schema 
#
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
performance-schema-instrument='memory/%=COUNTED'
performance-schema-consumer-events-transactions-current = ON
performance-schema-consumer-events-transactions-history = ON
performance-schema-instrument='transaction%=ON'
query_cache_limit   = 1M
query_cache_size        = 16M

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
server-id      = 1
log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 5
max_binlog_size   = 100M
master-info-repository = TABLE
relay-log-info-repository = TABLE

#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
innodb_buffer_pool_size = 8G
innodb-log-file-size = 512M
innodb_flush_neighbors = 1
innodb_max_dirty_pages_pct_lwm = 10
innodb_max_dirty_pages_pct = 90
slave_skip_errors=1062,1032,1050,1236,1813,1146,1305,1396,1217

Can someone please point out how should i fix the above error? Thank you.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

From the looks of the logs, it appears you are using some form of Linux. If it’s Ubuntu (or one of the derivatives), you may need to confirm that the OS is set up to handle the number of open files that you have specified in innodb_open_files. Rarely will a system be set to handle 100,000 out of the box as that requires a large amount of RAM to be present.

You can check what the server is capable of with the following command (via Terminal or an SSH connection):

ulimit -n

The number may be somewhere around 5,000~10,000. If you need to increase this value, you can set a new limit with:

ulimit -n 50,000

Be advised that setting this number too high can have some unfortunate consequences and result in a system memory exhaustion within seconds. Increase in increments of 2,500 or so. In the event the server has RAM measured in the 100s of GB, you may be able to test larger increases.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply