[OJS-3.4.0-4] Performance issue OJS-3.3.0-14 to OJS-3.4.0.4

Software Support
, ,
1

Hi @asmecher @jonasraoni
OJS-3.4.0.4
PHP-8.1

I have upgraded from OJS-3.3.0-14 to OJS-3.4.0-4 and after live server we are now facing slow performance issue. Some time out error occurred.

Have similar problem but don’t understand what to do.

select s.* from submissions as s left join publications as po on s.current_publication_id = po.publication_id where s.context_id in (53) and s.status in (3) and (exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER('access') and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = 'title' and LOWER(ps.setting_value) LIKE CONCAT('%', LOWER('access'), '%')) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in ('givenName', 'familyName', 'orcid') and LOWER(aus.setting_value) LIKE CONCAT('%', LOWER('access'), '%')) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER('resources') and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = 'title' and LOWER(ps.setting_value) LIKE CONCAT('%', LOWER('resources'), '%')) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in ('givenName', 'familyName', 'orcid') and LOWER(aus.setting_value) LIKE CONCAT('%', LOWER('resources'), '%')) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER('control') and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = 'title' and LOWER(ps.setting_value) LIKE CONCAT('%', LOWER('control'), '%')) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in ('givenName', 'familyName', 'orcid') and LOWER(aus.setting_value) LIKE CONCAT('%', LOWER('control'), '%')) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER('fisheries') and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = 'title' and LOWER(ps.setting_value) LIKE CONCAT('%', LOWER('fisheries'), '%')) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in ('givenName', 'familyName', 'orcid') and LOWER(aus.setting_value) LIKE CONCAT('%', LOWER('fisheries'), '%')) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER('gender') and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = 'title' and LOWER(ps.setting_value) LIKE CONCAT('%', LOWER('gender'), '%')) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in ('givenName', 'familyName', 'orcid') and LOWER(aus.setting_value) LIKE CONCAT('%', LOWER('gender'), '%')) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER('roles') and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = 'title' and LOWER(ps.setting_value) LIKE CONCAT('%', LOWER('roles'), '%')) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in ('givenName', 'familyName', 'orcid') and LOWER(aus.setting_value) LIKE CONCAT('%', LOWER('roles'), '%')) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER('chain') and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = 'title' and LOWER(ps.setting_value) LIKE CONCAT('%', LOWER('chain'), '%')) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in ('givenName', 'familyName', 'orcid') and LOWER(aus.setting_value) LIKE CONCAT('%', LOWER('chain'), '%'))) and s.submission_id not in (109072) order by (select COUNT(DISTINCT sskl.keyword_id) from submission_search_objects as sso inner join submission_search_object_keywords as ssok on ssok.object_id = sso.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where (sskl.keyword_text = LOWER('access') or sskl.keyword_text = LOWER('resources') or sskl.keyword_text = LOWER('control') or sskl.keyword_text = LOWER('fisheries') or sskl.keyword_text = LOWER('gender') or sskl.keyword_text = LOWER('roles') or sskl.keyword_text = LOWER('chain')) and s.submission_id = sso.submission_id) desc, (select COUNT(0) from submission_search_objects as sso inner join submission_search_object_keywords as ssok on ssok.object_id = sso.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where (sskl.keyword_text = LOWER('access') or sskl.keyword_text = LOWER('resources') or sskl.keyword_text = LOWER('control') or sskl.keyword_text = LOWER('fisheries') or sskl.keyword_text = LOWER('gender') or sskl.keyword_text = LOWER('roles') or sskl.keyword_text = LOWER('chain')) and s.submission_id = sso.submission_id) desc limit 10 offset 0 |

ojs34_db process
ojs34_db process1351×442 17.5 KB

I have checked the MYSQL process, a lot of the queries mentioned above are executing, but not completing.

I have not uploaded any plugin and any kind of data, just upgraded and uploaded to server but after some time the above problem has appeared.

Please provide solution.

1 Like
2

Hi @shantanusingh!

I’m very interested on these performance issues :slight_smile:

I’ve tried to optimize these queries, which are really heavy, and I got nice improvements on our servers. But I see they are still problematic for you.

  • What’s your MySQL version?
  • Is the recommendBySimilarity plugin active?
  • Do you have SSD disks on your server?
  • Is there any possibility to get access to your database in private?

If you can’t provide access to your database, then a database dump only with the structure (using the --no-data flag) and some statistics can be useful. About the statistics, the query below should be enough.

Tunning the database might bring great outcomes (especially increasing the memory limit for temporary queries).

SELECT
(SELECT COUNT(0) FROM submissions) AS submissions,
(SELECT COUNT(0) FROM publications) AS publications,
(SELECT COUNT(0) FROM publication_settings) AS publication_settings,
(SELECT COUNT(0) FROM authors) AS authors,
(SELECT COUNT(0) FROM author_settings) AS author_settings,
(SELECT COUNT(0) FROM submission_search_objects) AS submission_search_objects,
(SELECT COUNT(0) FROM submission_search_keyword_list) AS submission_search_keyword_list,
(SELECT COUNT(0) FROM submission_search_object_keywords) AS submission_search_object_keywords

In general, this is a problem that we need to address on our side, so there’s an issue for it here: Improve search framework · Issue #8920 · pkp/pkp-lib · GitHub

Best,
Jonas Raoni

4

Hi @jonasraoni Thanks for reply

Around 150 queries as mentioned above and more are standing in a que being incomplete and keep on adding consequently which makes the server slow down and after reaching the max limit it results in time out connection.

  • MYSQL 8.0
  • recommendBySimilarity not active
  • I think SSD disk is not available on the server. may be it needs conformation.
  • I am providing database schema of the upgraded OJS-3.4.0-4.

ojs34_schema
ojs34_schema1352×246 6.65 KB

Due to this problem I prefer to stay on OJS-3.3.0-14 and wait for permanent solution.

5

Hmm, I see your database is much larger than what I had here :grin:

If you still have the installation alive, you can try to disable the sorting of results by ranking, just to see if it solves the issue. If yes, it could be a potential workaround (i.e. simplify the query for huge installations).

On the file lib/pkp/classes/submission/Collector.php, you can comment the line 338 and the 341, basically replace this:

if (!$keywords->count()) {
    $q->orderBy('s.date_submitted', $this->orderDirection);
    break;
}

By this

//if (!$keywords->count()) {
    $q->orderBy('s.date_submitted', $this->orderDirection);
    break;
//}

If it solves, it would be a simple way for us to address this issue temporarily.

Best,
Jonas Raoni

2 Likes
6

Ok, I am checking and will update you within 1 hours.

7

Thank you @jonasraoni for quick solution. Now the MYSQL process in executing normally and I hope this will not trouble me again. :grin:

ojs34_db process
ojs34_db process1352×703 22.9 KB

8

Great! It will decrease the quality of the results (good hits will be mixed with not so great ones), but it’s better than having no results at all…

That’s a good reminder to restart working on the indexing/searching issues :slight_smile:

Best,
Jonas Raoni

1 Like