Исправляем ошибку нехватки файловых дескрипторов при выполнении резервного копирования с помощью 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 am very frequently getting this error in MySQL:
OS errno 24 — Too many open files
What’s the cause and what are the solutions?
BenMorel
33.5k49 gold badges174 silver badges310 bronze badges
asked May 16, 2017 at 16:34
2
I was getting the errno: 24 - Too many open files
too often when i was using many databases at the same time.
Solution
- ensure that the connections to db server close propertly
-
edit /etc/systemd/system.conf. Uncomment and make
DefaultLimitNOFILE=infinity DefaultLimitMEMLOCK=infinity
then run
systemctl daemon-reload
andservice mysql restart
.
You can check the results with the query: SHOW GLOBAL VARIABLES LIKE 'open_files_limit'
and you may notice that the value has changed. You should not have any errno 24 now.
Please notice that the solution may differ from other OS/versions. You can try to locate the variables first.Tested with Ubuntu 16.04.3 and mysql 5.7.19.
In my case it was useless to setting up the open_files_limit
variable in mysql configuration files as the variable is flagged as a readonly.
I hope it helped!
answered Aug 17, 2017 at 20:13
2
You probably have a connection
leak in your application, that is why open connections
are not closed once the function
completes it’s execution.
I would probably look into the application code and see where the connections
/preparedstatement
(if it’s java) objects are not closed and fix it.
A quick workaround is to increase ulimit
of the server (explained here) which would increase number of open file descriptors (i.e. connections
). However, if you have a connection leak, you will encounter this error again, at later stages.
answered May 16, 2017 at 16:40
Darshan MehtaDarshan Mehta
29.7k9 gold badges67 silver badges98 bronze badges
2
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.
Ошибка error number 24 вызвана типичной проблемой, когда на процесс пользователя наложено ограничение по лимиту открытых файлов. Для её решения нужно глубокое понимание, почему такое происходит. Я же опишу решение с небольшими комментариями, по теории предоставлю ссылки.
Итак, хотим настроить репликацию (или сделать бэкап) базы MySQL на движке InnoDB (это важно) через утилиту xtrabackup, и на самом первом шаге получаем выхлоп ошибок:
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 ./sbtest/sbtest132841.ibd: 'open' returned OS error 124. Cannot continue operation
InnoDB: Cannot continue operation.
Проверим, сколько же у нас файлов в базе:
find /var/lib/mysql/ -name "*.ibd" | wc -l
17365
Казалось бы, не так уж и много. И правда.
А теперь проверим количество открытых файлов в системе:
cat /proc/sys/fs/file-max
3262006
Число выглядит намного больше, чем количество файлов в нашей базе…
А теперь вызовем команду, которая покажет наши лимиты для пользователя:
ulimit -n
1024
Или другим способом:
cat /proc/$(pgrep mysqld)/limits
[root(0)uniform-db-p-1 ~]# cat /proc/6072/limits
Limit Soft Limit Hard Limit Units
Max cpu time unlimited unlimited seconds
Max file size unlimited unlimited bytes
Max data size unlimited unlimited bytes
Max stack size 10485760 unlimited bytes
Max core file size 0 unlimited bytes
Max resident set unlimited unlimited bytes
Max processes 96024 96024 processes
Max open files 1024 4096 files
Max locked memory 65536 65536 bytes
Max address space unlimited unlimited bytes
Max file locks unlimited unlimited locks
Max pending signals 96024 96024 signals
Max msgqueue size 819200 819200 bytes
Max nice priority 0 0
Max realtime priority 0 0
Max realtime timeout unlimited unlimited us
Вот где собака зарыта! Нужно всего лишь изменить значение, я сделал это налету:
ulimit -n 30000
Но это на один сеанс до ребута, для постоянных значений нужно править файл:
/etc/security/limits.conf:
username hard nofile 2000000
username soft nofile 1900000
Обратите внимание, софт лимит должен быть меньше.
Полезные ссылки для детального изучения причин такой ошибки:
- https://www.percona.com/blog/2016/12/28/using-percona-xtrabackup-mysql-instance-large-number-tables/
- https://www.percona.com/blog/2017/10/12/open_files_limit-mystery/
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.
PREVENT YOUR SERVER FROM CRASHING!
Never again lose customers to poor server speed! Let us help you.
Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.
GET STARTED
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. Радуемся результату.
Rating: 5.0/5 (1 vote cast)
Ошибка mysql или mariaDB — Can’t open file (errno: 24 — Too many open files), 5.0 out of 5 based on 1 rating