Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've got a query that's stalling, and I have no idea why. It looks sensible, but it runs "forever" (I went and had my lunch and left it running, hadn't finished 20 mins later).

Looking at "SHOW PROCESSLIST", I see my connection has the status "Copying to tmp table".

I looked in my my.cnf, and it looks like temp files should be in /tmp, but there's nothing relating to MySQL in there (there's only 2 directories, both empty).

To help me diagnose what was wrong, I thought I'd have a look at the temporary file it was supposedly copying to. If it was huge, I must have messed up a query. If not, perhaps it's a bug.

But I can't figure out where it's storing them. Is it an in-memory "file"?

I'm running MySQL 5.1.31 on a Ubuntu 9.04 JeOS install.

share|improve this question
add comment

4 Answers

up vote 3 down vote accepted

Many temporary tables can and will be created in-memory. Whether a table will be created in-memory or on-disk is determined by a few factors like size of the table, structure (Does it have BLOBs) and a few more.

I suggest you read up on TMP_TABLE_SIZE and MAX_HEAP_TABL\E_SIZE to gain insight on how temporary tables work.

You can check the output of

mysql> show status like '%tmp%';

if you are actually creating on-disk tables. Note, however, that the actual temporary table file will be of little to no help in determining if there is a problem.

You could also try to EXPLAIN your query and see how well it should perform.

share|improve this answer
1  
Of course, my didn't I think of EXPLAIN?! Many thanks for the informative links, too. –  Drarok Aug 13 '09 at 14:05
add comment

in debian - by default in /tmp [ afair ]

whenever in doubt just run

lsof|grep -i mysqld

mysql's temporary files are deleted just after creation - they show up on lsof, but not in ls ; they do take disk space until file handles are closed.

share|improve this answer
    
Wasn't me, I found this useful, edited it slightly as it was returning PHP's mysql libs too (box is a webserver also). I used: lsof | grep -iE "^mysqld.*?/tmp" –  Drarok Aug 13 '09 at 14:50
    
Nice, thanks. I wonder why my mysql isn't following it's tmpfile settings, odd... –  Kevin Jul 25 '13 at 23:38
add comment

It depends what kind of temporary table. If it says "Copy to temporary table" then it is an in-memory copy. If it says "Copy to temporary table on disk" then it has exceeded the size for in-memory temp tables and is dumping to disk.

In the event of the latter than it will be stored in the location defined by the tmpdir variable. You can find out what that is set to by:

SHOW VARIABLES LIKE 'tmpdir';
share|improve this answer
add comment

There is a mysql variable that indicates where the files will be stored:

mysql> show variables like '%tmp%';

Look for the tmpdir variable. That will tell you where the tmp files are being stored. If a temporary table is less than 'tmp_table_size' it will be memory based rather than disk based.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.