数据库软限制相关研究-拓冰建站

某服务器在执行全量备份期间，MySQL 错误日志中出现如下报错：

[ERROR] [MY-012592] [InnoDB] Operating system error number 24 in a file operation.
[ERROR] [MY-012596] [InnoDB] Error number 24 means ‘Too many open files’
[Note] [MY-012597] [InnoDB] Refer to your operating system documentation for operating system error code information.
OS error 24（EMFILE）表示进程打开的文件描述符数量已达到操作系统限制。在备份过程中，InnoDB 需要同时访问大量 .ibd 数据文件，导致瞬时文件描述符需求大幅增加，从而触发该限制。

此次报错反映出数据库实例及相关进程在操作系统资源软限制（soft limit）和 MySQL 文件描述符参数两个层面存在配置不当的问题，需要对相关参数进行统一梳理与优化。同时也需要对数据库实例的相关资源使用进行系统性的研究。

文章后续示例使用linux系统版本为AlmaLinux 9.2，mysql以及mysql源码选择的是8.0.44版本。

【2. 文件句柄数】
文件句柄（又称文件描述符）是操作系统内核分配给进程的整数编号，用于标识已打开的资源。Linux 系统采用"一切皆文件"的设计理念，不仅普通文件会占用文件句柄，网络连接、管道、设备等资源也会占用。

对于 MySQL 而言，以下每个资源均会消耗文件句柄：

每个 .ibd 数据文件（innodb_file_per_table=ON 时每表一个）
binlog、redo log、undo log 等日志文件
每个客户端连接对应的 socket
临时文件
【2.1 系统级别限制】
/proc/sys/fs/file-max

这是整个操作系统所有进程能打开的文件描述符总量上限，由内核管理：

[@**** ~]$ cat /proc/sys/fs/file-max
9223372036854775807
/proc/sys/fs/file-nr

记录文件句柄使用情况的只读文件，包含三个数值：已分配句柄数、未使用句柄数（通常为 0）、系统最大句柄数

[@***** fs]$ cat /proc/sys/fs/file-nr
2081 32 9223372036854775807
【2.2 进程级别限制】
/etc/security/limits.conf

对所有用户的设置，在/etc/security/limits.conf文件，其是可以对系统用户、组进行cpu、文件数等限制的，通过它可以针对某个用户或全部进行限制。但不能超越系统的限制，以下是一个典型的配置：

/etc/security/limits.conf

#This file sets the resource limits for the users logged in via PAM.
#It does not affect resource limits of the system services.

#Also note that configuration files in /etc/security/limits.d directory,
#which are read in alphabetical order, override the settings in this
#file in case the domain is the same or more specific.
#That means for example that setting a limit for wildcard domain here
#can be overriden with a wildcard setting in a config file in the
#subdirectory, but a user specific setting here can be overriden only
#with a user specific setting in the subdirectory.

#Each line describes a limit for a user in the form:

#Where:
# can be:

- a user name

- a group name, with @group syntax

- the wildcard *, for default entry

- the wildcard %, can be also used with %group syntax,

for maxlogin limit

# can have the two values:

- “soft” for enforcing the soft limits

- “hard” for enforcing hard limits

# can be one of the following:

- core - limits the core file size (KB)

- data - max data size (KB)

- fsize - maximum filesize (KB)

- memlock - max locked-in-memory address space (KB)

- nofile - max number of open file descriptors

- rss - max resident set size (KB)

- stack - max stack size (KB)

- cpu - max CPU time (MIN)

- nproc - max number of processes

- as - address space limit (KB)

- maxlogins - max number of logins for this user

- maxsyslogins - max number of logins on the system

- priority - the priority to run user process with

- locks - max number of file locks the user can hold

- sigpending - max number of pending signals

- msgqueue - max memory used by POSIX message queues (bytes)

- nice - max nice priority allowed to raise to values: [-20, 19]

- rtprio - max realtime priority

