I’m using Magento 2.3 and a production site that has a decently large number of products. The site has been running fine for over a year but in the last week we suddenly have an issue where a runaway query will take up all of the site CPU and, over time, crash the site.
I can show processlist in MySQL and the query looks like this:
INSERT INTO `search_tmp_5f9b11f4bfb569_80230545` SELECT `main_select`.`entity_id`, SUM(score) AS `re`
This query will never finish, and then another one appears, and then more, and eventually the site crashes. I can restart the database, or I can kill the job as root, but they keep coming back.
This question has come up before on Stack Exchange, but I’ve tried the various suggestions to no luck, and also ours was not started by a specific action or change. We do not have Amasty Sorting Module installed.
One thing I did try was the patch described at this Github thread. This was a local edit: changing entity_id to source_id in the file CustomAttributeFilter. That did nothing, in fact it didn’t even change the query that I can see, which surprised me.
I am also having issues getting Elastic Search to work properly with our current setup, which I think is unrelated but which has also been a suggested fix.
At this point I just want my site to function and not crash without regular manual intervention. Can someone advise where I should be looking, or what kind of further research I should do? I’d like to know for sure what pages are even causing this in the first place, and how to duplicate the issue on a different server so I can troubleshoot outside of production.