I represent Vilnius University, Lithuania. We are running a journal archive using PKP OJS. The archive is fairly big and daily visit/activity load is huge.
For some time now we are facing MySQL overload issue. All CPU cores are hitting 100% load during busy perionds.
Upon investigation of the cause, i have Traced it down to ./classes/issue/Collector.php class, getQueryBuilder() method.
I have spent some time trying to improve database queries by making small changes such as:
// Original
whereRaw("LOWER(iss_t.setting_value) LIKE ?", [$likePattern]);
// Changed
whereRaw("iss_t.setting_value LIKE ?", [$likePattern]);
However that did not help much. Digging deeper i have rewrote bigger parts of the method, which is not ideal, due to file is a core class and would be replaced upon next OJS upgrade.
When rewritten some parts of method, noticed that MySQL server overload drops down, however that causes some functionality loss in admin or frontend part.
Has anyone encountered this problem and if so how did that get solved?
Perhaps that is something creators should look upon?
I was facing similar problem in my OJS instance. (3.4.0.1)
I found that it was caused by the site’s search functionality that uses the classes/search/ArticleSearchDAO.php that has a cast that causes this block of the database
JOIN issues i ON (CAST(i.issue_id AS CHAR(20)) = ps.setting_value AND i.journal_id = s.context_id)
To outline this, I removed the CAST:
JOIN issues i ON (CAST(i.issue_id AS CHAR(20)) = ps.setting_value AND i.journal_id = s.context_id)
This solved the problem for me. I hope it can help you too.
@evici, if you’ve enabled the Recommend Similar Articles plugin, that (along with aggressive search indexing) could be the cause. See e.g. this thread: