Failure analysis | xtrabackup multi-table backup error "too many open files" - 个人文章

Author: Tang Wei
A member of the DBA team in the Southern District of Aikesheng, mainly responsible for the daily maintenance and troubleshooting of MySQL. Good at operation and maintenance experience and troubleshooting of various databases.
Source of this article: original contribution
*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

1. Background description

When using the xtrabackup tool to back up a mysql instance recently, because the instance has too many ibd files and the value of the open files parameter of the backup user is too small, the number of files opened during the backup instance exceeds the limit of the number of files that the backup user is allowed to open. , causing the backup to fail with the following error:

 220330 08:28:47 >> log scanned up to (328701072168)
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 ./stage/ts_cg_inteltaxtochanges #P#P_20220322.ibd: 'open' returned OS
error 124. Cannot continue operation
InnoDB: Cannot continue operation.

2. Simulate failure scenarios

1. Environmental description

mysql version: v5.7.35

xtrabackup version: v2.4.24

2. View the known parameter values of open files

(1) The current number of open files of the operating system

 [root@test ~]# ulimit -a
core file size (blocks, -c) 0
...
open files (-n) 1024
...

(2) Parameter values of innodb_file_per_table and open_files_limit of the mysql instance configuration file my.cnf

 [root@test ~]# less /opt/mysql/etc/3306/my.cnf |grep innodb_file_per_table
innodb_file_per_table = 1
[root@test ~]# less /opt/mysql/etc/3306/my.cnf |grep open_files_limit
open_files_limit = 20000

(3) Back up the limit of the open files of the configuration file of the user actiontech-mysql in /etc/security/limits.d

 [root@test ~]# cd /etc/security/limits.d
[root@test limits.d]# cat actiontech-mysql.conf
actiontech-mysql soft nofile 10240
actiontech-mysql hard nofile 10240
actiontech-mysql soft nproc 65535
actiontech-mysql hard nproc 65535
[root@test limits.d]# su -s $(which bash) actiontech-mysql
bash-4.2$ ulimit -a
core file size (blocks, -c) 0
...
open files (-n) 10240
...

3. Use sysbench to generate a large number of data tables

 [root@test ~]# /usr/share/sysbench/oltp_read_write.lua --table-size=1000 --tables=12000 --mysql-user=root --mysql-password=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=testdb --threads=250 --time=60 --report-interval=3 --skiptrx=on --mysql-ignore-errors=1062 prepare

4. Simulate the error report of backup under root user and non-root user

(1) Execute the backup command under the non-root user actiontech-mysql to reproduce the error phenomenon

 [root@test ~]# su -s $(which bash) actiontech-mysql
bash-4.2$ xtrabackup --defaults-file=/opt/mysql/etc/3306/my.cnf --user=root --
password=1 --socket=/opt/mysql/data/3306/mysqld.sock --backup --target-dir=/opt/mysql/backup/3306
xtrabackup: recognized server arguments: --server-id=1224415609 --
datadir=/opt/mysql/data/3306 --log_bin=/opt/mysql/log/binlog/3306/mysql-bin --
tmpdir=/opt/mysql/tmp/3306 --
innodb_log_group_home_dir=/opt/mysql/log/redolog/3306 --
innodb_buffer_pool_size=1G --innodb_data_file_path=ibdata1:1G:autoextend --
innodb_file_per_table=1 --innodb_flush_log_at_trx_commit=1 --
innodb_flush_method=O_DIRECT --innodb_io_capacity=1000 --
innodb_log_buffer_size=64M --innodb_log_file_size=2G --
innodb_log_files_in_group=2 --innodb_max_dirty_pages_pct=60 --
innodb_undo_tablespaces=3 --innodb_read_io_threads=8 --innodb_write_io_threads=8
--innodb_open_files=4096 --open_files_limit=20000
...
xtrabackup: open files limit requested 20000, set to 10240
...
220413 13:58:50 >> log scanned up to (6550086912)
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.ht ml
InnoDB: File ./testdb/sbtest4576.ibd: 'open' returned OS error 124. Cannot
continue operation
InnoDB: Cannot continue operation.

(2) Backup directly as root user

Prerequisite: Change the open_files_limit parameter value in the mysql configuration file to a smaller value (20000->10000), and restart the database to take effect

 [root@test ~]# less /opt/mysql/etc/3306/my.cnf |grep open_files_limit
open_files_limit = 10000
[root@test ~]# systemctl start mysqld_3306

Backup under the root user and reproduce the error

 [root@test ~]# xtrabackup --defaults-file=/opt/mysql/etc/3306/my.cnf --user=root
