Locked Threads

[Peter Bryant]

First, an update. After waiting a few days for a FULLTEXT index to build on a 3.23.5* database, I updated the database server to MySQL 4.0.10 on advice from Sergei Golubchik on this list: http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:132146:200302:cdpckkcecgnggcboabda

The index rebuild took 'only' several hours, then. So, that problem is resolved.

I did had to run it twice after running out of /tmp space (I set TMPDIR to a partition with mroe space the second time around). I'd be curious what MySQL is using the /tmp space for, but that is incidental to this post.

After the index was built, I started using the table.

Now onto the problem.

I found that there were 8 threads with a "Sending data" status and a long list of sql threads with a status of "Locked" (doing deletes, selects and replaces). All threads looking solely at the table with the fulltext index.

Query 1282 Sending data SELECT MESSAGE_OID FROM IP_INDEXED_WORDS
Query 1259 Sending data SELECT MESSAGE_OID FROM IP_INDEXED_WORDS
Query 1259 Sending data SELECT MESSAGE_OID FROM IP_INDEXED_WORDS
Query 1254 Sending data SELECT MESSAGE_OID FROM IP_INDEXED_WORDS
Query 1206 Sending data SELECT MESSAGE_OID FROM IP_INDEXED_WORDS
Query 1167 Sending data SELECT MESSAGE_OID FROM IP_INDEXED_WORDS
Query 1049 Sending data SELECT MESSAGE_OID FROM IP_INDEXED_WORDS
Query 957 Sending data SELECT MESSAGE_OID FROM IP_INDEXED_WORDS
Query 870 Locked REPLACE INTO IP_INDEXED_WORDS ( MESSAGE_BODY,SUBJECT,MESSAGE_OID,SITE_OID,DATETIME_POSTED,AUTHOR_OID
Query 868 Locked SELECT MESSAGE_OID FROM IP_INDEXED_WORDS
Query 862 Locked SELECT MESSAGE_OID FROM IP_INDEXED_WORDS
Query 858 Locked SELECT MESSAGE_OID FROM IP_INDEXED_WORDS
Query 856 Locked DELETE FROM IP_INDEXED_WORDS
Query 847 Locked SELECT MESSAGE_OID FROM IP_INDEXED_WORDS

I'm sure the problem is exacerbated by the fact that the selects are taking too long to run. I'm working to resolve that.

What is the reason for most of the threads having a locked status? I understood MySQL could insert rows into tables that were being used in selects.

I notice that innodb_thread_concurrency is 8. Is it a coincidence there were that many threads "Sending data". Obviously with a fulltext index, this is not an innodby table.

Since some of the threads were doing replace SQL I had a look at the "insert delayed" command. However, it seems from the manual that this won't necessarily help much with MyISAM tables.

Any help appreciated,

Regards, Peter Bryant

_________________________________________________________________
Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail


---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php