Merge request `diffs.json` performs poorly in projects with many deployed environments

Summary

When loading diffs.json for a project, a query will be run for each environment in success status. For projects with a very large number of environments this causes MRs to load very slowly - ~45-60 seconds to load a 2-line diff in the case of the reporting customer.

821 queries like this were executed in the case of the reporting customer:

SELECT "deployments".* FROM "deployments" WHERE "deployments"."environment_id" = <ID> AND "deployments"."status" = 2 ORDER BY deployments.id DESC LIMIT 1;

In addition, the app server will use Rugged to read the repo for each of these requests, causing the following files to be accessed excessively:

COUNT PATH
2478 /var/opt/gitlab/.gitconfig
2463 /var/opt/gitlab/git-data/repositories/group/project.git/config
 821 /var/opt/gitlab/git-data/repositories/group/project.git/objects/pack/
 821 /var/opt/gitlab/git-data/repositories/group/project.git/objects/ab/0123456789abcdef0123456789abcdef012345

Steps to reproduce

1. Create multiple environments with successful deployments
2. Load diffs.json for an MR in this project

What is the current bug behavior?

diffs.json load time is proportional to the number of deployed environments

What is the expected correct behavior?

diffs.json loads quickly regardless of the number of deployed environments

Relevant logs and/or screenshots

{"method":"GET","path":"/app/editor/merge_requests/2299/diffs.json","format":"json","controller":"Projects::MergeRequests::DiffsController","action":"show","status":200,"duration":57778.51,"view":5.94,"db":784.43,"time":"2019-10-15T20:10:56.130Z","params":[{"key":"w","value":"0"},{"key":"namespace_id","value":"group"},{"key":"project_id","value":"project"},{"key":"id","value":"1"}],"remote_ip":"MASKED","user_id":1,"username":"user","ua":"Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Firefox/60.0","queue_duration":null,"gitaly_calls":3,"gitaly_duration":5.12,"rugged_calls":810,"rugged_duration_ms":46853.73,"correlation_id":"KoBz9a5w298"}

Output of checks

Results of GitLab environment info

Expand for output related to GitLab environment info

System information
System:         Ubuntu 16.04
Proxy:          no
Current User:   git
Using RVM:      no
Ruby Version:   2.6.3p62
Gem Version:    2.7.9
Bundler Version:1.17.3
Rake Version:   12.3.3
Redis Version:  3.2.12
Git Version:    2.22.0
Sidekiq Version:5.2.7
Go Version:     unknown
GitLab information
Version:        12.4.0-ee
Revision:       572e09f5e8f
Directory:      /opt/gitlab/embedded/service/gitlab-rails
DB Adapter:     PostgreSQL
DB Version:     10.9
Elasticsearch:  no
Geo:            no
Using LDAP:     no
Using Omniauth: yes
Omniauth Providers:
GitLab Shell
Version:        10.2.0
Repository storage paths:

• default: /var/opt/gitlab/git-data/repositories
• mkdir-test: /srv/test-dir/repositories
• branch-test: /var/opt/gitlab/git-data/repositories GitLab Shell path: /opt/gitlab/embedded/service/gitlab-shell Git: /opt/gitlab/embedded/bin/git

Results of GitLab application Check

Expand for output related to the GitLab application check

