Feature #19102

Redmine doesnt scale with large amount of projects

Added by Dell Green almost 3 years ago. Updated 2 days ago.

Status:Needs feedbackStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Performance
Target version:-
Resolution:

Description

I have approx 500 projects with subprojects included in that number. The quick jump project pop up causes massive slow downs on view rendering and huge amounts of html sent to the browser. They appear top right on virtually all pages and when editing issues.

It would be helpful if there was a smarter configuration option to turn these popups off and use live smart search box to match the project/projects required as popups are not appropriate for large data sets.

To work around the problem I have edited application_helper.rb and made the render_project_jump_box function a no-op and now my redmine naviagtion has gone from 5.5 secs view rendering to 500ms which is much better. Am using search bar to quick jumpt to project at the moment and will test other search plugins.

will live with popups on editing sections at the moment until I find a better solution for that.

redmine queries.zip - admin and user query logs for same query (18.4 KB) Dell Green, 2015-02-16 11:22


Related issues

Related to Redmine - Patch #21611: Do not collect ids of subtree in Query#project_statement Closed
Related to Redmine - Defect #26016: Redmine is slow to some users Closed
Duplicated by Redmine - Defect #19103: Access times for Admin vs normal user huge difference Closed

Associated revisions

Revision 14003
Added by Jean-Philippe Lang almost 3 years ago

