Error number 24 means too many open files

Исправляем ошибку нехватки файловых дескрипторов при выполнении резервного копирования с помощью Percona XtraBackup

При использовании Percona Xtrabackup 2.4.5 и младше необходимо иметь достаточную возможность по количеству открытых файлов в системе, чтобы открыть каждое табличное пространство InnoDB в инстансе для резервного копирования. Если вы используете innodb_file_per_table = 1 и имеете большое количество таблиц, то скорее всего получите ошибку следующего вида:

InnoDB: Operating system error number 24 in a file operation.
InnoDB: Error number 24 means 'Too many open files'
InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
InnoDB: File ./perconatst/perconatst154734.ibd: 'open' returned OS error 124. Cannot continue operation
InnoDB: Cannot continue operation.

Уточним какое количество файлов нам необходимо.

4753866

Проверим текущее максимальное значение открытых файлов

791540

Как видно из вывода, то текущих значений недостаточно для открытия всего табличного пространства, поэтому необходимо увеличить данный параметр.

Для того, чтобы временно изменить количество одновременно открытых файлов (до перезагрузки), выполняем команду:

Для установки значения на постоянную основу добавим параметр в конфигурационный файл ядра /etc/sysctl.conf и применим изменения “на лету”.

sysctl -p /etc/sysctl.conf

Проверим текущее количество открытых файлов:

1048576

Увеличим ограничение ресурсов для сервиса MySQL, для этого добавим в конец файла /etc/security/limits.conf следующие строки:

mysql hard nofile 2000000
mysql soft nofile 2000000

Одновременно с этим добавим параметр в конфигурационный файл ядра /etc/sysctl.conf и применим изменения “на лету”:

Альтернативные способы

Можно увеличить параметр в конфигурационном файле MySQL, но потребуется перезагрузка сервиса:

[xtrabackup]
open-files-limit=2000000

Перед выполнением резервной копии выполнять команду ulimit -n 2000000 или добавить в сценарий запуска:

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 ./minerva@002dv085/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.

I’m trying to backup using Percona XtraBackup and getting the following error:

[root@ads-dev1 ~]# innobackupex /root/db

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

140722 11:52:16  innobackupex: Starting mysql with options:  --unbuffered --
140722 11:52:16  innobackupex: Connected to database with mysql child process (pid=15930)
140722 11:52:22  innobackupex: Connection to database server closed
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

innobackupex: Using mysql  Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (x86_64) using readline 5.1
innobackupex: Using mysql server version Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

innobackupex: Created backup directory /root/db/2014-07-22_11-52-22
140722 11:52:22  innobackupex: Starting mysql with options:  --unbuffered --
140722 11:52:22  innobackupex: Connected to database with mysql child process (pid=15953)
140722 11:52:24  innobackupex: Connection to database server closed

140722 11:52:24  innobackupex: Starting ibbackup with command: xtrabackup_51  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/root/db/2014-07-22_11-52-22 --tmpdir=/tmp
innobackupex: Waiting for ibbackup (pid=15959) to suspend
innobackupex: Suspend file '/root/db/2014-07-22_11-52-22/xtrabackup_suspended'

xtrabackup_51 version 2.0.8 for MySQL server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 587)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: Target instance is assumed as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 536870912
xtrabackup: using O_DIRECT
>> log scanned up to (94 3209639289)
140722 11:52:25  InnoDB: Operating system error number 24 in a file operation.
InnoDB: Error number 24 means 'Too many open files'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/operating-system-error-codes.html
InnoDB: Error: could not open single-table tablespace file
InnoDB: ./roei_cc/transport.ibd!
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.
innobackupex: Error: ibbackup child process has died at /usr/bin/innobackupex line 386.
[root@ads-dev1 ~]# 

I’ve checked permissions on the files but couldn’t find a problem, I also tried running the command using mysql user and got the same error and even ran the next command:

# ulimit -n 4096 ; innobackupex /root/db

But got the exact same error, any ideas?

Wondering how to resolve ‘Mysql Errcode: 24 – Too many open files’? We can help you.

As part of our Server Management Services, we assist our customers with several MySQL queries.

Today, let us see how to fix this error in Plesk.

Mysql Errcode: 24 – Too many open files

Typically, error might look as shown below:

Error: SQLSTATE[HY000]: General error: 23 Out of resources when opening file ‘/tmp/#***.MYD’ (Errcode: 24 – Too many open files)

[ERROR] /usr/sbin/mysqld: Can’t open file: ‘./usr_web587_1/etqvi_languages.frm’ (errno: 24 – Too many open files)

Failed to process database ‘wordpress_f’ from domain ‘example.com’: MySQL error 1018: Can’t read dir of ‘./wordpress_f/’ (errno: 24 “Too many open files”) executing query: SHOW TABLE STATUS FROM `wordpress_f`

[ERROR] Error in accept: Too many open files

DB query failed: SQLSTATE[HY000]: General error: 1016 Can’t open file: ‘./psa/misc.frm’ (errno: 24), query was: select param, val from misc

You can see this  error in the mysql error log while performing backups.

• An error message related to MySQL service appears in one of these log files:

/var/log/plesk/panel.log
/var/log/mysql/error.log
/var/log/mariadb/mariadb.log
/var/log/syslog

