Slow SQL Queries (Problems with the performance of Redmine)

Added by Uttam Sapkota 7 months ago

I'm having problems with my application performance.

The tables in the database have the following records:
"issues" has more than 334823
"custom_values​​" has over 1350243
"users" has 1350
"projects" 494

The system currently has 1350 users. Currently only goes to 100 concurrent users.

The application is being way too slow as you can see in below picture..

As I can do to achieve a great performance suitable for the system. Currently the system uses an average of 80% of RAM and 50% of CPU on both servers.

The slowest query is :

SELECT 
    `issues`.*
FROM
    `issues`
        INNER JOIN
    `projects` ON `projects`.`id` = `issues`.`project_id`
WHERE
    (((projects.status <> 9
        AND EXISTS( SELECT 
            1 AS one
        FROM
            enabled_modules em
        WHERE
            em.project_id = projects.id
                AND em.name = 'issue_tracking'))
        AND (((projects.is_public = 1
        AND projects.id NOT IN (SELECT 
            project_id
        FROM
            members
        WHERE
            user_id IN (44 , 638)))
        AND ((issues.is_private = 0
        OR issues.author_id = 44
        OR issues.assigned_to_id IN (44 , 186, 898))))
        OR (projects.id IN (1 , 6,
        9,13,115,129,131,136,186,227,228,230,275,287,
        315,335,340,369,394,404,408,450,453,454,456,
        458,460,462,492,511,512,516,519,527,531,533,537)
        AND ((issues.is_private = 0
        OR issues.author_id = 44
        OR issues.assigned_to_id IN (44 , 186, 898))))
        OR (projects.id IN (40 , 68, 130,149,326,330,
        331,332,333,350,356,402,455,514,515,528)
        AND (1 = 1))
        OR (projects.id IN (91 , 111,128,130,149,235,
        257,322,326,328,330,331,332,333,350,356,489,
        508,514,515,525,528)
        AND ((issues.is_private = 0
        OR issues.author_id = 44
        OR issues.assigned_to_id IN (44 , 186, 898))))
        OR (projects.id IN (457)
        AND ((issues.is_private = 0
        OR issues.author_id = 44
        OR issues.assigned_to_id IN (44 , 186, 898)))))))
        AND (`issues`.`id` != 251880)
        AND (LOWER(issues.subject) LIKE LOWER('%255392%'))
ORDER BY `issues`.`id` DESC
LIMIT 10;

The tabular explanation is also attached. Can somebody suggest me the techniques to optimize sql queries in redmine?
Thanks for your help

Screenshot from 2019-12-16 16-38-49.png - Slow Query taking upto 20 secs. (69.9 KB)

Screenshot from 2019-12-16 16-50-25.png - Tabular Explain (31.4 KB)