Good news and bad news.
Since last night I am glad to say that indexing the search doesn’t appear to be causing any 500 errors anymore. The culprit was due to the fact that we would ask for 250 posts each query and this caused some locking issues with MySQL.
A “locking issue” is caused when we’re trying to read a part of the database which is being written to, as a result that read query is blocked or stopped and caused to wait before the writes are submitted.
By reducing the number of posts to read at each query to 10, we’ve decreased locking contention and improved smoothness in indexing the search.
The bad news?
Well, the 500 errors are still occurring and we need to see what queries is now causing it. The worse case that we could see is that our resources on both servers are maxed out already and we need more capacity for PHP and MySQL to grow.
