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
|