关于ulimits中nproc与nofile的定义、作用及数据库应用问询

Hey there! Let's dive into nproc and nofile—two critical ulimit settings that often trip up folks running databases. I'll break down what they are, their core roles, share practical examples, and highlight exactly how they impact database environments.

These are resource limits enforced by Linux to prevent any single user or process from hogging system resources, which would destabilize the entire system.

nproc - Maximum Processes/Threads Per User #

This setting controls the maximum number of processes (and threads, since Linux treats threads as lightweight processes) a single user can create. Every running task—whether a full process or a thread within an application—counts against this limit.

nofile - Maximum Open File Descriptors Per Process #

In Linux, almost everything is a file: regular data files, network sockets, pipes, device files, and even database connections. This limit caps how many of these "file-like" resources a single process can have open at once.

Let's break down why these matter, especially for databases.

For nproc #

• System Stability: Prevents a single user (like the mysql or postgres system user) from spawning so many processes/threads that it exhausts the system's PID (Process ID) pool. If the PID pool runs dry, no new processes can start—including critical system services.
• General Example: Run ulimit -u to check your current nproc limit. If it's set to 1024, and your Java-based application tries to spin up 1500 worker threads, you'll hit a fork: Resource temporarily unavailable error.
• Database-Specific Role: Databases rely on background processes and per-connection threads to handle workloads. For example:
  • MySQL uses a thread per connection (by default) to handle client requests. If you set max_connections to 2000 but the mysql user's nproc limit is only 1024, MySQL can't create enough threads to handle all 2000 connections. You'll start seeing errors like Could not create thread to handle connection and new connections will be rejected.
  • PostgreSQL uses background processes for tasks like WAL writing, autovacuum, and connection handling—all counting against the postgres user's nproc limit.

For nofile #

• System Stability: Stops a single process from opening so many files/sockets that it drains the system's global file descriptor pool, leaving other processes unable to open necessary files or establish network connections.
• General Example: Run ulimit -n to check your current nofile limit. If it's 1024, a high-concurrency Nginx server will throw too many open files errors when it can't create new sockets for incoming requests.
• Database-Specific Role: Databases are heavy users of file descriptors—here's how:
  • Data & Log Files: Databases keep multiple data files, redo logs, binlogs, and transaction logs open simultaneously.
  • Client Connections: Every client connection to the database uses a network socket, which counts as a file descriptor. So your max_connections setting directly ties to nofile—each connection needs at least one descriptor.
  • Background IO: Processes handling data page reads/writes, log flushing, and replication also consume file descriptors.
    For example, if PostgreSQL's nofile limit is too low, you might see errors like could not open file "base/12345/6789": Too many open files when it tries to access a data file during a query. Most database vendors (like MySQL) recommend setting nofile to at least 65535 to accommodate high concurrency and large datasets.

Temporary Changes (Current Session Only) #

These changes won't survive a system reboot or user logout:

# Check current limits
ulimit -u  # View nproc
ulimit -n  # View nofile

# Modify temporarily
ulimit -u 65535  # Set nproc to 65535
ulimit -n 65535  # Set nofile to 65535

Permanent Changes (For a Specific User) #

To set limits permanently for a database user (e.g., mysql), edit /etc/security/limits.conf and add these lines:

mysql soft nproc 65535
mysql hard nproc 65535
mysql soft nofile 65535
mysql hard nofile 65535

• soft: The warning threshold—users get a warning when approaching this limit.
• hard: The absolute maximum—users can't exceed this, even temporarily.

After saving, the user will need to log out and back in for changes to take effect.

内容的提问来源于stack exchange，提问作者yogesh.j