Defect #37268

Performance problem with Redmine 4.2.7 and 5.0.2

Added by Alexander Meindl 8 days ago. Updated 2 days ago.

Status:ConfirmedStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Issues list
Target version:4.2.8
Resolution: Affected version:4.2.7

Description

With r21646 especially this change: https://www.redmine.org/projects/redmine/repository/revisions/21646/diff/trunk/app/models/issue_custom_field.rb

"((#{sql}) AND (#{tracker_condition}) AND (#{project_condition}))" 

to

"((#{sql}) AND (#{tracker_condition}) AND (#{project_condition}) AND (#{Issue.visible_condition(user)}))" 

we got massive performance problems on issue list.

Some information to the system environment:

  • Redmine 5.0.2 (for this issue I selected 5.0.1, because there is no 5.0.2 to select)
  • Ruby 3.0.2
  • PostgreSQL 14
  • 150.000 issues
  • 500 projects
  • 40 custom fields for issues

custom_field_visibility_fix.patch Magnifier (5.69 KB) Alexander Meindl, 2022-06-22 07:55

custom_field_visibility_fix_v2.patch Magnifier (960 Bytes) Alexander Meindl, 2022-06-22 07:56

custom_field_visibility_fix_v3.patch Magnifier - Fixed name of subquery alias (954 Bytes) Alexander Meindl, 2022-06-22 08:01

custom_field_visibility_fix_v4.patch Magnifier (921 Bytes) Alexander Meindl, 2022-06-22 18:52

sql_with_visible_condition.sql (3.2 KB) Marius BALTEANU, 2022-06-27 20:50

explain_with_visible_condition.csv Magnifier (20.5 KB) Marius BALTEANU, 2022-06-27 20:50

sql_without_visible_condition.sql (2.89 KB) Marius BALTEANU, 2022-06-27 20:52

explain_without_visible_condition.csv Magnifier (16.9 KB) Marius BALTEANU, 2022-06-27 20:52

perfomance_fixtures.rb Magnifier (423 Bytes) Marius BALTEANU, 2022-06-27 21:13

History

#1 Updated by Alexander Meindl 8 days ago

Sorry for syntax error in my code, I cannot edit it.

Just an info to execution time: if I change this line above back to the old version, database query needs 3 seconds. With the new security fix same query needs 59 seconds.

I found the problem: the created subquery with Issue.visible_condition(user) needs a relation to the main query, which is missing right know. We need main_query_issue.id = subquery_issue.id in subquery. This would fix the problem.
I am not sure, how this could be solved with Issue.visible_condition abstraction.

#2 Updated by Holger Just 8 days ago

  • Description updated (diff)

#3 Updated by Alexander Meindl 8 days ago

Hello,

@holger: thanks for fixing my broken syntax

More details to the problem: it appears, if a custom field is used as a filter.
We have a custom field with format "list" and used this filter with one selected value (operator => "="). If this filter is used, most of the time the database query needs forever (at least some minutes).

This is the created sql query:

