Backfill namespace_id on issues table (!91921) · Merge requests · GitLab.org / GitLab

Alexandru Croitor requested to merge backfill_namespace_id_on_issues into master Jul 06, 2022

What does this MR do and why?

This MR handles backfilling issues.namespace_id = issues.project.project_namespace_id. This is a first part of the initiative to add work items to group level.

Before we can get work items to group level, we need to make sure current issues can work with project namespace first. So we we want to backfill issues.namespace_id and make sure we do not miss any instances where namespace_id might be missing.

Steps

set namespace_id on issue save, !91387 (merged)
Backfill namespace_id, this MR 👈
Finalize backfilling and introduce not null constraint on issues.namespace_id

This MR is to be merged a week or two after !91387 (merged) is deployed on prod and we can check that all newly created and updated issues set namespace_id correctly.

Screenshots or screen recordings

Batched jobs duration

local batched background migration jobs durations

Most jobs take under 2 minutes locally.

gitlabhq_dblab=# select row_number() over() as nr, id, started_at, finished_at, finished_at - started_at as duration, min_value, max_value, batch_size, sub_batch_size, status from batched_background_migration_jobs where batched_background_migration_id = 207;
 nr |   id   |          started_at           |          finished_at          |    duration     | min_value | max_value | batch_size | sub_batch_size | status
