OJS [3.4.0.4] Problem with database?

Software Support
1

Hello to all.

We’re recently gettings warnings from our hosting telling us there are many queries to our ddbb and consuming a lot of resources. I saw on this forum a similar thread (here) and I have tried to edit the robots.txt file, but it seems is not working.

We have OJS 3.4.0.4 installed and the ddbb is MariaDB (10.5).

A recent example:

Summary

Time: 240520 15:28:15

User@Host: ojsaha_new[ojsaha_new] @ localhost

Thread_id: 790835 Schema: ojsaha_new QC_hit: No

Query_time: 27.825757 Lock_time: 0.000115 Rows_sent: 10 Rows_examined: 24917541

Rows_affected: 0 Bytes_sent: 1517

SET timestamp=1716211695;
select s.* from submissions as s left join publications as po on s.current_publication_id = po.publication_id where s.context_id in (1) 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(‘petr�leo’) 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(‘petr�leo’), ‘%’)) 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(‘petr�leo’), ‘%’)) 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(‘pol�ticas’) 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(‘pol�ticas’), ‘%’)) 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(‘pol�ticas’), ‘%’)) 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(‘conflicto’) 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(‘conflicto’), ‘%’)) 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(‘conflicto’), ‘%’)) 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(‘nigeria’) 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(‘nigeria’), ‘%’)) 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(‘nigeria’), ‘%’)) 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(‘sud�n’) 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(‘sud�n’), ‘%’)) 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(‘sud�n’), ‘%’)) 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(‘del’) 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(‘del’), ‘%’)) 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(‘del’), ‘%’)) 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(‘sur’) 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(‘sur’), ‘%’)) 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(‘sur’), ‘%’))) and s.submission_id not in (1669) 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(‘petr�leo’) or sskl.keyword_text = LOWER(‘pol�ticas’) or sskl.keyword_text = LOWER(‘conflicto’) or sskl.keyword_text = LOWER(‘nigeria’) or sskl.keyword_text = LOWER(‘sud�n’) or sskl.keyword_text = LOWER(‘del’) or sskl.keyword_text = LOWER(‘sur’)) 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(‘petr�leo’) or sskl.keyword_text = LOWER(‘pol�ticas’) or sskl.keyword_text = LOWER(‘conflicto’) or sskl.keyword_text = LOWER(‘nigeria’) or sskl.keyword_text = LOWER(‘sud�n’) or sskl.keyword_text = LOWER(‘del’) or sskl.keyword_text = LOWER(‘sur’)) and s.submission_id = sso.submission_id) desc limit 10 offset 0;

Time: 240520 15:28:24

User@Host: ojsaha_new[ojsaha_new] @ localhost

Thread_id: 791007 Schema: ojsaha_new QC_hit: No

Query_time: 5.685415 Lock_time: 0.000211 Rows_sent: 10 Rows_examined: 3397544

Rows_affected: 0 Bytes_sent: 1517

SET timestamp=1716211704;
select s.* from submissions as s left join publications as po on s.current_publication_id = po.publication_id where s.context_id in (1) 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(‘autoritarismo’) 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(‘autoritarismo’), ‘%’)) 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(‘autoritarismo’), ‘%’)) 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(‘burocr�tico’) 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(‘burocr�tico’), ‘%’)) 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(‘burocr�tico’), ‘%’)) 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(‘conservadurismo’) 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(‘conservadurismo’), ‘%’)) 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(‘conservadurismo’), ‘%’)) 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(‘general’) 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(‘general’), ‘%’)) 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(‘general’), ‘%’)) 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(‘juan’) 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(‘juan’), ‘%’)) 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(‘juan’), ‘%’)) 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(‘carlos’) 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(‘carlos’), ‘%’)) 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(‘carlos’), ‘%’)) 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(‘ongania’) 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(‘ongania’), ‘%’)) 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(‘ongania’), ‘%’)) 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(‘revoluci�n’) 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(‘revoluci�n’), ‘%’)) 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(‘revoluci�n’), ‘%’)) 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(‘argentina’) 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(‘argentina’), ‘%’)) 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(‘argentina’), ‘%’)) 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(‘nacionalista’) 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(‘nacionalista’), ‘%’)) 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(‘nacionalista’), ‘%’)) 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(‘cat�lico’) 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(‘cat�lico’), ‘%’)) 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(‘cat�lico’), ‘%’))) and s.submission_id not in (268) 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(‘autoritarismo’) or sskl.keyword_text = LOWER(‘burocr�tico’) or sskl.keyword_text = LOWER(‘conservadurismo’) or sskl.keyword_text = LOWER(‘general’) or sskl.keyword_text = LOWER(‘juan’) or sskl.keyword_text = LOWER(‘carlos’) or sskl.keyword_text = LOWER(‘ongania’) or sskl.keyword_text = LOWER(‘revoluci�n’) or sskl.keyword_text = LOWER(‘argentina’) or sskl.keyword_text = LOWER(‘nacionalista’) or sskl.keyword_text = LOWER(‘cat�lico’)) 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(‘autoritarismo’) or sskl.keyword_text = LOWER(‘burocr�tico’) or sskl.keyword_text = LOWER(‘conservadurismo’) or sskl.keyword_text = LOWER(‘general’) or sskl.keyword_text = LOWER(‘juan’) or sskl.keyword_text = LOWER(‘carlos’) or sskl.keyword_text = LOWER(‘ongania’) or sskl.keyword_text = LOWER(‘revoluci�n’) or sskl.keyword_text = LOWER(‘argentina’) or sskl.keyword_text = LOWER(‘nacionalista’) or sskl.keyword_text = LOWER(‘cat�lico’)) and s.submission_id = sso.submission_id) desc limit 10 offset 0;

Thank you very much in advance.

Help Needed to Identify and Optimize Heavy SQL Query in OJS 3.4.0.5
Query SQL se quedan en colgados y satura el servidor SQL
SQL query uses all the CPU and OJS stops responding
2

Hi, I am having the same issue. Did you manage to solve it?

4

Hi all,

The query you’ve quoted (involving the submission_search_object_keywords, submission_search_objects, and submission_search_keyword_list) is a full-text search. You can find requests that invoke this query by looking through your access log for /search in the request URI.

These are heavyweight queries by nature, and can easily overwhelm a typical MySQL database if you have a lot of content or are receiving heavy search activity.

To resolve this, you could…

  • Look at your search traffic and throttle any unusual activity, using robots.txt (for well-behaved indexers) or something like fail2ban for users that are not well-behaved
  • Look into using the Lucene/Solr search engine replacement for OJS – there is a plugin for that in the Plugin Gallery, but you’ll need to be able to run the Java-based Lucene/Solr toolset. This is capable of scaling larger than the built-in MySQL fulltext search.

Regards,
Alec Smecher
Public Knowledge Project Team

6

Hello @Orlando_Antonio_Guti !

In our case it was not related to bots. Apparently, the query problem was caused by the “Recommend Similar Articles” plugin, so I’d recommend check this if the robots.txt does not work.

I hope this helps.

Best regards,
Alberto.

7

Hi @Almar93,

Thanks for the follow-up! In fact this is flagged for future improvement:

Regards,
Alec Smecher
Public Knowledge Project Team

2 Likes
8

This topic was automatically closed after 10 days. New replies are no longer allowed.