--password=1 --socket=/opt/mysql/data/3306/mysqld.sock --backup --targetdir=/opt/mysql/backup/3306
xtrabackup: recognized server arguments: --server-id=1224415609 --
datadir=/opt/mysql/data/3306 --log_bin=/opt/mysql/log/binlog/3306/mysql-bin --
tmpdir=/opt/mysql/tmp/3306 --
innodb_log_group_home_dir=/opt/mysql/log/redolog/3306 --
innodb_buffer_pool_size=1G --innodb_data_file_path=ibdata1:1G:autoextend --
innodb_file_per_table=1 --innodb_flush_log_at_trx_commit=1 --
innodb_flush_method=O_DIRECT --innodb_io_capacity=1000 --
innodb_log_buffer_size=64M --innodb_log_file_size=2G --
innodb_log_files_in_group=2 --innodb_max_dirty_pages_pct=60 --
innodb_undo_tablespaces=3 --innodb_read_io_threads=8 --innodb_write_io_threads=8
--innodb_open_files=4096 --open_files_limit=10000
...
xtrabackup: open files limit requested 10000, set to 10000
...
220413 14:42:33 >> log scanned up to (6550257170)
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 ./testdb/sbtest10796.ibd: 'open' returned OS error 124. Cannot
continue operation
InnoDB: Cannot continue operation.

The above two cases have successfully reproduced the fault scene.

3. Failure analysis

First, we count how many files need to be opened in the current database

 [root@test ~]# find /opt/mysql/data/3306 -name "*.ibd" |wc -l
12945

According to the above statistical results, we know that the running parameter value of the instance's open_files_limit is 20000, which is greater than the number of files that need to be opened during backup, so why is an error reported?

See the following official instructions, which means that when you perform a backup under a non-root user, the number of open files allowed by the user needs to be more than the number of files opened during the backup, and there is no limit under the root user, so there is no limit. will not have this problem.

Combined with the above official instructions, let's take a look at the actual value of the open files limit during the non-root user backup process.

Check the backup log to confirm the actual value of the open files limit set at that time

 xtrabackup: open files limit requested 20000, set to 10240

View the value of open files actually used by the backup process

 bash-4.2$ ps -ef|grep xtrabackup
actiont+ 19732 19630 0 16:03 pts/1 00:00:00 xtrabackup --defaultsfile=/opt/mysql/etc/3306/my.cnf --user=root --password=x --
socket=/opt/mysql/data/3306/mysqld.sock --backup --targetdir=/opt/mysql/backup/3306
bash-4.2$ cat /proc/19732/limits
Limit Soft Limit Hard Limit Units
Max cpu time unlimited unlimited seconds
...
Max open files 10240 10240 files
..

Obviously, the value of the open files limit request is 20000, and the value in the mysql configuration file is obtained, but the actual set value is 10240, the value of the open files allowed by the backup user, which is less than the number of files that need to be opened currently according to the above statistics. Backup failed.

Let's take a look at the actual value of the open files limit in the process of directly using the root user for backup.

Check the backup log to confirm the actual value of the open files limit set at that time

 xtrabackup: open files limit requested 10000, set to 10000

View the value of open files actually used by the backup process

 [root@test 3306]# ps -ef|grep xtrabackup
root 20826 8965 3 16:18 pts/1 00:00:00 xtrabackup --defaultsfile=/opt/mysql/etc/3306/my.cnf --user=root --password=x --
socket=/opt/mysql/data/3306/mysqld.sock --backup --targetdir=/opt/mysql/backup/3306
[root@test 3306]# cat /proc/20826/limits
Limit Soft Limit Hard Limit Units
Max cpu time unlimited unlimited seconds
...
Max open files 10000 10000 files
...

It can be seen that the value of the open files limit request is 10000, the value in the mysql configuration file is obtained, and the actual set value is also 10000, which has not changed. This shows that under the root user, the open files limit in the mysql configuration file is directly used as the actual limit of the number of open files allowed.

4. Summary

(1) When using xtrabackup to back up under a non-root user, the user's open files value needs to be greater than the number of open files during backup. It is recommended to set the value larger than the open_files_limit parameter value in the configuration file to ensure that the open files of the backup user are enough for the number of files opened during backup. You can modify the value of nofile under /etc/security/limits.d to take effect permanently, or you can directly modify open files under ulimit -n to make it take effect temporarily.

 # 修改用户的soft、hard的nofile值永久生效
* soft nofile 20000
* hard nofile 20000
# 或者登录到用户下ulimit -n临时生效

(2) When using xtrabackup backup under the root user, because the root user does not limit open files by default, it is necessary to ensure that the operating value of ope_files_limit in the configuration file meets the requirements, and the backup will be directly based on the parameters in the configuration file. value to limit the number of files allowed to open. You can also pass a value that meets your needs directly as a command-line option, or set ulimit -n 20000 directly before performing a backup to make the open files take effect temporarily.

 # 1.修改配置文件中的open_files_limit值
[mysqld]
open_files_limit=20000
# 2.执行指定命令行选项传递
--open-files-limit=20000
# 3、在执行备份前设置ulimit值
ulimit -n 20000

(3) Ensure the maximum number of files kept open in the system. If this number is too small, Percona Xtrabackup may monopolize open files on the system, causing other processes to fail when trying to open the file. This can cause the MySQL server to crash, and other processes to fail.

 # 查看系统的file-max
[root@test ~]# cat /proc/sys/fs/file-max
379560
# 修改file-max
[root@test ~]# sysctl -w fs.file-max=5000000
[root@test ~]# echo "fs.file-max=5000000" >> /etc/sysctl.conf

Reference: https://www.percona.com/blog/2016/12/28/using-percona-xtrabackup-mysql-instance-large-number-tables/

Failure analysis | xtrabackup multi-table backup error "too many open files"