Hi to all, I need assistance with a problem that some of our installations have. They are OJS systems that have been online for many years, lately we are noticing a big slowdown in displaying pages to the point of showing “504 Gateway Time-out” code on the browser. Consulting with our server people have noticed queries that remain running for the user, like this:
sisn_user localhost sisn_db Execute 30835 Statistics SELECT\n o.submission_id,\n MAX(s.context_i
d) AS journal_id,\n MAX(i.date_published) AS i_pub,\n MAX(p.date_published) AS s_pub,\n COUNT() AS count\n
FROM\n submissions s\n JOIN publications p ON (p.publication_id = s.current_publication_id)\n JOI
N publication_settings ps ON (ps.publication_id = p.publication_id AND ps.setting_name=‘issueId’ AND ps.locale=‘’)\n JOIN issues i ON (CAS
T(i.issue_id AS CHAR(20)) = ps.setting_value AND i.journal_id = s.context_id)\n JOIN submission_search_objects o ON (s.submission_id = o.s
ubmission_id)\n JOIN journals j ON j.journal_id = s.context_id\n LEFT JOIN journal_settings js ON j.journal_id = js.journal
_id AND js.setting_name = ‘publishingMode’\n NATURAL JOIN submission_search_object_keywords o0 NATURAL JOIN submission_search_keyword_list
k0, submission_search_object_keywords o1 NATURAL JOIN submission_search_keyword_list k1, submission_search_object_keywords o2 NATURAL JOIN submission_se
arch_keyword_list k2, submission_search_object_keywords o3 NATURAL JOIN submission_search_keyword_list k3, submission_search_object_keywords o4 NATURAL J
OIN submission_search_keyword_list k4, submission_search_object_keywords o5 NATURAL JOIN submission_search_keyword_list k5, submission_search_object_keyw
ords o6 NATURAL JOIN submission_search_keyword_list k6, submission_search_object_keywords o7 NATURAL JOIN submission_search_keyword_list k7, submission_s
earch_object_keywords o8 NATURAL JOIN submission_search_keyword_list k8, submission_search_object_keywords o9 NATURAL JOIN submission_search_keyword_list
k9, submission_search_object_keywords o10 NATURAL JOIN submission_search_keyword_list k10, submission_search_object_keywords o11 NATURAL JOIN submission
_search_keyword_list k11, submission_search_object_keywords o12 NATURAL JOIN submission_search_keyword_list k12, submission_search_object_keywords o13 NA
TURAL JOIN submission_search_keyword_list k13, submission_search_object_keywords o14 NATURAL JOIN submission_search_keyword_list k14, submission_search_o
bject_keywords o15 NATURAL JOIN submission_search_keyword_list k15, submission_search_object_keywords o16 NATURAL JOIN submission_search_keyword_list k16
, submission_search_object_keywords o17 NATURAL JOIN submission_search_keyword_list k17, submission_search_object_keywords o18 NATURAL JOIN submission_se
arch_keyword_list k18, submission_search_object_keywords o19 NATURAL JOIN submission_search_keyword_list k19\n WHERE\n (js.sett
ing_value <> ‘2’ OR\n js.setting_value IS NULL) AND j.enabled = 1 AND\n s.status = 3 AND\n i.published = 1 A
ND k0.keyword_text = ? AND k1.keyword_text = ? AND o0.object_id = o1.object_id AND o0.pos+1 = o1.pos AND k2.keyword_text = ? AND o0.object_id = o2.object
_id AND o0.pos+2 = o2.pos AND k3.keyword_text = ? AND o0.object_id = o3.object_id AND o0.pos+3 = o3.pos AND k4.keyword_text = ? AND o0.object_id = o4.obj
ect_id AND o0.pos+4 = o4.pos AND k5.keyword_text = ? AND o0.object_id = o5.object_id AND o0.pos+5 = o5.pos AND k6.keyword_text = ? AND o0.object_id = o6.
object_id AND o0.pos+6 = o6.pos AND k7.keyword_text = ? AND o0.object_id = o7.object_id AND o0.pos+7 = o7.pos AND k8.keyword_text = ? AND o0.object_id =
o8.object_id AND o0.pos+8 = o8.pos AND k9.keyword_text = ? AND o0.object_id = o9.object_id AND o0.pos+9 = o9.pos AND k10.keyword_text = ? AND o0.object_i
d = o10.object_id AND o0.pos+10 = o10.pos AND k11.keyword_text = ? AND o0.object_id = o11.object_id AND o0.pos+11 = o11.pos AND k12.keyword_text = ? AND
o0.object_id = o12.object_id AND o0.pos+12 = o12.pos AND k13.keyword_text = ? AND o0.object_id = o13.object_id AND o0.pos+13 = o13.pos AND k14.keyword_te
xt = ? AND o0.object_id = o14.object_id AND o0.pos+14 = o14.pos AND k15.keyword_text = ? AND o0.object_id = o15.object_id AND o0.pos+15 = o15.pos AND k16
.keyword_text = ? AND o0.object_id = o16.object_id AND o0.pos+16 = o16.pos AND k17.keyword_text = ? AND o0.object_id = o17.object_id AND o0.pos+17 = o17.
pos AND k18.keyword_text = ? AND o0.object_id = o18.object_id AND o0.pos+18 = o18.pos AND k19.keyword_text = ? AND o0.object_id = o19.object_id AND o0.po
s+19 = o19.pos AND i.journal_id = ?\n GROUP BY o.submission_id\n ORDER BY count DESC\n LIMIT 500 0.000
2641280 sisn_user localhost sisn_db Execute 30826 Statistics SELECT\n o.submission_id,\n MAX(s.context_i
d) AS journal_id,\n MAX(i.date_published) AS i_pub,\n MAX(p.date_published) AS s_pub,\n COUNT() AS count\n
FROM\n submissions s\n JOIN publications p ON (p.publication_id = s.current_publication_id)\n JOI
N publication_settings ps ON (ps.publication_id = p.publication_id AND ps.setting_name=‘issueId’ AND ps.locale=‘’)\n JOIN issues i ON (CAS
T(i.issue_id AS CHAR(20)) = ps.setting_value AND i.journal_id = s.context_id)\n JOIN submission_search_objects o ON (s.submission_id = o.s
ubmission_id)\n JOIN journals j ON j.journal_id = s.context_id\n LEFT JOIN journal_settings js ON j.journal_id = js.journal
_id AND js.setting_name = ‘publishingMode’\n NATURAL JOIN submission_search_object_keywords o0 NATURAL JOIN submission_search_keyword_list
k0, submission_search_object_keywords o1 NATURAL JOIN submission_search_keyword_list k1, submission_search_object_keywords o2 NATURAL JOIN submission_se
arch_keyword_list k2, submission_search_object_keywords o3 NATURAL JOIN submission_search_keyword_list k3, submission_search_object_keywords o4 NATURAL J
OIN submission_search_keyword_list k4, submission_search_object_keywords o5 NATURAL JOIN submission_search_keyword_list k5, submission_search_object_keyw
ords o6 NATURAL JOIN submission_search_keyword_list k6, submission_search_object_keywords o7 NATURAL JOIN submission_search_keyword_list k7, submission_s
earch_object_keywords o8 NATURAL JOIN submission_search_keyword_list k8, submission_search_object_keywords o9 NATURAL JOIN submission_search_keyword_list
k9, submission_search_object_keywords o10 NATURAL JOIN submission_search_keyword_list k10, submission_search_object_keywords o11 NATURAL JOIN submission
_search_keyword_list k11, submission_search_object_keywords o12 NATURAL JOIN submission_search_keyword_list k12, submission_search_object_keywords o13 NA
TURAL JOIN submission_search_keyword_list k13, submission_search_object_keywords o14 NATURAL JOIN submission_search_keyword_list k14, submission_search_o
bject_keywords o15 NATURAL JOIN submission_search_keyword_list k15, submission_search_object_keywords o16 NATURAL JOIN submission_search_keyword_list k16
, submission_search_object_keywords o17 NATURAL JOIN submission_search_keyword_list k17, submission_search_object_keywords o18 NATURAL JOIN submission_se
arch_keyword_list k18, submission_search_object_keywords o19 NATURAL JOIN submission_search_keyword_list k19\n WHERE\n (js.sett
ing_value <> ‘2’ OR\n js.setting_value IS NULL) AND j.enabled = 1 AND\n s.status = 3 AND\n i.published = 1 A
Can you advise how to solve the problem?
Thanks
Bye
Tiziano