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...
>
> Can anybody give me a hint, how to track down the problem ??
>
> --Andy
HI!
If you get locked state for any UPDATE, that means that you do have a
slow query somewhere reading the same table that the said UPDATE is
trying to update !!
You should try to pinpoint a query that takes so much time and thereby
locks other threads accessing the same table.
Regards,
Sinisa
+----------------------------------------------------------------------+
| TcX ____ __ _____ _____ ___ == mysql@stripped |
| /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic |
| /*/ /*/ /*/ \*\_ |*| |*||*| mailto:sinisa@stripped |
| /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaka, Cyprus |
| /*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____ |
| ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^ |
| /*/ \*\ Developers Team |
+----------------------------------------------------------------------+