#* soft core 0
#* hard rss 10000
#@student hard nproc 20
#@faculty soft nproc 20
#@faculty hard nproc 50
#ftp hard nproc 0
#@student - maxlogins 4

End of file

- nofile 65536
- nproc 65536
- sigpending 65536
  root soft nofile unlimited
  root soft nproc unlimited
  /etc/security/limits.conf 用于设置通过 PAM 登录（如 SSH）的用户的资源限制，不影响系统服务的资源限制。系统不仅会读取该文件，还会读取 /etc/security/limits.d/ 目录下的所有 .conf 文件，按文件名字母顺序依次读取，后读的配置会覆盖先前的。

但若 limits.conf 中为具体用户配置了限制：

mysql soft nofile 65536
则 limits.d/ 目录中的通配符配置无法覆盖该设置。只有在 limits.d/ 中也为该用户配置相同项，才能将其覆盖。

如下代码块中 20-nproc.conf 文件中 nofile 的参数就是进程真正的文件句柄数的限制

[@*** limits.d]$ cd /etc/security/limits.d/
[@*** limits.d]$ ll
total 4
-rw-r–r-- 1 root root 250 Oct 22 2024 20-nproc.conf
[@*** limits.d]$ cat 20-nproc.conf

Default limit for number of user’s processes to prevent

accidental fork bombs.

See rhbz #432903 for reasoning.

- nofile 65536
- nproc 65536
- sigpending 65536
  nproc
  root soft nofile unlimited
  root soft nproc unlimited
  【2.3 mysql级别限制】
  当前所有部署的mysql服务由systemd托管。

mysqld的systemd的配置文件在/usr/lib/systemd/system/mysqld.service，也可以通过systemctl show system-mysqld查看

[@** system]$ cat /usr/lib/systemd/system/mysqld.service

Copyright © 2015, 2025, Oracle and/or its affiliates.

This program is free software; you can redistribute it and/or modify

it under the terms of the GNU General Public License, version 2.0,

as published by the Free Software Foundation.

This program is designed to work with certain software (including

but not limited to OpenSSL) that is licensed under separate terms,

as designated in a particular file or component or in included license

documentation. The authors of MySQL hereby grant you an additional

permission to link the program and your derivative works with the

separately licensed software that they have either included with

the program or referenced in the documentation.

This program is distributed in the hope that it will be useful,

but WITHOUT ANY WARRANTY; without even the implied warranty of

MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

GNU General Public License, version 2.0, for more details.

You should have received a copy of the GNU General Public License

along with this program; if not, write to the Free Software

Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA

systemd service file for MySQL forking server

