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