Any idea what could be causing this query/locking issues?

[jawatkin]

From show processlist;

It's happened twice today (and it locked for 240 seconds and I just killed mysqld and restarted it)... Why is there no WHERE clause on this SELECT? Any idea what would generate such a query? With almost 350k threads, that'd be quite a tmp table....

Query   |   61 | Copying to tmp table | SELECT
                        DISTINCT thread.threadid, thread.forumid, post.userid
                        FROM thread AS thread 

[Jake Bunce]

That query is not familiar. If you can find a specific URL or file in the logs then that will help to identify it.

[jawatkin]

Yikes, my log files are a good 3GB every day. That would be very difficult to track down. So SELECT DISTINCT on the thread table is not a vB standard query? Must be some hack then. Thanks!

ETA: I just duplicated the error myself and figured it out... When I upgraded to 3.7.3 it re-wrote my search.php which was using sphinx (have about 8.5M posts). In overwriting my search.php, it reverted to FULLTEXT search and when searching a VERY common term, it will lock up the database for 5-6 minutes.

[Jake Bunce]

You may want to adjust your MySQL settings for the fulltext search to restrict it further. For example, increase the minimum word length:

http://www.vbulletin.com/docs/html/main/search_type

[jawatkin]

You may want to adjust your MySQL settings for the fulltext search to restrict it further. For example, increase the minimum word length:

http://www.vbulletin.com/docs/html/main/search_type

Thanks Jake, it already is fulltext, sphinx does all the heavy-lifting and allows me to use the search function. With the # of posts I have, there's just no way fulltext can even handle the load for commonly-used words.

[dimopoulos]

I am having the same problem. This query is killing the server.

SELECT
DISTINCT thread.threadid
FROM vb_thread AS thread
INNER JOIN vb_post AS post ON(thread.threadid = post.threadid )
WHERE MATCH(post.title, post.pagetext) AGAINST ('hair')
AND thread.forumid NOT IN (0,26,15,5,13,18,25)
AND thread.forumid IN(27) AND post.visible = 1
LIMIT 200;

We have close to 4M posts and have changed to FULLTEXT when we upgraded to 3.7+

Would reverting back to the old search method make things faster then and rectify the problems we have? Changing the minimum word length is not an option for us since our content has a lot of 3 character length words.

[dimopoulos]

Another question, what is the preferred storage engine? MyISAM or InnoDB? I know MyISAM is faster but InnoDB allows transactions. Is there a preference considering performance?

[Jake Bunce]

The only two options are:

1) Try the other search engine.

2) Disable the search feature.

Unfortunately the current search engines in vBulletin don't scale well on forums with excessive numbers of posts.

I know that MyISAM is recommended, but I am not familiar with the benefits of both table types. You may want to ask this in the server forum:

http://www.vbulletin.com/forum/forumdisplay.php?f=14

[dimopoulos]

Thank you very much for the reply.

[hydn]

What is the solution if already on fulltext search? Disable search? Seriously?