Defect #11904

Projects with many members are very slow in Redmine 2.0.3

Added by Kristoffer Renholm over 10 years ago. Updated almost 10 years ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:Jean-Philippe Lang% Done:

0%

Category:Issues
Target version:-
Resolution:Fixed Affected version:2.0.3

Description

When having a project with ~100 members (many of them are reporters) Redmine slows down and new issue and show issue takes up to 6 seconds to load. This seems to be due to that each and every Principal is queried one by one, see log below. I have tried to work out where these queries come from but have not succeeded so far. Any ideas?

  SQL (320.6ms)  SELECT `members`.`id` AS t0_r0, `members`.`user_id` AS t0_r1, `members`.`project_id` AS t0_r2, `members`.`created_on` AS t0_r3, `members`.`mail_notification` AS t0_r4, `users`.`id` AS t1_r0, `users`.`login` AS t1_r1, `users`.`hashed_password` AS t1_r2, `users`.`firstname` AS t1_r3, `users`.`lastname` AS t1_r4, `users`.`mail` AS t1_r5, `users`.`admin` AS t1_r6, `users`.`status` AS t1_r7, `users`.`last_login_on` AS t1_r8, `users`.`language` AS t1_r9, `users`.`auth_source_id` AS t1_r10, `users`.`created_on` AS t1_r11, `users`.`updated_on` AS t1_r12, `users`.`type` AS t1_r13, `users`.`identity_url` AS t1_r14, `users`.`mail_notification` AS t1_r15, `users`.`salt` AS t1_r16, `roles`.`id` AS t2_r0, `roles`.`name` AS t2_r1, `roles`.`position` AS t2_r2, `roles`.`assignable` AS t2_r3, `roles`.`builtin` AS t2_r4, `roles`.`permissions` AS t2_r5, `roles`.`issues_visibility` AS t2_r6 FROM `members` LEFT OUTER JOIN `users` ON `users`.`id` = `members`.`user_id` AND `users`.`type` IN ('User', 'AnonymousUser') LEFT OUTER JOIN `member_roles` ON `member_roles`.`member_id` = `members`.`id` LEFT OUTER JOIN `roles` ON `roles`.`id` = `member_roles`.`role_id` WHERE `members`.`project_id` = 234 AND (users.type='User' AND users.status=1)
  Principal Load (1.2ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 3 LIMIT 1
  Principal Load (1.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 6 LIMIT 1
  Principal Load (1.2ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 10 LIMIT 1
  Principal Load (1.2ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 11 LIMIT 1
  Principal Load (1.5ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 13 LIMIT 1
  Principal Load (0.8ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 14 LIMIT 1
  Principal Load (1.3ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 15 LIMIT 1
  Principal Load (0.8ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 17 LIMIT 1
  Principal Load (0.8ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 24 LIMIT 1
  Principal Load (1.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 25 LIMIT 1
  Principal Load (1.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 26 LIMIT 1
  Principal Load (1.2ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 29 LIMIT 1
  Principal Load (1.7ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 30 LIMIT 1
  Principal Load (3.1ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 32 LIMIT 1
  Principal Load (1.6ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 34 LIMIT 1
  Principal Load (2.0ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 35 LIMIT 1
  Principal Load (1.6ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 38 LIMIT 1
  Principal Load (2.0ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 42 LIMIT 1
  Principal Load (2.3ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 47 LIMIT 1
  Principal Load (316.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 49 LIMIT 1
  Principal Load (1.2ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 52 LIMIT 1
  Principal Load (1.3ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 54 LIMIT 1
  Principal Load (2.1ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 57 LIMIT 1
  Principal Load (1.1ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 60 LIMIT 1
  Principal Load (0.8ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 64 LIMIT 1
  Principal Load (0.7ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 67 LIMIT 1
  Principal Load (0.9ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 68 LIMIT 1
  ...

Ruby/Rails information:

About your application's environment
Ruby version              1.8.7 (x86_64-linux)
RubyGems version          1.4.2
Rack version              1.4
Rails version             3.2.6
Active Record version     3.2.6
Action Pack version       3.2.6
Active Resource version   3.2.6
Action Mailer version     3.2.6
Active Support version    3.2.6
Middleware                Rack::Cache, ActionDispatch::Static, Rack::Lock, #<ActiveSupport::Cache::Strategy::LocalCache::Middlewa0x7ff2d68f84e8>, Rack::Runtime, Rack::MethodOverride, ActionDispatch::RequestId, Rails::Rack::Logger, ActionDispatch::ShowExcepti, ActionDispatch::DebugExceptions, ActionDispatch::RemoteIp, ActionDispatch::Callbacks, ActiveRecord::ConnectionAdapters::ConnectManagement, ActiveRecord::QueryCache, ActionDispatch::Cookies, ActionDispatch::Session::CookieStore, ActionDispatch::Flash, Actiospatch::ParamsParser, ActionDispatch::Head, Rack::ConditionalGet, Rack::ETag, ActionDispatch::BestStandardsSupport, OpenIdAuthenttion
Application root          /opt/redmine/releases/upgrade-2.0.3
Environment               production
Database adapter          mysql
Database schema version   20120422150750

Database: mysql Ver 14.14 Distrib 5.1.52, for redhat-linux-gnu (x86_64) using readline 5.1

Associated revisions

Revision 10440
Added by Jean-Philippe Lang over 10 years ago

Avoid to run one SQL query per member on Project#assignable_users (#11904).

Revision 10441
Added by Jean-Philippe Lang over 10 years ago

Use eager loaded #principal association instead of #user (#11904).

Revision 10442
Added by Jean-Philippe Lang over 10 years ago

Use eager loaded #principal association instead of #user (#11904).

History

#1 Updated by Jean-Philippe Lang over 10 years ago

  • Assignee set to Jean-Philippe Lang

Kristoffer Renholm wrote:

This seems to be due to that each and every Principal is queried one by one

Fixed in r10440. The patch is pretty simple, you can try it out to see if it solves your performance issue.

#2 Updated by Jean-Philippe Lang over 10 years ago

FTR, the "New issue" page in a project with 200 members now loads in ~250ms on my dev machine and Redmine 2.1.

#3 Updated by Daniel Felix over 10 years ago

Jean-Philippe Lang wrote:

FTR, the "New issue" page in a project with 200 members now loads in ~250ms on my dev machine and Redmine 2.1.

I tried it with the current Revision 10463, and it loads much faster! Thanks!

#4 Updated by Kristoffer Renholm over 10 years ago

  • Status changed from New to Resolved

Thanks, works much better! Impressed of the fast response time.

#5 Updated by Kristoffer Renholm over 10 years ago

In which upcoming Redmine version is this fix included?

#6 Updated by Filou Centrinov almost 10 years ago

It has been implemented in Redmine 2.3: source:branches/2.3-stable/app/models/project.rb#L31

Status: Close?

#7 Updated by Kristoffer Renholm almost 10 years ago

Yes please.

#8 Updated by Toshi MARUYAMA almost 10 years ago

  • Status changed from Resolved to Closed
  • Resolution set to Fixed

Thank you for your feedback.

Also available in: Atom PDF