Eternal query OJS 3.2.1-1 + PostgreSQL

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

1 Like

Hi @Tarcisio_Pereira,

Can you clarify which patches you’ve applied or indexes you’ve created since starting to work with OJS 3.2.1-1?

Regards,
Alec Smecher
Public Knowledge Project Team

Hi @asmecher

I have create no index since we starting to work with OJS 3.2.1-1.
I won’t know how to specify exactly which patches have been applied. But I can say that it was about 3 or 4 at most.

This query is very strange because I did not receive reports of something not working. I noticed this looking at the logs.
As I went down the timeout for 1 minute and 30 seconds, the server ends up killing almost all of those queries. So I have no more performance problems.
Of course, this is not a definitive solution but so far it has not been a major problem for me.

What kind of action in the system would trigger this query?
I imagine it is a case of rewriting the query to have a good performance both on MySql and Postgres.

Regards,
Tarcisio Pereira

Hi @Tarcisio_Pereira,

There is SQL to add two indexes to your 3.2.1-x installation suggested here:

Regards,
Alec Smecher
Public Knowledge Project Team

1 Like