SELECT COUNT(*)
FROM "issues" 
INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" 
INNER JOIN "issue_statuses" ON "issue_statuses"."id" = "issues"."status_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 ((ISSUES.STATUS_ID IN
                                (SELECT ID
                                    FROM ISSUE_STATUSES
                                    WHERE IS_CLOSED = FALSE))
                        AND ISSUES.ID IN
                            (SELECT ISSUES.ID
                                FROM ISSUES
                                LEFT OUTER JOIN CUSTOM_VALUES ON CUSTOM_VALUES.CUSTOMIZED_TYPE = 'Issue'
                                AND CUSTOM_VALUES.CUSTOMIZED_ID = ISSUES.ID
                                AND CUSTOM_VALUES.CUSTOM_FIELD_ID = 61
                                WHERE (CUSTOM_VALUES.VALUE IN ('Alignment'))
                                    AND (((1 = 1)
                                                            AND (ISSUES.TRACKER_ID IN
                                                                                    (SELECT TRACKER_ID
                                                                                        FROM CUSTOM_FIELDS_TRACKERS
                                                                                        WHERE CUSTOM_FIELD_ID = 61))
                                                            AND (EXISTS
                                                                                    (SELECT 1
                                                                                        FROM CUSTOM_FIELDS IFA
                                                                                        WHERE IFA.IS_FOR_ALL = TRUE
                                                                                            AND IFA.ID = 61)
                                                                                OR ISSUES.PROJECT_ID IN
                                                                                    (SELECT PROJECT_ID
                                                                                        FROM CUSTOM_FIELDS_PROJECTS
                                                                                        WHERE CUSTOM_FIELD_ID = 61))
                                                            AND (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 this is a test to fix it manually by sql (with "AND main_query.ID = ISSUES.ID"):

SELECT COUNT(*)
FROM "issues" AS main_query
INNER JOIN "projects" ON "projects"."id" = main_query."project_id" 
INNER JOIN "issue_statuses" ON "issue_statuses"."id" = main_query."status_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 ((main_query.STATUS_ID IN
                                (SELECT ID
                                    FROM ISSUE_STATUSES
                                    WHERE IS_CLOSED = FALSE))
                        AND main_query.ID IN
                            (SELECT ISSUES.ID
                                FROM ISSUES
                                LEFT OUTER JOIN CUSTOM_VALUES ON CUSTOM_VALUES.CUSTOMIZED_TYPE = 'Issue'
                                AND main_query.ID = ISSUES.ID
                                AND CUSTOM_VALUES.CUSTOMIZED_ID = ISSUES.ID
                                AND CUSTOM_VALUES.CUSTOM_FIELD_ID = 61
                                WHERE (CUSTOM_VALUES.VALUE IN ('Alignment'))
                                    AND (((1 = 1)
                                                            AND (ISSUES.TRACKER_ID IN
                                                                                    (SELECT TRACKER_ID
                                                                                        FROM CUSTOM_FIELDS_TRACKERS
                                                                                        WHERE CUSTOM_FIELD_ID = 61))
                                                            AND (EXISTS
                                                                                    (SELECT 1
                                                                                        FROM CUSTOM_FIELDS IFA
                                                                                        WHERE IFA.IS_FOR_ALL = TRUE
                                                                                            AND IFA.ID = 61)
                                                                                OR ISSUES.PROJECT_ID IN
                                                                                    (SELECT PROJECT_ID
                                                                                        FROM CUSTOM_FIELDS_PROJECTS
                                                                                        WHERE CUSTOM_FIELD_ID = 61))
                                                            AND (PROJECTS.STATUS <> 9
                                                                                AND EXISTS
                                                                                    (SELECT 1 AS ONE
                                                                                        FROM ENABLED_MODULES EM
                                                                                        WHERE EM.PROJECT_ID = PROJECTS.ID
                                                                                            AND EM.NAME = 'issue_tracking'))))))

#4 Updated by Alexander Meindl 8 days ago

Hi,

I attached a fix, which solves the problem. Question is, are there any other problems with custom field filters.

#5 Updated by Alexander Meindl 8 days ago

Here is the right version of the patch.

#6 Updated by Alexander Meindl 8 days ago

And this version uses an abstract name for subquery (because it is not only issue related).

#8 Updated by Alexander Meindl 7 days ago

This version fixes errors with VersionCustomFields for issues.

Some tests for queries are broken, because filter operator "none" (!*) does not work anymore with this patch.

#9 Updated by Simon Hori 7 days ago

We also got a serious performance issue after upgrade from 4.2.6 to 4.2.7 as well.

The same change has been applied to 4.2.7.
https://github.com/redmine/redmine/blob/4.2.7/app/models/issue_custom_field.rb#L42

Thank you very much for your time for this issue.

#10 Updated by Marius BALTEANU 7 days ago

  • Target version set to 4.2.8
  • Affected version changed from 5.0.1 to 4.2.6

#11 Updated by Holger Just 7 days ago

Simon, which database type (and version) are you using?

#12 Updated by Go MAEDA 6 days ago

  • Affected version changed from 4.2.6 to 4.2.7

#13 Updated by Marius BALTEANU 5 days ago

  • Subject changed from Performance problem with Redmine 5.0.2 to Performance problem with Redmine 4.2.7 and 5.0.2
  • Status changed from New to Confirmed
I've confirmed the performance issue with:
  • 5000 issues
  • 500 projects
  • 40 custom fields for issues

4.2.7: Query runs in ~ 5 seconds
4.2.6: Query runs in ~ 0,5 seconds.

Holger, Felix, should we revert the fix until we have this fixed properly?

#14 Updated by Holger Just 3 days ago

The fix was security related and should not be reverted, lest we re-introduce the underlying security issue. However, we may want to fix the performance issue.

Marius: Could you please confirm which database you observed the issue on? Could you please also show the emitted SQL queries for both versions, in addition to an EXPLAIN of both queries?

#15 Updated by Marius BALTEANU 3 days ago

Holger Just wrote:

The fix was security related and should not be reverted, lest we re-introduce the underlying security issue. However, we may want to fix the performance issue.

Marius: Could you please confirm which database you observed the issue on? Could you please also show the emitted SQL queries for both versions, in addition to an EXPLAIN of both queries?

I've tested with postgres:14-alpine, I'll be back with the explains and the scripts used to generate the necessary fixtures.

#16 Updated by Marius BALTEANU 2 days ago

Holger, I've attached the requested details:

1. SQL and explain from PGAdmin for the query with visible condition
2. SQL and explain from PGAdmin for the query before the security fix was implemented (without visible condition).

I made very basic performance tests using the Chrome Dev Tool for /issues page filtered by a custom field:

Database With visible condition Without visible condition
postgres 14 ~11s ~123ms
mysql 81 ~5s ~5s

From my tests, the degradation appears only when postgres is used as database.

I've used the attached script to generate the fixtures on top of the test fixtures.

[1]: I expected to obtain same results on mysql 5.7.

Also available in: Atom PDF