Cacheing before writing sounds like exactly the type of thing that would
explain the problem. However, I'm on FreeBSD (Sorry, didn't give too many
details). Anybody know if there is something similar in to bdflush BSD?
I did run iostat to monitor the the activity during the freeze. It appears
that the writes to the disk about double the usual, but average < 1MB/s.
7200 RPM SCSI Barracuda, seems like it should be able to handle more. CPU is
even 50% idle
Currently I do not have the TMPDIR on a separate physical disk from the
data. This is looking like the most obvious solution. But I'm still
confused -- I had no problems at all 48 hours ago. Now suddenly disk
locking.
Thanks.
>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 <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
