Okay, here's my problem. Running a dual socket woodcrest (4 cores), 2GB of RAM, PHP 5.1.4, MySQL 4.1.20 with fulltext enabled. When I do a search (example: Machine Deck) it will lock the post table so no other concurrent reads (or writes obviously) can be done, thus when a search takes in excess of a minute a ton of queries build up and things start to fall apart shortly thereafter...
(edit - low-priority-updates semi-fixes the issue allow selects to be processed, but all new insert / updates must wait)
Examply of the query that causes the lock (even searching just 1 forum takes a long time):
Is there any sort of remedy? Why would a search lock the post table?
(edit - low-priority-updates semi-fixes the issue allow selects to be processed, but all new insert / updates must wait)
Examply of the query that causes the lock (even searching just 1 forum takes a long time):
Code:
SELECT thread.threadid FROM thread AS thread INNER JOIN post AS post ON ( thread.threadid = post.threadid ) WHERE MATCH ( post.title, post.pagetext ) AGAINST ( '+Machine +Deck' IN BOOLEAN MODE ) AND thread.forumid IN ( 107 )
Comment