It’s 02:14. The app is “up” in the dashboard, but every request that touches the database returns a polite 500 and a very impolite log line: Too many open files. You bump a limit, restart, and it “works.” For three days. Then it happens again, during payroll, or the quarterly close, or whatever ritual your business uses to summon chaos.
This is one of those failures that looks like an OS trivia question and is actually a systems design problem. MariaDB and PostgreSQL hit it differently, for different reasons, with different knobs. The fix is rarely “set nofile to a million and move on.” That’s not a fix. That’s a bet.
What “Too many open files” actually means (and why it lies)
On Linux, “Too many open files” usually maps to EMFILE: the process hit its per-process file descriptor limit. Sometimes it’s ENFILE: the system hit its global file descriptor limit. Sometimes it’s neither and you’re looking at an application-level resource cap that gets logged as “open files” because engineers are optimists and naming things is hard.
A file descriptor (FD) is a handle to an open “thing”: a regular file, a directory, a Unix domain socket, a TCP socket, a pipe, an eventfd, an inotify watch, an epoll instance. Databases use all of them. If you only think “table files,” you’ll diagnose the wrong problem and you’ll fix it wrong.
Two important operational truths:
- FD exhaustion is rarely a single knob problem. It’s an interaction between OS limits, systemd defaults, database configuration, connection behavior, and workload shape.
- FD exhaustion is a symptom. The root cause is usually: too many connections, too many relations (tables/indexes/partitions), or a cache setting that turned “open file reuse” into “open everything forever.”
Also: you can “fix” EMFILE by raising limits until the server can open enough files to progress, then push the failure somewhere else: memory pressure, inode exhaustion, kernel dentry cache churn, or plain old operational complexity. The goal isn’t infinite descriptors. The goal is controlled resource use.
One quote worth keeping on a sticky note: “Hope is not a strategy.” — General Gordon R. Sullivan. In ops, this is less a motto and more a diagnostic tool.
How file descriptors get consumed in real database servers
If you’re debugging this in production, you need a mental model of what’s actually holding FDs open. Here’s the non-exhaustive list that matters.
Connections: the silent FD factory
Every client connection consumes at least one FD on the server side (the socket), plus some internal plumbing. With TLS, you add CPU overhead; with connection pooling done badly, you add connection churn and bursts. If you run 5,000 active connections because “microservices,” you’re not modern—you’re just paying per-socket rent.
Data files, index files, and relation files
Databases try to avoid reopening files constantly. Caches exist partly to keep FDs around so the OS page cache can do its job and the DB can avoid syscall overhead. But caches can be oversized or mis-tuned.
- MariaDB/InnoDB: multiple tablespaces, redo logs, undo logs, temporary tables, per-table .ibd files when
innodb_file_per_table=ON. - PostgreSQL: each relation fork (main, FSM, VM) maps to files; large relations are segmented into multiple files; temp files show up under
base/pgsql_tmpor per-tablespace temp dirs.
Temp files and spill-to-disk behavior
Sorts, hashes, large aggregates, and certain query plans spill to disk. That means temp files. Enough parallel queries and you get a small blizzard of open descriptors.
Replication and background workers
Replication threads, WAL senders/receivers, I/O threads, and background workers all hold sockets and files. Usually not your biggest consumer, but in a busy cluster with multiple replicas, it adds up.
Logs, slow logs, audit logs, and “just add more observability”
Logs are files. Some logging configurations open multiple files (rotate patterns, separate audit logs, error logs, general logs). If you tail logs with tools that open extra file handles or you run sidecars that do the same, you can contribute to FD pressure. Not typically the main culprit, but it’s part of the bill.
Joke #1: “Too many open files” is the server’s way of saying it’s emotionally unavailable right now.
MariaDB vs PostgreSQL: how they behave under FD pressure
MariaDB (InnoDB) failure modes: table cache meets filesystem reality
MariaDB’s most common FD pain comes from table/index file usage and table cache behavior combined with high concurrency. Historically, MySQL-family servers leaned on table caches (table_open_cache, table_definition_cache) to reduce open/close churn. That’s good—until it’s not.
What happens in the “bad” case:
- You have many tables, or many partitions (which are effectively table-like objects), or many schemas.
- You set
table_open_cachehigh because someone said it improves performance. - Workload touches many distinct tables across many sessions.
- MariaDB tries to keep them open to satisfy cache hits.
- The process hits
RLIMIT_NOFILE(per-process), or the server’s internal open file limit, and starts failing operations.
InnoDB adds its own angles:
innodb_open_filesprovides a target for how many InnoDB files it can keep open, but it’s bounded by OS limits and other file users in the process.- Temporary table usage (disk-based temp tables) can spike FDs.
- Backup tools (logical or physical) can add load and open handles.
PostgreSQL failure modes: connections and per-session overhead
PostgreSQL uses a process-per-connection model (with caveats like background workers). That means each connection is its own process with its own FD table. The good news: per-process FD exhaustion is less likely if each backend has modest FD usage. The bad news: too many connections means too many processes, too many sockets, too much memory, too much context switching, and a thundering herd of resource use.
PostgreSQL commonly hits “too many open files” in these scenarios:
- High connection counts plus a low FD limit for the postmaster/backends under systemd.
- Large numbers of relations plus query patterns that touch many relations in one session (think partitioned tables with wide scans).
- Heavy temp file creation from sorts/hashes and parallel query, compounded by low
work_mem(more spills) or too-high parallelism (more concurrent spills). - Autovacuum and maintenance on many relations, plus user workload. Lots of file opens.
PostgreSQL also has a subtle but real behavior: even if you raise the OS FD limit, you can still be limited by internal expectations or by other OS limits (like max processes, shared memory settings, or cgroup resource caps). EMFILE is rarely lonely.
The practical difference that changes your fix
MariaDB tends to hit FD exhaustion due to open table files and caches. The fix is usually a combination of proper LimitNOFILE, proper open_files_limit, and sane table cache sizing—plus addressing table/partition explosion.
PostgreSQL tends to hit FD exhaustion via connection behavior and temp file churn. The fix is often: connection pooling, lowering connection counts, raising OS limits appropriately, and tuning memory/parallelism to reduce spill storms.
Interesting facts and historical context (that actually matters)
- Unix file descriptors were designed as a unifying abstraction for “everything is a file,” which is elegant until your DB treats everything as “open and never let go.”
- Early Unix had tiny default FD limits (often 64), and the habit of conservative defaults never fully died—systemd defaults still trip modern servers.
- PostgreSQL’s process-per-connection model is a long-standing architectural choice that trades some simplicity and isolation for higher overhead at very high concurrency.
- MySQL’s table cache knobs came from a world where filesystem metadata ops were expensive and “keep it open” was a measurable win.
- Linux’s
/procfilesystem made FD introspection dramatically easier; before it, diagnosing FD leaks was more like archaeology. - cgroups and containers changed the game: you can have high host limits but low container limits; the process sees the smaller world and fails there.
- Modern filesystems made open/close cheaper than they used to be, but “cheap” isn’t “free” when multiplied by thousands of queries per second.
- Replication increased FD usage patterns in both ecosystems, adding more sockets and log file activity—especially in multi-replica topologies.
Fast diagnosis playbook
This is the part you follow when you’re on-call, half awake, and your brain is trying to negotiate a ceasefire with reality.
First: confirm what limit you’re hitting (process vs system)
- Check the error source: database logs, system logs, and application logs. Determine whether the database process itself is failing to open files, or clients are failing to connect.
- Check per-process limit: inspect the database process
Max open filesfrom/proc. If it’s low (often 1024/4096), you found a likely immediate cause. - Check system-wide file handle pressure:
/proc/sys/fs/file-nr. If system-wide is near max, raising per-process won’t help without raising global capacity and finding the consumer.
Second: identify who is holding the FDs
- Count open FDs per PID and identify the top consumers. If it’s the DB, proceed. If it’s a sidecar, log shipper, or backup agent, you have a different incident.
- Classify FD types: are they mostly sockets (connections) or regular files (tables, temp files, logs)? That tells you which database knobs matter.
Third: determine if this is “spike” or “leak”
- Spike: FDs shoot up during a traffic surge or batch job, then drop after. Fix: capacity and concurrency control.
- Leak/sticky growth: FDs trend upward and never return. Fix: identify what’s being held open (cache too large, bug, stuck connections, file handle leak in tooling).
Fourth: stop the bleeding safely
- Short-term: raise limits only if you’re confident the kernel has headroom and you won’t induce memory pressure. Prefer a controlled restart with corrected limits over random ulimit twiddling.
- Reduce concurrency: throttle batch jobs, reduce app worker counts, or enable pooling. A database that can’t open files also can’t serve queries.
Practical tasks: commands, outputs, and decisions (12+)
These are the tasks that turn “I think” into “I know.” Each includes a command, a realistic output snippet, what it means, and what you decide next.
Task 1: Confirm the DB process and PID
cr0x@server:~$ ps -eo pid,comm,args | egrep 'mariadbd|mysqld|postgres' | head
1287 mariadbd /usr/sbin/mariadbd
2140 postgres /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main
2142 postgres postgres: checkpointer
Meaning: You have MariaDB on PID 1287 and PostgreSQL postmaster on PID 2140 (plus workers). Know which one is failing; don’t “fix” both.
Decision: Pick the PID(s) you’ll inspect in subsequent steps. If the error is in the app, confirm which DB endpoint is in use.
Task 2: Check per-process max open files (the one that usually bites)
cr0x@server:~$ cat /proc/1287/limits | egrep -i 'open files|max processes'
Max open files 1024 1048576 files
Max processes 127636 127636 processes
Meaning: Soft limit is 1024; hard is 1048576. MariaDB is living on a starvation diet.
Decision: Fix the service unit or PAM limits so the DB starts with a sane soft limit (e.g., 65535 or higher based on sizing). Don’t just raise hard limit and forget the soft.
Task 3: Count current open FDs for a PID
cr0x@server:~$ ls -1 /proc/1287/fd | wc -l
1008
Meaning: The process is sitting near the 1024 ceiling. EMFILE is imminent or already happening.
Decision: Immediate remediation: reduce load and prepare a restart with corrected limits. Also find what’s consuming the FDs (next tasks).
Task 4: Identify what types of FDs are open (files vs sockets)
cr0x@server:~$ ls -l /proc/1287/fd | awk '{print $11}' | sed -e 's/.*socket:.*/socket/' -e 's/.*pipe:.*/pipe/' -e 's/.*anon_inode:.*/anon_inode/' | sort | uniq -c | sort -nr | head
612 socket
338 /var/lib/mysql/db1/orders.ibd
42 anon_inode
16 pipe
Meaning: Mostly sockets and InnoDB table files. This isn’t just “too many tables” or just “too many connections.” It’s both.
Decision: Investigate connection counts and table cache settings in parallel. Fixing only one side may just shift the bottleneck.
Task 5: Check system-wide file handle usage (global pressure)
cr0x@server:~$ cat /proc/sys/fs/file-nr
38144 0 9223372036854775807
Meaning: System-wide allocated handles are fine; global limit is effectively huge. This is a per-process issue, not global.
Decision: Focus on systemd/PAM limits and DB config, not kernel fs.file-max.
Task 6: Inspect systemd service limits (the hidden culprit)
cr0x@server:~$ systemctl show mariadb -p LimitNOFILE -p LimitNPROC -p TasksMax
LimitNOFILE=1024
LimitNPROC=127636
TasksMax=4915
Meaning: systemd is explicitly setting LimitNOFILE=1024. You can edit /etc/security/limits.conf all day; systemd will still win for services.
Decision: Add a systemd override with a higher LimitNOFILE and restart the service. Also consider TasksMax if you’re on PostgreSQL with many backends.
Task 7: Apply a systemd override for MariaDB or PostgreSQL
cr0x@server:~$ sudo systemctl edit mariadb
# (opens editor)cr0x@server:~$ sudo cat /etc/systemd/system/mariadb.service.d/override.conf
[Service]
LimitNOFILE=65535
Meaning: You’ve set a new service-level FD limit. This is the correct layer for services.
Decision: Reload systemd and restart MariaDB during a controlled window. Then re-check /proc/<pid>/limits.
Task 8: Reload systemd and validate the new limit is live
cr0x@server:~$ sudo systemctl daemon-reload
cr0x@server:~$ sudo systemctl restart mariadb
cr0x@server:~$ systemctl show mariadb -p LimitNOFILE
LimitNOFILE=65535
Meaning: The service now starts with a higher FD ceiling.
Decision: If you still hit EMFILE, it’s not “the limit is too low”—it’s “the workload consumes too many FDs.” Continue diagnosing.
Task 9: MariaDB—check current open file and table cache settings
cr0x@server:~$ mariadb -e "SHOW VARIABLES WHERE Variable_name IN ('open_files_limit','table_open_cache','table_definition_cache','innodb_open_files');"
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| innodb_open_files | 2000 |
| open_files_limit | 65535 |
| table_definition_cache | 4000 |
| table_open_cache | 8000 |
+------------------------+--------+
Meaning: MariaDB is allowed to open many files, and it’s configured to keep lots of tables open. That may be appropriate—or wildly optimistic—depending on table count and memory.
Decision: Compare to reality: number of tables/partitions, workload pattern, and FD usage. If you’re opening 30k files in steady state, 65k may be fine; if you’re at 60k and climbing, you need design changes.
Task 10: MariaDB—estimate table count and partition explosion
cr0x@server:~$ mariadb -N -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys');"
18432
Meaning: Eighteen thousand tables (or partitions represented as tables in metadata) is a lot. Table caches set to 8000 might churn or might keep thousands open, depending on access pattern.
Decision: If this is a partitioning strategy gone feral, consider consolidating partitions, using fewer schemas, or shifting archival data out of hot DB. If it’s legitimate, size FD limits and caches deliberately and monitor.
Task 11: PostgreSQL—check max connections and active sessions
cr0x@server:~$ sudo -u postgres psql -c "SHOW max_connections; SELECT count(*) AS current_sessions FROM pg_stat_activity;"
max_connections
-----------------
800
(1 row)
current_sessions
------------------
742
(1 row)
Meaning: You are near the configured connection cap. Each connection is a process. Even if FD limits are high, this is a “resource pressure” smell.
Decision: If the app opens hundreds of idle connections, implement pooling (PgBouncer in transaction mode is the usual grown-up choice) and reduce max_connections to a number you can afford.
Task 12: PostgreSQL—check per-backend FD usage quickly
cr0x@server:~$ for p in $(pgrep -u postgres -d ' ' postgres); do printf "%s " "$p"; ls -1 /proc/$p/fd 2>/dev/null | wc -l; done | sort -k2 -n | tail
3188 64
3191 68
3201 71
3210 74
3222 91
Meaning: Backends aren’t individually huge FD consumers (dozens each), but multiplied by 700 sessions you still get a lot of sockets and internal handles across processes.
Decision: If postmaster or a shared subsystem is hitting a limit, raise service LimitNOFILE. If the system is generally overloaded, fix connection strategy first.
Task 13: PostgreSQL—find temp file pressure (spills)
cr0x@server:~$ sudo -u postgres psql -c "SELECT datname, temp_files, temp_bytes FROM pg_stat_database ORDER BY temp_bytes DESC LIMIT 5;"
datname | temp_files | temp_bytes
-----------+------------+--------------
appdb | 18233 | 429496729600
postgres | 0 | 0
template1 | 0 | 0
template0 | 0 | 0
(4 rows)
Meaning: Lots of temp files and hundreds of GB spilled since stats reset. This correlates with FD churn and disk I/O storms during heavy queries.
Decision: Identify queries causing spills, tune work_mem carefully, and/or reduce concurrency/parallelism. Spilling less reduces temp files and open handles.
Task 14: See who else is consuming FDs (top processes)
cr0x@server:~$ for p in $(ps -e -o pid=); do n=$(ls -1 /proc/$p/fd 2>/dev/null | wc -l); echo "$n $p"; done | sort -nr | head
18421 1287
2290 1774
1132 987
640 2140
Meaning: MariaDB is the top FD consumer (18421). PostgreSQL postmaster is far lower. The incident is probably MariaDB-related, not “the host.”
Decision: Focus the fix. If a log shipper or proxy is second place, inspect it too—sometimes the “DB issue” is actually a misbehaving sidecar.
Task 15: Check kernel messages for FD-related failures
cr0x@server:~$ sudo dmesg -T | tail -n 10
[Wed Dec 31 02:13:51 2025] mariadbd[1287]: EMFILE: too many open files
[Wed Dec 31 02:13:52 2025] mariadbd[1287]: error opening file ./db1/orders.ibd (errno: 24)
Meaning: Clear confirmation: errno 24 (EMFILE). It’s not a storage error; it’s an FD limit issue.
Decision: Treat as capacity/config issue. Do not waste time on filesystem checks unless you see I/O errors.
Three corporate-world mini-stories from the trenches
Mini-story 1: The incident caused by a wrong assumption
They migrated a monolith to “services,” kept the same MariaDB backend, and celebrated the first week of green dashboards. The new services team had a tidy habit: every service kept a warm pool of connections “for performance.” Nobody coordinated; everyone just did what worked locally.
On month-end, a batch job ran that touched a wide set of tables. Meanwhile, the services were doing their normal thing—plus retry storms because latency spiked. MariaDB started throwing “Too many open files.” The on-call engineer assumed it was a kernel limit and bumped fs.file-max. The error continued.
The real limiter was systemd’s LimitNOFILE=1024 for the MariaDB service. And even after raising it, the server still sat in the danger zone because the connection count had doubled, driving socket FDs up. The “wrong assumption” was that system-wide tuning would override service-level limits, and that connection pools are free.
They fixed it properly: set explicit LimitNOFILE, sized MariaDB caches to realistic values, and introduced a proper pooling layer at the app edge. They also made a rule: connection pool sizes must be budgeted like memory—because they are memory, and also file descriptors.
Mini-story 2: The optimization that backfired
A different company ran PostgreSQL and had a chronic latency issue during analytics queries. A well-meaning engineer increased parallel query settings and bumped a few planner-related knobs. The first benchmark looked great. Everyone clapped, quietly.
Then the real workload arrived: many concurrent reporting users, each running a query that spilled to disk. Parallel workers multiplied the number of temp file creators. Temp files exploded. Disk I/O surged. And, yes, FD usage spiked because each worker opened its own set of files.
The failure wasn’t immediate “too many open files” every time. It was intermittent: a few sessions failing, some queries hanging, and the app timing out. The incident timeline became a mess because the symptom looked like “slow storage,” then like “bad query plans,” and finally like “random OS flakiness.”
The optimization backfired because it increased concurrency at the worst place: inside the DB engine, during spill-heavy operators. The fix was to dial back parallelism, raise work_mem carefully for the reporting role, and enforce connection limits for the reporting tier. Performance improved, and FD spikes stopped being an event.
Mini-story 3: The boring but correct practice that saved the day
One team had a dull-sounding operational standard: every database host had an FD budget documented, with alarms at 60% and 80% of the effective per-service limit. They also logged “top FD consumers” as a periodic metric, not just in incidents.
It looked like bureaucracy until a vendor application upgrade rolled out with a subtle change: it opened a new connection per request when a certain feature flag was enabled. Connection count climbed steadily over a week. No outage yet—just a creeping increase in sockets.
The 60% alert fired during business hours. They investigated without pressure, saw the trend, and traced it to the feature flag. They rolled it back, then implemented PgBouncer and rate-limited connection creation in the app.
Nothing caught fire. Nobody had to explain a preventable outage to finance. It was the least exciting incident report they’d ever filed, which is the highest compliment you can pay an SRE practice.
The real fix: sizing, limits, and the knobs that actually matter
“Raise ulimit” is the aspirin. Sometimes you need aspirin. But if you’re taking aspirin every day, you’re not treating the disease.
Step 1: Set sane OS/service limits (correct layer, correct persistence)
For modern Linux deployments, the truth is: systemd is the source of reality for services. Set LimitNOFILE in a drop-in override for the database service. Verify after restart via /proc/<pid>/limits.
Pick a number intentionally:
- Small-ish servers (single instance, moderate schema): 65535 is a common baseline.
- Large MariaDB with many tables/partitions or heavy concurrency: 131072+ may be reasonable.
- PostgreSQL with pooling and controlled connections: you may not need huge values, but don’t leave it at 1024. That’s self-sabotage.
Also: avoid setting it to “infinite” just because you can. Every FD has kernel overhead. And enormous limits hide leaks until they become catastrophes.
Step 2: Reduce the actual FD demand
Here’s where MariaDB and PostgreSQL diverge in practice.
MariaDB: stop hoarding tables like it’s 2009
MariaDB can keep thousands of tables open if you tell it to. If your schema has tens of thousands of tables/partitions, “keep lots open” becomes a structural risk.
What to do:
- Right-size
table_open_cacheandtable_definition_cache. Bigger is not always better. If you don’t have enough memory to keep metadata and handlers warm, you’ll just thrash differently. - Set
open_files_limitandinnodb_open_filesconsistently. Don’t let one be tiny while the other is huge. That’s how you get misleading “it should work” confidence. - Watch for partition explosion. Thousands of partitions feel neat until they become a file descriptor problem and a query planning problem.
PostgreSQL: fix connections first, then spills
PostgreSQL’s easiest FD win is not an FD knob. It’s connection pooling. If you’re running hundreds to thousands of client sessions directly against Postgres, you’re treating the database like a web server. It is not.
What to do:
- Use a pooler (PgBouncer is the common choice) and reduce
max_connectionsto a number you can support. - Fix retry storms. If clients reconnect aggressively on transient errors, they can create socket storms that push FDs over the edge.
- Reduce temp spills. Spills create temp files; temp files consume FDs during their lifetime. Tune memory per workload class and reduce parallel worker fan-out if it creates more spill concurrency than you can handle.
Joke #2: Setting LimitNOFILE to a million is like buying a bigger closet instead of throwing away your collection of conference swag.
Step 3: Validate you didn’t just move the bottleneck
After raising FD limits and reducing demand, check the next failure modes:
- Memory pressure: more connections and caches mean more RSS. Watch swapping like a hawk; swapping a database is performance cosplay.
- CPU and context switching: too many PostgreSQL backends can melt CPU without any single query being “bad.”
- Disk and inode usage: heavy temp file use can consume inodes and disk space quickly, especially on small root volumes.
- Kernel limits beyond nofile: max processes, cgroup pids limit, ephemeral port exhaustion (client side), and network backlog settings.
Common mistakes: symptom → root cause → fix
This section is intentionally blunt. Most EMFILE incidents are self-inflicted, just not by the person currently holding the pager.
Mistake 1: “We raised fs.file-max, why didn’t it work?”
Symptom: “Too many open files” continues after raising /proc/sys/fs/file-max.
Root cause: Per-process/service limit (RLIMIT_NOFILE) is still low, often set by systemd.
Fix: Set LimitNOFILE in the systemd unit override, restart the DB, validate via /proc/<pid>/limits.
Mistake 2: “We set ulimit in /etc/security/limits.conf; still broken”
Symptom: Manual shell sessions show high ulimit -n, but service doesn’t.
Root cause: PAM limits affect login sessions; systemd services don’t inherit them the same way.
Fix: Configure the systemd service. Treat PAM limits as relevant to interactive sessions, not daemons.
Mistake 3: “We increased table_open_cache; now we get EMFILE” (MariaDB)
Symptom: MariaDB errors while opening tables; logs show errno 24; FD count keeps climbing.
Root cause: Table cache too large for schema/workload; server tries to keep too many table handlers open.
Fix: Reduce table_open_cache to a measured value, increase LimitNOFILE to match realistic needs, and address table/partition count.
Mistake 4: “Postgres can handle 2000 connections, it’s fine”
Symptom: Random connection failures, high load, sometimes EMFILE, sometimes just timeouts.
Root cause: Too many backend processes; FD usage and memory overhead scale with sessions; spikes push limits.
Fix: Add pooling, reduce max_connections, and enforce per-service connection budgets.
Mistake 5: “The DB is leaking FDs” (when it’s actually temp file storms)
Symptom: FD counts spike during certain queries/batches, then drop later.
Root cause: Spill-to-disk temp files and parallelism create transient FD bursts.
Fix: Identify spill-heavy queries; tune memory/parallelism; schedule batches; cap concurrency.
Mistake 6: “It’s storage” (when it’s actually descriptors)
Symptom: Queries fail opening files; people suspect filesystem corruption or slow disks.
Root cause: errno 24 (EMFILE) is not an I/O error; it’s an FD limit.
Fix: Confirm errno via logs/dmesg; check /proc limits; adjust service and database settings.
Mistake 7: “We fixed it by restarting”
Symptom: Restart temporarily resolves issue; it returns under load.
Root cause: Restart resets FD usage and caches; underlying demand is unchanged.
Fix: Do the sizing work: limits + connection strategy + schema/table cache sanity + monitoring.
Checklists / step-by-step plan
Checklist A: Emergency stabilization (15–30 minutes)
- Confirm whether it’s MariaDB or PostgreSQL throwing EMFILE (logs + PID).
- Check
/proc/<pid>/limitsfor Max open files. - Count open FDs:
ls /proc/<pid>/fd | wc -l. - Classify FD types: sockets vs table files vs temp files.
- If service limit is low, apply systemd override (
LimitNOFILE), schedule a controlled restart. - Throttle: reduce app worker concurrency, pause heavy batch jobs, and disable retry storms if possible.
- After restart, validate the limit is applied and FD usage stabilizes below 60% of limit.
Checklist B: Root cause and durable fix (same day)
- Document baseline FD usage at idle, normal peak, and worst peak.
- For MariaDB: inventory table/partition counts; review
table_open_cache,open_files_limit,innodb_open_files. - For PostgreSQL: measure connection counts over time; identify which clients create most sessions; deploy pooling.
- Check temp file statistics and slow queries; correlate FD spikes with batch schedules.
- Set alerting on FD usage per DB PID and on connection counts.
- Run a controlled load test to confirm the fix under realistic concurrency and schema footprint.
Checklist C: Prevention (this is where grown-ups win)
- Create a descriptor budget per environment: dev, staging, production.
- Enforce connection budgets per service. No exceptions without a review.
- Track schema growth (tables, partitions, indexes) as a first-class capacity metric.
- Make systemd overrides part of configuration management, not tribal knowledge.
- Test failover and restart behavior with your chosen limits to ensure fast recovery.
FAQ
1) Is “Too many open files” always the database’s fault?
No. It’s often triggered by the DB, but it can be a proxy (like HAProxy), a log shipper, a backup agent, or even the application server exhausting its own FDs and misreporting it.
2) What’s the difference between EMFILE and ENFILE?
EMFILE means the process hit its per-process FD limit. ENFILE means the system hit its global file handle limit. Most DB incidents are EMFILE.
3) Why does systemd ignore my /etc/security/limits.conf changes?
PAM limits generally apply to login sessions. systemd services use their own limits unless configured otherwise. Fix the unit with LimitNOFILE.
4) What’s a reasonable LimitNOFILE for MariaDB?
Start with 65535 if you don’t know. Then size it based on: connections (sockets), open tables/partitions, temp files, and log/auxiliary FDs. If you run huge partition counts, you may need 131072 or more—but then you should ask why you have that many partitions.
5) What’s a reasonable LimitNOFILE for PostgreSQL?
Often 65535 is fine as a baseline. The bigger win is controlling connections and reducing temp-file storms. If you need massive FD counts for Postgres, you probably have uncontrolled concurrency or extreme relation churn.
6) Can I just increase max_connections to fix connection errors?
You can, but that’s how you trade “connection refused” for “server on fire.” For PostgreSQL, use pooling and keep max_connections within a range your memory and CPU can handle.
7) Why do I see lots of sockets in FD lists?
Because every client connection is a socket FD. If sockets dominate, focus on connection counts, pooling, and retry behavior. If regular files dominate, focus on table cache behavior, schema footprint, and temp file churn.
8) Does raising FD limits have downsides?
Yes. Higher limits make it easier for a leak or runaway workload to consume more kernel resources before failing. You’ll fail later, possibly harder, and the blast radius can increase. Raise limits, but also reduce demand and monitor.
9) How do I tell if it’s a leak vs a spike?
If FD usage climbs steadily and doesn’t fall after load subsides, suspect a leak or cache behavior keeping things open indefinitely. If it spikes during a batch or traffic surge and then returns to baseline, it’s a concurrency/capacity spike.
10) Do partitions really matter for FDs?
Yes. In both ecosystems, partitions increase the number of relation-like objects. More objects can mean more metadata, more open file handles, and more planner/maintenance overhead. Partitioning is a tool, not a personality.
Practical next steps
If you’re in the middle of an incident: apply the fast diagnosis playbook, fix the service-level FD limit, and throttle concurrency. That gets you breathing room.
Then do the adult work:
- Measure FD usage by type (sockets vs files) and by steady-state vs peak.
- MariaDB: right-size table caches and confront schema/partition growth; align
open_files_limitandinnodb_open_fileswith OS limits. - PostgreSQL: pool connections, reduce
max_connections, and tackle temp spills by tuning memory/parallelism and fixing the worst queries. - Monitor FD usage and set alerts before you hit the cliff. The cliff is not a learning opportunity; it’s a downtime generator.