Anormal database resources consum

We are experiencing a problem related to excessive consumption of database resources. After analysis, our DBA realizes that the problem comes from above query. (after some analysis, we think that are keywords are concateneted and create a query in runtime).

I would like know if there are more people with this same problem and, if possible, what the resolution for this problem.

The operating environment we are using is:
OJS version 3.3.0.17
Memory: 12GB
Processors: 8 colors
Operating System: Ubuntu 22.04
Database: Postgres 15
PHP: 7.4

##############################
SELECT COUNT() AS row_count FROM (select “s”. from “submissions” as “s” where “s”.“context_id” = $1 and “s”.“status” in ($2) 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($3) and

“s”.“submission_id” = “sso”.“submission_id”) 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($4) and “s”.“submission_id” = “sso”.“submission_id”) 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($5) and “s”.“submission_id” = “sso”.“submission_id”) 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($6)

and “s”.“submission_id” = “sso”.“submission_id”) 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($7) and “s”.“submission_id” = “sso”.“submission_id”) 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($8) and

“s”.“submission_id” = “sso”.“submission_id”)) and “s”.“submission_id” <> 0 group by “s”.“submission_id”

order by (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($10) or “sskl”.“keyword_text” = LOWER($11) or “sskl”.“keyword_text” = LOWER($12) or “sskl”.“keyword_text” = LOWER($13) or

“sskl”.“keyword_text” = LOWER($14) or “sskl”.“keyword_text” = LOWER($15)) and “s”.“submission_id” = “sso”.“submission_id”) desc,

(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($16) or “sskl”.“keyword_text” = LOWER($17) or “sskl”.“keyword_text” = LOWER($18) or “sskl”.“keyword_text” = LOWER($19) or

“sskl”.“keyword_text” = LOWER($20) or “sskl”.“keyword_text” = LOWER($21)) and “s”.“submission_id” = “sso”.“submission_id”) desc) AS count_subquery
##############################

Hi @richard_valdivia,

These queries are coming from the full-text search. I’d suggest reviewing your access log to see if there is abnormal search behaviour there. If your collection has grown to a size that the built-in search engine seemingly can’t support, you might consider trying the Lucene plugin.

Regards,
Alec Smecher
Public Knowledge Project Team

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