Change to FULL TEXT search - queries crash my site

[hilary]

Hello,

We have vbulletin 3.8.6
Posts: 4,679,968, Members: 73,762
There is a web server and a separate database server.

We are running the default vbulletin search, which is slow. We'd like to speed it up and therefore implement FULLTEXT search.

To do this we are supposed to run the queries
ALTER TABLE vb3_post ADD FULLTEXT INDEX (title,pagetext);
ALTER TABLE vb3_thread ADD FULLTEXT INDEX (title);

I duplicated my database to run these queries in case there was a problem.
I went to the command line and in mysql ran the first query. It ran for 20 minutes and then the database server was locked up with 'too many connections.' I canceled the query and then because the server was still saying 'too many connections', I restarted mysql and it was fine.

Is there a way to run the queries so that they don't use up so much resources?
Will turning off vbulletin make a difference to the duration of the query?
How long do you think the query would take?
Would it help if I doubled my database server's memory? Its currently 4GB.

[Zachery]

It is going to take a bit to run the query. Any mysql writes could slow it down a bit.

If you can allocate more resources to mysql that would help.

[hilary]

Our forum is constantly used, so I don't like to turn it off. Do you think that if I did turn it off, it would speed up the query?
How much resources do I need to run this query? If I had 8GB would that be enough?

[hilary]

Hi, I bit the bullet and turned the forum off and ran the queries manually on the command line in SSH.
I had 5,000,000 rows in the vb3_post table and it took 20 minutes to complete the query.
I had only 300,000 rows in the vb3_thread table and this query only took 10 seconds.

Once this was done I expected that Fulltext search would start functioning but it didn't, there was something else I needed to do (not written in the help files?) which I got from http://www.vbulletin.com/forum/showt...ge-it-manually

I had to change the value of the row 'fulltextsearch' in the vb3_setting table to '1'.
UPDATE `parentsp_vbulletin`.`vb3_setting` SET `value` = '1' WHERE `vb3_setting`.`varname` = 'fulltextsearch';

Then I had to rebuild the settings cache for this to take affect. To do this, I changed any value in the vbulletin > vbulletin options > general settings, in the vbulletin admin cp.

Then go to vbulletin options > search type and it should now say 'Your forum is currently using the Fulltext search engine.'