files in TMP

MySQL & MariaDB Other MySQL® Questions
1

Hi,

I’m having my /tmp folder filling up with some mysql files created by Percona:

-??? ? ? ? ? ? #sql_3fe3_0.MYD
-??? ? ? ? ? ? #sql_3fe3_0.MYI

Just need to restart mysql, and everything goes smooth.

Can someone help me in this problem?

Thanks.

2

Hi slence;

Those are temporary on-disk tables created by MySQL. This happens when MySQL cannot perform a SELECT statement fully in memory (i.e. if the in-memory table grows larger than tmp_table_size or max_heap_table_size).

Trying monitoring the status variable “Created_tmp_disk_tables”; it likely will be rising steadily given what you are seeing. If you have extra memory available, you can try increasing the tmp_table_size and max_heap_table_size values to help prevent as many temp tables going to disk instead of staying in memory.

Check out the below manual pages for additional information about this:

[url]MySQL :: MySQL 8.0 Reference Manual :: 5.1.10 Server Status Variables
[url]http://dev.mysql.com/doc/refman/5.5/en/temporary-files.html[/url]

-Scott

3

Hi Scoot,

Thanks for your reply. I’ve 7 servers with the same configuration, but this just happen in only 2.

The files lose the information, like i’ve show above. No owner. This is a normal behavior? I don’t have the variable “Created_tmp_disk_tables” , just need to created?

Thanks.

4

Hi slence;

Created_tmp_disk_Tables is a status variable, not a configuration setting. You can see it by running “show global status like ‘Created_tmp_disk_tables’;” from within MySQL. This value shows you a count of how many temporary tables were created on disk. So if this value increases a lot over time, you probably could do some optimizing.

Generally when you are seeing a lot of temporary tables on disk it is due to poorly written SQL statements. That is why one or more of your servers could be showing this but others not; it all depends on the data in the database and the SQL statements being ran against that data (and also the values of tmp_table_size and max_heap_table_size, but it sounds like you have those set consistently). The way to investigate this would be to run a slow query log on the affected servers and look for queries that could be causing the temporary tables to be created, likely ones with sub-queries or complicated WHERE clauses (look for OR’s). You could then test the queries by recording the value of Created_tmp_disk_tables, running the query, and then looking at Created_tmp_disk_tables to see if it increased.

-Scott