[ruby-core:114576] [Ruby master Bug#19856] Redmine query is very slow after upgrading from MySql 5.7 to 8
From:
"singhkushal929@... (Kushal Singh) via ruby-core" <ruby-core@...>
Date:
2023-08-29 09:02:59 UTC
List:
ruby-core #114576
Issue #19856 has been reported by singhkushal929@gmail.com (Kushal Singh).
----------------------------------------
Bug #19856: Redmine query is very slow after upgrading from MySql 5.7 to 8
https://bugs.ruby-lang.org/issues/19856
* Author: singhkushal929@gmail.com (Kushal Singh)
* Status: Open
* Priority: Normal
* ruby -v: 4.1.1.stable, 5.0.5
* Backport: 3.0: UNKNOWN, 3.1: UNKNOWN, 3.2: UNKNOWN
----------------------------------------
Recently we upgraded the MySql version for Redmine from 5.7 to 8.0.32. After the upgrade the MyPage query is taking a very long time to execute (Around 1 minute). Previously in version 5.7 it used to execute within a few seconds. This is resulting in our CPU usage to reach 99% very quickly.
On initial research looks like MySql 8 uses derived query optimization concept due to which the logic that the query is interpreted in 5.7 and 8 is different.
Redmine Version: 4.1.1.stable (Tried this on latest redmine version 5.0.5 but faced the same issue. Also all the appropriate index have been done as well)
My Page Query:
~~~
SELECT
issues.id AS t0_r0,
issues.tracker_id AS t0_r1,
issues.project_id AS t0_r2,
issues.subject AS t0_r3,
issues.description AS t0_r4,
issues.due_date AS t0_r5,
issues.category_id AS t0_r6,
issues.status_id AS t0_r7,
issues.assigned_to_id AS t0_r8,
issues.priority_id AS t0_r9,
issues.fixed_version_id AS t0_r10,
issues.author_id AS t0_r11,
issues.lock_version AS t0_r12,
issues.created_on AS t0_r13,
issues.updated_on AS t0_r14,
issues.start_date AS t0_r15,
issues.done_ratio AS t0_r16,
issues.estimated_hours AS t0_r17,
issues.parent_id AS t0_r18,
issues.root_id AS t0_r19,
issues.lft AS t0_r20,
issues.rgt AS t0_r21,
issues.is_private AS t0_r22,
issues.position AS t0_r23,
issues.remaining_hours AS t0_r24,
issues.story_points AS t0_r25,
issues.closed_on AS t0_r26,
issue_statuses.id AS t1_r0,
issue_statuses.name AS t1_r1,
issue_statuses.is_closed AS t1_r2,
issue_statuses.position AS t1_r3,
issue_statuses.default_done_ratio AS t1_r4,
projects.id AS t2_r0,
projects.name AS t2_r1,
projects.description AS t2_r2,
projects.homepage AS t2_r3,
projects.is_public AS t2_r4,
projects.parent_id AS t2_r5,
projects.created_on AS t2_r6,
projects.updated_on AS t2_r7,
projects.identifier AS t2_r8,
projects.status AS t2_r9,
projects.lft AS t2_r10,
projects.rgt AS t2_r11,
projects.inherit_members AS t2_r12,
projects.default_version_id AS t2_r13,
projects.default_assigned_to_id AS t2_r14
FROM
issues
INNER JOIN projects ON projects.id = issues.project_id
INNER JOIN issue_statuses ON issue_statuses.id = issues.status_id
LEFT OUTER JOIN enumerations ON enumerations.id = issues.priority_id
WHERE
(projects.status <> 9
AND EXISTS (
SELECT 1
FROM enabled_modules em
WHERE em.project_id = projects.id AND em.name='issue_tracking'
))
AND (
issues.status_id IN (SELECT id FROM issue_statuses WHERE is_closed=FALSE)
AND issues.assigned_to_id IN ('1051', '2643')
AND projects.status IN ('1')
)
ORDER BY
enumerations.position DESC,
issues.updated_on DESC,
issues.id DESC
LIMIT 10;
~~~
Explain for MySQL 8.0.32
id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra
-|-|-|-|-|-|-|-|-|-|-|-
1|SIMPLE|issue_statuses||ALL|PRIMARY||||19|100|Using temporary; Using filesort
1|SIMPLE|issue_statuses||eq_ref|PRIMARY,index_issue_statuses_on_is_closed|PRIMARY|4|deermine.issue_statuses.id|1|89.47|Using where
1|SIMPLE|<subquery2>||ALL||||||100|Using where; Using join buffer (hash join)
1|SIMPLE|projects||eq_ref|PRIMARY|PRIMARY|4|<subquery2>.project_id|1|9|Using where
1|SIMPLE|issues||ref|issues_project_id,index_issues_on_status_id,index_issues_on_assigned_to_id|issues_project_id|4|<subquery2>.project_id|1917|0.03|Using where
1|SIMPLE|enumerations||eq_ref|PRIMARY,index_enumerations_on_id_and_type|PRIMARY|4|deermine.issues.priority_id|1|100|
2|MATERIALIZED|em||ALL|enabled_modules_project_id||||3545|10|Using where
Explain for MySQL 5.7
id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra
-|-|-|-|-|-|-|-|-|-|-|-
1|PRIMARY|issues||range|issues_project_id,index_issues_on_status_id,index_issues_on_assigned_to_id|index_issues_on_assigned_to_id|5||2560|100|Using index condition; Using temporary; Using filesort
1|PRIMARY|issue_statuses||eq_ref|PRIMARY,index_issue_statuses_on_is_closed|PRIMARY|4|deermine.issues.status_id|1|89.47|Using where
1|PRIMARY|issue_statuses||eq_ref|PRIMARY|PRIMARY|4|deermine.issues.status_id|1|100|
1|PRIMARY|enumerations||eq_ref|PRIMARY,index_enumerations_on_id_and_type|PRIMARY|4|deermine.issues.priority_id|1|100|
1|PRIMARY|projects||eq_ref|PRIMARY|PRIMARY|4|deermine.issues.project_id|1|9|Using where
2|DEPENDENT SUBQUERY|em||ref|enabled_modules_project_id|enabled_modules_project_id|5|deermine.projects.id|5|10|Using where
As you can see, in 5.7 all the rows are being pulled in single query where as for 8.0.32 it is being pulled in multiple query.
--
https://bugs.ruby-lang.org/
______________________________________________
ruby-core mailing list -- ruby-core@ml.ruby-lang.org
To unsubscribe send an email to ruby-core-leave@ml.ruby-lang.org
ruby-core info -- https://ml.ruby-lang.org/mailman3/postorius/lists/ruby-core.ml.ruby-lang.org/