FWIW, I'm still having this problem.
I've completely dropped the table and re-built it from the ground up.
It's a bizarre problem... The table is totally simple. A primary key,
and then three varchar fields. The FULLTEXT index spans the 3 varchar
fields. There are only 6500+ rows, so it's pretty tiny.
A mysqldump of the table is only 442K.
Has no-one else seen anything like this? I can't imagine I'm the only one.
Don MacAskill wrote:
> Hi there,
> I've got a bizarre problem I can't seem to solve. I have a small MyISAM
> table (6533 rows) with a small FULLTEXT index (3 columns per row, an
> average of 1 word per column). When I do an INSERT on the table, many
> times the thread gets stuck perpetually in "Query | update". Future
> reads from other threads, of course, stay "Locked".
> When I try to kill the thread using mysqladmin, the thread sticks around
> for thousands of seconds in "Killed | update" until I finally just have
> to kill mysqld manually and let it restart.
> I've tried REPAIR, ANALYZE, and OPTIMIZE on the table, both using mysql
> and myisamchk. Tried all the options, such as extended and force and
> the like.
> I've even tried wrapping the INSERT with "LOCK TABLE table WRITE" and
> "UNLOCK TABLES". Still no dice, the INSERT still hangs sometimes. I
> haven't bothered trying INSERT DELAYED since LOCK TABLE seems more
> drastic anyway and it didn't work.
> This happens both on 4.0.17 and 4.0.18. This is a RHEL3 WS dual AMD64
> box w/8GB of RAM.
> Strangely enough, I have another MyISAM table with 1,285,742 rows and a
> larger FULLTEXT index, and it never locks this way.
> I have a third MyISAM table with 61,834 rows and a larger FULLTEXT index
> that locked on me once like this last night, but then I ran through and
> updated nearly every row overnight, and inserted a few hundred new ones,
> and it didn't lock at all.
> FWIW, it only seems to hang on INSERT, not on UPDATE. I haven't tried
> DELETE yet.
> I'm stumped. Any help would be greatly appreciated!