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

[Drew Van Zandt]

Master --> slave with all inserts on the master copy?  Lets you at
least split the load onto 2 servers...

--DTVZ

On 9/26/06, Fred <puissante at lrc.puissante.com> wrote:
> 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?
> _______________________________________________
> gnhlug-discuss mailing list
> gnhlug-discuss at mail.gnhlug.org
> http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss
>