>>>>> ">" == sinisa <sinisa@stripped> writes:
>> Andreas Vierengel writes: Hi, I'm using 3.22.27, compiled
from
>> source with gcc-2.95.2 under Linux Redhat 6.1 my tables are
>> normalised. all selects use index. I have also a lot of
>> updates in one table. About 50-200 per second (depends on
>> daytime)... selects are also 50-200 per second.
>>
>> My updates also use const index an doesn't update the keys,
>> only fields...
>>
>> Normally they are fast as hell, but sometimes an update got
>> stuck in state "locked" (mysqladmin processlist) and stays
here
>> between 30-70 seconds (!). At that time, all incoming
selects
>> are also locked (they are using the same table as the
updates)
>> and the number of connected threads increase to double or
>> triple ! However the "locked" update, did it after a while
and
>> then all runs normal a few seconds. Then another update get
>> stuck, and the whole thing repeats.
>>
>> Since i'm replicating my db-content in "realtime" from a
master
>> server, I have the same updates and nearly the same numbers
of
>> selects (due to external loadbalancing) on different
machines.
>> The behaviour I described only happens on an RedHat6.1,
>> 2.2.14-SMP machine (Dual 500MHz PIII, 768 MB RAM). On my
>> single processor machines, this behaviour will NOT show up
>> (RedHat6.1, 2.2.13, 500MHz PIII, 512 MB RAM). I have nearly
>> zero disk I/O on both machines, so disk should not be the
>> cause.
>>
>> all mysql-variables are the same on the single and
>> dual-machines...
> I have the exactly same problem.
> Can anybody give me a hint more precisely?
Hi!
This is just a though --- so, please don't take this seriousely.
Well, I use dual processor machine (Turbo Linux 4.2 Kernel 2.2.9) and
have very similar problem, though in my case the table gets corrupted. I
have a table with 200,000 records, and it gets about 60-200 queries
(simple UPDATEs, SELECTs and INSERTs) Every 3-40 hours, the table got
corrupted. I tried everything I could think of, and finally I placed
LOW_PRIORITY to the SLQ statesments. It seems that the clause solved the
problem -- almost. (the table got corrupted this morning after 7 days
without the problem)
Now, here's my guessing : as Andreas wrote, the UPDATE blocks the other
queries. If this is the origin of the problem, LOW_PRIORITY could avoid
the problem --- at least reduce the number of the problem sources. I
wonder if the thread manager for dual CPU linux has some kind of bug.
I'll make a single Linux machine, and run MySQL on it next week.
Masayuki
http://www.teglet.co.jp
