Earlier I posted a message about SHOW PROCESSLIST reporting queries "locked" whenever
another thread is "Copying to tmp table"
After many more hours of diagnosis, the actual problem is somewhat different:
MySQL does report other processes as locked. But they do not wait until the temp table
is completely written to execute. For example:
Query #1: SELECT * FROM a JOIN B WHERE.... Status: copying to tmp table
Query #2: UPDATE C SET x=x+1 WHERE...... Status: locked
Query #3: UPDATE C SET x=x+1 WHERE...... Status: locked
With these queries, Query #2 does eventually finish even if #1 does not. The problem
is that as soon as query #1 is issued and starts copying to temp table, everything
else slows down to almost a crawl. It appears as if #3,#4,#5.... are permanently
locked because it is so slow. For practical purposes they are locked until query #1 is
done.
I have hundreds more locked queries identical to #2 & #3. Although mysqladmin reports
all as locked, it is most likely one of them that is locking the others and not #1.
The problem is that it is extremely slow.
MySQL acts as if Query#1 gets some kind of priority. As if the thread scheduling gives
almost no time to any other thread. At least no disk time.
What would cause writing to a temp table to preempt other disk operations?
Can it be stopped?