[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network-online.target
Wants=network-online.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql

Type=forking
PIDFile=/var/run/mysqld/mysqld.pid

Disable service start and stop timeout logic of systemd for mysqld service.

TimeoutSec=0

Execute pre and post scripts as root

hence, + prefix is used

Needed to create system tables

ExecStartPre=+/usr/bin/mysqld_pre_systemd

Start main service

ExecStart=/usr/bin/numactl --interleave=all /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS

Use this to switch malloc implementation

EnvironmentFile=-/etc/sysconfig/mysql

Sets open_files_limit

LimitNOFILE=65535

Restart=on-failure

RestartPreventExitStatus=1

Set enviroment variable MYSQLD_PARENT_PID. This is required for restart.

Environment=MYSQLD_PARENT_PID=1

PrivateTmp=false
StartLimitInterval=120
StartLimitBurst=30
mysql的参数里和LimitNOFILE直接对应的是open_files_limit

【2.4 文件描述符总结】
系统级 (fs.file-max)
└── 用户进程级 (limits.conf)
└── systemd (system-mysqld)
└── MySQL (open_files_limit)
【2.5 fd运维操作】
【2.5.1 修改操作系统fd限制】

临时修改（重启后失效）

sysctl -w fs.file-max=1000000

永久修改

echo “fs.file-max = 1000000” >> /etc/sysctl.conf
sysctl -p

验证是否生效

cat /proc/sys/fs/file-max
【2.5.2 修改进程级fd限制】

查看当前进程级限制

ulimit -n # soft limit
ulimit -Hn # hard limit

临时修改（当前会话生效）

ulimit -n 65536

永久修改，编辑 /etc/security/limits.conf

vi /etc/security/limits.conf

添加以下内容（针对所有用户）

```
soft nofile 65536
```
```
hard nofile 65536
```

针对 mysql 用户单独设置（推荐）

mysql soft nofile 65536
mysql hard nofile 65536

修改后重新登录生效，验证

ulimit -n
【2.5.3 修改systemd fd限制】

查看 mysqld service 当前 fd 限制

systemctl show mysqld | grep LimitNOFILE

使用 override 文件（修改）

sudo mkdir -p /etc/systemd/system/system-mysqld.d/
sudo touch /etc/systemd/system/mysqld.service.d/override.conf
echo -e “[Service] LimitNOFILE = 65536” | sudo tee /etc/systemd/system/system-mysqld.slice.d/override.conf

重载配置

sudo systemctl daemon-reload

验证是否生效（查看 mysqld 进程实际 fd 限制）

cat /proc/$(pidof mysqld)/limits | grep “open files”
【2.5.4 linux查看进程文件句柄使用数量】
#查看特定进程的文件句柄
ls /proc//fd | wc -l
#实时监控进程的文件句柄
watch -n 1 “ls /proc//fd | wc -l”
【3. 打开表】
【3.1 mysql表打开机制】
mysql官方文档

在 MySQL 中，"打开表"不是一个抽象概念，而是具有明确系统行为的操作。当 MySQL 执行涉及某张表的 SQL 时，在实际访问数据前，必须先完成对该表的打开操作——即将表的元信息、文件句柄等资源加载到内存中。

MySQL 采用多线程架构，因此多个客户端可以同时对同一张表发送查询。每个 session 各自维护独立的表句柄，而不是所有连接共享一个表句柄。

下图是一条sql打开表的完整的链路
image
在以下情况下，MySQL 会关闭未使用的表并将其从表缓存中删除：

当缓存已满，线程尝试打开缓存中不存在的表时。
当缓存包含的条目超过 table_open_cache一定数量，并且缓存中的某个表不再被任何线程使用时。
当发生表刷新操作时。这发生在有人发出flush table等命令时。
【3.2 Table Cache】
【3.2.1 tablecache基本介绍】
Table Cache 是 MySQL 在内存中维护的专用缓冲区，用于存储已打开表的文件句柄和相关状态信息。其核心作用是用内存换 I/O：将已打开的表句柄保留在内存中，下次访问同一张表时直接复用，避免重复的文件打开操作。

table_open_cache是 Table Cache 的核心参数，表示 Table Cache 中最多可以同时缓存的表句柄数。其所需值与 max_connections 直接相关，假设数据库有 N 张表，集群支持 M 个并发连接，则 table_open_cache 的理论最大值为 N × M。

在高并发场景下，所有线程共享同一个 Table Cache 会产生严重的锁竞争——每次访问 Cache 都需要加锁。MySQL 5.6.6 引入了 table_open_cache_instances 参数来解决该问题，将全局表缓存拆分成 N 个独立分区，每个分区各自拥有锁。table_open_cache_instances 的值代表分区数量，线程在某个分区获取所需表句柄后即可直接使用。

MySQL 8.0 的 table_open_cache_instances 默认值为 16，建议不超过 CPU 核心数。若存在大量触发器，可能会增加内存压力。

【3.2.2 源码解析】
mysql中每一个表由table这个结构体定义，table结构体中定义了一个TABLE_SHARE指针，TABLE_SHARE中存放了这个表的元数据。包括表名、库名、字段定义、索引结构、字符集、触发器解析代码、frm 文件读出的所有静态信息等。

//table.h:1400
struct TABLE
{
…
TABLE_SHAREs;
…
}
//table.h :691
struct TABLE_SHARE
{
TABLE_SHARE() { memset(this, 0, sizeof(this)); }
/Category of this table./
TABLE_CATEGORY table_category;
/hash of field names (contains pointers to elements of field array)/
HASH name_hash; /hash of field names/
MEM_ROOT mem_root;
TYPELIB keynames; /Pointers to keynames/
TYPELIB fieldnames; /Pointer to fieldnames */
TYPELIBintervals; /pointer to interval info/
mysql_mutex_t LOCK_ha_data; /To protect access to ha_data */
TABLE_SHARE *next, *prev; /Link to unused shares */
Table_cache_element **cache_element;
…
}
其中，cache_element指向了一个一张表在某一个分片（Table_cache_element）中的存在记录。

Table_cache_element记录了这个cache中的used_tables和free_tables。

//table.h
class Table_cache_element
{
private:
typedef I_P_List > TABLE_list;

TABLE_list used_tables;
TABLE_list free_tables;
TABLE_SHARE *share;

public:

Table_cache_element(TABLE_SHARE *share_arg)

share(share_arg)
{
}

TABLE_SHARE * get_share() const { return share; };

friend class Table_cache;
friend class Table_cache_manager;
friend class Table_cache_iterator;
};
此外，MySQL 全局维护一个 Table_cache，其充当加锁的基本单位。

LOCK_open 是 MySQL 的全局互斥锁，用于保护表的打开/关闭操作，包括 TABLE_SHARE 的创建与销毁以及数据字典的访问。

引入分片 Table_cache 的目的之一是减少对 LOCK_open 的竞争。

//table_cache.cc
class Table_cache
{
private:
/**
table cache 锁保护以下数据：
m_unused_tables 链表。
m_cache 哈希表。
本 cache 中各 Table_cache_element 对象的 used_tables、free_tables 链表。
m_table_count —— 本 cache 中 TABLE 对象的总数。
TABLE_SHARE::cache_element[] 数组中对应本 cache 的元素。
TABLE 对象中的 in_use 成员。
此外，更新 refresh_version、table_def_shutdown_in_progress 变量以及 TABLE_SHARE::version 成员时，需要持有所有 cache 的互斥锁。
设计意图是：任何在指定 table cache 中找到缓存表对象的查询，只需锁定该互斥锁实例，而无需锁定 LOCK_open。
但创建和释放 TABLE 对象时仍需要 LOCK_open。
不过，大多数 MySQL Server 的使用场景应能将 cache 大小设置得足够大，使得绝大多数查询只需锁定该互斥锁实例，而无需锁定 LOCK_open。
/
mysql_mutex_t m_lock;
/*
Table_cache_element 对象的哈希表。
凡是在本 Table_cache 中有 TABLE 对象的表/table share，都在此哈希表中有一个对应的 Table_cache_element，
其中存储了该表在本 cache 中的空闲 TABLE 对象链表和已使用 TABLE 对象链表。
哈希键为 Table_cache_element::share::table_cache_key。
*/
HASH m_cache;
…
}

bool Table_cache_manager::init()
{
Table_cache::init_psi_keys();
for (uint i= 0; i < table_cache_instances; i++)
{
if (m_table_cache[i].init())
{
for (uint j= 0; j < i; j++)
m_table_cache[i].destroy();
return true;
}
}
return false;
}
table_open_cache_instances可以在Table_cache_manager::init()看到是直接控制m_table_cache生成个数的参数。

table_open_cache保存的是TABLE对象本身

TABLE对象是一个表被某个连接打开后的"运行时实例"，mysql官方文档说table_open_cache_instances过高导致内存压力大，主要是因为TABLE对象的对象的触发器开销本身挂在在TABLE::mem_root上，每一个TABLE实例都是独立的,并发连接数乘上去之后内存消耗就很显著了。如果表中有大型触发器需要适当调低table_open_cache_instances。