Tuning FULLTEXT performance (was Re: Memory upgrade and swap partition size)

Fred puissante at lrc.puissante.com
Tue Sep 26 19:21:02 EDT 2006


This is somewhat related in a roundabout fashion.

Yeah, right!

Anyway, I am having a dilly of a time tuning the FULLTEXT performance with 
MySQL, because I have many FULLTEXT inserts going on at the same time with 
FULLTEXT searches, and every INSERT locks the entire table, causing 
everything else to wait -- and exhaust swap space and crash the server!

I've tuned things to the point the server doesn't crash, but then all the 
available Apache requests are exhausted and queries take *forever* to 
complete.

So, I've had to turn off the fulltext operations during heavy loads, which is 
annoying, but works. The bottleneck is clearly that the table becomes locked 
during updates, and that *all* fulltext selects are blocked for the 
duration.

Does anyone know of a clever way to tune fulltext operations in MySQL? Even 
using INSERT DELAYED on fulltext updates doesn't work, because it just 
queues up lots of inserts than results in locking the tables later.

There are some "brute force" approaches I can take, like saving all the 
fulltext updates for the end of the day and doing them in one batch (which I 
understand is supposed to be faster anyway), but I was hoping for better 
approaches.

Any ideas?



More information about the gnhlug-discuss mailing list