List:General Discussion« Previous MessageNext Message »
From:Don MacAskill Date:April 12 2004 11:08pm
Subject:Re: Table lock problem on INSERT with FULLTEXT index?
View as plain text  
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!
> Thanks,
> Don
Table lock problem on INSERT with FULLTEXT index?Don MacAskill7 Apr
  • Re: Table lock problem on INSERT with FULLTEXT index?Don MacAskill13 Apr