This issue is to organize an effort within the development department to address the recent issues related to pressure put on the database by some queries in CI. This issue will serve as the SSOT for this effort and will contain more details as they are defined. Moved from https://gitlab.com/groups/gitlab-com/-/epics/1374 which was originally created in the wrong group. ## The reason for this effort Our primary database is under substantial load and it's worth keeping this in mind as the reason we are running this rapid action. Latencies in the application are up on where they were a few months ago and we've had to reduce the Apdex SLO for GitLab.com from 99.9% to 99.8%. Ultimately, we can judge this rapid action to be a success if we're able to restore our 99.9% SLO. However, since there are many reasons that the application latency can degrade, this is not being used as an exit criteria. In line with this aim, we have selected three goals, each with a measurable and clear exit criteria as the scope of this rapid action. ## Workflow Board https://gitlab.com/groups/gitlab-org/-/boards/2471748?scope=all&utf8=%E2%9C%93&label_name[]=rapid%20action%3A%3Ashift%20db%20load%20from%20primary ## Participants Below is a table of Rapid Action participants and their roles | team member | role | | ------ | ------ | | @cheryl.li | DRI for this Rapid Action | | @andrewn | Infrastructure Stable Counterpart DRI - Act as DRI on scoping and priority questions. Review the team’s priorities and strategies to ensure they align with the most urgent needs for GitLab.com. | | @ayufan | Distinguished Engineer, Memory - leading technical initiatives and providing oversight and support | | @grzesiek | Staff Engineer, Verify - leading technical initiatives and providing oversight and support | | @msmiley | SRE - has also been collecting additional data and other insights. Available for DB expertise, analysis, etc. | | @reprazent, @smcgivern, @qmnguyen0711 | SRE Scalability - available to help with issues as needed | | @alipniagov, @mkaeppler, @nmilojevic1, @alexbuijs, @ssarka, @allison.browne | BE Engineers assigned to help with this RA | Plan ================================================ (Taken from https://gitlab.com/groups/gitlab-org/-/epics/5434#note_520204672) @andrewn proposed that before we set the exit criteria for this rapid action effort, we first put in some extra effort into understanding the problem. The Incomplete Information Problem ------------------------------------------- At present, we are not measuring the problem directly. Instead we know that there is a problem because the entire application, in aggregate, is slowing down. This is a secondary symptom, but relatively disconnected from the primary problem. We have collected lots of evidence which points to the cause being certain queries issued by the application against on our primary database, but we don't measure this directly right now. Because we aggregate our rails performance measurements over primary and secondary instances, and don't currently include Sidekiq in this measurement, the problems on the primary are masked by the relative good performance of the secondaries. This is because the are many replicas, but only one primary, the bad signal from the primary is diluted by the good signals from the secondaries. This is best illustrated by the apparent good performance of the `rails_sql` SLI (the current measurement) even though we know that the primary component this measurement is very bad: https://dashboards.gitlab.net/d/patroni-main/patroni-overview?viewPanel=2459326260&orgId=1 <br><small>The `rails_sql` SLI appears to be healthy, but the most important component of it, the primary SQL database queries, is performing badly.</small> Plan of Action ------------------------------------------- 1. **Stage 1: Instrumentation Phase** 1. Firstly, we add more instrumentation to the application 1. Differentiate primary and secondary requests in our instrumentation. At present that are aggregated together, making it difficult to understand problems on a single host (our of 9). 1. Ensure that we include Sidekiq in our database monitoring. At present, it is not included, but is a major source of primary database traffic. 1. Ensure that ActiveRecord queries from the API include route information, to assist with Marginalia sampling of the database. 1. Once these steps have been completed we can define new SLI apdex scores: `rails_sql_primary` and `rails_sql_secondary`, and start monitoring them against an SLO. 1. With the SLI/SLO in place, I expect that we'll be able to clearly see the problem in the apdex score. 1. **Stage 2: Define Exit Criteria** 1. Use the new SLI/SLO to restate the problem and define an improved target SLO as the exit criteria 1. If the current SLO is (say) 99.0% (that is _"1% of rails SQL queries are taking longer than they should"_), we can choose a target new SLO as the exit criteria for the rapid action, for example 99.9% (_"0.1% of rails SQL queries are taking longer than they should"_). 1. We will be in a much better place to define this goal once we know the current state. 1. Likewise, using the Web apdex as the goal for the rapid action risks including to many other variables within scope, for example, spam, redis slowdowns, etc. 1. **Stage 3**: Work towards the new SLO goal by tackling endpoints which are contributing the most to the apdex degradation. 1. We use [**Apdex Attribution**](https://gitlab.com/gitlab-com/runbooks/-/merge_requests/2963) to focus on the endpoints that are high-frequency **and** slow. 1. Apdex attribution excludes very slow endpoints which don't get called frequently and focuses on high traffic endpoints instead. These charts are automatically generated for each SLI in the metrics-catalog. [Example](https://dashboards.gitlab.net/d/web-main/web-overview?viewPanel=49&orgId=1). 1. **Stage 4: Resolution** 1. Follow the rapid action resolution process Or, as a flowchart: ```mermaid graph TD A[Start] -->|No direct instrumentation of the problem| B(Stage 1: Improve Observability and<br/>Direct Measurement of the Problem) B --> C(Stage 1: Define SLI and SLO for Primary Database Calls) C -->|Once we understand the current availability characteristics| D(Stage 2: Define Rapid Action Goal SLO for apdex, eg: 99.9%) D --> E{Stage 3:<br/>Target SLO<br/>attained?} E --> |no| G(Stage 3:<br/>Select endpoints contributing to the<br/>degradation using `Apdex Attribution`) E --> |yes| F(Stage 4: Retrospective) G --> H(Stage 3: Implement Improvements) H --> E F --> I(Stage 4: Tighten Monitoring SLOs to new target) I --> J[End] ``` # Exit Criteria (taken from https://gitlab.com/groups/gitlab-org/-/epics/5434#note_526563854) A new SLI is being setup to monitor latencies on our primary postgres instance separately from the replicas. This should allow us to track the progress globally. SLI MR in https://gitlab.com/gitlab-com/runbooks/-/merge_requests/3320. ## In scope --------------------------------------------------------------- ### [Goal 1: Reduce primary database load from read-only HTTP requests from 7.6% of requests to 4% of requests](https://gitlab.com/groups/gitlab-org/-/epics/5590) On [Monday 8 March 2021, about 7.59%](https://log.gprd.gitlab.net/goto/56bb061b4932ac49e30908fd27695f19) of read-only `GET` requests queried the primary database. In general `GET` http requests (which usually represent read-only traffic) should be serviced from read replicas. **Exit criteria: less than 4% of GET requests query the postgres primary** This metric can be measured by this query: https://log.gprd.gitlab.net/goto/387337b86f77bc275a5317835554dfa9 (Percentage can be calculated as taking the values from this report and `primary/(no_primary+primary)`) The key issue here is https://gitlab.com/gitlab-org/gitlab/-/issues/322133, "`ApplicationRecord#with_fast_statement_timeout` not running on primary". Hopefully this change is low-hanging fruit and once rolled out, many of the `MergeRequestController` GET requests will be routed to postgres replicas. Other endpoints can be prioritised using the following query: https://log.gprd.gitlab.net/goto/1e42b31ac51ae858f2668aca4ca85613 --------------------------------------------------------------- ### [Goal 2: Reduce the number of CI requests spending more than 50ms on primary db wall-time per request from 4.2% to 2%](https://gitlab.com/groups/gitlab-org/-/epics/5591) A very small proportion of requests are responsible for a large share of the time we spend in statements and transactions in the primary database. For CI Sidekiq jobs, 2% of jobs represent 74% of primary db query time. For the CI api, a similar 2% of jobs represent 36% of query time on the primary database. Many of these high latency queries are the result of n+1 sql queries, gaps in application limits, pathological edge cases, etc. If we prioritise this reducing these expensive queries, we can make a resolve the low-hanging fruit and potentially make a big difference to the amount of traffic these edge cases contribute to load. This is how this metric has performed (since Monday, when we obtained the instrumentation we needed to measure this), and keeping in mind that Tuesday involved a major outage. **Progress towards this goal is being tracked in this spreadsheet** https://docs.google.com/spreadsheets/d/1EGcN7B1J6PJq3F6ES0_j4AecWdXy6ouUpNL8qpf9grE/edit#gid=0 the spreadsheet includes ELK links reports for adding new data. Finding expensive rails endpoints to prioritise -- use this ELK query: https://log.gprd.gitlab.net/goto/18ed63d7946a98bc7e1724ad0d06536b Finding expensive sidekiq endpoints to prioritise -- use this ELK query: https://log.gprd.gitlab.net/goto/f27771a86b399a100e112e3df79f9947 --------------------------------------------------------------- ### [Goal 3: `BuildHooksWorker` queries read-replicas](https://gitlab.com/groups/gitlab-org/-/epics/5592) The broader goal is to allow Sidekiq to use read replicas, so that read-only jobs don't put pressure on the primary postgres instance. However, in the scope of the rapid action, we focus on getting just the first worker, on the assumption that others should be relatively easy to migration once the pattern has been established. https://gitlab.com/gitlab-org/gitlab/-/issues/322452 **Exit criteria** https://gitlab.com/gitlab-org/gitlab/-/merge_requests/55881 has been rolled out and any requisite infrastructure changes (eg: pgbouncer pools for Sidekiq to postgres read-replicas) are complete. --------------------------------------------------------------------- ## Out of Scope It's important to note that the both of these items are considered critical to the functioning of GitLab.com, and are no less urgent than the tasks that are within scope for this rapid action. However in the interests of avoiding scope creep and because they rely on different teams for their implementation, they have been left out of scope of this rapid action. ### Parallel Effort 1: Complete the effort to remove namespace `traversal_ids` Between 20% and 30%, but peaking at up to 70%, of our statement time is consumed by recursive namespace queries, split across various endpoints. These could be addressed by completing the https://gitlab.com/groups/gitlab-org/-/epics/4115 epic. Some analysis of the impact of these recursive queries is available in https://gitlab.com/gitlab-org/gitlab/-/issues/324009. Primary goal here is https://gitlab.com/gitlab-org/gitlab/-/issues/233298, current work is in https://gitlab.com/gitlab-org/gitlab/-/merge_requests/52854 @timzallmann will drive this forward. ### Parallel Effort 2: Reduce the impact of pipeline abuse Confidential, but more details in https://gitlab.com/groups/gitlab-org/-/epics/5583 A DRI needs to be selected for this effort.