Exclude Project Bots from Deactivation in DeactivateDormantUsersWorker
What does this MR do and why?
According to #340346 (closed), project_bot
users should not be deactivated automatically by the DeactivateDormantUsersWorker
.
This MR excludes project_bot
users from deactivation.
Issue: #340346 (closed)
Database Migrations
Up
$ bin/rails db:migrate
== 20211027203921 RemoveIndexForDormantUsers: migrating =======================
-- transaction_open?()
-> 0.0000s
-- indexes(:users)
-> 0.0112s
-- execute("SET statement_timeout TO 0")
-> 0.0005s
-- remove_index(:users, {:algorithm=>:concurrently, :name=>"index_users_on_id_and_last_activity_on_for_non_internal_active"})
-> 0.0087s
-- execute("RESET statement_timeout")
-> 0.0006s
== 20211027203921 RemoveIndexForDormantUsers: migrated (0.0286s) ==============
== 20211027203950 AddUpdatedIndexForDormantUsers: migrating ===================
-- transaction_open?()
-> 0.0000s
-- index_exists?(:users, [:id, :last_activity_on], {:where=>"state = 'active' AND (users.user_type IS NULL OR users.user_type = 4)", :name=>"index_users_on_id_and_last_activity_on_for_active_human_service", :algorithm=>:concurrently})
-> 0.0085s
-- add_index(:users, [:id, :last_activity_on], {:where=>"state = 'active' AND (users.user_type IS NULL OR users.user_type = 4)", :name=>"index_users_on_id_and_last_activity_on_for_active_human_service", :algorithm=>:concurrently})
-> 0.0084s
== 20211027203950 AddUpdatedIndexForDormantUsers: migrated (0.0186s) ==========
$
Index after migration:
"index_users_on_id_and_last_activity_on_for_active_human_service" btree (id, last_activity_on) WHERE state::text = 'active'::text AND (user_type IS NULL OR user_type = 4)
Down
$ STEP=2 bin/rails db:rollback
== 20211027203950 AddUpdatedIndexForDormantUsers: reverting ===================
-- transaction_open?()
-> 0.0000s
-- indexes(:users)
-> 0.0107s
-- execute("SET statement_timeout TO 0")
-> 0.0005s
-- remove_index(:users, {:algorithm=>:concurrently, :name=>"index_users_on_id_and_last_activity_on_for_active_human_service"})
-> 0.0036s
-- execute("RESET statement_timeout")
-> 0.0006s
== 20211027203950 AddUpdatedIndexForDormantUsers: reverted (0.0173s) ==========
== 20211027203921 RemoveIndexForDormantUsers: reverting =======================
-- transaction_open?()
-> 0.0000s
-- index_exists?(:users, [:id, :last_activity_on], {:where=>"state = 'active' AND (users.user_type IS NULL OR users.user_type IN (NULL, 6, 4))", :name=>"index_users_on_id_and_last_activity_on_for_non_internal_active", :algorithm=>:concurrently})
-> 0.0074s
-- add_index(:users, [:id, :last_activity_on], {:where=>"state = 'active' AND (users.user_type IS NULL OR users.user_type IN (NULL, 6, 4))", :name=>"index_users_on_id_and_last_activity_on_for_non_internal_active", :algorithm=>:concurrently})
-> 0.0034s
== 20211027203921 RemoveIndexForDormantUsers: reverted (0.0122s) ==============
$
Index after rollback:
"index_users_on_id_and_last_activity_on_for_non_internal_active" btree (id, last_activity_on) WHERE state::text = 'active'::text AND (user_type IS NULL OR (user_type = ANY (ARRAY[NULL::integer, 6, 4])))
Index creation
Index creation took about 37 seconds in database lab: https://gitlab.slack.com/archives/CLJMDRD8C/p1635370971298600
Database Performance
SQL before change:
UPDATE "users"
SET "state" = 'deactivated'
WHERE "users"."id" IN (
(SELECT "users"."id" FROM "users" WHERE ("users"."state" IN ('active')) AND ("users"."user_type" IS NULL OR "users"."user_type" IN (6, 4)) AND (last_activity_on <= '2021-07-27') LIMIT 200)
UNION
(SELECT "users"."id" FROM "users" WHERE ("users"."state" IN ('active')) AND ("users"."user_type" IS NULL OR "users"."user_type" IN (6, 4)) AND "users"."last_activity_on" IS NULL LIMIT 200)
LIMIT 200
)
Query plan: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7036/commands/24892
In database lab: https://gitlab.slack.com/archives/CLJMDRD8C/p1635196319180100
Summary:
Time: 2.853 s
- planning: 7.126 ms
- execution: 2.845 s
- I/O read: 2.705 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 14628 (~114.30 MiB) from the buffer pool
- reads: 4278 (~33.40 MiB) from the OS file cache, including disk I/O
- dirtied: 2747 (~21.50 MiB)
- writes: 21 (~168.00 KiB)
SQL after change:
UPDATE "users"
SET "state" = 'deactivated'
WHERE "users"."id" IN (
(SELECT "users"."id" FROM "users" WHERE ("users"."state" IN ('active')) AND ("users"."user_type" IS NULL OR "users"."user_type" = 4) AND (last_activity_on <= '2021-07-29') LIMIT 200)
UNION
(SELECT "users"."id" FROM "users" WHERE ("users"."state" IN ('active')) AND ("users"."user_type" IS NULL OR "users"."user_type" = 4) AND "users"."last_activity_on" IS NULL LIMIT 200)
LIMIT 200
)
Query plan: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7081/commands/25047
Database lab link: https://gitlab.slack.com/archives/CLJMDRD8C/p1635371033299400
Summary:
Time: 1.157 s
- planning: 7.567 ms
- execution: 1.149 s
- I/O read: 1.063 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 14317 (~111.90 MiB) from the buffer pool
- reads: 4324 (~33.80 MiB) from the OS file cache, including disk I/O
- dirtied: 2834 (~22.10 MiB)
- writes: 23 (~184.00 KiB)
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.