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.
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):
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 🙂