Add search parameter to the environments controller (!97015) · Merge requests · GitLab.org / GitLab

Vladimir Shushlin requested to merge 10754-search-for-environment-name-in-the-environments-overview-page into master Sep 05, 2022

What does this MR do and why?

Extracted from PoC for environments search (!86115 - closed)

And based on the Fix performance of the environments search (!96774 - merged)

This MR adds the search parameter to the environments controller which will be used #10754 (closed)

Migration logs

Click to expand

➜  gitlab git:(10754-search-for-environment-name-in-the-environments-overview-page) ✗ ./bin/rails db:rollback:main STEP=2
main: == 20220909114220 DropEnvironmentsProjectNameLowerPatternOpsIndex: reverting ==
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:environments, "project_id, lower(name) varchar_pattern_ops", {:name=>"index_environments_on_project_name_varchar_pattern_ops", :algorithm=>:concurrently})
main:    -> 0.0467s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- add_index(:environments, "project_id, lower(name) varchar_pattern_ops", {:name=>"index_environments_on_project_name_varchar_pattern_ops", :algorithm=>:concurrently})
main:    -> 0.0049s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20220909114220 DropEnvironmentsProjectNameLowerPatternOpsIndex: reverted (0.0621s) 

main: == 20220909113809 AddEnvironmentsProjectNameLowerPatternOpsStateIndex: reverting 
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:environments)
main:    -> 0.0048s
main: -- remove_index(:environments, {:algorithm=>:concurrently, :name=>"index_environments_on_project_name_varchar_pattern_ops_state"})
main:    -> 0.0029s
main: == 20220909113809 AddEnvironmentsProjectNameLowerPatternOpsStateIndex: reverted (0.0092s) 

➜  gitlab git:(10754-search-for-environment-name-in-the-environments-overview-page) ✗ ./bin/rails db:migrate:main        
main: == 20220909113809 AddEnvironmentsProjectNameLowerPatternOpsStateIndex: migrating 
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:environments, "project_id, lower(name) varchar_pattern_ops, state", {:name=>"index_environments_on_project_name_varchar_pattern_ops_state", :algorithm=>:concurrently})
main:    -> 0.0106s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- add_index(:environments, "project_id, lower(name) varchar_pattern_ops, state", {:name=>"index_environments_on_project_name_varchar_pattern_ops_state", :algorithm=>:concurrently})
main:    -> 0.0032s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20220909113809 AddEnvironmentsProjectNameLowerPatternOpsStateIndex: migrated (0.0199s) 

main: == 20220909114220 DropEnvironmentsProjectNameLowerPatternOpsIndex: migrating ==
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:environments)
main:    -> 0.0045s
main: -- remove_index(:environments, {:algorithm=>:concurrently, :name=>"index_environments_on_project_name_varchar_pattern_ops"})
main:    -> 0.0028s
main: == 20220909114220 DropEnvironmentsProjectNameLowerPatternOpsIndex: migrated (0.0087s)

SQL queries and plans:

Summary table

query name	old MiB accessed	narrow search MiB accessed	generic search MiB accessed
getting available/closed counters	`22.10 MiB`	`1.30 MiB`	`33 MiB`
Selecting number of environments per folder	`27.60 MiB`	`1.20 MiB`	`27.20 MiB`
Actually getting top 20 environments for each folder	`27.70 MiB`	`1.20 MiB`	`27.20 MiB`

(last 2 rows are not duplicates, they just process very similar rows)

getting available/closed counters

Specific queries like ‘review/my\_review%’ are very fast

explain SELECT COUNT(*) AS count_all, "environments"."state" AS environments_state FROM "environments" WHERE "environments"."project_id" = 278964 AND (LOWER(environments.name) LIKE LOWER('review/r') || '%') GROUP BY "environments"."state"

