Bug #3424 Creating full text Index on a table halts all other process.
Submitted: 8 Apr 2004 19:22 Modified: 28 Apr 2004 13:44
Reporter: Padmanabhan Krishnamoorthy Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.0-alpha OS:FreeBSD (FreeBSD 4.8-RELEASE-p13)
Assigned to: CPU Architecture:Any

[8 Apr 2004 19:22] Padmanabhan Krishnamoorthy
Description:
I have a table called "PRODUCT" that has 78 columns and about 4 Million rows. I have one FULLTEXT index based on 4 columns. We search about 20,000 time a day. 

I have to recreate the PRODUCT table every day with updated data. In order to keep the PRODUCT table operational, I load the daily data into another swap table called "PRODUCT_SWAP" (which is identical to PRODUCT table). I drop the FULLTEXT index on PRODUCT_SWAP table, and once all the data is loaded, I recreate the FULLTEXT index. Atlast I swap PRODUCT_SWAP with PRODUCT table.

The problem that I have is, every time I drop/recreate the FULLTEXT index, all other process hangs. All other fulltext searches on PRODUCT table hangs and the state says "Sending data". 
As soon as the FULLTEXT index creation completes, all other process seems to resume, and cleans up slowly. 

The server has upto 40GB free storage space, and has alteast 2MB RAM free. Here is the dump on the TOP command 

136 processes: 2 running, 134 sleeping
CPU states: 20.4% user,  0.0% nice,  1.0% system,  0.2% interrupt, 78.4% idle
Mem: 602M Active, 2753M Inact, 293M Wired, 197M Cache, 199M Buf, 5460K Free
Swap: 1024M Total, 1336K Used, 1023M Free

Any help to resolve the problem is appreciated. 

How to repeat:
Create a table with alteast 5 columns with column type as longtext. Create a FULLTEXT index based on 4 columns. Insert upto 4 Milion rows. Create a similiar table. In the second table, drop the FULLTEXT index, and recreate the same index. At the same time, try to query the fulltext index on the first table.
[19 Apr 2004 18:51] Padmanabhan Krishnamoorthy
The problem continues to exist and any help would be really apprecited.
[28 Apr 2004 13:44] Alexander Keremidarski
Sorry, but the bug system is not the appropriate forum for asking
support questions. Your problem is not the result of a bug.
For a list of more appropriate places to ask for help using MySQL
products, please visit http://www.mysql.com/support/

Thank you for your interest in MySQL.

Additional info:

Full Text engine is known to be high CPU bound, but acording to your top output this is not the case: CPU states: ... 78.4% idle

It looks more like disk I/O problem which is OS and configuration problem, not MySQL one.

Hint: Put datadir and tmpdir on dedicated (and separate) hard disks. Use vmstat, iostat