Randomly rewriting a file that mysqld has open seems a lot more dangerous than restarting mysqld. What if that was e.g. where mysqld was batching up query results before streaming them to the client? Now you've not merely had a query fail, you've silently sent back incorrect results.
If you can't restart mysqld you have an architectural problem. Your database will go down sometimes and your system should be built to tolerate that.
i was just going to write the exact same thing. one should at least have a simple master to master setup for redundancy imho - then these kind of problems are much easier to solve, just restart mysql.
I did not even know this was a thing (without using some less well-known mysql extension like Galera), but just googled and found a brief document from Digital Ocean on exactly how to do this with stock MySQL. And here it is, right in the fine manual, supported since 5.1.18.
I don't know how to ask this more precisely, but how are races and concurrent writes on both masters handled? Is it still "ACID safe" writing across the cluster, or would you use something like this as a hot-spare for promotion in case of failover only?
Yeah. I thought about it, and there is so much work on things like etcd and zookeeper, I would have already heard about it if multi-master MySQL was really a thing worth writing home about. But still, cool to find out they're advertising it.
Good thing the author wasn't randomly rewriting a file, then, and that the file was deleted and inferably unlikely to be used in a critical operation!
Restarting MySQL is a violent operation. For large databases, it can take several minutes. If I can avoid it, I do so, and this was a really clever workaround for an issue that indeed avoided a restart. That's a good thing.
> Good thing the author wasn't randomly rewriting a file, then, and that the file was deleted and inferably unlikely to be used in a critical operation!
That's not at all true. It's very common unix practice to open a file, delete it, and then use it for critical operations.
I find it quite ironic that you and author of the article completely misunderstand what is happening.
Creating then deleting a temporary file is very common technique to make sure that that for will be removed as soon as the application closes it, it also is used to make sure that nothing will tamper with it. I find amusing that author found a way to still tamper with it.
Now depending on what the file was used for either the corrupted files caused database to return corrupted data to the user, write corrupted data to the database, our in the best scenario return error to the application issuing the query.
open's O_TMPFILE (since Linux 3.11) does exactly that without creating a named file and immediately removing it from the filesystem. I don't argue that the other technique isn't common though.
Former MySQL admin's advice (for OP, and others who run into this same issue):
Someone probably started running queries which were doing disk sorts. Look for abusive queries coming in and kill those, the temporary files will go away at that point. Based on the size of the files, simply looking for long running queries should be sufficient; if it's the backend for a web server, it's likely that the client and server have already given up on the query (or in the worst case, re-sent the debilitating query).
As stated by other users, truncating random files will cause more problems for MySQL than just restarting mysql. In fact, I'd recommend going in and restarting it now, to ensure that you're at a good state. Failing over to your slave (you do have a slave and failover procedure, right?) is less of a headache than trying to identify what problems were caused by manually truncating these sort files.
Finally, have a look at Skyline from Etsy[1]; a trend monitoring tool like this would have alerted you when the ramp started closer to 1am, well before this was suddenly an outage event.
There's a quick mention that this was a reporting server. I'd guess the reports have a common query in them that yesterday did not spill to file sorts, and today does, so literally overnight the report process goes from using virtually no disk space to using arbitrary multiples of how much you have.
Nothing really great leaps to mind to solve this in the general case. These sorts of correlated behaviors can really be jerks.
In this case I'd look for a recent table alter which involves a blob-style column - queries involving those will always be sorted on disk. Limiting the queries to not include the blob fields, or doing alters to varchar fields (even big ones) will help this out.
Having a daemon which kills off long running queries (such as ones with extensive disk sorts) can help as well, just be sure to follow up on the queries which were killed to fix the frontend or chastise the person doing `SELECT * FROM blob_table ORDER BY id`.
Opening files and the deleting them is a classical pattern for using temp files; it makes sure that they will be gone when the process exits. Finding a deleted but still opened file is no indication that it's not needed anymore.
My approach would have been to kill the mysql worker process that keeps them alive. That way the program that started the query gets an error message, instead of whatever undefined behaviour you get by empying the files and surprising mysqld.
First of all... If you're getting paged on disk alerts at 3 in the morning,
you're doing it wrong. Write a script that checks every file system and
makes sure that X amount of free space is always present, adding free space
as-needed, and emailing you on the backend during business hours whenever
the volume group is nearing depletion.
I'm sorry, but no -- I'm not going to automate adding more disk to a VM, extending a volume group, and growing a filesystem. Not now, not ever. No way in hell.
That needs to be done by a human after a backup has been tested and writes have been quiesced.
This is what grown-ups do. Storage is cheap. Downtime is not.
Then I don't want to grow up like you. Have fun recovering your corrupted filesystem.
I'm not the person who posted that comment, but let's take a step back. I have scripts which do this for me. However, I also have automation that takes back ups of the systems, restores the systems on other virtual hosts, and verifies that the restores are up and running properly. In fact if you can script one, you can generally script all of those scenarios.
For expanding database volumes, for example, I have a system which first monitors the size of my current database systems. If they get too large, it will create a new virtual which is bigger, restores a current backup of the running system to it, verify that, put it into rotation, make sure that all current data is replicated, then remove the old system.
Frankly, doing this by hand would be highly error prone and dangerous. I say, script everything.
Of course I have scripts to do that stuff, but considering the potential danger involved and being burned so many times in the past from bad filesystems/tools/kernels it is something I would only initiate manually -- never as a knee-jerk reaction to "low disk space" alerts.
Eh, sort of. I do Devops/Infrastructure exclusively, and while the tedious stuff is automated away, I've seen too much shit break on the other side of the fence to trust it fully (AWS API throwing errors when you absolutely need it to work, etc).
If something is broken, you should have enough automation to get it into a known good state without a human involved, while maintaining data consistency. Anything else should be automated, but you should still be around to babysit it while its going through the motions.
If you think you can automate everything and always trust it to work flawlessly, you haven't been around long enough for the edge cases.
> If you think you can automate everything and always trust it to work flawlessly, you haven't been around long enough for the edge cases. <
And then you have some "clever" coder coming along with a fix for said edge case (that invariably create some new edge cases just outside the domain of the fix).
That's a falacy of an argument really. It's not as if admins can't properly script environments and changes. Monitoring, updating, and scripting of changes happened long before the term devops came into existence.
The "just add more to the pool" solution seems akin to the "solution" in the strip above.
And i have seen similar arguments for when a daemon or server goes down. Hook it up to a script that reboots it automatically (or fires up a new instance over and over and over) and go back to coding.
It is sometimes useful to keep around a 1-10GB 'ballast' file full of zero you can remove if you're in this kind of emergency. Tuning the root reserved space as mentioned in the article comments is another useful trick.
I love this ballast file idea. It reminds me of a programming story by Noel Llopis[0] where a game developer was working with a strict memory limit and placed static char buffer[1024 * 1024 * 2]; in the code as an insurance policy[1]:
This is the canonical story of databases, I had similar frightening nights several years ago with my MongoDB setup. I'm not a DevOps guy and having to wake up to figure out what is broken (and it always being the database) eventually took a toll on me. What's the point of running my database in the cloud if my storage space is still finite? Isn't the whole point of the cloud unlimited scalability? So why on earth do I have to be an expert in MDADM, LVM and all that other junk in order to make use of those unlimited number of hard drives? Ugh, sorry for the rant but this story causes triggers in me back to those days. I tried doing stuff differently since, experimenting with new database concepts, trying to make things better, and the good news is that I have never had this problem since - so hopefully I can save the pain and woes of future souls, http://gunDB.io/ .
Second this in general, although I don't think du (and consequently ncdu) will show anything about disk space claimed by deleted but unclosed file descriptors.
In Windows we use TreeSizeFree to find the offending files. Tracking down the lock and unlocking can be tricky, but Process Monitor and File Unlocker are usually go to tools.
I'm still looking for a way to close open sockets of a process. I know about the gdb hack [1] mentioned in the comments of the article, but it seems like an opportunity for somebody to write a nice tool for this.
If you can't restart mysqld you have an architectural problem. Your database will go down sometimes and your system should be built to tolerate that.