Skip to content

Remove GitlabIssueTrackerService records

Andy Schoenen requested to merge 218526_backstage_remove_gitlab_issue_tracker_service_records into master

What does this MR do?

Related to #218526 (closed)

It is not possible to add custom settings by editing the service in the UI or API so there is no need of having database records. In general, it looks like GitlabIssueTrackerService is not used anymore.

This merge request removes all GitlabIssueTrackerService records from the database. In production, this will try to remove 2.93M records out of the total 3.74M records in the services table (~78%).

gitlabhq_production=> SELECT COUNT(*) FROM services WHERE type = 'GitlabIssueTrackerService';
  count
---------
 2931397

gitlabhq_production=> SELECT COUNT(*) FROM services;
  count
---------
 3740347

@iroussos calculated how long it will take to run the migration !35221 (comment 374847815). ~6.25 min

DELETE FROM services WHERE type = 'GitlabIssueTrackerService' AND id >= XXXX AND id < YYYY
SELECT id FROM services WHERE id > YYYY ORDER BY id ASC LIMIT 1 OFFSET 5000

T hose deletions take roughly ~350ms per deletion.

There are currently 3,742,438 service records, 2,930,288 of which are GitlabIssueTrackerService ones and will be deleted.

So, we are going to have 3,742,438 / 5,000 = 749 batches, which we expect to be done in ~375 seconds (6.25 min)

Migration output

== 20200623142159 RemoveGitlabIssueTrackerServiceRecords: migrating ===========
== 20200623142159 RemoveGitlabIssueTrackerServiceRecords: migrated (0.2609s) ==

I run this in databse-lab without batches. If I'm reading this right, there are 3271995 records to be deleted

explain DELETE FROM services WHERE type = 'GitlabIssueTrackerService';

Session: joe-brtf6g8350j25dhvg3d0

Plan with execution:

 ModifyTable on public.services  (cost=0.56..461851.46 rows=3271995 width=6) (actual time=134649.548..134649.548 rows=0 loops=1)
   Buffers: shared hit=8257638 read=123406 dirtied=101069
   I/O Timings: read=122067.380
   ->  Index Scan using index_services_on_project_id_and_type on public.services  (cost=0.56..461851.46 rows=3271995 width=6) (actual time=9.931..128188.015 rows=2931418 loops=1)
         Index Cond: ((services.type)::text = 'GitlabIssueTrackerService'::text)
         Buffers: shared hit=2294148 read=123399 dirtied=694
         I/O Timings: read=122061.943

Recommendations: ❗ Query processes too much data to return a relatively small number of rows. – Reduce data cardinality as early as possible during the execution, using one or several of the following techniques: new indexes, partitioning, query rewriting, denormalization. See the visualization of the plan to understand which plan nodes are the main bottlenecks. Show details

Summary:

Time: 6.030 min
  - planning: 0.111 ms
  - execution: 6.030 min
    - I/O read: 2.034 min
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 8257638 (~63.00 GiB) from the buffer pool
  - reads: 123406 (~964.10 MiB) from the OS file cache, including disk I/O
  - dirtied: 101069 (~789.60 MiB)
  - writes: 0

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • [-] Label as security and @ mention @gitlab-com/gl-security/appsec
  • [-] The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • [-] Security reports checked/validated by a reviewer from the AppSec team
Edited by Arturo Herrero

Merge request reports