df and du disagree by more than a rounding error, the missing space is in files that have been deleted but are still open, and the kernel will name the process holding them. MySQL deletes its temporary files the instant it creates them, on purpose, so anything else that opens those files keeps their space alive after MySQL is done. Here that was an endpoint security agent, a 10 GB database filled a 200 GB disk, and two rounds of bigger hardware bought a few days each.The page says the MySQL host is at 92 percent disk. It’s a 200 GB volume. The data directory, which is the only thing on that machine anyone would expect to grow, comes to 10 GB.
| |
df asks the filesystem how many blocks are in use. du walks every directory entry and adds up sizes. They agree on a healthy machine to within the metadata overhead. Here they disagree by 165 GB, and the binary logs, the general log, the slow log, the OS journal, and /tmp have all been checked and are all small. The space is being used, and nothing on the disk is using it.
Restarting MySQL brings df down a little. Over the next day it climbs back.
Buy a bigger box
The first response was the reasonable one. Grow the volume. Disk is cheap, the alert stops, and the investigation can wait for business hours. The alert came back within the week at the same 92 percent, which on a bigger disk meant more space had vanished, not less. The worst day was a burst of GROUP BY and ORDER BY queries the application couldn’t serve from cache. Every one of them spilled to temp files, and the volume went from healthy to nearly full in a few hours. The second response was a bigger instance class, more memory and more cores, on the theory that if something inside MySQL was spilling to disk, more RAM would keep it in memory.
Memory usage did not move. The graph stayed flat through both upgrades, exactly where it had been for a year. Nothing in this failure consumes memory, and that was the first real clue, though it took a while to be read as one. The second was that restarting MySQL only ever recovered a few gigabytes. If MySQL had been the one holding the space, restarting it would have returned all of it.
“Something is writing to a path we haven’t checked” is the next theory, and it’s usually right. Not here. Every mount point had been walked, and find / -size +1G returned the InnoDB files and nothing else. The space had no filename, and the search had been for filenames the whole time.
Where space goes when the file has no name
On Linux a file is an inode with data blocks, and a filename is a directory entry pointing at it. rm removes the directory entry. The blocks are returned to the filesystem only when the last reference to the inode goes away, and an open file descriptor is a reference. A process that opens a file, and keeps it open while the name is removed, keeps the data too. ls can’t see it, du can’t count it, df counts it.
MySQL does this to itself deliberately. From the manual’s page on temporary files:
MySQL arranges that temporary files are removed if mysqld is terminated. On platforms that support it (such as Unix), this is done by unlinking the file after opening it. The disadvantage of this is that the name does not appear in directory listings and you do not see a big temporary file that fills up the file system in which the temporary file directory is located. (In such cases,
lsof +L1may be helpful in identifying large files associated with mysqld.)
Every sort that outgrows sort_buffer_size, and in MySQL 8.0 every internal temporary table that outgrows temptable_max_ram (1 GiB by default), lands in a file under tmpdir that has already been deleted by the time the first byte is written. Here is a GROUP BY over a million-row table on MySQL 8.0.46, caught mid-flight by reading the server’s own file descriptor table:
| |
Two files, just under 2 GB each, from one report query against a table that is 1 GB on disk. The temporary table is wider than the base rows because it carries the grouped columns in full, and the sort that follows gets its own file. Zero entries in /tmp. du says zero. The moment the query finishes, MySQL closes both descriptors, the reference count hits zero, and the 4 GB is back. That is the design, and on a healthy host it works so well that most people who run MySQL for a living have never seen these files.
Unless something else opens them first.
tmpdir (temptable_use_mmap=ON by default). MySQL 8.4 turns that path off by default, and the same query overflows into the InnoDB session temporary tablespace under #innodb_temp inside the data directory, a file with a name that du finds without help. Filesort still uses unlinked files in tmpdir on every version, so the gap can appear on 8.4 too, just with a lower ceiling.Ask the kernel who is holding it
lsof +L1 lists open files with a link count below one, meaning every open file whose last name has been removed. On the incident host, trimmed:
| |
The first two lines are MySQL, holding the temp files for the report that is running right now, which is what the manual said to expect. Everything after that is the endpoint security agent. (The vendor’s name in the COMMAND column is the one detail this write-up leaves out.) Several hundred descriptors, every one of them a deleted MySQL temp file, every one opened read-only, and the sum of the SIZE column came to a few gigabytes short of the gap between df and du.
| |
None of those inodes were MySQL’s anymore. MySQL had created each file, unlinked it, written a report’s worth of sort data into it, and closed it, hours or days earlier. From MySQL’s side the file was gone. From the kernel’s side the agent still had it open, and the agent’s descriptor was the only thing standing between 163 GB and the free list.
The mechanism on the agent’s side is ordinary. An on-access scanner subscribes to file events, and a file being created in /tmp by a process it doesn’t have an exclusion for is precisely what it exists to look at. It opens the file to scan it. MySQL, meanwhile, has already unlinked the name and is writing 2 GB into the inode at disk speed, so the scanner is reading a file that changes under it, on a queue with every other temp file from every other report. Whether it re-queued the file each time it changed, waited for it to settle, or leaked the descriptor outright is the vendor’s business and was never established. The effect on the filesystem is the same in all three cases: the inode outlives MySQL’s interest in it by as long as the agent holds it, and on a reporting host that runs a few dozen heavy GROUP BY queries a day, at a couple of gigabytes each, 200 GB is a week of ordinary days, or a few hours on the afternoon the cache stopped absorbing the aggregation queries and every GROUP BY with a sort behind it spilled at once.
This also explains the two hardware upgrades. More disk raised the ceiling the agent’s backlog could fill and changed nothing about the rate. More RAM changed nothing at all: temptable_max_ram is a configuration value, not a function of the memory in the box, so the spill happened at the same 1 GiB after the upgrade as before it, and the sort files would have gone to tmpdir regardless. The graph that stayed flat was telling the truth. The problem was never in MySQL’s memory or MySQL’s files. It was in a reference count.
tmpdir either, since the names are already gone and there is nothing to delete. If the holder is a security agent, restarting or stopping it on a production database host is a change the security team owns, not the DBA; the lsof output is what gets that conversation to a yes in one message instead of three.The confession, and the fix
The proof was a restart of the agent, agreed with the team that owned it. df dropped by 163 GB in the time it took the process to exit, which is the closest thing this kind of investigation gets to a signed confession. The durable fix was to remove the agent from the database hosts, on the grounds that a scanner that opens every file a database writes has no business on a machine whose entire job is writing files. Replacing it with a product that respects exclusions was the compromise position.
Two smaller options exist, and each trades something. Excluding tmpdir and the data directory from scanning is what the MySQL manual has recommended for years (the Windows installation page has a section titled “MySQL and Virus Checking Software” that says exactly this), and it leaves the agent in place for everything else on the host. It also depends on the agent honouring the exclusion for a file whose name disappeared a millisecond after creation, which is worth testing rather than assuming. Moving tmpdir to its own filesystem bounds the damage instead of preventing it: a full tmpdir fails the report with “The table is full” and leaves the data directory, the redo log, and the binary logs untouched, where a full root volume takes the server down. That is a smaller blast radius, and it is still a broken report every week.
There is a general signal here that is worth more than the specific story. df and du disagreeing is a specific symptom with a two-item differential. Either files are hidden under a mount point (a directory that had files in it before something was mounted on top of it, which mount --bind / /mnt/root and a second du will reveal), or files have been deleted while open, which lsof +L1 will list along with the PID of whoever is responsible. Both take under a minute to check. Neither is what a capacity alert suggests, and the disk post in the queries series already made the argument that the storage dashboard is the last place to look for a storage problem. This is the version of that argument where the culprit is not even a query.
The same method worked on the 3-billion-row query in the previous post: work out what the answer should cost, find the one line that owns the difference, and refuse to buy hardware until you can say what the hardware is for. Here the answer was 10 GB of database and a few gigabytes of transient temp files, the line was a process name in the COMMAND column, and the hardware, twice, was for nothing.