Description of issue or problem I’m having:
My production server is experimenting a high usage using OJS 3.3.0.8.
issue seems to be present,nt since whe migrate to 3.3.0.8
Steps I took leading up to the issue:
What I tried to resolve the issue:
Chek if engine is INNODB
Do some mysql engine optimization on table_definition_cache, tmp_table_size and, max_heap_table_size, join_buffer_size
Check if SQL queries are slow on my test environnement => they are slow on the test environnement
Application Version - e.g., OJS 3.1.2:
3.3.0.8
5 journal installation
Additional information, such as screenshots and error log messages if applicable:
A lot of queries in state “Sending Data”
with 2 kind of queries
Queries 1
`SELECT a.*, ug.show_title, s.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 = 'givenName' AND agl.locale = 'fr_FR')
LEFT JOIN author_settings agpl ON (a.author_id = agpl.author_id AND agpl.setting_name = 'givenName' AND agpl.locale = s.locale)
LEFT JOIN author_settings afl ON (a.author_id = afl.author_id AND afl.setting_name = 'familyName' AND afl.locale = 'fr_FR')
LEFT JOIN author_settings afpl ON (a.author_id = afpl.author_id AND afpl.setting_name = 'familyName' AND afpl.locale = s.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), ''), ' '
,
COALESCE(asg1.setting_value, ''), ' ',
COALESCE(asf1.setting_value, ''), ' ',
COALESCE(SUBSTRING(asa1.setting_value FROM 1 FOR 255), ''), ' '
,
COALESCE(asg2.setting_value, ''), ' ',
COALESCE(asf2.setting_value, ''), ' ',
COALESCE(SUBSTRING(asa2.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 = 'issueId' AND ppss.setting_value = CAST(i.issue_id AS CHAR(20)) 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')
LEFT JOIN author_settings asg1 ON (asg1.author_id = aa.author_id AND asg1.setting_name = 'givenName' AND asg1.locale = 'es_ES')
LEFT JOIN author_settings asf1 ON (asf1.author_id = aa.author_id AND asf1.setting_name = 'familyName' AND asf1.locale = 'es_ES')
LEFT JOIN author_settings asa1 ON (asa1.author_id = aa.author_id AND asa1.setting_name = 'affiliation' AND asa1.locale = 'es_ES')
LEFT JOIN author_settings asg2 ON (asg2.author_id = aa.author_id AND asg2.setting_name = 'givenName' AND asg2.locale = 'fr_FR')
LEFT JOIN author_settings asf2 ON (asf2.author_id = aa.author_id AND asf2.setting_name = 'familyName' AND asf2.locale = 'fr_FR')
LEFT JOIN author_settings asa2 ON (asa2.author_id = aa.author_id AND asa2.setting_name = 'affiliation' AND asa2.locale = 'fr_FR')
WHERE j.enabled = 1
AND j.journal_id = 5 AND (LOWER(asf0.setting_value) LIKE LOWER('%') OR LOWER(asf1.setting_value) LIKE LOWER('%') OR LOWER(asf2.setting_value) LIKE LOWER('%'))
GROUP BY names
) as t1 ON (t1.author_id = a.author_id)
ORDER BY author_family, author_given LIMIT 30 OFFSET 3330`
or
Queries 2
`SELECT a.*, ug.show_title, s.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 = 'givenName' AND agl.locale = 'fr_FR')
LEFT JOIN author_settings agpl ON (a.author_id = agpl.author_id AND agpl.setting_name = 'givenName' AND agpl.locale = s.locale)
LEFT JOIN author_settings afl ON (a.author_id = afl.author_id AND afl.setting_name = 'familyName' AND afl.locale = 'fr_FR')
LEFT JOIN author_settings afpl ON (a.author_id = afpl.author_id AND afpl.setting_name = 'familyName' AND afpl.locale = s.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), ''), ' '
,
COALESCE(asg1.setting_value, ''), ' ',
COALESCE(asf1.setting_value, ''), ' ',
COALESCE(SUBSTRING(asa1.setting_value FROM 1 FOR 255), ''), ' '
,
COALESCE(asg2.setting_value, ''), ' ',
COALESCE(asf2.setting_value, ''), ' ',
COALESCE(SUBSTRING(asa2.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 = 'issueId' AND ppss.setting_value = CAST(i.issue_id AS CHAR(20)) 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')
LEFT JOIN author_settings asg1 ON (asg1.author_id = aa.author_id AND asg1.setting_name = 'givenName' AND asg1.locale = 'es_ES')
LEFT JOIN author_settings asf1 ON (asf1.author_id = aa.author_id AND asf1.setting_name = 'familyName' AND asf1.locale = 'es_ES')
LEFT JOIN author_settings asa1 ON (asa1.author_id = aa.author_id AND asa1.setting_name = 'affiliation' AND asa1.locale = 'es_ES')
LEFT JOIN author_settings asg2 ON (asg2.author_id = aa.author_id AND asg2.setting_name = 'givenName' AND asg2.locale = 'fr_FR')
LEFT JOIN author_settings asf2 ON (asf2.author_id = aa.author_id AND asf2.setting_name = 'familyName' AND asf2.locale = 'fr_FR')
LEFT JOIN author_settings asa2 ON (asa2.author_id = aa.author_id AND asa2.setting_name = 'affiliation' AND asa2.locale = 'fr_FR')
WHERE j.enabled = 1
AND j.journal_id = 5 AND (LOWER(asf0.setting_value) LIKE LOWER('%') OR LOWER(asf1.setting_value) LIKE LOWER('%') OR LOWER(asf2.setting_value) LIKE LOWER('%'))
GROUP BY names
) as t1 ON (t1.author_id = a.author_id)
ORDER BY author_family, author_given LIMIT 30 OFFSET 3390`