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

  • This smells like a dup of stackoverflow.com/questions/51430105/… – Rick James Jul 20 '18 at 14:27
  • Additional information request, please. Post on pastebin.com or here. RAM size of your MySQL Host server A) complete (not edited) my.cnf-ini Text results of: B) SHOW GLOBAL STATUS; after minimum of 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) complete MySQLTuner report Optional very helpful information, if available includes - top for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, for server tuning analysis. – Wilson Hauck Jul 20 '18 at 18:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.