>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?
This could also be something to do with the way bdflush works on linux (I assume you
are using linux?). I have noticed that with huge writes, especially systems with lots
of memory, will cause write contention. The way the process works is to cache writes
in memory till a threshold is reached of cache memory. This threshold is usually 40%
of cache memory, which could be rather HUGE on systems with lots of memory. On my 1gig
mem system, this yeilds a total of 300meg that can be cached before bdflush wakes up
to start writing to disk. When it does eventually start, contention! This is easy to
hit is a very short time on a very fast system.
Look here for more info, particularly chapter 2. You want to modify the nfract
parameter of /proc/sys/vm/bdflush. The web page is
http://www.ieee.calpoly.edu/LUG/LinuxWorld00/expo/track-1/session-62/
Maybe this kind of info should find itself into the manual under tuning?
--
Richard Ellerbrock
[EMAIL PROTECTED]
---------------------------------------------------------------------
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 <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
