Re: Problem with insert delayed thread. Perhaps a linuxthread problem ?

[Steven Roussey]

--------------------
I want to sum up here the problems I encounter with insert delayed
thread.
From time to time (quite rare, but I reproduced the problem on several
servers), the insert delayed thread seems to "lock" the table. (all
process
are in "waiting for the table" state)
Show processlist looks like :
--------------------

As my use of MySQL (in terms of queries per second) has increased over
the years, I'm come to find all sorts of issues with insert delayed.
Including this one you mention (which is a variation of other
incarnations) which was in 3.23.x. Eventually we rewrote things to not
use or need it and haven't looked back.

Debugging will not be fun. As I remember, it only occurred over a
certain QPS threshold, which means full debugging will slow the server
to a point that it won't happen. Yuck!

But the fact that you are not a MySQL developer gives you a great
opportunity to code audit how it is done. I find it extremely difficult
to track down a rare bug in my own code, and I doubt that experience is
limited to myself. If you plan to do such an audit, God bless you. 

Not many people use the feature at a high QPS to find it, so it likely
is not the highest of priorities. 

If you go about reading the code for a weekend, here are some tips off
the top of my head:

o Find the code that handles inserts that are over delayed_queue_size
for any one insert delayed thread. I can't remember if it creates a new
thread (I don't think so) or holds the query until the delayed thread is
done or has room or something. I think it does create a new delayed
insert thread if the table is ALTERed or FLUSHed (maybe other things
like KILL, CHECK, REPAIR, OPTIMIZE?).

o Check how the delayed insert code deals with delayed_insert_limit in
order to let a SELECT through. Might also let a normal INSERT through
(or UPDATE/REPLACE), but I don't know.

o Check the code that closes the thread after delayed_insert_timeout
seconds.

o Check the code for ALTER, FLUSH, CHECK, REPAIR, OPTIMIZE, etc.

o Check how it is logged (update log, binlog, slowlog, etc.)

Also,

o Do you insert into that table with non insert delayed queries?

o Do you have cron scripts or something that might do something with the
tables on an event or schedule?

o Does the problem occur with no other operations done on that table (no
selects or normal inserts).

As you can see, the opportunity for a race condition to exist is pretty
good. Heavy load tends to find these. If only heavy load made it easy to
debug these....

Sincerely,
Steven Roussey
http://Network54.com/?pp=e