I have some large MySQL databases that have full text indices, and after a recent power outage, they are noticeably slower, and even failing on the larger ones. Previously, these queries were a couple seconds at most on the largest databases (>1B rows), by utilizing MATCH AGAINST and limiting results. Now, they time out with "FTS query exceeds result cache limit". This error does not occur on smaller databases, but they are still slow. Result sets are minimal, and there's no way that ~200 characters worth of result data should be hitting the cache limit which is much much larger.
Profiling shows 99% of time spent on "FULLTEXT Initialization" - best reference I can find is here. My DBs are read only, so they have no writes or deletions, so that didn't help.
I can't find any further information on FULLTEXT Initialization, or how to profile that state to figure out why it's so slow.
I also have two server that are essentially clones, and both suffer from this issue after the power outage.
I've tried everything I can think of, short of totally deleting the databases and reloading data, which would take a very long time. I'm at a complete loss as to why this would be. I've tried things such as: re-install mysqlserver, analyze, check, recreating indices, explain, and profiling, without learning much useful information