Michael Griffith wrote:
>
> 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?
Can your tmpdir be set to a different physical drive?
You can't expect to write and sort temporary files without
severly impacting read access to other files on the same drive.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
