Redmine

Problem¶

When updating a user's group membership via PUT /users/:id with group_ids, Group#user_added generates an excessive number of SQL queries, causing extremely slow responses.

Root cause¶

There are two bottlenecks in Group#user_added:

1. Recursive callback chain in MemberRole#add_role_to_subprojects

Each MemberRole created inside user_added fires after_create, which triggers add_role_to_subprojects. This recursively creates MemberRole records for child projects with inherit_members: true. At each level, member.project.leaf? and children.where(:inherit_members => true).ids issue SELECT queries, so the total query count grows proportionally to: projects x roles x subproject tree depth.

2. Repeated Member.find_or_initialize_by per project

For each project, a separate SELECT is issued to find or initialize the user's Member record. No batch loading is performed.

Impact in production¶

In a production environment, changing membership of just 2 groups resulted in 123,332 queries / 186 seconds, causing a 504 Gateway Timeout from the reverse proxy:

PUT "/users/165" - 186,044ms
ActiveRecord: 91,198ms (123,332 queries, 1,902 cache)
Parameters: {"user"=>{"group_ids"=>["31", "41", ""]}}

Solution¶

The patch modifies only the Group#user_added code path. Other code paths (Project#add_inherited_member_roles, MemberRole#add_role_to_group_users, Group#user_removed) are not changed.

• Suppress the recursive add_role_to_subprojects callback chain for MemberRole records created inside Group#user_added, using a transient flag (skip_subproject_propagation). Subproject propagation is handled explicitly instead.
• Batch-load inheriting descendant projects, existing Member records, and existing MemberRole records upfront, replacing per-record find_or_initialize_by SELECTs and per-record leaf? / children.where(...) queries.
• Add regression tests for subproject propagation (child, grandchild, and sibling cases).

Benchmark¶

Conditions: 50 projects x 3 roles x subproject depth 5 (300 total projects)

	Queries	SELECT	INSERT	Time
Before	16,805	13,352	3,451	10,241ms
After	8,262	4,809	3,451	6,005ms
Reduction	-51%	-64%	0	-41%

INSERT count is identical (the number of records created does not change). The improvement comes from SELECT reduction.

The benchmark script is attached. It has no dependency on its file location, so it can be run from anywhere:

bin/rails runner /path/to/benchmark_group_user_added.rb

Scope of changes¶

• Only the Group#user_added path is modified
• Group#user_removed is unchanged
• Project#add_inherited_member_roles (triggered when toggling inherit_members) is unchanged
• MemberRole#add_role_to_group_users (triggered when adding roles to a group) is unchanged