• Disk usage statistics in Plesk is not calculated with one of the errors above.
• MySQL service goes down periodically.
• Plesk fails to load or operations with MySQL databases (for example backup) fail with one of the following error messages:

ERROR: PleskDBException: Unable to connect to database: mysql_connect(): MySQL server has gone away (Error code: 2006) (Abstract.php:69)

DB query failed: SQLSTATE[HY000]: General error: 23 Out of resources when opening file ‘/tmp/#sql_2ff_0.MAI’ (Errcode: 24 “Too many open files”), query was: DESCRIBE `sessions`

• A Plesk backup is create with the following warning:

WARNING : (mysql object ‘johndoe_opencart_e’) Not all the data was backed up into /var/lib/psa/dumps/clients/john_doe/domains/example.com/databases/johndoe_opencart_e_1 successfully. mysqldump: Couldn’t execute ‘show table status like ‘address”: Can’t read dir of ‘./john_doe_opencart_e/’ (errno: 24 – Too many open files) (1018)

When MySQL fails to open required files, the process gets hanged and systemd fails to stop MySQL process.

How to resolve?

Let us how our Support Techs found solution for this error in Plesk and Directadmin servers.

Mysql Errcode: 24 – Too many open files in Plesk.

With MariaDB installed

• Firstly, connect to the server via SSH.
• Modify MariaDB startup script:

# systemctl edit mariadb

• Set open files limit for the MariaDB service:

[Service]
LimitNOFILE=8192

• Restart the MariaDB service:

# systemctl restart mariadb

• Verify the changes:

# egrep “open files” /proc/$(cat `plesk db -Ne “show variables like ‘pid_file'” | awk ‘{print $2}’`)/limits
Max open files 8192 8192 files

With MySQL installed

• Firstly, connect to the server via SSH.
• Modify MySQL startup script:

# systemctl edit mysql

• Set open files limit for the MySQL service:

[Service]
LimitNOFILE=8192

• Restart the MySQL service:

# systemctl restart mysql

• Verify the changes:

# egrep “open files” /proc/$(cat `plesk db -Ne “show variables like ‘pid_file'” | awk ‘{print $2}’`)/limits
Max open files 8192 8192 files

Mysql Errcode: 24 – Too many open files in Directadmin

Some of solutions provided by our Support Techs in Directadmin.

1.Edit /etc/init.d/mysqld and add this to the top, after #!/bin/sh

ulimit -HSn 1024
ulimit -HSn 32768
ulimit -HSn 1024000

Which should force it to try and set it as high as it can go. If one of the value is too high, delete that line, or lower the value.

2.Check /etc/my.cnf. Under the [mysqld] section, if you have a open_files_limit variable, increase it, eg:

open_files_limit=32768

Then restart mysqld.

3.Confirm in mysql itself by checking the open_files_limit variable to ensure it’s increased, by running the SQL query:

Show global variables like ‘open%’;

[Finding it hard to fix? We’d be happy to assist you]

Conclusion

In short, today we saw how our Support Techs resolved MySQL error in Plesk and DirectAdmin.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

При использовании большого количества баз и/или таблиц в MySQL или MariaDB может вылазить ошибка:

 Can’t open file: ‘./mysql/user.frm’ (errno: 24)

или

[ERROR] /usr/sbin/mysqld: Can’t open file: ‘./database/table.frm’ (errno: 24 — Too many open files)

errno: 24 — Too many open files дословно переводится, как открыто слишком много файлов.

Обычно проблема кроется в ограничении количества одновременно открытых файлов сервером. Решение данной проблемы простое. Состоит из нескольких шагов.

1. В консоли сервера вбиваем: lsof -u mysql | wc -l

В результате мы узнаем сколько сейчас mysql открыла файлов.

2. Увеличиваем количество одновременно открытых файлов. Для этого идем /etc/my.cnf. В секции [mysqld] ищем параметр open_files_limit и увеличиваем его. Если такого параметра нет, то создаем его. Например, open_files_limit = 20000 . Значение параметра должно быть больше значения из пункта 1.

Далее выполняем перезагрузку базы данных MySQL или MariaDB. Из консоли из PhpMyAdmin заходим в базу и вводим запрос:

SHOW VARIABLES LIKE ‘open_files_limit’;

В большинстве случаев будет написано:

Variable_name       Value

open_files_limit    1024

Кому-то будет достаточно этих шагов. А кому-то придется выполнить остальные шаги.

3. Увеличим количество файловых дескрипторов пользователю с правами mysql, которые разрешено открывать. Обычно это пользователь с именем mysql. Идем /etc/security/limits.d/mysql.conf

Пишем в открытом файле или редактируем: mysql  —  nofile  20000

Цифра 20000 — это количество файлов, которое можно будет открывать пользователю с правами mysql.

4. Идем в каталог /etc/systemd/system/multi-user.target.wants/ . Редактируем файл mysql.service.

В файле ищем секцию [Service]. В нее дописываем или редактируем параметр LimitNOFILE=20000, 5. Далее перезапускаем демона в командной строке: systemctl daemon-reload .

6. Перезапускаем MySQL: systemctl restart mysql

7. Радуемся результату.

Ошибка mysql или mariaDB — Can’t open file (errno: 24 — Too many open files), 5.0 out of 5 based on 1 rating