Not the answer you need?
Register and ask your own question!

files in TMP

slenceslence ContributorInactive User Role Beginner
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.

Comments

  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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:

    http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Created_tmp_disk_tables
    http://dev.mysql.com/doc/refman/5.5/en/temporary-files.html

    -Scott
  • slenceslence Contributor Inactive User Role Beginner
    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.
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.