mysql Copying to tmp table constantly, crashing server
 

In my mysql processlist i have a bunch of:
Copying to tmp
queries going.

In the /tmp directory, I have the following mysql files:
-rw-rw---- 1 mysql mysql 1.2G Oct 28 11:59 #sql_51aa_0.MYD
-rw-rw---- 1 mysql mysql 1.0K Oct 28 11:58 #sql_51aa_0.MYI
-rw-rw---- 1 mysql mysql 1.2G Oct 28 11:59 #sql_51aa_1.MYD
-rw-rw---- 1 mysql mysql 1.0K Oct 28 11:58 #sql_51aa_1.MYI
-rw-rw---- 1 mysql mysql 1.1G Oct 28 12:00 #sql_51aa_2.MYD
-rw-rw---- 1 mysql mysql 1.0K Oct 28 11:59 #sql_51aa_2.MYI

This is driving the load of my server above 10, I stop mysql and apache, and the copying to tmp just starts right back up again.

My /tmp is only 4GB, and as you can see, there are 3.3GB taken up right now by mysql, it seems when a query finishes the file in /tmp goes away, but then 2 seconds later, process list is showing Copying to tmp again, and there is a huge file in /tmp.

What exactly causes mysql to "Copying to tmp"?

Anyone know a fix for this?

TIA

Copying to tmp table
 

I work for a hosting company. We have a free project where this is a frequent issue. Users just simply forget to add indexes to their tables (or don't even know, they need to). You should inspect the running queries using SHOW PROCESSLIST; or SHOW FULL PROCESSLIST;. In normal operation, queries shouldn't take more than 1-2 seconds to finish even during rush hours.

There might be other reasons, though.