Hi,
After solving the search and OAI problems:
There is a query that runs forever until the postgresql timeout is reached (12 hours in my case).
During this 12 hour period, several queries of these are executed on the server, causing a performance problem.
The first ones end up being solved in 1 hour, but the time goes up according to the quantity. Getting to the point of stopping the database.
The query:
2020-11-25 00:20:48 -03 [37312]: [1-1] user=revistas,db=revistas3 ERROR: canceling statement due to statement timeout
2020-11-25 00:20:48 -03 [37312]: [2-1] user=revistas,db=revistas3 STATEMENT: SELECT a.*, ug.show_title, p.locale,
COALESCE(agl.setting_value, agpl.setting_value) AS author_given,
CASE WHEN agl.setting_value <> ‘’ THEN afl.setting_value ELSE afpl.setting_value END AS author_family
FROM authors a
JOIN user_groups ug ON (a.user_group_id = ug.user_group_id)
JOIN publications p ON (p.publication_id = a.publication_id)
JOIN submissions s ON (s.current_publication_id = p.publication_id)
LEFT JOIN author_settings agl ON (a.author_id = agl.author_id AND agl.setting_name = $1 AND agl.locale = $2)
LEFT JOIN author_settings agpl ON (a.author_id = agpl.author_id AND agpl.setting_name = $3 AND agpl.locale = p.locale)
LEFT JOIN author_settings afl ON (a.author_id = afl.author_id AND afl.setting_name = $4 AND afl.locale = $5)
LEFT JOIN author_settings afpl ON (a.author_id = afpl.author_id AND afpl.setting_name = $6 AND afpl.locale = p.locale)
JOIN (
SELECT
MIN(aa.author_id) as author_id,
CONCAT(
ac.setting_value,
’ ’
,
COALESCE(asg0.setting_value, ‘’), ’ ',
COALESCE(asf0.setting_value, ‘’), ’ ',
COALESCE(SUBSTRING(asa0.setting_value FROM 1 FOR 255), ‘’), ’ ’
) as names
FROM authors aa
JOIN publications pp ON (pp.publication_id = aa.publication_id)
LEFT JOIN publication_settings ppss ON (ppss.publication_id = pp.publication_id)
JOIN submissions ss ON (ss.submission_id = pp.submission_id AND ss.current_publication_id = pp.publication_id AND ss.status = 3)
JOIN journals j ON (ss.context_id = j.journal_id)
JOIN issues i ON (ppss.setting_name = $7 AND CAST(ppss.setting_value AS INTEGER) = i.issue_id AND i.published = 1)
LEFT JOIN author_settings ac ON (ac.author_id = aa.author_id AND ac.setting_name = ‘country’)
LEFT JOIN author_settings asg0 ON (asg0.author_id = aa.author_id AND asg0.setting_name = ‘givenName’ AND asg0.locale = ‘en_US’)
LEFT JOIN author_settings asf0 ON (asf0.author_id = aa.author_id AND asf0.setting_name = ‘familyName’ AND asf0.locale = ‘en_US’)
LEFT JOIN author_settings asa0 ON (asa0.author_id = aa.author_id AND asa0.setting_name = ‘affiliation’ AND asa0.locale = ‘en_US’)
WHERE j.enabled = 1
AND j.journal_id = $8 AND (LOWER(asf0.setting_value) LIKE LOWER( $9))
GROUP BY names
) as t1 ON (t1.author_id = a.author_id)
ORDER BY author_family, author_given LIMIT 25 OFFSET 2650
Something can be done?
Regards,
Tarcisio Pereira