Time: 1.422 ms
  - planning: 0.340 ms
  - execution: 1.082 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 172 (~1.30 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/12016/commands/42639

Less specific ones are slower, e.g. search for `r%`

explain SELECT COUNT(*) AS count_all, “environments”.“state” AS environments_state FROM “environments” WHERE “environments”.“project_id” = 278964 AND (LOWER(environments.name) LIKE LOWER(‘r’) || ‘%’) GROUP BY “environments”.“state”

Time: 109.947 ms
  - planning: 0.250 ms
  - execution: 109.697 ms
    - I/O read: 91.657 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 554 (~4.30 MiB) from the buffer pool
  - reads: 3676 (~28.70 MiB) from the OS file cache, including disk I/O
  - dirtied: 422 (~3.30 MiB)
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/12016/commands/42640

But they weren't too fast before too:

explain SELECT COUNT(*) AS count_all, “environments”.“state” AS environments_state FROM “environments” WHERE “environments”.“project_id” = 278964 GROUP BY “environments”.“state”

Time: 217.208 ms
  - planning: 0.187 ms
  - execution: 217.021 ms
    - I/O read: 206.575 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 2608 (~20.40 MiB) from the buffer pool
  - reads: 218 (~1.70 MiB) from the OS file cache, including disk I/O
  - dirtied: 56 (~448.00 KiB)
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/12016/commands/42641

Selecting number of environments per folder

Again, specific search is very fast

explain SELECT COUNT(*) AS count_all, COALESCE(environment_type, id::text) AS coalesce_environment_type_id_text, COALESCE(environment_type, name) AS coalesce_environment_type_name FROM “environments” WHERE “environments”.“project_id” = 278964 AND (LOWER(environments.name) LIKE LOWER(‘review/r’) || ‘%’) AND (“environments”.“state” IN (‘available’)) GROUP BY COALESCE(environment_type, id::text), COALESCE(environment_type, name) LIMIT 10001

Time: 1.453 ms
  - planning: 0.671 ms
  - execution: 0.782 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 155 (~1.20 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/12016/commands/42642

But more generic is slower

explain SELECT COUNT(*) AS count_all, COALESCE(environment_type, id::text) AS coalesce_environment_type_id_text, COALESCE(environment_type, name) AS coalesce_environment_type_name FROM “environments” WHERE “environments”.“project_id” = 278964 AND (LOWER(environments.name) LIKE LOWER(‘r’) || ‘%’) AND (“environments”.“state” IN (‘available’)) GROUP BY COALESCE(environment_type, id::text), COALESCE(environment_type, name) LIMIT 10001

Time: 18.722 ms
  - planning: 0.296 ms
  - execution: 18.426 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 3477 (~27.20 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/12016/commands/42643

And again it wasn't super fast before

explain SELECT COUNT(*) AS count_all, COALESCE(environment_type, id::text) AS coalesce_environment_type_id_text, COALESCE(environment_type, name) AS coalesce_environment_type_name FROM “environments” WHERE “environments”.“project_id” = 278964 AND (“environments”.“state” IN (‘available’)) GROUP BY COALESCE(environment_type, id::text), COALESCE(environment_type, name) LIMIT 10001

Time: 40.447 ms
  - planning: 0.307 ms
  - execution: 40.140 ms
    - I/O read: 29.287 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 3531 (~27.60 MiB) from the buffer pool
  - reads: 8 (~64.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/12016/commands/42644

Actually getting top 20 environments for each folder

Again, fast specific search

explain SELECT COALESCE(environment_type, id::text), COALESCE(environment_type, name) AS folder, COUNT(*) AS size, MAX(id) AS last_id FROM “environments” WHERE “environments”.“project_id” = 278964 AND (LOWER(environments.name) LIKE LOWER(‘review/r’) || ‘%’) AND (“environments”.“state” IN (‘available’)) GROUP BY COALESCE(environment_type, id::text), COALESCE(environment_type, name) ORDER BY folder ASC LIMIT 20 OFFSET 0

Time: 1.624 ms
  - planning: 0.581 ms
  - execution: 1.043 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 158 (~1.20 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/12016/commands/42645

slow generic search

explain SELECT COALESCE(environment_type, id::text), COALESCE(environment_type, name) AS folder, COUNT(*) AS size, MAX(id) AS last_id FROM “environments” WHERE “environments”.“project_id” = 278964 AND (LOWER(environments.name) LIKE LOWER(‘r’) || ‘%’) AND (“environments”.“state” IN (‘available’)) GROUP BY COALESCE(environment_type, id::text), COALESCE(environment_type, name) ORDER BY folder ASC LIMIT 20 OFFSET 0

Time: 20.991 ms
  - planning: 0.324 ms
  - execution: 20.667 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 3480 (~27.20 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/12016/commands/42646

And slow without search

explain SELECT COALESCE(environment_type, id::text), COALESCE(environment_type, name) AS folder, COUNT(*) AS size, MAX(id) AS last_id FROM “environments” WHERE “environments”.“project_id” = 278964  AND (“environments”.“state” IN (‘available’)) GROUP BY COALESCE(environment_type, id::text), COALESCE(environment_type, name) ORDER BY folder ASC LIMIT 20 OFFSET 0

Time: 19.457 ms
  - planning: 3.020 ms
  - execution: 16.437 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 3542 (~27.70 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/12016/commands/42647

Screenshots or screen recordings

No UI changes yet.

How to set up and validate locally

Enable the FF
```
Feature.enable(:environments_search)
```
Go to the environments page, create a few environments(use folders like review/my-review...)
open developer console and find requests like:

https://gitlab.com/gitlab-org/gitlab/-/environments.json?nested=true&page=1&scope=available
https://gitlab.com/gitlab-org/gitlab/-/environments/folders/review.json?scope=available&per_page=3 (Note that I added .json here, it isn't present in the logs because JS just set the accept header instead)

add &search=something to them -->

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

I have evaluated the MR acceptance checklist for this MR.

Related to #10754 (closed)

Edited Sep 09, 2022 by Vladimir Shushlin

Add search parameter to the environments controller