Use query with custom select for faster rendering of the project drop-down (#19102).

History

#1 Updated by Jean-Philippe Lang almost 3 years ago

  • Status changed from New to Needs feedback

I've done a quick test with a user having 500 projects, I get an average 600ms response time and was able to reduce it to 400ms with r14003.

5s for rendering the project quick jump seems a lot, could you post the application log with log level set to debug to see what is going on? Could you also give r14003 a try on your setup?

#2 Updated by Dell Green almost 3 years ago

Ok great. When i get back to work tmr i'll post the debug logs as requested.

In the meantime can you please try 50 projects each with a subproject that has itself 10 subprojects as this would more closely match our setup
I have also edited redmine file i think it was query.rb to remove entire project list from issues filter section and get a huge speed increase for this page

Strangely all these speed increases are only seen for anyone logged in as an adminustrator. Normal users still experience 5secs, admins 500ms.

Am using latest bitnami redmine stack, but have seen this problem for years but never found the problem

Gut feeling its related to the number of levels of nedted suprojects. No ides why admin would see faster times than normal user

#3 Updated by Jean-Philippe Lang almost 3 years ago

Dell Green wrote:

In the meantime can you please try 50 projects each with a subproject that has itself 10 subprojects as this would more closely match our setup

I've tried this exact setup with current trunk and 2.6-stable, response times are not much different.

Started GET "/" for 127.0.0.1 at 2015-02-15 09:07:26 +0100
Processing by WelcomeController#index as HTML
  Current user: xxxx (id=xxxx)
  Rendered news/_news.html.erb (3.0ms)
  Rendered welcome/index.html.erb within layouts/base (7.0ms)
Completed 200 OK in 422ms (Views: 124.0ms | ActiveRecord: 24.0ms)

#4 Updated by Dell Green almost 3 years ago

Hmm, ok thanks. I'll post the debug logs tmr, maybe this will shed some light on the problem.

It must be my data is some way as i have installed the data on fresh installs of bitnami redmine on mac and linux and the problem is the same.

#5 Updated by Dell Green almost 3 years ago

Ok so i have ran the view all issues query both as admin and as a normal user, and the admin user generates approx 15k query data, and the normal user generates 82k.

Also there are a lot of SELECT - WHERE - IN sql statements using arrays of ids. I would sensitively suggest that there are probably more optimal ways to to write these queries (to be better scalable for larger data-sets), as a single query using JOINS and temp tables if necessary rather than multiple queries and code manipulation. I appreciate you don't have Common Table expressions in mysql to make life easier for you.

#6 Updated by Toshi MARUYAMA almost 3 years ago

  • Related to Defect #19103: Access times for Admin vs normal user huge difference added

#7 Updated by Toshi MARUYAMA almost 3 years ago

  • Related to deleted (Defect #19103: Access times for Admin vs normal user huge difference)

#8 Updated by Toshi MARUYAMA almost 3 years ago

  • Duplicated by Defect #19103: Access times for Admin vs normal user huge difference added

#9 Updated by Dell Green almost 3 years ago

Please let me know if you require any further information.

#10 Updated by Go MAEDA almost 2 years ago

  • Related to Patch #21611: Do not collect ids of subtree in Query#project_statement added

#11 Updated by Luca Lenardi over 1 year ago

We are experiencing the exact same issue:

  1. long project list (around 300) – we have just 3 sub-projects.
  2. Redmine v3.3.0
  3. load time for normal user (same number of projects, roles, permissions and same page): 5 sec (avg)
  4. load time for admin user: 1.5 sec (avg)

This was tested in a staging machine on Bitnami Redmine with Apache, Passenger and MySQL. Single request mode.
Is there something else we can do to gather more information about this issue?

#12 Updated by Luca Lenardi about 1 year ago

After other research on the subject we found what follow:

  1. removing the quick jump bar does not solve the problem at all;
  2. users participating in few (10-20) projects have lighting fast responses (in 8-500 ms range);

There should be something related to the number of projects in connection with the membership and permissions check; the point is that it happens even inside a project and not only in the projects list, where there's no reason (apparently) to check permissions on each request. This problem makes Redmine not usable with more then a few project (actually we have just 350).

So, anyone has some suggestions on how to delve deeper in this issue?
Thanks!

#13 Updated by Jean Louis 12 months ago

+1

I have ~1000 projects and ~100 users and have same problem.

The performance is good (loading pages between 0,3 and 1,0 second) for admin account, but when access to the same pages with other users (with limited permissions), the performance is bad (loading pages between 5 and 7 seconds)

In debug mode, I can see that the SQL requests are very big with users permissions on hundred of projects (a lot of project_id).

JL, a french user.

#14 Updated by Anders Thomsen 10 months ago

+1

When member of a group that's in 600 projects (actually, member of one top project with 600 sub-projects with inherit members enabled), the issues page takes 3-4 sec to show. When not a member it takes a few 100 ms. (Non-members can view issues too).

RM 3.1.1-stable
Ruby 2.1.7-p400 [x64-mingw32]
Rails 4.2.7.1
DB: SQL Server 2014

#15 Updated by Renê Barbosa 7 months ago

+1

Environment:
Redmine version: 3.3.3.stable
Ruby version: 2.1.10-p492 (2016-04-01) [x86_64-linux]
Rails version: 4.2.7.1
Environment: production
Database adapter: Mysql2
SCM:
Filesystem
Redmine plugins:
no plugin installed

#16 Updated by Toshi MARUYAMA 7 months ago

#17 Updated by Dell Green 7 months ago

As an update. Previously i was running our redmine instance on a mac mini core i7 with mac os, recently i moved it over to an HPE Proliant dl380 g6 runnng linux and the exact same problem exists. In both cases whenever anyone loads a page i see a ruby process for each user running at 100% for 5 to 10 seconds while the page is retrieved for each user. So its not platform or resource related.

Any tips for reducing the query time would be greatly appreciated.

#18 Updated by Renê Barbosa 6 months ago

About:

As an update. Previously i was running our redmine instance on a mac mini core i7 with mac os, recently i moved it over to an HPE Proliant dl380 g6 runnng linux and the exact same problem exists. In both cases whenever anyone loads a page i see a ruby process for each user running at 100% for 5 to 10 seconds while the page is retrieved for each user. So its not platform or resource related.

I can confirm it. We increased the vCPUs of our server (2 to 4 Intel Xeon E5-2640 2.60GHz cores) and the exact same problem exists. This is clearly an application issue.

#19 Updated by Renê Barbosa 6 months ago

It looks like the issue was fixed in 3.4.0.

#20 Updated by Mischa The Evil 6 months ago

@Dell Green, Luca Lenardi, Jean Louis & Anders Thomsen,

As posted by Renê Barbosa, there are several performance improvements included in 3.4.0 that might fix your issues too. Could you all please test with Redmine 3.4.0 and report back your feedback in this issue? Thanks in advance.

#21 Updated by Anders Thomsen 2 days ago

Yes, performance is much better now. Still not as fast as with admin users though.

Also available in: Atom PDF