----+--------+-------------------------------+-------------------------------+-----------------+-----------+-----------+------------+----------------+--------
  1 | 111455 | 2022-07-14 11:33:59.314237+00 | 2022-07-14 11:35:01.03716+00  | 00:01:01.722923 |         4 |      1987 |       1000 |             10 |      3
  2 | 111456 | 2022-07-14 11:35:04.219174+00 | 2022-07-14 11:36:06.96534+00  | 00:01:02.746166 |      1988 |      3586 |       1000 |             10 |      3
  3 | 111457 | 2022-07-14 11:36:09.716324+00 | 2022-07-14 11:37:11.827472+00 | 00:01:02.111148 |      3587 |      4977 |       1000 |             10 |      3
  4 | 111458 | 2022-07-14 11:37:14.966077+00 | 2022-07-14 11:38:10.967374+00 | 00:00:56.001297 |      4978 |      6390 |       1000 |             10 |      3
  5 | 111459 | 2022-07-14 11:38:13.929449+00 | 2022-07-14 11:40:32.868075+00 | 00:02:18.938626 |      6391 |      7797 |       1000 |             10 |      3
  6 | 111460 | 2022-07-14 11:40:35.607336+00 | 2022-07-14 11:41:34.62479+00  | 00:00:59.017454 |      7798 |      9377 |       1000 |             10 |      3
  7 | 111461 | 2022-07-14 11:41:37.431425+00 | 2022-07-14 11:42:36.434744+00 | 00:00:59.003319 |      9379 |     10583 |       1000 |             10 |      3
  8 | 111462 | 2022-07-14 11:42:39.314034+00 | 2022-07-14 11:43:36.224108+00 | 00:00:56.910074 |     10584 |     11792 |       1000 |             10 |      3
  9 | 111463 | 2022-07-14 11:43:38.958961+00 | 2022-07-14 11:44:35.554576+00 | 00:00:56.595615 |     11793 |     13076 |       1000 |             10 |      3
 10 | 111464 | 2022-07-14 11:44:38.433336+00 | 2022-07-14 11:45:37.196379+00 | 00:00:58.763043 |     13077 |     14275 |       1000 |             10 |      3
 11 | 111465 | 2022-07-14 11:45:40.116927+00 | 2022-07-14 11:46:39.47072+00  | 00:00:59.353793 |     14276 |     15625 |       1000 |             10 |      3
 12 | 111466 | 2022-07-14 11:46:42.311588+00 | 2022-07-14 11:47:42.67725+00  | 00:01:00.365662 |     15626 |     16957 |       1000 |             10 |      3
 13 | 111467 | 2022-07-14 11:47:45.479687+00 | 2022-07-14 11:48:44.010696+00 | 00:00:58.531009 |     16958 |     18226 |       1000 |             10 |      3
 14 | 111468 | 2022-07-14 11:48:46.809218+00 | 2022-07-14 11:49:45.3795+00   | 00:00:58.570282 |     18227 |     19479 |       1000 |             10 |      3
 15 | 111469 | 2022-07-14 11:49:48.229669+00 | 2022-07-14 11:50:48.178005+00 | 00:00:59.948336 |     19481 |     20726 |       1000 |             10 |      3
 16 | 111470 | 2022-07-14 11:50:50.995077+00 | 2022-07-14 11:51:52.567886+00 | 00:01:01.572809 |     20727 |     21991 |       1000 |             10 |      3
 17 | 111471 | 2022-07-14 11:51:55.587581+00 | 2022-07-14 11:52:54.533628+00 | 00:00:58.946047 |     21992 |     23166 |       1000 |             10 |      3
 18 | 111472 | 2022-07-14 11:52:57.204056+00 | 2022-07-14 11:53:54.539679+00 | 00:00:57.335623 |     23167 |     25129 |       1000 |             10 |      3
 19 | 111473 | 2022-07-14 11:53:57.253135+00 | 2022-07-14 11:54:57.290407+00 | 00:01:00.037272 |     25130 |     26280 |       1000 |             10 |      3
 20 | 111474 | 2022-07-14 11:55:00.002358+00 | 2022-07-14 11:55:53.73568+00  | 00:00:53.733322 |     26283 |     27610 |       1000 |             10 |      3
 21 | 111475 | 2022-07-14 11:55:58.488705+00 | 2022-07-14 11:57:05.217726+00 | 00:01:06.729021 |     27611 |     29281 |       1200 |             10 |      3
 22 | 111476 | 2022-07-14 11:57:11.083733+00 | 2022-07-14 11:58:35.552816+00 | 00:01:24.469083 |     29282 |     31146 |       1440 |             10 |      3
 23 | 111477 | 2022-07-14 11:58:40.368502+00 | 2022-07-14 12:00:21.619068+00 | 00:01:41.250566 |     31147 |     33343 |       1728 |             10 |      3
 24 | 111478 | 2022-07-14 12:00:26.039415+00 | 2022-07-14 12:02:24.755435+00 | 00:01:58.71602  |     33344 |     35793 |       2073 |             10 |      3
 25 | 111479 | 2022-07-14 12:02:29.558795+00 | 2022-07-14 12:04:43.218006+00 | 00:02:13.659211 |     35794 |     38505 |       2431 |             10 |      3
 26 | 111480 | 2022-07-14 12:04:47.242994+00 | 2022-07-14 12:07:06.137202+00 | 00:02:18.894208 |     38506 |     41600 |       2431 |             10 |      3
 27 | 111481 | 2022-07-14 12:07:10.782517+00 | 2022-07-14 12:09:32.854383+00 | 00:02:22.071866 |     41601 |     44938 |       2264 |             10 |      3
 28 | 111482 | 2022-07-14 12:09:37.420685+00 | 2022-07-14 12:11:27.766245+00 | 00:01:50.34556  |     44939 |     46924 |       1973 |             10 |      3
 29 | 111483 | 2022-07-14 12:11:32.183084+00 | 2022-07-14 12:13:08.903907+00 | 00:01:36.720823 |     46925 |     51212 |       1837 |             10 |      3
 30 | 111484 | 2022-07-14 12:13:12.720524+00 | 2022-07-14 12:15:11.466369+00 | 00:01:58.745845 |     51213 |     53431 |       1837 |             10 |      3
 31 | 111485 | 2022-07-14 12:15:16.136025+00 | 2022-07-14 12:17:35.401755+00 | 00:02:19.26573  |     53432 |     55548 |       1817 |             10 |      3
 32 | 111486 | 2022-07-14 12:17:40.338808+00 | 2022-07-14 12:19:50.742306+00 | 00:02:10.403498 |     55549 |     57558 |       1659 |             10 |      3
 33 | 111487 | 2022-07-14 12:19:55.384482+00 | 2022-07-14 12:21:52.995+00    | 00:01:57.610518 |     57563 |     59323 |       1486 |             10 |      3
 34 | 111488 | 2022-07-14 12:21:57.745181+00 | 2022-07-14 12:25:06.812414+00 | 00:03:09.067233 |     59324 |     60709 |       1370 |             10 |      3
 35 | 111489 | 2022-07-14 12:25:11.449576+00 | 2022-07-14 12:27:07.792744+00 | 00:01:56.343168 |     60710 |     61811 |       1041 |             10 |      3
 36 | 111490 | 2022-07-14 12:27:12.439035+00 | 2022-07-14 12:29:08.421948+00 | 00:01:55.982913 |     61812 |     62966 |       1000 |             10 |      3
 37 | 111491 | 2022-07-14 12:29:12.883466+00 | 2022-07-14 12:32:00.768693+00 | 00:02:47.885227 |     62967 |     63968 |       1000 |             10 |      3
 38 | 111492 | 2022-07-14 12:32:05.381799+00 | 2022-07-14 12:34:20.330275+00 | 00:02:14.948476 |     63969 |     65092 |       1000 |             10 |      3
 39 | 111493 | 2022-07-14 12:34:25.597099+00 | 2022-07-14 12:35:51.630851+00 | 00:01:26.033752 |     65093 |     66411 |       1000 |             10 |      3
 40 | 111494 | 2022-07-14 12:35:56.120826+00 | 2022-07-14 12:37:29.719906+00 | 00:01:33.59908  |     66412 |     67658 |       1000 |             10 |      3
 41 | 111495 | 2022-07-14 12:37:33.715555+00 | 2022-07-14 12:39:01.1906+00   | 00:01:27.475045 |     67659 |     68866 |       1000 |             10 |      3
 42 | 111496 | 2022-07-14 12:39:06.087308+00 | 2022-07-14 12:40:33.401737+00 | 00:01:27.314429 |     68867 |     70257 |       1144 |             10 |      3
 43 | 111497 | 2022-07-14 12:40:38.24182+00  | 2022-07-14 12:42:31.320986+00 | 00:01:53.079166 |     70258 |     71922 |       1372 |             10 |      3
 44 | 111498 | 2022-07-14 12:42:35.974523+00 | 2022-07-14 12:44:37.128395+00 | 00:02:01.153872 |     71923 |     74242 |       1533 |             10 |      3
 45 | 111499 | 2022-07-14 12:44:42.160752+00 | 2022-07-14 12:46:42.7409+00   | 00:02:00.580148 |     74243 |     76085 |       1533 |             10 |      3
 46 | 111500 | 2022-07-14 12:46:47.056113+00 | 2022-07-14 12:49:23.8276+00   | 00:02:36.771487 |     76086 |     77831 |       1533 |             10 |      3
 47 | 111501 | 2022-07-14 12:49:28.331616+00 | 2022-07-14 12:51:00.918106+00 | 00:01:32.58649  |     77832 |     79432 |       1336 |             10 |      3
 48 | 111502 | 2022-07-14 12:51:05.507791+00 | 2022-07-14 12:53:33.715281+00 | 00:02:28.20749  |     79433 |     80935 |       1322 |             10 |      3
 49 | 111503 | 2022-07-14 12:53:38.074526+00 | 2022-07-14 12:55:20.571056+00 | 00:01:42.49653  |     80936 |     82634 |       1173 |             10 |      3
 50 | 111504 | 2022-07-14 12:55:25.045738+00 | 2022-07-14 12:57:08.836994+00 | 00:01:43.791256 |     82635 |     83941 |       1125 |             10 |      3
 51 | 111505 | 2022-07-14 12:57:13.569178+00 | 2022-07-14 12:58:32.56122+00  | 00:01:18.992042 |     83942 |     85297 |       1125 |             10 |      3
 52 | 111506 | 2022-07-14 12:58:37.333775+00 | 2022-07-14 13:00:29.701123+00 | 00:01:52.367348 |     85298 |     86891 |       1287 |             10 |      3
 53 | 111507 | 2022-07-14 13:00:34.5145+00   | 2022-07-14 13:02:57.224315+00 | 00:02:22.709815 |     86894 |     88599 |       1405 |             10 |      3
 54 | 111508 | 2022-07-14 13:03:01.845281+00 | 2022-07-14 13:05:12.92226+00  | 00:02:11.076979 |     88600 |     90198 |       1334 |             10 |      3
 55 | 111509 | 2022-07-14 13:05:17.583785+00 | 2022-07-14 13:07:19.629614+00 | 00:02:02.045829 |     90199 |     91652 |       1218 |             10 |      3
 56 | 111510 | 2022-07-14 13:07:24.001286+00 | 2022-07-14 13:09:06.744832+00 | 00:01:42.743546 |     91653 |     92948 |       1123 |             10 |      3
 57 | 111511 | 2022-07-14 13:09:11.744732+00 | 2022-07-14 13:10:57.51158+00  | 00:01:45.766848 |     92949 |     94306 |       1111 |             10 |      3
 58 | 111512 | 2022-07-14 13:11:01.204333+00 | 2022-07-14 13:12:38.286247+00 | 00:01:37.081914 |     94307 |     95665 |       1111 |             10 |      3
 59 | 111513 | 2022-07-14 13:12:42.782589+00 | 2022-07-14 13:14:22.887366+00 | 00:01:40.104777 |     95666 |     97146 |       1199 |             10 |      3
 60 | 111514 | 2022-07-14 13:14:27.449695+00 | 2022-07-14 13:16:31.656231+00 | 00:02:04.206536 |     97147 |     98765 |       1324 |             10 |      3
 61 | 111515 | 2022-07-14 13:16:35.431728+00 | 2022-07-14 13:18:08.945985+00 | 00:01:33.514257 |     98766 |    100000 |       1324 |             10 |      3