Checking GitLab subtasks ...
Checking GitLab Shell ...
GitLab Shell: ... GitLab Shell version >= 10.2.0 ? ... OK (10.2.0)
Running /opt/gitlab/embedded/service/gitlab-shell/bin/check
Internal API available: OK
Redis available via internal API: OK
gitlab-shell self-check successful
Checking GitLab Shell ... Finished
Checking Gitaly ...
Gitaly: ... default ... OK
mkdir-test ... OK
Checking Gitaly ... Finished
Checking Sidekiq ...
Sidekiq: ... Running? ... yes
Number of Sidekiq processes ... 1
Checking Sidekiq ... Finished
Checking Incoming Email ...
Incoming Email: ... Reply by email is disabled in config/gitlab.yml
Checking Incoming Email ... Finished
Checking LDAP ...
LDAP: ... LDAP is disabled in config/gitlab.yml
Checking LDAP ... Finished
Checking GitLab App ...
Git configured correctly? ... yes
Database config exists? ... yes
All migrations up? ... yes
Database contains orphaned GroupMembers? ... no
GitLab config exists? ... yes
GitLab config up to date? ... yes
Log directory writable? ... yes
Tmp directory writable? ... yes
Uploads directory exists? ... yes
Uploads directory has correct permissions? ... yes
Uploads directory tmp has correct permissions? ... skipped (no tmp uploads folder yet)
Init script exists? ... skipped (omnibus-gitlab has no init script)
Init script up-to-date? ... skipped (omnibus-gitlab has no init script)
Projects have namespace: ...
1/1 ... yes
1/2 ... yes
1/3 ... yes
1/4 ... yes
1/5 ... yes
1/6 ... yes
1/7 ... yes
1/8 ... yes
1/9 ... yes
1/10 ... yes
1/11 ... yes
1/12 ... yes
1/13 ... yes
1/14 ... yes
1/15 ... yes
1/16 ... yes
1/17 ... yes
1/18 ... yes
1/19 ... yes
1/20 ... yes
1/21 ... yes
1/22 ... yes
1/23 ... yes
1/24 ... yes
1/25 ... yes
1/26 ... yes
1/27 ... yes
1/28 ... yes
1/29 ... yes
1/30 ... yes
1/31 ... yes
1/32 ... yes
1/33 ... yes
1/34 ... yes
1/35 ... yes
1/36 ... yes
1/37 ... yes
1/38 ... yes
1/39 ... yes
1/40 ... yes
1/41 ... yes
1/42 ... yes
1/43 ... yes
1/44 ... yes
1/45 ... yes
1/46 ... yes
1/47 ... yes
1/48 ... yes
1/49 ... yes
1/50 ... yes
1/51 ... yes
2/52 ... yes
2/53 ... yes
1/55 ... yes
1/56 ... yes
3/57 ... yes
1/58 ... yes
1/59 ... yes
1/60 ... yes
1/61 ... yes
12/62 ... yes
1/63 ... yes
1/64 ... yes
1/65 ... yes
1/66 ... yes
1/67 ... yes
1/68 ... yes
3/69 ... yes
5/70 ... yes
4/71 ... yes
6/72 ... yes
2/73 ... yes
6/74 ... yes
4/75 ... yes
1/76 ... yes
1/77 ... yes
1/78 ... yes
1/79 ... yes
1/80 ... yes
1/81 ... yes
12/82 ... yes
12/83 ... yes
4/84 ... yes
3/85 ... yes
1/86 ... yes
1/87 ... yes
2/88 ... yes
1/89 ... yes
1/90 ... yes
1/91 ... yes
1/92 ... yes
1/93 ... yes
1/94 ... yes
2/95 ... yes
1/96 ... yes
1/97 ... yes
1/98 ... yes
1/99 ... yes
1/100 ... yes
1/101 ... yes
1/102 ... yes
1/103 ... yes
1/104 ... yes
1/105 ... yes
1/106 ... yes
1/107 ... yes
1/108 ... yes
1/109 ... yes
1/110 ... yes
1/111 ... yes
1/112 ... yes
1/113 ... yes
1/114 ... yes
1/115 ... yes
1/116 ... yes
1/117 ... yes
1/118 ... yes
1/120 ... yes
1/121 ... yes
1/122 ... yes
1/123 ... yes
1/124 ... yes
1/125 ... yes
1/126 ... yes
1/127 ... yes
1/128 ... yes
1/129 ... yes
1/130 ... yes
1/131 ... yes
1/132 ... yes
1/133 ... yes
1/134 ... yes
1/135 ... yes
1/136 ... yes
1/137 ... yes
1/138 ... yes
1/139 ... yes
1/140 ... yes
1/141 ... yes
1/142 ... yes
1/143 ... yes
1/144 ... yes
1/145 ... yes
1/146 ... yes
1/147 ... yes
1/148 ... yes
1/149 ... yes
1/150 ... yes
1/151 ... yes
1/152 ... yes
1/153 ... yes
1/154 ... yes
1/155 ... yes
1/156 ... yes
1/157 ... yes
1/158 ... yes
1/159 ... yes
1/160 ... yes
1/161 ... yes
1/162 ... yes
1/163 ... yes
1/165 ... yes
1/166 ... yes
1/167 ... yes
1/168 ... yes
1/169 ... yes
1/170 ... yes
1/171 ... yes
1/172 ... yes
1/173 ... yes
1/174 ... yes
1/175 ... yes
1/176 ... yes
1/177 ... yes
1/178 ... yes
1/179 ... yes
1/180 ... yes
1/181 ... yes
1/182 ... yes
1/183 ... yes
1/184 ... yes
1/185 ... yes
1/186 ... yes
1/187 ... yes
1/188 ... yes
1/189 ... yes
1/190 ... yes
1/191 ... yes
1/192 ... yes
1/193 ... yes
1/194 ... yes
15/195 ... yes
1/196 ... yes
1/198 ... yes
1/199 ... yes
1/200 ... yes
1/201 ... yes
1/202 ... yes
1/203 ... yes
1/204 ... yes
1/205 ... yes
1/206 ... yes
1/207 ... yes
1/208 ... yes
1/209 ... yes
1/210 ... yes
1/211 ... yes
1/212 ... yes
1/213 ... yes
18/214 ... yes
18/215 ... yes
18/216 ... yes
18/217 ... yes
4/218 ... yes
4/219 ... yes
18/220 ... yes
4/221 ... yes
4/222 ... yes
4/223 ... yes
1/224 ... yes
1/225 ... yes
1/226 ... yes
1/227 ... yes
1/228 ... yes
1/229 ... yes
1/230 ... yes
9/231 ... yes
13/232 ... yes
9/233 ... yes
9/234 ... yes
13/235 ... yes
1/236 ... yes
9/237 ... yes
13/238 ... yes
1/239 ... yes
9/240 ... yes
13/241 ... yes
1/242 ... yes
9/243 ... yes
13/244 ... yes
1/245 ... yes
1/246 ... yes
1/247 ... yes
1/248 ... yes
1/249 ... yes
1/250 ... yes
1/251 ... yes
1/254 ... yes
1/255 ... yes
1/256 ... yes
1/257 ... yes
20/258 ... yes
20/259 ... yes
1/260 ... yes
1/261 ... yes
1/262 ... yes
1/268 ... yes
1/269 ... yes
Redis version >= 2.8.0? ... yes
Ruby version >= 2.5.3 ? ... yes (2.6.3)
Git version >= 2.22.0 ? ... yes (2.22.0)
Git user has default SSH configuration? ... yes
Active users: ... 7
Is authorized keys file accessible? ... yes
Elasticsearch version 5.6 - 6.x? ... skipped (elasticsearch is disabled)
Checking GitLab App ... Finished
Checking GitLab subtasks ... Finished

Possible fixes

Performance bar shows the request is originating from the environments_for method on merge_request.

[
  "app/models/environment.rb:110:in `includes_commit?'",
  "app/finders/environments_finder.rb:36:in `block in execute'",
  "app/finders/environments_finder.rb:35:in `select!'",
  "app/finders/environments_finder.rb:35:in `execute'",
  "app/models/merge_request.rb:1090:in `block in environments_for'",
  "app/models/merge_request.rb:1100:in `environments_for'",
  "app/controllers/projects/merge_requests/diffs_controller.rb:52:in `render_diffs'",
  "app/controllers/projects/merge_requests/diffs_controller.rb:13:in `show'",
]

/cc @nick.thomas @oswaldo