3 ms·
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 a
by falcolas 11y ago
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.
[1] https://github.com/etsy/skyline https://github.com/etsy/skyline
- jerf 11y agoThere'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.
- falcolas 11y agoIn 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`.