(61 rows)

metrics

The metrics data locally shows that most updates took between 200-300ms, with ~10% going over 300ms and ~0.5% over 0.5s

-- all updates count
select count(*) from (select id, unnest(concat('{', substring(metrics#>>'{timings, update_all}' from 2 for(length(metrics#>>'{timings, update_all}') - 2)), '}')::float[]) as s
from batched_background_migration_jobs where batched_background_migration_id = 207) metrics
 count
-------
  7808
(1 row)

-- updates over 300ms count
gitlabhq_dblab=# select count(*) from (select id, unnest(concat('{', substring(metrics#>>'{timings, update_all}' from 2 for(length(metrics#>>'{timings, update_all}') - 2)), '}')::float[]) as s
gitlabhq_dblab(# from batched_background_migration_jobs where batched_background_migration_id = 207) metrics where metrics.s > 0.3;
 count
-------
   762
(1 row)

-- updates over 500ms count
gitlabhq_dblab=# select count(*) from (select id, unnest(concat('{', substring(metrics#>>'{timings, update_all}' from 2 for(length(metrics#>>'{timings, update_all}') - 2)), '}')::float[]) as s                                                  from batched_background_migration_jobs where batched_background_migration_id = 207) metrics where metrics.s > 0.5;
 count
-------
    46
(1 row)

--max update duration
gitlabhq_dblab=# select max(metrics.s) from (select id, unnest(concat('{', substring(metrics#>>'{timings, update_all}' from 2 for(length(metrics#>>'{timings, update_all}') - 2)), '}')::float[]) as s                                            from batched_background_migration_jobs where batched_background_migration_id = 207) metrics;
        max
-------------------
 82.21609899998293
(1 row)

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

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.

Edited Jul 27, 2022 by Alexandru Croitor

Admin message

Backfill namespace_id on issues table