GitLab / Database Office Hours

No description provided.

No description provided.

Agenda doc (internal): https://docs.google.com/document/d/1wgfmVL30F8SdMg-9yY6Y8djPSxWNvKmhR5XmsvYX1EI/edit#

We give a demo for accessing database thin clones using psql, talk about Database Lab and our plans to implement fully automated database (migration) testing

No description provided.

No description provided.

No description provided.

No description provided.

No description provided.

No description provided.

No description provided.

No description provided.

No description provided.

No description provided.

No description provided.

No description provided.

No description provided.

No description provided.

Agenda: https://docs.google.com/document/d/1wgfmVL30F8SdMg-9yY6Y8djPSxWNvKmhR5XmsvYX1EI/edit?usp=sharing (GitLab internal)

No description provided.

Database Office Hours - 2019-09-28

No description provided.

No description provided.

We optimize a query to retrieve TOP-100 projects for a user, bringing down query runtime from 7s to less than 20ms.

Context: https://gitlab.com/gitlab-org/gitlab-ce/issues/65854

The tricks applied are:
1. UNION push down instead of OR
2. ORDER BY, LIMIT push down into UNION branches
3. Partial index to efficiently retrieve TOP-K by ORDER BY criteria

0:00 Efficient counters, usage ping, approximate counting, batched counting
2:10 Migrations: Background, post-deploy, regular

In this video, I look at a rather complex query along with a proposed change. We figure out the difference this change makes and find ways to drastically improve the overall query performance.

We also touch on two rather typical query optimizations: Partial indexes and UNION over OR. This brings the query runtime down from 5,000ms to less than 100ms in our example.

Andreas Brandl - Sr. Database Reliability Engineer at GitLab: https://gitlab.com/abrandl

---

This is the MR we are looking at: https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/28887#note_185007453

We started to discuss about this complex query in https://www.youtube.com/watch?v=s70JhO1zt_8&t=2469s and we were wondering how to approach this from a reviewer perspective. I hope this recording gives an idea how even complex queries can be approached.

* 0:00': Process to become database reviewer/maintainer and discussion around it
* 8:50' : Proposal to extend ChatOps to allow engineers to access a read/write copy of the production database for code reviews for insights (without actually having access to the data itself) [1]
* 22:50': Monitor transaction length [2], disallow unrelated IO during database transaction, improve transaction length generally
* 28:27': Normalized schema for storing timezones [3]
* 38:00': Example of high IO related to storing a lot of text in a column [4]
* 41:09': Review large query in a MR - how do approach it? [5]

Agenda and notes (internal to GitLab only):
https://docs.google.com/document/d/1wgfmVL30F8SdMg-9yY6Y8djPSxWNvKmhR5XmsvYX1EI/edit#

[1] https://gitlab.com/gitlab-com/www-gitlab-com/merge_requests/24156
[2] https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/29928
[3] https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/28339/diffs#note_185204007
[4] https://gitlab.com/gitlab-com/gl-infra/production/issues/908
[5] https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/28887#note_185007453

Mostly around database reviews and process

First part revolves around database review process and documentation, particularly these issues:
https://gitlab.com/gitlab-org/gitlab-ce/issues/52210
https://gitlab.com/gitlab-com/www-gitlab-com/merge_requests/19980
https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/25867

Performing 3 MR reviews on the call:
* Two MRs with regular migrations from 29:10
* Query optimization with partial index from 38:57

Episode 2 of Optimising Rails Database Queries. This time featuring a better microphone.

In this video series we will take a look at optimising database queries in Rails applications. We'll be using GitLab as an example, but the techniques can be applied to other Rails applications as well.

The audio quality is unfortunately not the best, but I plan to sort this out before recording the second episode. Make sure to watch it in 1080p, otherwise the text will be too blurry.

The explain visualiser used in this episode can be found at https://explain.depesz.com/.