100 CPU usage using OJS 3.3.0.8

tdolley · February 22, 2022, 1:57pm

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`

Francisco_Javier_Gar · May 11, 2022, 3:56pm

I have the same problem

Any solution?

Vitaliy · May 11, 2022, 4:45pm

Hi @tdolley,

Sorry for the late reply. Is this problem still relevant?

Vitaliy · May 11, 2022, 4:46pm

Hi @Francisco_Javier_Gar,

Can you open a new thread here on the forum with more details on the problem and steps taken to debug it?

Francisco_Javier_Gar · May 11, 2022, 5:02pm

Update to version 3.3.0_8 and load times increased.

CPU usage is at 100%

The site is not functional due to the delay in loading the pages

Actualize a la version 3.3.0_8 y los tiempo de carga se elevaron mucho.
El consumo de la CPU esta al 100%

El sitio no es funcional por la demora en la carga de las paginas

tdolley · May 12, 2022, 7:33am

@Francisco_Javier_Gar, no solution for the moment.
@Vitaliy , yes the problem is still relevenat

Vitaliy · May 12, 2022, 11:43am

Can you check the logs for the associated requests? Specifically, regarding the SQL query its searchAuthorIndex page (.../search/authors/[query parameters]). How many such and overall requests are you getting?

Francisco_Javier_Gar · May 12, 2022, 1:22pm

@Vitaliy muchas mas de 900 solicitudes de este tipo solo al cargar el index de una revistas, mi instalación de OJS tiene mas de 90 revistas

many more than 900 requests of this type only when loading the index of a journal, my OJS installation has more than 90 journals

Vitaliy · May 12, 2022, 2:23pm

That shouldn’t happen, that query is executed only with a request to the Search Author Index page.

Are you maybe using a custom theme or plugin which makes that call to the database on the journal index page? I don’t see anything related in OJS source code.

asmecher · May 12, 2022, 5:08pm

Hi @Francisco_Javier_Gar,

I suspect a search engine is trying to index a bunch of URLs in the author search page. We have deprecated and removed that feature from future releases because of problems like this; I’d recommend blocking access to it. See the standard robots.txt file for one way of doing this.

Regards,
Alec Smecher
Public Knowledge Project Team

Infotegra_S.A.S · September 1, 2022, 2:33pm

Hi tdolley, could you solve this problem? We have the same problem

Tatiana_Marielos_Agu · September 2, 2022, 5:30pm

Hi, I use the same version and have had a problem with the section ordering. Currently, we are uploading old issues and when we make a new section everything gets messed up, I want to know if you have the same problem and how did you overcome it?