So I have received a DigitalOcean monitoring alert telling me that the disk is almost full and after a long time poking around I figured that MySql is using more than 30Gb of temporary storage in /var/lib/mysql/ibtemp1.
I've been trying to figure out why this is happening but apparently there is no way of finding what internal temporary tables mysql is storing in there and why. It is especially worrying since the total size of the actual database is around 25 Gb.
I have already EXPLAIN'ed some of the slowest queries that were recorded in Telescope but I haven't found any queries where the "extra" column indicates "Using temporary".
Does anybody know how/where I can find out why this is happening and how I can fix it?
Thanks for any help
Try checking out your /etc/mysql/mysql.conf.d/mysqld.cnf for configuration settings for temporary table space. See if you can find an option for innodb_temp_data_file_path and see what it is set at.
If you can't find that option, it is likely using default settings. You could try this sql command to see what it's currently set at:
SELECT @@innodb_temp_data_file_path;
If it shows autoextend, most likely at some point you were performing some large operation that required a lot of temporary space to be created. Once it is created, the file size does not reduce in size until the MySQL daemon is reloaded. Try restarting the mysql service, and I suspect the file size will shrink.
Thanks for your answer. Yes the configuration is set to default so there is no size limit. Although it is tempting to simply put a limit there, I would like to understand why this happened and how I can prevent it or at least detect when it is going to happen. I try to optimise all queries and set the relevant indices so I really don't understand how mysql decided to make a temp file bigger than the actual database. Any clues?
As per the definition of temp files, why didn't mysql delete those when not needed anymore. You can't have a server fill up randomly just because there's some elevated load on the DB...
Although I already restarted the mysql service in Forge (which had no effect), restarting it with the command line did actually reduce the temp file significantly. So Thanks for that
Another possibility is temp tables are created per connection. If you have 50 open connections using a query that needs a temp table, that's 50 temp tables it creates. It could also have to do with the number of indexes you have, and whether those are used in temp tables.
From my understanding, once MySQL is done with the data, it is freed up again to be used by other tasks; it's just that the file size doesn't reduce. So, in other words, it shouldn't grow every time the server needs temporary space, it just grows when something that requires more space exceeds the current file size.
I really have no idea what would have caused it, especially without knowing what kind of queries are behind handled by the server. I'm like you, I would like to know why it happened, but maybe just keep an eye on it for awhile and make sure it doesn't grow into some monster file again.
So I've been trying and experimenting with indices, rebooting and different queries and still the problem pops up every once in a while. As I really don't have the time to get to know the inner workings of MySql I decided to migrate to Postgres where these sort of problems should not happen.
Thanks for your help guys
