1

I had a stored procedure that was cycling, so it started to generate data into its temp table.

I guess that as the table grew, mysql decided to use disk tables instead of memory ones.

Now, the faulty process was stopped but the storage used for that temp table is not gone even after restarting the instance.

Is there a way to find and delete that data? Or the only way is to dump into a new database?

htafoya
  • 18,261
  • 11
  • 80
  • 104
  • Firstly, by saying "storage used for that temp table is not gone" do you mean that you see that the storage is still in use or that it was expanded and hasn't shrunk? Second thing, if you are using a 2nd generation instance the space used for temporary tables should be cleared after the restart (which might not be the case for a 1st generation instance). Which instance are you using? Lastly, which version of MySQL is that? – arudzinska Apr 04 '18 at 15:58
  • @AniaRudzińska It is 2nd Generation on Mysql 5.7. In fact after restart the temp caches weren't erased. However Today I noticed that the space has been dropping huge amounts in daily steps until it got stabilised in the normal storage usage. – htafoya Apr 06 '18 at 09:53
  • Hmm, this could have been [binary logs](https://issuetracker.google.com/69217042). They get erased after one week. Did the space start to stabilise 7 days after you stopped the process? – arudzinska Apr 06 '18 at 14:00
  • @AniaRudzińska, sure just about that. That makes sense! – htafoya Apr 06 '18 at 16:55
  • well, more or less. Because the problem was a cycled stored procedure, no logs should've been generated because of that. – htafoya Apr 06 '18 at 17:55

0 Answers0