[OJS-3.4.0-5] MYSQL query runing long and make que


Hi @jonasraoni
Some queries are running long and create a huge list of around 60 queries and after some time we face out of memory error.


I’d say this is the worst part of the system regarding performance, I’m planning to work on it soon (Improve search framework · Issue #8920 · pkp/pkp-lib · GitHub) :grin:

You can try to apply the changes from this PR (it should improve a bit the situation, but not much):

This is what we’re using for now:

  • Decrease the quality of the search in exchange by performance (this code below should be placed on the file classes/search/ArticleSearchDAO.php)
    Handicapped search code for OJS 3.4 · GitHub

  • Limit the running time of queries (I’ve set to 20s) for the cases that are still problematic by adding a setting to the MySQL configuration:

Jonas Raoni

OK, I will try to replace the code with provided file pkp/pkp-lib#9829 Attempt to improve the search query performance by jonasraoni · Pull Request #4224 · pkp/ojs · GitHub and update you.

I have updated the missing CONSTRAINT and FOREIGN KEY. Let’s see how it works for two-three days.

Okay, if it doesn’t help, there’s still the second approach, which will work :grin:

Jonas Raoni

Can we try replacing this file’s hole code (Handicapped search code for OJS 3.4 · GitHub) with the current code file?

Yes. This will reduce the number of keywords that are searched from “unknown” to 1000 (you can try to increase the number at the line 89, if your server is behaving well, this is just the value that worked for me), so the user will see less results that normal, but it’s better than not seeing anything.

Jonas Raoni

I also advice to setup the max_execution_time, I can’t think about a single query in the system that should take so much time to run, this will ensure that such long queries are killed, and then your database will not suffer so much.

Jonas Raoni