Analyze merge_request_diff_files data

Taken from: gitlab-com/database#110 (comment 97527141)

Let's analyze the data in merge_request_diff_files and compile statistics for:

1. size of records over time (on a daily basis)
2. size of individual MRs and distribution of this - are there any outstanding MRs we may be able to delete data from?
3. Number of distinct file data (useful for https://gitlab.com/gitlab-org/gitlab-ce/issues/37632 in context of deduplication).
4. What else?

I'm thinking we should stand up a database in gstg for this analysis or at least use a production replica for this (maybe one that is not running other transactions).

changed the description

@abrandl great, thanks for doing this! I can't think of anything else off the top of my head, but maybe @oswaldo has some ideas (count diffs where merge_request_diff_files.diff is greater than a certain size, maybe?).

(copied from database#110):

@smcgivern / @abrandl regarding this question:

It would also be interesting to see the distribution of space by the created_at of the corresponding row in merge_request_diffs - is most of the space used by recent MRs, or older MRs?

This is an interesting question! And actually I can imagine how helpful such analysis could be, not only for this table, in many other cases.

If the table would be time-partitioned, it would be an easy task. But we have a single table.

First early thoughts how this could be done – two possibilities:

1. we could use ctid, take page ids, and group by time frames.

Example of the raw data and ctid decomposition to take the page id:

gitlab_dbteam=# select ctid, (ctid::text::point)[0]::int8 as page, created_at, id from merge_request_diffs limit 10;
  ctid  | page |          created_at           |    id
--------+------+-------------------------------+----------
 (0,3)  |    0 | 2015-05-29 22:51:19.696428+00 |    94815
 (0,5)  |    0 | 2017-01-24 11:54:27.96344+00  |  2467157
 (0,6)  |    0 | 2018-02-14 02:59:26.103169+00 | 11165342
 (0,8)  |    0 | 2018-02-17 08:33:17.149463+00 | 11276529
 (0,9)  |    0 | 2017-11-01 15:44:25.379691+00 |  7949895
 (0,10) |    0 | 2017-11-01 15:35:28.702635+00 |  7949547
 (0,11) |    0 | 2017-11-01 15:35:57.888413+00 |  7949561
 (0,12) |    0 | 2017-11-01 15:46:09.264463+00 |  7950162
 (0,14) |    0 | 2017-11-01 15:52:28.33308+00  |  7950367
 (0,15) |    0 | 2017-11-01 15:47:28.479654+00 |  7950219
(10 rows)

2. operating on "logical level", we could just calculate length of each record and group by time frames. Example (non-grouped raw data):

gitlab_dbteam=# create index i_tmp_31524 on merge_request_diffs using brin(created_at);
CREATE INDEX
gitlab_dbteam=# select created_at, length((r)::text), id from merge_request_diffs r where created_at between '2018-07-01' and '2018-07-03'  order by 1 limit 10;
          created_at           | length |    id
-------------------------------+--------+----------
 2018-07-01 00:00:00.813666+00 |    221 | 18961546
 2018-07-01 00:00:19.455508+00 |    221 | 18961547
 2018-07-01 00:00:23.588169+00 |    221 | 18961548
 2018-07-01 00:00:42.36439+00  |    220 | 18961550
 2018-07-01 00:00:56.941735+00 |    221 | 18961551
 2018-07-01 00:01:06.579898+00 |    224 | 18961552
 2018-07-01 00:01:53.810075+00 |    220 | 18961553
 2018-07-01 00:02:04.725786+00 |    220 | 18961554
 2018-07-01 00:02:16.769219+00 |    221 | 18961555
 2018-07-01 00:02:17.34446+00  |    213 | 18961556
(10 rows)

Correction: we actually need to analyze size of merge_request_diff_files, using created_at from merge_request_diff.

assigned to @abrandl

I wonder if is that possible that we don't have the column latest_merge_request_diff_id updated for every merge request (including oldest).

That means we wasn't able to really fetch all merge_request_diffs that are not the latest, then delete its merge_request_diff_files.

cc @abrandl

added database~3617056 label

unassigned @abrandl

mentioned in epic gitlab-org&887 (closed)

mentioned in issue #8843 (closed)

Hi @abrandl,

This message is being generated because this issue meets the following criteria:

• This issue is open
• There has been no activity in the past two years (since 2020-01-07T11:55:31.443Z)
• This issue does not have one of the team labels applied team::{Reliability,Delivery,Scalability}
• This issue is unassigned
• This issue is not a corrective action

As part of efforts to improve our workflow, we are closing inactive issues without team labels and asking that new issues be opened directly in one of the following trackers:

• gl-infra/delivery - For anything related to Delivery.
• gl-infra/reliability - For anything related to Reliability.
• gl-infra/scalability - For anything related to Scaling or Scalability

If this issue is still relevant to you and/or your team, please re-open the issue in the proper issue tracker

For any questions on this please reach out to any Infrastructure Manager:

Thanks for your help!

---

You are welcome to help improve this comment.

closed